Cloud

unix_seconds

The unix_seconds() function returns a given timestamp to a UNIX timestamp in seconds, from 1970-01-01 00:00:00-00. Its syntax is:

SELECT UNIX_SECONDS(TIMESTAMP)

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

Examples

Basic unix_seconds() function

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

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS unix_secondsvalues;

The query returns:

+-----------------------------+
| unix_secondsvalues          |
+-----------------------------+
| 1230219000.000000           |
+-----------------------------+

unix_seconds() function using columns

Suppose a table named time_example has these timestamp values in the time_stampvalues column:

CREATE TABLE time_example (
  time_stampvalues timestamp
);

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

The query returns the table:

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

  1. To convert all timestamp values into UNIX timestamp values in seconds, run the query:

    SELECT time_stampvalues, UNIX_SECONDS(time_stampvalues)
AS time_secondsvalues
FROM time_example;

  2. The output displays all the timestamp entries of the table in the time_stampvalues column and the converted UNIX seconds timestamp entries in the column time_secondsvalues.

    +-------------------------+-----------------------+
| time_stampvalues        | time_secondsvalues    |
+-------------------------+-----------------------+
| 2022-12-25 13:30:00     | 1671975000.000000     |
| 2020-09-25 07:25:00     | 1601018700.000000     |
| 2008-12-25 15:30:00     | 1230219000.000000     |
| 2021-10-02 06:30:00     | 1633156200.000000     |
+-------------------------+-----------------------+