Skip to main content

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.

The cluster system administrator is responsible for keeping the OS time zone database (e.g., on , 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, such as 'America/Chicago'.

​
Syntax

SQL
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(timestamp,timezone)
ArgumentData TypeDescription
timestampTIMESTAMP or VARCHARTimestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format.
timezoneVARCHARVARCHAR 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
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 02:09:00','America/Chicago');
Output
Text
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
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,'America/Chicago') FROM test_timezone;
Output
Text
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
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,timezone_name) FROM test_timezone;
Output
Text
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
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago');
Output
Text
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
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-09 01:59:00', 'America/Chicago'),
    'America/Chicago');
Output
Text
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
SELECT HOUR(CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago'));
Output
Text
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
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
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
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
SELECT * FROM test_timezone
    WHERE ts=CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 14:09:00', 'America/Chicago');
Output
Text
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
SELECT * FROM test_timezone
    WHERE CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000';
Output
Text
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
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
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
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
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
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(timestamp,timezone)
ArgumentData TypeDescription
timestampTIMESTAMP or VARCHARTimestamp to convert that you specify as a TIMESTAMP or VARCHAR that you typecast in a timestamp format.
timezoneVARCHARVARCHAR 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
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00','America/Chicago');
Output
Text
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
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,'America/Chicago') FROM test_timezone;
Output
Text
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
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,timezone_name) FROM test_timezone;
Output
Text
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
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(TO_TIMESTAMP(1534587890), 'America/Chicago');
Output
Text
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
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
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
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
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
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
SELECT * FROM test_timezone
    WHERE ts=CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00', 'America/Chicago');
Output
Text
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
SELECT * FROM test_timezone
    WHERE CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000';
Output
Text
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
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
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
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
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
General SQL Syntax Date and Time Functions Conversion Functions CREATE TABLE INSERT INTO TABLE
Last modified on May 21, 2026