UNIX_SECONDS

9min
This article will assist you in using the UNIX_SECONDS() function to convert a given timestamp to a UNIX timestamp in seconds.
OverviewThe UNIX_SECONDS() function returns a given timestamp to a UNIX timestamp in seconds, from 1970-01-01 00:00:00-00. Its syntax is illustrated below:
Syntax
|
SELECT UNIX_SECONDS(TIMESTAMP)
SELECT UNIX_SECONDS(TIMESTAMP)
﻿
Its input type is a TIMESTAMP expression, and the return data type is int64 representing time in seconds.
Examples#Case 1: Basic UNIX_SECONDS() functionThe below example uses the UNIX_SECONDS() function to convert a given timestamp into a UNIX timestamp in seconds:
Unix seconds
|
SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS unix_secondsvalues;
SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS unix_secondsvalues;
﻿
The final output will be as follows:
Output
|
+-----------------------------+
| unix_secondsvalues          |
+-----------------------------+
| 1230219000.000000           |
+-----------------------------+
+-----------------------------+
| unix_secondsvalues          |
+-----------------------------+
| 1230219000.000000           |
+-----------------------------+
﻿
#Case 2: UNIX_SECONDS() function using columnsLet’s suppose we have a table named time_example with the following timestamp values in the time_stampvalues column:
Create a table
|
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');
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');
﻿
Display the table
|
SELECT * FROM time_example;
SELECT * FROM time_example;
﻿
The above query will return the following table:
Output
|
+-------------------------+
| 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     | 
+-------------------------+
+-------------------------+
| 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) We want to convert all timestamp values into UNIX timestamp values in seconds. To do that, we have to run the following query:
Select unix timestamp
|
SELECT time_stampvalues, UNIX_SECONDS(time_stampvalues)
AS time_secondsvalues
FROM time_example;
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.
Output
|
+-------------------------+-----------------------+
| 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     |
+-------------------------+-----------------------+
+-------------------------+-----------------------+
| 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     |
+-------------------------+-----------------------+
﻿
﻿
Updated 11 May 2023
Did this page help you?
Yes
FORMAT_TIMESTAMP
UNIX_MILLIS
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: Basic UNIX_SECONDS() function
#Case 2: UNIX_SECONDS() function using columns