TIMESTAMP_MICROS
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 |
+-------------------------+-----------------------+