Skip to main content

​
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(BIGINT)
Its input type is a BIGINT 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   |
+-------------------------+-----------------------+