A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST), with microsecond precision. For more information please refer to BigQuery Timestamp data type.
Grammar syntax
Name
Range
TIMESTAMP
0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTC
TIMESTAMP data type currently transformed to TIMESTAMP_TZ.
It is important to remark that BigQuery stores TIMESTAMP data in Coordinated Universal Time (UTC).
Sample Source Patterns
TIMESTAMP without time
BigQuery
CREATE OR REPLACETABLEtimestampTable( COL1 TIMESTAMP);INSERT INTO timestampTable VALUES ('2008-12-26 15:30:00');INSERT INTO timestampTable VALUES (TIMESTAMP'2008-12-27 18:30:00');SELECT*FROM timestampTable;
2008-12-26 15:30:00 UTC
2008-12-27 18:30:00 UTC
Snowflake
CREATE OR REPLACETABLEtimestampTable( COL1 TIMESTAMP_TZ);INSERT INTO timestampTable VALUES ('2008-12-26 15:30:00');INSERT INTO timestampTable VALUES (TIMESTAMP'2008-12-27 18:30:00');SELECT*FROM timestampTable;
When the time zone is defined you need to use the CONVERT_TIMEZONE function to store the data in Coordinated Universal Time (UTC). Also the timezone name inside the timestamp literal is not supported by Snowflake, in that case it is necessary to use this function as well.
BigQuery
CREATE OR REPLACETABLEtest.timestampType( COL1 TIMESTAMP);INSERT INTO test.timestampType VALUES ('2008-12-25 15:30:00 America/Chicago');INSERT INTO test.timestampType VALUES ('2018-04-05 12:00:00+02:00');INSERT INTO test.timestampType VALUES ('2008-12-26 15:30:00-08:00');INSERT INTO test.timestampType VALUES (TIMESTAMP'2022-12-25 15:30:00 America/North_Dakota/New_Salem');INSERT INTO test.timestampType VALUES (TIMESTAMP'2022-04-05 12:00:00+02:00');INSERT INTO test.timestampType VALUES (TIMESTAMP'2022-12-26 15:30:00-08:00');SELECT*FROM test.timestampType ORDER BY COL1;
2008-12-2521:30:00 UTC 2008-12-2623:30:00 UTC 2018-04-0510:00:00 UTC 2022-04-0510:00:00 UTC 2022-12-2521:30:00 UTC 2022-12-2623:30:00 UTC
Snowflake
CREATE OR REPLACETABLEtest.timestampType( COL1 TIMESTAMP_TZ);INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('America/Chicago', 'UTC', '2008-12-25 15:30:00'));INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('UTC','2018-04-05 12:00:00+02:00'));INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('UTC','2008-12-26 15:30:00-08:00'));INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('America/North_Dakota/New_Salem', 'UTC', '2022-12-25 15:30:00'));INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('UTC', '2022-04-05 12:00:00+02:00'));INSERT INTO test.timestampTypeVALUES (CONVERT_TIMEZONE('UTC', '2022-12-26 15:30:00-08:00'));SELECT*FROM test.timestampType ORDER BY COL1;