Skip to main content

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