Cloud

timestamp_seconds

The timestamp_seconds() function converts a given UNIX timestamp value in seconds from 1970-01-01 00:00:00 UTC into a timestamp. Its syntax is:

SELECT TIMESTAMP_SECONDS(int64)

Its input type is an int64 expression representing a UNIX timestamp in seconds, and the return data type is a timestamp.

Examples

Basic timestamp_seconds() function

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

SELECT TIMESTAMP_SECONDS(1671975000) AS timestamp_secondsvalue;

The query returns:

+-----------------------------+
| timestamp_secondsvalue      |
+-----------------------------+
| 2022-12-25 13:30:00         |
+-----------------------------+

timestamp_seconds() function using columns

Suppose a table named unix_time contains these UNIX time values in seconds:

CREATE TABLE unix_time (
  unix_time int64
);

INSERT INTO unix_time VALUES
('982384720'),
('1671975000'),
('171472000');
SELECT * FROM unix_time;

The query shows the table:

+-------------+
| unix_time   |
+-------------+
| 982384720   |
| 1671975000  |
| 171472000   |
+-------------+

To convert all UNIX timestamp values in seconds to timestamp values, run the query:

SELECT unix_time, TIMESTAMP_SECONDS(unix_time)
AS timestamp_value
FROM unix_time ;

The output displays all the entries in the table in UNIX timestamp format (in seconds) in the unix_time column, and in the timestamp format without timezone in the column timestamp_value.

+-------------------------+-----------------------+
| unix_time               | timestamp_value       |
+-------------------------+-----------------------+
| 982384720               | 2001-02-17 04:38:40   |
| 1671975000              | 2022-12-25 13:30:00   |
| 171472000               | 1975-06-08 15:06:40   |
+-------------------------+-----------------------+