Time Zone Functions
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.
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' .
Argument | Data Type | Description |
|---|---|---|
timestamp | TIMESTAMP or VARCHAR | Timestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format. |
timezone | VARCHAR | VARCHAR that contains the full name of the target time zone. |
Convert timestamp 2022-11-06 02:09:00 to the local time in Chicago.
Output
Convert timestamp in the ts column to the local time in Chicago using the test_timezone table.
Output
Convert timestamp in the ts column to the local time specified by the timezone_name column using the test_timezone table.
Output
Use the TO_TIMESTAMP function to convert a numeric timestamp to the local time in Chicago.
Output
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.
Output
Extract the hour portion of the local Chicago time using the output of the TO_TIMESTAMP function on a numeric timestamp.
Output
Create a table using a SELECT statement that converts a timestamp in local Chicago time.
Output
Insert a timestamp in local Chicago time into the test_timezone_2 table.
Use timestamp as a numeric constant value in local Chicago time as a filter on the column ts in the test_timezone table.
Output
Use timestamp as a runtime value from the table column ts in local Chicago time as a filter on the test_timezone table.
Output
Group the query results by a timestamp converted to local time in Chicago.
Output
Using a grouping, omit the 2022-11-10 02:09:00 timestamp using the HAVING clause.
Output
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' .
Argument | Data Type | Description |
|---|---|---|
timestamp | TIMESTAMP or VARCHAR | Timestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format. |
timezone | VARCHAR | VARCHAR that contains the full name of the target time zone. |
Convert timestamp 2022-11-06 02:09:00 to Chicago UTC time.
Output
Convert timestamp in the ts column to Chicago UTC time using the test_timezone table.
Output
Convert timestamp in the ts column to the UTC time specified by the timezone_name column using the test_timezone table.
Output
Use the TO_TIMESTAMP function to convert a numeric timestamp to the Chicago UTC time.
Output
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.
Output
Create a table using a SELECT statement that converts a timestamp in Chicago UTC time.
Output
Insert a timestamp in Chicago UTC time into the test_timezone table.
Use a UTC timestamp in Chicago as a numeric constant value to filter on the column ts in the test_timezone table.
Output
Use timestamp in Chicago UTC time as a runtime value from the table column ts as a filter on the test_timezone table.
Output
Group the query results by a timestamp converted to UTC time in Chicago.
Output
Using a grouping, omit the 2022-11-05 14:09:00 timestamp using the HAVING clause.
Output
CREATE TABLE