TIMESTAMP_MILLIS

This article will assist you in how to use the TIMESTAMP_MILLIS() function to convert a given UNIX timestamp in milliseconds to a timestamp.

Overview

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 can be seen below:

SELECT TIMESTAMP_MILLIS(Int64)

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

Examples

#Case 1: Basic TIMESTAMP_MILLIS() function

The below example uses 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 final output will be as follows:

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

#Case 2: TIMESTAMP_MILLIS() function using columns

Let’s suppose we have a table named unix_example with the following UNIX time values in milliseconds in the unix_timestamp column:

CREATE TABLE unix_example (
  unix_timestamp long
);

INSERT INTO unix_timestamp VALUES 
('171472000000'),
('1671975000000'),
('153276000000');

SELECT * FROM unix_example;

The above query will show the following table:

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

We want to convert all UNIX timestamp values in milliseconds to timestamp values. To do that, we have to run the following 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 column timestamp_value 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   |
+-------------------------+-----------------------+