TIMESTAMP_MICROS

9min
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:
Syntax
|
SELECT TIMESTAMP_MICROS(Int64)
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:
Timestamp micros
|
SELECT TIMESTAMP_MICROS(2280419000000000) AS timestamp_microsvalues;
SELECT TIMESTAMP_MICROS(2280419000000000) AS timestamp_microsvalues;
﻿
The final output will be as follows:
Output
|
+-----------------------------+
| timestamp_microsvalues      |
+-----------------------------+
| 2042-04-06 17:43:20         |
+-----------------------------+
+-----------------------------+
| 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 a table
|
CREATE TABLE timemirco_example (
  unix_timestamp long
);

INSERT INTO timemirco_example VALUES 
('1350417000000000'),
('2130215000000000'),
('1110115000000000'),
('2310112000000000');
CREATE TABLE timemirco_example (
  unix_timestamp long
);

INSERT INTO timemirco_example VALUES 
('1350417000000000'),
('2130215000000000'),
('1110115000000000'),
('2310112000000000');
﻿
Display the table
|
SELECT * FROM timemirco_example;
SELECT * FROM timemirco_example;
﻿
The above query will show the following table:
Output
|
+--------------------+
| unix_timestamp     | 
+--------------------+
| 1350417000000000   |
| 2130215000000000   |
| 1110115000000000   |
| 2310112000000000   |
+--------------------+
+--------------------+
| 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:
Unix timestamp
|
SELECT unix_timestamp, TIMESTAMP_MICROS(unix_timestamp)
AS timestamp_value
FROM timemicro_example;
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:
Output
|
+-------------------------+-----------------------+
| 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   |
+-------------------------+-----------------------+
+-------------------------+-----------------------+
| 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   |
+-------------------------+-----------------------+
﻿
﻿
Updated 31 Aug 2023
Did this page help you?
TABLE OF CONTENTS
Overview
Examples
#Case 1: Basic TIMESTAMP_MICROS() function
#Case 2: TIMESTAMP_MICROS() function using columns