Cloud

timestamp_millis

The timestamp_millis() function converts a given UNIX timestamp value in milliseconds since 1970-01-01 00:00:00 UTC into a timestamp. Its syntax is:

SELECT TIMESTAMP_MILLIS(BIGINT)

Its input type is a bigint expression which represents a UNIX timestamp in milliseconds and the return data type is a timestamp.

Examples

Basic timestamp_millis() function

This example shows how to use the timestamp_millis() function to convert a given UNIX timestamp in milliseconds into a timestamp without a timezone.

SELECT TIMESTAMP_MILLIS(1671975000000) AS timestamp_millisvalues;

The query returns:

+-----------------------------+
| timestamp_millisvalues      |
+-----------------------------+
| 2022-12-25 13:30:00         |
+-----------------------------+

timestamp_millis() function using columns

Suppose a table named unix_example has these UNIX time values in milliseconds in the unix_timestamp column:

CREATE TABLE unix_example (
  unix_timestamp long
);

INSERT INTO unix_example VALUES
('171472000000'),
('1671975000000'),
('153276000000');
SELECT * FROM unix_example;

This query shows the table:

+----------------+
| unix_timestamp |
+----------------+
| 171472000000   |
| 1671975000000  |
| 153276000000   |
+----------------+

To convert all UNIX timestamp values in milliseconds to timestamp values, run the query:

SELECT unix_timestamp, TIMESTAMP_MILLIS(unix_timestamp)
AS timestamp_value
FROM unix_example;

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

+-------------------------+-----------------------+
| unix_timestamp          | timestamp_value       |
+-------------------------+-----------------------+
|171472000000             | 1975-06-08 15:06:40   |
|1671975000000            | 2022-12-25 13:30:00   |
|153276000000             | 1974-11-10 00:40:00   |
+-------------------------+-----------------------+