Cloud

unix_micros

The unix_micros() function returns a given timestamp into a UNIX timestamp in microseconds, from 1970-01-01 00:00:00-00 (can be negative):

SELECT UNIX_MICROS(TIMESTAMP)

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

Examples

Basic unix_micros() function

This example shows how to use the unix_micros() function to convert a given timestamp into a UNIX timestamp in microseconds:

SELECT UNIX_MICROS(TIMESTAMP "2022-12-25 13:30:00+00") AS unix_microsvalues;

The query returns:

+-----------------------------+
| unix_microsvalues           |
+-----------------------------+
| 1671975000000000.000000     |
+-----------------------------+

unix_micros() function using columns

Suppose a table named time_example has these timestamp values:

CREATE TABLE time_example (
  time_stamp timestamp
);

INSERT INTO time_example VALUES
('2022-12-25 13:30:00'),
('2021-10-02 06:30:00'),
('2020-09-25 07:25:00');
SELECT * FROM time_example;

This query shows the table:

+-------------------------+
| time_example            |
+-------------------------+
| 2022-12-25 13:30:00     |
| 2021-10-02 06:30:00     |
| 2020-09-25 07:25:00     |
+-------------------------+

To convert all timestamp values into UNIX timestamp values in microseconds, run the query:

SELECT time_stamp, UNIX_MICROS(time_stamp)
AS time_micros
FROM time_example;

The output displays all the timestamp entries in the time_stamp column and the converted UNIX timestamps in microseconds in the column time_micros.

+-------------------------+--------------------------+
| time_stamp               | time_micros              |
+-------------------------+--------------------------+
| 2022-12-25 13:30:00     | 1671975000000000.000000  |
| 2021-10-02 06:30:00     | 1633156200000000.000000  |
| 2020-09-25 07:25:00     | 1601018700000000.000000  |
+-------------------------+--------------------------+