UNIX_MILLIS
Overview
The UNIX_MILLIS() function returns a given timestamp to a UNIX timestamp in milliseconds from 1970-01-01 00:00:00-00 (can be negative). Its syntax is illustrated below:
SELECT UNIX_MILLIS(TIMESTAMP)
Its input type is a TIMESTAMP expression, and the return data type is int64 representing time in milliseconds.
Examples
#Case 1: Basic UNIX_MILLIS() function
The below example uses the UNIX_MILLIS() function to convert a given timestamp into a UNIX timestamp in milliseconds:
SELECT UNIX_MILLIS(TIMESTAMP "1996-5-02 7:15:00+00") AS unix_millisvalues;
The final output will be as follows:
+-----------------------------+
| unix_millisvalues |
+-----------------------------+
| 831021300000.000000 |
+-----------------------------+
#Case 2: UNIX_MILLIS() function using columns
Let’s suppose we have a table named time_example with the following timestamp values in the time_stamp column:
CREATE TABLE time_example (
time_stamp timestamp
);
INSERT INTO time_example VALUES
('2004-07-23 11:30:00+00'),
('2011-02-12 04:45:00+00'),
('1975-08-03 07:50:00+00');
SELECT * FROM time_example;
The above query will show the following table:
+-------------------------+
| time_example |
+-------------------------+
| 2004-07-23 11:30:00 |
| 2011-02-12 04:45:00 |
| 1975-08-03 07:50:00 |
+-------------------------+
We want to convert all timestamp values into UNIX timestamp values in milliseconds. To do that, we have to run the following query:
SELECT time_stamp, UNIX_MILLIS(time_stamp) AS time_millis FROM time_example;
The output displays all the timestamp entries of the table in the **time_stamp **column and the converted UNIX milliseconds timestamp entries in the column time_millis.
+-------------------------+-----------------------+
| time_stamp | time_millis |
+-------------------------+-----------------------+
| 2004-07-23 11:30:00 | 1090582200000.000000 |
| 2011-02-12 04:45:00 | 1297485900000.000000 |
| 1975-08-03 07:50:00 | 176284200000.000000 |
+-------------------------+-----------------------+