Time Zone Functions

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, such as 'america/chicago' syntax select convert utc timestamp to local(timestamp,timezone) 112,154,396 false true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples 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'); output 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 select convert utc timestamp to local(ts,'america/chicago') from test timezone; output 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 select convert utc timestamp to local(ts,timezone name) from test timezone; output 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'); output 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'); output 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')); output 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 output 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, 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'); output 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 select from test timezone where convert utc timestamp to local(ts timestamp, 'america/chicago')='2022 11 06 08 09 00 000000000'; output 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'); output 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'; output 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 select convert local timestamp to utc(timestamp,timezone) 107,155,396 false true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples 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'); output 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 select convert local timestamp to utc(ts,'america/chicago') from test timezone; output 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 select convert local timestamp to utc(ts,timezone name) from test timezone; output 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'); output 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'); output 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; output 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'); output ts b \ 2022 11 06 08 09 00 000000000 1 use timestamp in chicago utc time, 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'; output 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'); output 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'; output 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 docid\ qcf0x9ao4a56x id39pkr date and time functions docid\ zcon ufstf4uhc5airgpg conversion functions docid\ uq rlzenk gtplkr2hyb5 database, tables, views, and indexes docid\ uacarixqhe493vlhudb5b database, tables, views, and indexes docid\ uacarixqhe493vlhudb5b