> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Time Zone Functions export const Ubuntu = "Ubuntu®"; export const Ocient = "Ocient®"; The {Ocient} 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 tracks the associated time zone with a `TIMESTAMP` column, whether 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,](https://www.iana.org/time-zones) such as `'America/Chicago'`. ### **Syntax** ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(timestamp,timezone) ``` | **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. | ### **Examples** #### Convert a Constant Time Zone Value Convert timestamp `2022-11-06 02:09:00` to the local time in Chicago. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 02:09:00','America/Chicago'); ``` *Output* ```text Text theme={null} 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 the timestamp in the `ts` column to the local time in Chicago using the `test_timezone` table. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,'America/Chicago') FROM test_timezone; ``` *Output* ```text Text theme={null} 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 the timestamp in the `ts` column to the local time specified by the `timezone_name` column using the `test_timezone` table. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,timezone_name) FROM test_timezone; ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-09 01:59:00', 'America/Chicago'), 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT HOUR(CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago')); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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 ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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, a numeric constant value in local Chicago time, as a filter on the column `ts` in the `test_timezone` table. ```sql SQL theme={null} SELECT * FROM test_timezone WHERE ts=CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 14:09:00', 'America/Chicago'); ``` *Output* ```text Text theme={null} ts                                          b           -------------------------------------------------------- 2022-11-06 08:09:00.000000000               1 ``` Use timestamp, a runtime value from the table column `ts` in local Chicago time, as a filter on the `test_timezone` table. ```sql SQL theme={null} SELECT * FROM test_timezone WHERE CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000'; ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago') FROM test_timezone GROUP BY CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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'; ``` *Output* ```text Text theme={null} 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'`. ### **Syntax** ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(timestamp,timezone) ``` | **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. | ### **Examples** #### Convert a Constant Time Zone Value Convert timestamp `2022-11-06 02:09:00` to Chicago UTC time. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00','America/Chicago'); ``` *Output* ```text Text theme={null} 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 the timestamp in the `ts` column to Chicago UTC time using the `test_timezone` table. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,'America/Chicago') FROM test_timezone; ``` *Output* ```text Text theme={null} 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 the timestamp in the `ts` column to the UTC time specified by the `timezone_name` column using the `test_timezone` table. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,timezone_name) FROM test_timezone; ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(TO_TIMESTAMP(1534587890), 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(TO_TIMESTAMP(CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 01:59:00', 'America/Chicago'), 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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; ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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. ```sql SQL theme={null} SELECT * FROM test_timezone WHERE ts=CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00', 'America/Chicago'); ``` *Output* ```text Text theme={null} ts                                          b           -------------------------------------------------------- 2022-11-06 08:09:00.000000000              1 ``` Use a timestamp in Chicago UTC time, a runtime value from the table column `ts`, as a filter on the `test_timezone` table. ```sql SQL theme={null} SELECT * FROM test_timezone WHERE CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000'; ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago') FROM test_timezone GROUP BY CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago'); ``` *Output* ```text Text theme={null} 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. ```sql SQL theme={null} 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'; ``` *Output* ```text Text theme={null} 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 ``` ### Related Links [General SQL Syntax](/general-sql-syntax) [Date and Time Functions](/date-and-time-functions) [Conversion Functions](/conversion-functions) [CREATE TABLE](/tables#create-table) [INSERT INTO TABLE](/tables#insert-into-table)