The cluster system administrator is responsible for keeping the OS time zone database (e.g., on Ubuntu ® , tzdata ) up to date. Outdated time zone database files might cause unexpected results due to IANA rule updates around name changes, DST observance, and UTC offsets for time zones.
The TIMESTAMP data type has no associated time zone value. The database administrator is responsible for tracking the associated time zone with a TIMESTAMP column, whether that is through an associated time_zone VARCHAR column or other documentation. Ocient recommends storing all TIMESTAMP data types in the UTC time zone to avoid ambiguity and using these functions to convert to arbitrary time zones. You can still store TIMESTAMP data types associated with an arbitrary time zone and use these functions to convert to UTC and from UTC to another time zone.
CONVERT_UTC_TIMESTAMP_TO_LOCAL The function converts a timestamp from the UTC time zone to a specified local time zone.
This function can take the timezone argument as a VARCHAR literal or the argument can be a column. When the argument is a literal, you can arbitrarily capitalize it. When the argument is a column, the values in the column must be capitalized according to the canonical IANA time zone names such as 'America/Chicago' .
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( timestamp , timezone)
Timestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format.
VARCHAR that contains the full name of the target time zone.
Convert a Constant Time Zone Value Convert timestamp 2022-11-06 02:09:00 to the local time in Chicago.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(timestamp(('2022-11-06 02:09:00')), ('America/Chicago'))
------------------------------------------------------------------------------------
2022-11-05 21:09:00.000000000
Convert a Timestamp Value in a Column Convert timestamp in the ts column to the local time in Chicago using the test_timezone table.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts, 'America/Chicago' ) FROM test_timezone;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts, ('America/Chicago'))
-----------------------------------------------------
2023-01-09 17:23:12.173997444
Convert a Timestamp Value and Time Zone Name in Columns Convert timestamp in the ts column to the local time specified by the timezone_name column using the test_timezone table.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts, timezone_name) FROM test_timezone;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts, timezone_name)
-----------------------------------------------------
2023-01-09 17:43:21.193229991
Convert a Timestamp Value Using Other Date and Time Functions Use the TO_TIMESTAMP function to convert a numeric timestamp to the local time in Chicago.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( TO_TIMESTAMP( 1534587890 ) , 'America/Chicago' ) ;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(to_timestamp((1534587890)), ('America/Chicago'))
-----------------------------------------------
2018-08-18 05:24:50.000000000
Use a UTC timestamp from the output of the CONVERT_LOCAL_TIMESTAMP_TO_UTC function with Chicago time and convert it back to local time in Chicago.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( CONVERT_LOCAL_TIMESTAMP_TO_UTC( Timestamp '2022-11-09 01:59:00' , 'America/Chicago' ) ,
'America/Chicago' ) ;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(CONVERT_LOCAL_TIMESTAMP_TO_UTC(timestamp(('2022-11-09 01:59:00')), ('America/Chicago')), ('America/Chicago'))
------------------------------------------------------------------------------------
2022-11-09 01:59:00.000000000
Extract the hour portion of the local Chicago time using the output of the TO_TIMESTAMP function on a numeric timestamp.
SELECT HOUR ( CONVERT_UTC_TIMESTAMP_TO_LOCAL( TO_TIMESTAMP( 1534587890 ) , 'America/Chicago' ) ) ;
hour(CONVERT_UTC_TIMESTAMP_TO_LOCAL(to_timestamp((1534587890)), ('America/Chicago')))
----------------------------------------------------------------------------------
5
Convert a Timestamp During Table Creation Create a table using a SELECT statement that converts a timestamp in local Chicago time.
CREATE TABLE test_timezone_2 ( ts timestamp , b int )
AS ( SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ,
1 FROM sys. dummy1) ;
Modified 1 row
SELECT * FROM test_timezone_2; 1
ts_1 b_1
------------------------------------------------------------------------------------
2022-11-05 21:09:00.000000000
Convert a Timestamp During an Insert Operation Insert a timestamp in local Chicago time into the test_timezone_2 table.
INSERT INTO test_timezone_2 SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ,
1 FROM sys. dummy1;
Modified 1 row
Convert a Timestamp in a Query Filter Use timestamp as a numeric constant value in local Chicago time as a filter on the column ts in the test_timezone table.
SELECT * FROM test_timezone
WHERE ts= CONVERT_UTC_TIMESTAMP_TO_LOCAL( Timestamp '2022-11-06 14:09:00' , 'America/Chicago' ) ;
ts b
--------------------------------------------------------
2022-11-06 08:09:00.000000000 1
Use timestamp as a runtime value from the table column ts in local Chicago time as a filter on the test_timezone table.
SELECT * FROM test_timezone
WHERE CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' ) = '2022-11-06 08:09:00.000000000' ;
ts b
--------------------------------------------------------
2022-11-06 08:09:00.000000000 1
Convert a Timestamp in the Grouping Part of the Query Group the query results by a timestamp converted to local time in Chicago.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' )
FROM test_timezone GROUP BY CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' ) ;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts, ('America/Chicago'))
-----------------------------------------------------
2022-11-05 04:09:00.000000000
2022-11-09 02:09:00.000000000
2022-11-04 04:09:00.000000000
2022-11-06 02:09:00.000000000
Using a grouping, omit the 2022-11-10 02:09:00 timestamp using the HAVING clause.
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' )
FROM test_timezone GROUP BY CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' )
HAVING CONVERT_UTC_TIMESTAMP_TO_LOCAL( ts::timestamp , 'America/Chicago' ) != '2022-11-10 02:09:00' ;
CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts, ('America/Chicago'))
-----------------------------------------------------
2022-11-06 02:09:00.000000000
2022-11-04 04:09:00.000000000
2022-11-05 04:09:00.000000000
CONVERT_LOCAL_TIMESTAMP_TO_UTC The function converts a timestamp in a specified local time zone to the UTC time zone. In the case of ambiguous timestamps, the database returns the earliest of the two possible times in the UTC time zone. Ambiguous timestamps refer to the times when the DST time zone change happens and 1 hour can transform to either 1 or +1 and 1 or -1. Ambiguous timestamps can also refer to non-existent times such as 2023-03-12 02:01:00 'America/New_York' which are similarly converted to the earliest possible UTC time, 2023-03-12 07:00:00 'UTC' , due to the skipping of hour 02:00:00 entirely with DST spring forward.
This function can take the timezone argument as a VARCHAR literal or the argument can be a column. When the argument is a literal, you can arbitrarily capitalize it. When the argument is a column, the values in the column must be capitalized according to the canonical IANA time zone names such as 'America/Chicago' .
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( timestamp , timezone)
Timestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format.
VARCHAR that contains the full name of the target time zone.
Convert a Constant Time Zone Value Convert timestamp 2022-11-06 02:09:00 to Chicago UTC time.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(timestamp(('2022-11-06 02:09:00')), ('America/Chicago'))
------------------------------------------------------------------------------------
2022-11-06 08:09:00.000000000
Convert a Timestamp Value in a Column Convert timestamp in the ts column to Chicago UTC time using the test_timezone table.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts, 'America/Chicago' ) FROM test_timezone;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts, ('America/Chicago'))
-----------------------------------------------------
2023-01-09 17:23:12.173997444
Convert a Timestamp Value and Time Zone Name in Columns Convert timestamp in the ts column to the UTC time specified by the timezone_name column using the test_timezone table.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts, timezone_name) FROM test_timezone;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts, timezone_name)
-----------------------------------------------
2023-01-09 17:43:21.193229991
Convert a Timestamp Value Using Other Date and Time Functions Use the TO_TIMESTAMP function to convert a numeric timestamp to the Chicago UTC time.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( TO_TIMESTAMP( 1534587890 ) , 'America/Chicago' ) ;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(to_timestamp((1534587890)), ('America/Chicago'))
-----------------------------------------------
2018-08-18 15:24:50.000000000
Use a local timestamp from the output of the CONVERT_UTC_TIMESTAMP_TO_LOCAL function with Chicago time and convert it back to UTC time.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( TO_TIMESTAMP( CONVERT_UTC_TIMESTAMP_TO_LOCAL( Timestamp '2022-11-06 01:59:00' , 'America/Chicago' ) ,
'America/Chicago' ) ;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(CONVERT_UTC_TIMESTAMP_TO_LOCAL(timestamp(('2022-11-06 01:59:00')), ('America/Chicago')), ('America/Chicago'))
------------------------------------------------------------------------------------
2022-11-06 01:59:00.000000000
Convert a Timestamp During Table Creation Create a table using a SELECT statement that converts a timestamp in Chicago UTC time.
CREATE TABLE test_timezone ( ts timestamp , b int )
AS ( SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( Timestamp '2022-11-09 02:09:00' , 'America/Chicago' ) ,
1 FROM sys. dummy1) ;
Modified 1 row
SELECT * FROM test_timezone;
ts b
---------------------------------------------------
2022-11-09 08:09:00.000000000 1
Convert a Timestamp During an Insert Operation Insert a timestamp in Chicago UTC time into the test_timezone table.
INSERT INTO test_timezone SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ,
1 from sys. dummy1;
Modified 1 row
Convert a Timestamp in a Query Filter Use a UTC timestamp in Chicago as a numeric constant value to filter on the column ts in the test_timezone table.
SELECT * FROM test_timezone
WHERE ts= CONVERT_LOCAL_TIMESTAMP_TO_UTC( Timestamp '2022-11-06 02:09:00' , 'America/Chicago' ) ;
ts b
--------------------------------------------------------
2022-11-06 08:09:00.000000000 1
Use timestamp in Chicago UTC time as a runtime value from the table column ts as a filter on the test_timezone table.
SELECT * FROM test_timezone
WHERE CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' ) = '2022-11-06 08:09:00.000000000' ;
ts b
--------------------------------------------------------
2022-11-06 08:09:00.000000000 1
Convert a Timestamp in the Grouping Part of the Query Group the query results by a timestamp converted to UTC time in Chicago.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' )
FROM test_timezone GROUP BY CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' ) ;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts, ('America/Chicago'))
-----------------------------------------------------
2022-11-09 14:09:00.000000000
2022-11-06 14:09:00.000000000
2022-11-04 14:09:00.000000000
2022-11-05 14:09:00.000000000
Using a grouping, omit the 2022-11-05 14:09:00 timestamp using the HAVING clause.
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' )
FROM test_timezone GROUP BY CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' )
HAVING CONVERT_LOCAL_TIMESTAMP_TO_UTC( ts::timestamp , 'America/Chicago' ) != '2022-11-05 14:09:00' ;
CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts, ('America/Chicago'))
-----------------------------------------------------
2022-11-09 14:09:00.000000000
2022-11-06 14:09:00.000000000
2022-11-04 14:09:00.000000000