Cloud

unix_millis

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:

SELECT UNIX_MILLIS(TIMESTAMP)

Its input type is a timestamp expression, and the return data type is bigint representing time in milliseconds.

Examples

Basic unix_millis() function

This example shows how to use 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 query returns:

+-----------------------------+
| unix_millisvalues           |
+-----------------------------+
| 831021300000.000000         |
+-----------------------------+

unix_millis() function using columns

Suppose a table named time_example has these 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;

This query shows the table:

+-------------------------+
| time_example            |
+-------------------------+
| 2004-07-23 11:30:00     |
| 2011-02-12 04:45:00     |
| 1975-08-03 07:50:00     |
+-------------------------+

To convert all timestamp values into UNIX timestamp values in milliseconds, run the 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   |
+-------------------------+-----------------------+