Materialize/test/sqllogictest/timezone.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# Test (lack of) timezone support.

statement ok
SET TIME ZONE UTC

statement ok
SET TIME ZONE 'UTC'

statement ok
SET TIME ZONE 'uTc'

statement ok
SET TimeZone = 'uTc'

statement error invalid value for parameter "TimeZone": "bad"
SET TIME ZONE bad

query T
SHOW TIMEZONE
----
UTC

query T
SHOW TIME ZONE
----
UTC

statement ok
SET TimeZone = '+00:00'

statement ok
SET TIMEZONE to '+00:00'

query T
SHOW TIMEZONE
----
+00:00

query T
SHOW TIME ZONE
----
+00:00

query T
SELECT TIMESTAMP '2020-12-21 18:53:49' AT TIME ZONE 'America/New_York'
----
2020-12-21 23:53:49+00

query T
SELECT TIMESTAMPTZ '2020-12-21 18:53:49 America/New_York'
----
2020-12-21 23:53:49+00

query T
SELECT timezone('America/New_York', TIMESTAMP '2020-12-21 18:53:49')
----
2020-12-21 23:53:49+00

query T
SELECT TIMESTAMP '2020-12-21 18:53:49' AT TIME ZONE 'PST'
----
2020-12-22 02:53:49+00

query T
SELECT TIMESTAMPTZ '2020-12-21 18:53:49 PST'
----
2020-12-22 02:53:49+00

query T
SELECT timezone('PST', TIMESTAMP '2020-12-21 18:53:49')
----
2020-12-22 02:53:49+00

query T
SELECT TIMESTAMP '2020-12-21 18:53:49' AT TIME ZONE 'America/New_York'
----
2020-12-21 23:53:49+00

query T
SELECT TIMESTAMPTZ '2020-12-21 18:53:49 America/New_York'
----
2020-12-21 23:53:49+00

query T
SELECT timezone('America/New_York', TIMESTAMP '2020-12-21 18:53:49')
----
2020-12-21 23:53:49+00

query T
SELECT timezone(INTERVAL '+11'HOUR, TIME '18:53:49')::time
----
05:53:49

query T
SELECT TIMESTAMPTZ '2020-12-21 18:53:49 Pacific/Auckland' AT TIME ZONE 'Turkey'
----
2020-12-21 08:53:49

query T
SELECT TIMESTAMPTZ '2007-02-01 00:00:00+5:30:16';
----
2007-01-31 18:29:44+00

statement error timezone interval must not contain months or years
SELECT timezone(INTERVAL '+11'MONTH, TIME '18:53:49')

statement error invalid input syntax for type timestamp with time zone: Invalid timezone string \(\+16:60\): timezone hour invalid 16
SELECT TIMESTAMPTZ '2020-01-01 00:00:00+16:60'

query T
SELECT TIMESTAMPTZ '2020-03-08 02:59:59 America/New_York'
----
2020-03-08 07:59:59+00

query T
SELECT TIMESTAMPTZ '2020-03-08 03:00:00 America/New_York'
----
2020-03-08 07:00:00+00

query T
SELECT TIMESTAMPTZ '2020-11-01 00:59:59 America/New_York'
----
2020-11-01 04:59:59+00

query T
SELECT TIMESTAMPTZ '2020-11-01 01:00:00 America/New_York'
----
2020-11-01 06:00:00+00

# Regression for 20324
query error timestamp out of range
SELECT pg_catalog.timezone(-INTERVAL '1' MINUTE, TIMESTAMP '95143-12-31 23:59:59' + INTERVAL '167 MILLENNIUM')

# Regression for database-issues#6170
query error timestamp out of range
SELECT pg_catalog.timezone('JAPAN', TIMESTAMPTZ '95143-12-31 23:59:59+06' + INTERVAL '167 MILLENNIUM')

# Test that POSIX is used for timezone() and AT TIME ZONE.

query T
SELECT timezone('+5', '0001-01-01 12:00:00 +6'::TIMESTAMPTZ);
----
0001-01-01 01:00:00

query T
SELECT '0001-01-01 12:00:00 +6'::TIMESTAMPTZ AT TIME ZONE '+5';
----
0001-01-01 01:00:00

query T
SELECT timezone('-5', '0001-01-01 12:00:00 -6'::TIMESTAMPTZ);
----
0001-01-01 23:00:00

query T
SELECT '0001-01-01 12:00:00 -6'::TIMESTAMPTZ AT TIME ZONE '-5';
----
0001-01-01 23:00:00

query T
SELECT timezone_offset('America/New_York', '2023-01-01T00:00:00+00')
----
(EST,-05:00:00,00:00:00)

query T
SELECT timezone_offset('America/Los_Angeles', '2023-01-01T00:00:00+00')
----
(PST,-08:00:00,00:00:00)

query T
SELECT timezone_offset('america/new_york', '2023-01-01T00:00:00+00')
----
(EST,-05:00:00,00:00:00)

query T
SELECT timezone_offset('America/New_York', '2023-01-01T00:00:00+00').abbrev
----
EST

query T
SELECT timezone_offset('America/New_York', '2023-01-01T00:00:00+00').base_utc_offset
----
-05:00:00

query T
SELECT timezone_offset('America/New_York', '2023-01-01T00:00:00+00').dst_offset
----
00:00:00

query T
SELECT timezone_offset('America/New_York', '2023-06-01T00:00:00+00').base_utc_offset
----
-05:00:00

query T
SELECT timezone_offset('America/New_York', '2023-06-01T00:00:00+00').dst_offset
----
01:00:00

query T
SELECT timezone_offset('America/New_York', '2023-11-05T05:00:00+00')
----
(EDT,-05:00:00,01:00:00)

query T
SELECT timezone_offset('America/New_York', '2023-11-05T05:30:00+00')
----
(EDT,-05:00:00,01:00:00)

query T
SELECT timezone_offset('America/New_York', '2023-11-05T06:00:00+00')
----
(EST,-05:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05T06:00:00 UTC')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05T06:00:00 CET')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05T06:00:00 CEST')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-06-05T06:00:00 CET')
----
(CEST,01:00:00,01:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-06-05T06:00:00 CEST')
----
(CEST,01:00:00,01:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05T06:00:00.123+00')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05T06:00:00')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset('Europe/Vienna', '2023-11-05')
----
(CET,01:00:00,00:00:00)

query T
SELECT timezone_offset(NULL, '2023-11-05T06:00:00+00')
----
NULL

query T
SELECT timezone_offset('Europe/Vienna', NULL)
----
NULL

query error invalid IANA Time Zone Database identifier: ''
SELECT timezone_offset('', '2023-11-05T06:00:00+00')

query error invalid IANA Time Zone Database identifier: 'America/New_Yorks'
SELECT timezone_offset('America/New_Yorks', now())

query error invalid IANA Time Zone Database identifier: 'PST'
SELECT timezone_offset('PST', now())

query error invalid IANA Time Zone Database identifier: '-05'
SELECT timezone_offset('-05', now())