TIMESTAMP_MICROS

This article will assist you in using the TIMESTAMP_MICROS() function to convert a given UNIX timestamp in microseconds to a timestamp.

Overview

The TIMESTAMP_MICROS() function converts a given UNIX timestamp value in microseconds since 1970-01-01 00:00:00 UTC into a timestamp. Its syntax can be seen below:

SELECT TIMESTAMP_MICROS(Int64)

Its input type is an int64 expression representing a UNIX timestamp in microseconds, and the return data type is a timestamp.

Examples

#Case 1: Basic TIMESTAMP_MICROS() function

The below example uses the TIMESTAMP_MICROS() function to convert a given UNIX timestamp in microseconds into a timestamp without a timezone:

SELECT TIMESTAMP_MICROS(2280419000000000) AS timestamp_microsvalues;

The final output will be as follows:

+-----------------------------+
| timestamp_microsvalues      |
+-----------------------------+
| 2042-04-06 17:43:20         |
+-----------------------------+

#Case 2: TIMESTAMP_MICROS() function using columns

Let’s suppose we have a table named timemirco_example with the following UNIX time values in microseconds in the unix_timestamp column:

CREATE TABLE timemirco_example (
  unix_timestamp long
);

INSERT INTO timemirco_example VALUES 
('1350417000000000'),
('2130215000000000'),
('1110115000000000'),
('2310112000000000');

SELECT * FROM timemirco_example;

The above query will show the following table:

+--------------------+
| unix_timestamp     | 
+--------------------+
| 1350417000000000   |
| 2130215000000000   |
| 1110115000000000   |
| 2310112000000000   |
+--------------------+

We want to convert all UNIX timestamp values in microseconds to timestamp values. To do that, we have to run the following query:

SELECT unix_timestamp, TIMESTAMP_MICROS(unix_timestamp)
AS timestamp_value
FROM timemicro_example;

The output displays all the entries in the table in UNIX timestamp format (in microseconds) in the unix_timestamp column and in the timestamp format in the column timestamp_value without timezone:

+-------------------------+-----------------------+
| unix_timestamp          | timestamp_value       |
+-------------------------+-----------------------+
|1350417000000000         | 2012-10-16 19:50:00   |
|2130215000000000         | 2037-07-03 06:23:20   |
|1110115000000000         | 2005-03-06 13:16:40   |
|2310112000000000         | 2043-03-16 09:46:40   |
+-------------------------+-----------------------+