Cloud

timestamp_micros

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 is:

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

Basic timestamp_micros() function

This example shows how to use 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 query returns:

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

timestamp_micros() function using columns

Suppose a table named timemicro_example has these UNIX time values in microseconds in the unix_timestamp column:

CREATE TABLE timemicro_example (
  unix_timestamp long
);

INSERT INTO timemicro_example VALUES
(1350417000000000),
(2130215000000000),
(1110115000000000),
(2310112000000000);
SELECT * FROM timemicro_example;

This query shows the table:

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

To convert all UNIX timestamp values in microseconds to timestamp values, run the 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   |
+-------------------------+-----------------------+