1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
|
# name: test/sql/timezone/test_icu_timezone.test
# description: Test the ICU TimeZone interface
# group: [timezone]
require icu
statement ok
SET Calendar = 'gregorian';
statement error
SET TimeZone = 'Europe/Duck';
----
Unknown TimeZone
statement ok
SET TimeZone = 'America/Los_Angeles';
query IIIII
SELECT name, value, description, input_type, scope FROM duckdb_settings() WHERE name = 'TimeZone';
----
TimeZone America/Los_Angeles The current time zone VARCHAR LOCAL
#
# AT TIME ZONE
#
# Single row
query I
SELECT '2001-02-16 20:38:40'::TIMESTAMP AT TIME ZONE 'America/Denver';
----
2001-02-16 19:38:40-08
# case insensitive
query I
SELECT '2001-02-16 20:38:40'::TIMESTAMP AT TIME ZONE 'america/denver';
----
2001-02-16 19:38:40-08
# recommendation
statement error
SELECT '2001-02-16 20:38:40'::TIMESTAMP AT TIME ZONE 'America/Donver';
----
America/Denver
query I
SELECT '2001-02-16 20:38:40-05'::TIMESTAMPTZ AT TIME ZONE 'America/Denver';
----
2001-02-16 18:38:40
# Invalid time zone
statement error
SELECT '2001-02-16 20:38:40'::TIMESTAMP AT TIME ZONE 'Europe/Duck';
----
Unknown TimeZone
statement error
SELECT '2001-02-16 20:38:40-05'::TIMESTAMPTZ AT TIME ZONE 'Europe/Duck';
----
Unknown TimeZone
# Infinities
foreach datatype TIMESTAMP TIMESTAMPTZ
query I
SELECT 'infinity'::${datatype} AT TIME ZONE 'America/Denver';
----
infinity
query I
SELECT '-infinity'::${datatype} AT TIME ZONE 'America/Denver';
----
-infinity
statement error
SELECT 'infinity'::${datatype} AT TIME ZONE 'Europe/Duck';
----
Unknown TimeZone
statement error
SELECT '-infinity'::${datatype} AT TIME ZONE 'Europe/Duck';
----
Unknown TimeZone
endloop
# NULLs
query I
SELECT NULL::TIMESTAMP AT TIME ZONE 'America/Denver';
----
NULL
query I
SELECT NULL::TIMESTAMPTZ AT TIME ZONE 'America/Denver';
----
NULL
query I
SELECT '2001-02-16 20:38:40'::TIMESTAMP AT TIME ZONE NULL;
----
NULL
query I
SELECT '2001-02-16 20:38:40-05'::TIMESTAMPTZ AT TIME ZONE NULL;
----
NULL
# Normalise to TZ, add interval, then set offset
query I
select '12:15:37.123456-08'::TIMETZ AT TIME ZONE 'America/Phoenix';
----
13:15:37.123456-07
query I
select timezone('America/Phoenix', '12:15:37.123456-08'::TIMETZ);
----
13:15:37.123456-07
# Date => TIMESTAMPTZ
query I
SELECT '2020-01-01'::DATE::TIMESTAMPTZ AS dttz;
----
2020-01-01 00:00:00-08
query I
SELECT 'infinity'::DATE::TIMESTAMPTZ AS dttz;
----
infinity
query I
SELECT '-infinity'::DATE::TIMESTAMPTZ AS dttz;
----
-infinity
statement error
select '5877642-06-25 (BC)'::DATE::TIMESTAMPTZ AS dttz;
----
Invalid Input Error
statement error
select '5881580-07-10'::DATE::TIMESTAMPTZ AS dttz;
----
Invalid Input Error
# Table
statement ok
CREATE TABLE attimezone (ts TIMESTAMP, tstz TIMESTAMPTZ, ttz TIMETZ, tz VARCHAR);
statement ok
INSERT INTO attimezone (VALUES
('2001-02-16 20:38:40', '2001-02-16 19:38:40-08', '19:38:40-08', 'America/Phoenix'),
('2001-02-16 18:38:40', '2001-02-16 20:38:40-05', '20:38:40-05', 'America/Phoenix'),
('infinity', 'infinity', '19:38:40-08', 'America/Phoenix'),
('-infinity', '-infinity', '20:38:40-05', 'America/Phoenix'),
(NULL, NULL, NULL, 'America/Phoenix'),
('2001-02-16 20:38:40', '2001-02-16 19:38:40-08', '19:38:40+08', 'UTC'),
('2001-02-16 18:38:40', '2001-02-16 20:38:40-05', '20:38:40+15:59', 'UTC'),
('infinity', 'infinity', '12:15:37.123456-08', 'UTC'),
('-infinity', '-infinity', '20:38:40-15:59', 'UTC'),
(NULL, NULL, NULL, 'UTC'),
('2001-02-16 20:38:40', '2001-02-16 19:38:40-08', '19:38:40-08', NULL),
('2001-02-16 18:38:40', '2001-02-16 20:38:40-05', '20:38:40-05', NULL),
('infinity', 'infinity', '12:15:37.123456-08', NULL),
('-infinity', '-infinity', '20:38:40-15:59', NULL),
);
# By Row
query III
SELECT ts AT TIME ZONE tz, tstz AT TIME ZONE tz, ttz AT TIME ZONE tz
FROM attimezone
----
2001-02-16 19:38:40-08 2001-02-16 20:38:40 20:38:40-07
2001-02-16 17:38:40-08 2001-02-16 18:38:40 18:38:40-07
infinity infinity 20:38:40-07
-infinity -infinity 18:38:40-07
NULL NULL NULL
2001-02-16 12:38:40-08 2001-02-17 03:38:40 11:38:40+00
2001-02-16 10:38:40-08 2001-02-17 01:38:40 04:39:40+00
infinity infinity 20:15:37.123456+00
-infinity -infinity 12:37:40+00
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
# Filtered
query III
SELECT ts AT TIME ZONE tz, tstz AT TIME ZONE tz, ttz AT TIME ZONE tz
FROM attimezone
WHERE ts > '2001-02-16 18:38:40'::TIMESTAMP
----
2001-02-16 19:38:40-08 2001-02-16 20:38:40 20:38:40-07
infinity infinity 20:38:40-07
2001-02-16 12:38:40-08 2001-02-17 03:38:40 11:38:40+00
infinity infinity 20:15:37.123456+00
NULL NULL NULL
NULL NULL NULL
#
# Verify UTC±NNNN translation
# Note sign inversion (see https://en.wikipedia.org/wiki/Tz_database#Area)
#
foreach utc UTC-0800 UTC-08 UTC-8 UTC-08:00
statement ok
set TimeZone = '${utc}';
query I
select value from duckdb_settings() where name = 'TimeZone';
----
Etc/GMT+8
endloop
foreach utc UTC+0500 UTC+05 UTC+5 UTC+05:00
statement ok
set TimeZone = '${utc}';
query I
select value from duckdb_settings() where name = 'TimeZone';
----
Etc/GMT-5
endloop
foreach utc UTC+1400 UTC+14 UTC+14:00
statement ok
set TimeZone = '${utc}';
query I
select value from duckdb_settings() where name = 'TimeZone';
----
Etc/GMT-14
endloop
foreach utc UTC-0 UTC-00 UTC-000 UTC-0000 UTC+0 UTC+00 UTC+000 UTC+0000
statement ok
set TimeZone = '${utc}';
query I
select value from duckdb_settings() where name = 'TimeZone';
----
Etc/GMT+0
endloop
#
# Verify local time functions are implemented.
#
statement ok
select localtimestamp;
statement ok
select localtime;
statement ok
select current_localtimestamp();
statement ok
select current_localtime();
#
# ICU time zone list
#
query II
SELECT name, abbrev FROM pg_timezone_names() ORDER BY name;
----
ACT ACT
AET AET
AGT AGT
ART ART
AST AST
Africa/Abidjan Iceland
Africa/Accra Iceland
Africa/Addis_Ababa EAT
Africa/Algiers Africa/Algiers
Africa/Asmara EAT
Africa/Asmera EAT
Africa/Bamako Iceland
Africa/Bangui Africa/Bangui
Africa/Banjul Iceland
Africa/Bissau Africa/Bissau
Africa/Blantyre CAT
Africa/Brazzaville Africa/Brazzaville
Africa/Bujumbura CAT
Africa/Cairo ART
Africa/Casablanca Africa/Casablanca
Africa/Ceuta Africa/Ceuta
Africa/Conakry Iceland
Africa/Dakar Iceland
Africa/Dar_es_Salaam EAT
Africa/Djibouti EAT
Africa/Douala Africa/Douala
Africa/El_Aaiun Africa/El_Aaiun
Africa/Freetown Iceland
Africa/Gaborone CAT
Africa/Harare CAT
Africa/Johannesburg Africa/Johannesburg
Africa/Juba Africa/Juba
Africa/Kampala EAT
Africa/Khartoum Africa/Khartoum
Africa/Kigali CAT
Africa/Kinshasa Africa/Kinshasa
Africa/Lagos Africa/Lagos
Africa/Libreville Africa/Libreville
Africa/Lome Iceland
Africa/Luanda Africa/Luanda
Africa/Lubumbashi CAT
Africa/Lusaka CAT
Africa/Malabo Africa/Malabo
Africa/Maputo CAT
Africa/Maseru Africa/Maseru
Africa/Mbabane Africa/Mbabane
Africa/Mogadishu EAT
Africa/Monrovia Africa/Monrovia
Africa/Nairobi EAT
Africa/Ndjamena Africa/Ndjamena
Africa/Niamey Africa/Niamey
Africa/Nouakchott Iceland
Africa/Ouagadougou Iceland
Africa/Porto-Novo Africa/Porto-Novo
Africa/Sao_Tome Africa/Sao_Tome
Africa/Timbuktu Iceland
Africa/Tripoli Libya
Africa/Tunis Africa/Tunis
Africa/Windhoek Africa/Windhoek
America/Adak America/Adak
America/Anchorage AST
America/Anguilla PRT
America/Antigua PRT
America/Araguaina America/Araguaina
America/Argentina/Buenos_Aires AGT
America/Argentina/Catamarca America/Argentina/Catamarca
America/Argentina/ComodRivadavia America/Argentina/ComodRivadavia
America/Argentina/Cordoba America/Argentina/Cordoba
America/Argentina/Jujuy America/Argentina/Jujuy
America/Argentina/La_Rioja America/Argentina/La_Rioja
America/Argentina/Mendoza America/Argentina/Mendoza
America/Argentina/Rio_Gallegos America/Argentina/Rio_Gallegos
America/Argentina/Salta America/Argentina/Salta
America/Argentina/San_Juan America/Argentina/San_Juan
America/Argentina/San_Luis America/Argentina/San_Luis
America/Argentina/Tucuman America/Argentina/Tucuman
America/Argentina/Ushuaia America/Argentina/Ushuaia
America/Aruba PRT
America/Asuncion America/Asuncion
America/Atikokan EST
America/Atka America/Atka
America/Bahia America/Bahia
America/Bahia_Banderas America/Bahia_Banderas
America/Barbados America/Barbados
America/Belem America/Belem
America/Belize America/Belize
America/Blanc-Sablon PRT
America/Boa_Vista America/Boa_Vista
America/Bogota America/Bogota
America/Boise America/Boise
America/Buenos_Aires AGT
America/Cambridge_Bay America/Cambridge_Bay
America/Campo_Grande America/Campo_Grande
America/Cancun America/Cancun
America/Caracas America/Caracas
America/Catamarca America/Catamarca
America/Cayenne America/Cayenne
America/Cayman EST
America/Chicago CST
America/Chihuahua America/Chihuahua
America/Ciudad_Juarez America/Ciudad_Juarez
America/Coral_Harbour EST
America/Cordoba America/Cordoba
America/Costa_Rica America/Costa_Rica
America/Coyhaique America/Coyhaique
America/Creston MST
America/Cuiaba America/Cuiaba
America/Curacao PRT
America/Danmarkshavn America/Danmarkshavn
America/Dawson America/Dawson
America/Dawson_Creek America/Dawson_Creek
America/Denver Navajo
America/Detroit America/Detroit
America/Dominica PRT
America/Edmonton America/Edmonton
America/Eirunepe America/Eirunepe
America/El_Salvador America/El_Salvador
America/Ensenada America/Ensenada
America/Fort_Nelson America/Fort_Nelson
America/Fort_Wayne IET
America/Fortaleza America/Fortaleza
America/Glace_Bay America/Glace_Bay
America/Godthab America/Godthab
America/Goose_Bay America/Goose_Bay
America/Grand_Turk America/Grand_Turk
America/Grenada PRT
America/Guadeloupe PRT
America/Guatemala America/Guatemala
America/Guayaquil America/Guayaquil
America/Guyana America/Guyana
America/Halifax America/Halifax
America/Havana Cuba
America/Hermosillo America/Hermosillo
America/Indiana/Indianapolis IET
America/Indiana/Knox America/Indiana/Knox
America/Indiana/Marengo America/Indiana/Marengo
America/Indiana/Petersburg America/Indiana/Petersburg
America/Indiana/Tell_City America/Indiana/Tell_City
America/Indiana/Vevay America/Indiana/Vevay
America/Indiana/Vincennes America/Indiana/Vincennes
America/Indiana/Winamac America/Indiana/Winamac
America/Indianapolis IET
America/Inuvik America/Inuvik
America/Iqaluit America/Iqaluit
America/Jamaica Jamaica
America/Jujuy America/Jujuy
America/Juneau America/Juneau
America/Kentucky/Louisville America/Kentucky/Louisville
America/Kentucky/Monticello America/Kentucky/Monticello
America/Knox_IN America/Knox_IN
America/Kralendijk PRT
America/La_Paz America/La_Paz
America/Lima America/Lima
America/Los_Angeles PST
America/Louisville America/Louisville
America/Lower_Princes PRT
America/Maceio America/Maceio
America/Managua America/Managua
America/Manaus America/Manaus
America/Marigot PRT
America/Martinique America/Martinique
America/Matamoros America/Matamoros
America/Mazatlan America/Mazatlan
America/Mendoza America/Mendoza
America/Menominee America/Menominee
America/Merida America/Merida
America/Metlakatla America/Metlakatla
America/Mexico_City America/Mexico_City
America/Miquelon America/Miquelon
America/Moncton America/Moncton
America/Monterrey America/Monterrey
America/Montevideo America/Montevideo
America/Montreal America/Montreal
America/Montserrat PRT
America/Nassau America/Nassau
America/New_York EST5EDT
America/Nipigon America/Nipigon
America/Nome America/Nome
America/Noronha America/Noronha
America/North_Dakota/Beulah America/North_Dakota/Beulah
America/North_Dakota/Center America/North_Dakota/Center
America/North_Dakota/New_Salem America/North_Dakota/New_Salem
America/Nuuk America/Nuuk
America/Ojinaga America/Ojinaga
America/Panama EST
America/Pangnirtung America/Pangnirtung
America/Paramaribo America/Paramaribo
America/Phoenix MST
America/Port-au-Prince America/Port-au-Prince
America/Port_of_Spain PRT
America/Porto_Acre America/Porto_Acre
America/Porto_Velho America/Porto_Velho
America/Puerto_Rico PRT
America/Punta_Arenas America/Punta_Arenas
America/Rainy_River America/Rainy_River
America/Rankin_Inlet America/Rankin_Inlet
America/Recife America/Recife
America/Regina America/Regina
America/Resolute America/Resolute
America/Rio_Branco America/Rio_Branco
America/Rosario America/Rosario
America/Santa_Isabel America/Santa_Isabel
America/Santarem America/Santarem
America/Santiago America/Santiago
America/Santo_Domingo America/Santo_Domingo
America/Sao_Paulo BET
America/Scoresbysund America/Scoresbysund
America/Shiprock Navajo
America/Sitka America/Sitka
America/St_Barthelemy PRT
America/St_Johns CNT
America/St_Kitts PRT
America/St_Lucia PRT
America/St_Thomas PRT
America/St_Vincent PRT
America/Swift_Current America/Swift_Current
America/Tegucigalpa America/Tegucigalpa
America/Thule America/Thule
America/Thunder_Bay America/Thunder_Bay
America/Tijuana America/Tijuana
America/Toronto America/Toronto
America/Tortola PRT
America/Vancouver America/Vancouver
America/Virgin PRT
America/Whitehorse America/Whitehorse
America/Winnipeg America/Winnipeg
America/Yakutat America/Yakutat
America/Yellowknife America/Yellowknife
Antarctica/Casey Antarctica/Casey
Antarctica/Davis Antarctica/Davis
Antarctica/DumontDUrville Antarctica/DumontDUrville
Antarctica/Macquarie Antarctica/Macquarie
Antarctica/Mawson Antarctica/Mawson
Antarctica/McMurdo NZ
Antarctica/Palmer Antarctica/Palmer
Antarctica/Rothera Antarctica/Rothera
Antarctica/South_Pole NZ
Antarctica/Syowa Antarctica/Syowa
Antarctica/Troll Antarctica/Troll
Antarctica/Vostok Antarctica/Vostok
Arctic/Longyearbyen Arctic/Longyearbyen
Asia/Aden Asia/Aden
Asia/Almaty Asia/Almaty
Asia/Amman Asia/Amman
Asia/Anadyr Asia/Anadyr
Asia/Aqtau Asia/Aqtau
Asia/Aqtobe Asia/Aqtobe
Asia/Ashgabat Asia/Ashgabat
Asia/Ashkhabad Asia/Ashkhabad
Asia/Atyrau Asia/Atyrau
Asia/Baghdad Asia/Baghdad
Asia/Bahrain Asia/Bahrain
Asia/Baku Asia/Baku
Asia/Bangkok Asia/Bangkok
Asia/Barnaul Asia/Barnaul
Asia/Beirut Asia/Beirut
Asia/Bishkek Asia/Bishkek
Asia/Brunei Asia/Brunei
Asia/Calcutta IST
Asia/Chita Asia/Chita
Asia/Choibalsan Asia/Choibalsan
Asia/Chongqing CTT
Asia/Chungking CTT
Asia/Colombo Asia/Colombo
Asia/Dacca BST
Asia/Damascus Asia/Damascus
Asia/Dhaka BST
Asia/Dili Asia/Dili
Asia/Dubai Asia/Dubai
Asia/Dushanbe Asia/Dushanbe
Asia/Famagusta Asia/Famagusta
Asia/Gaza Asia/Gaza
Asia/Harbin CTT
Asia/Hebron Asia/Hebron
Asia/Ho_Chi_Minh VST
Asia/Hong_Kong Hongkong
Asia/Hovd Asia/Hovd
Asia/Irkutsk Asia/Irkutsk
Asia/Istanbul Turkey
Asia/Jakarta Asia/Jakarta
Asia/Jayapura Asia/Jayapura
Asia/Jerusalem Israel
Asia/Kabul Asia/Kabul
Asia/Kamchatka Asia/Kamchatka
Asia/Karachi PLT
Asia/Kashgar Asia/Kashgar
Asia/Kathmandu Asia/Kathmandu
Asia/Katmandu Asia/Katmandu
Asia/Khandyga Asia/Khandyga
Asia/Kolkata IST
Asia/Krasnoyarsk Asia/Krasnoyarsk
Asia/Kuala_Lumpur Singapore
Asia/Kuching Asia/Kuching
Asia/Kuwait Asia/Kuwait
Asia/Macao Asia/Macao
Asia/Macau Asia/Macau
Asia/Magadan Asia/Magadan
Asia/Makassar Asia/Makassar
Asia/Manila Asia/Manila
Asia/Muscat Asia/Muscat
Asia/Nicosia Asia/Nicosia
Asia/Novokuznetsk Asia/Novokuznetsk
Asia/Novosibirsk Asia/Novosibirsk
Asia/Omsk Asia/Omsk
Asia/Oral Asia/Oral
Asia/Phnom_Penh Asia/Phnom_Penh
Asia/Pontianak Asia/Pontianak
Asia/Pyongyang Asia/Pyongyang
Asia/Qatar Asia/Qatar
Asia/Qostanay Asia/Qostanay
Asia/Qyzylorda Asia/Qyzylorda
Asia/Rangoon Asia/Rangoon
Asia/Riyadh Asia/Riyadh
Asia/Saigon VST
Asia/Sakhalin Asia/Sakhalin
Asia/Samarkand Asia/Samarkand
Asia/Seoul ROK
Asia/Shanghai CTT
Asia/Singapore Singapore
Asia/Srednekolymsk Asia/Srednekolymsk
Asia/Taipei ROC
Asia/Tashkent Asia/Tashkent
Asia/Tbilisi Asia/Tbilisi
Asia/Tehran Iran
Asia/Tel_Aviv Israel
Asia/Thimbu Asia/Thimbu
Asia/Thimphu Asia/Thimphu
Asia/Tokyo JST
Asia/Tomsk Asia/Tomsk
Asia/Ujung_Pandang Asia/Ujung_Pandang
Asia/Ulaanbaatar Asia/Ulaanbaatar
Asia/Ulan_Bator Asia/Ulan_Bator
Asia/Urumqi Asia/Urumqi
Asia/Ust-Nera Asia/Ust-Nera
Asia/Vientiane Asia/Vientiane
Asia/Vladivostok Asia/Vladivostok
Asia/Yakutsk Asia/Yakutsk
Asia/Yangon Asia/Yangon
Asia/Yekaterinburg Asia/Yekaterinburg
Asia/Yerevan NET
Atlantic/Azores Atlantic/Azores
Atlantic/Bermuda Atlantic/Bermuda
Atlantic/Canary Atlantic/Canary
Atlantic/Cape_Verde Atlantic/Cape_Verde
Atlantic/Faeroe Atlantic/Faeroe
Atlantic/Faroe Atlantic/Faroe
Atlantic/Jan_Mayen Atlantic/Jan_Mayen
Atlantic/Madeira Atlantic/Madeira
Atlantic/Reykjavik Iceland
Atlantic/South_Georgia Atlantic/South_Georgia
Atlantic/St_Helena Iceland
Atlantic/Stanley Atlantic/Stanley
Australia/ACT AET
Australia/Adelaide Australia/Adelaide
Australia/Brisbane Australia/Brisbane
Australia/Broken_Hill Australia/Broken_Hill
Australia/Canberra AET
Australia/Currie Australia/Currie
Australia/Darwin ACT
Australia/Eucla Australia/Eucla
Australia/Hobart Australia/Hobart
Australia/LHI Australia/LHI
Australia/Lindeman Australia/Lindeman
Australia/Lord_Howe Australia/Lord_Howe
Australia/Melbourne Australia/Melbourne
Australia/NSW AET
Australia/North ACT
Australia/Perth Australia/Perth
Australia/Queensland Australia/Queensland
Australia/South Australia/South
Australia/Sydney AET
Australia/Tasmania Australia/Tasmania
Australia/Victoria Australia/Victoria
Australia/West Australia/West
Australia/Yancowinna Australia/Yancowinna
BET BET
BST BST
Brazil/Acre Brazil/Acre
Brazil/DeNoronha Brazil/DeNoronha
Brazil/East BET
Brazil/West Brazil/West
CAT CAT
CET CET
CNT CNT
CST CST
CST6CDT CST
CTT CTT
Canada/Atlantic Canada/Atlantic
Canada/Central Canada/Central
Canada/East-Saskatchewan Canada/East-Saskatchewan
Canada/Eastern Canada/Eastern
Canada/Mountain Canada/Mountain
Canada/Newfoundland CNT
Canada/Pacific Canada/Pacific
Canada/Saskatchewan Canada/Saskatchewan
Canada/Yukon Canada/Yukon
Chile/Continental Chile/Continental
Chile/EasterIsland Chile/EasterIsland
Cuba Cuba
EAT EAT
ECT ECT
EET EET
EST EST
EST5EDT EST5EDT
Egypt ART
Eire Eire
Etc/GMT GMT
Etc/GMT+0 GMT
Etc/GMT+1 Etc/GMT+1
Etc/GMT+10 Etc/GMT+10
Etc/GMT+11 Etc/GMT+11
Etc/GMT+12 Etc/GMT+12
Etc/GMT+2 Etc/GMT+2
Etc/GMT+3 Etc/GMT+3
Etc/GMT+4 Etc/GMT+4
Etc/GMT+5 Etc/GMT+5
Etc/GMT+6 Etc/GMT+6
Etc/GMT+7 Etc/GMT+7
Etc/GMT+8 Etc/GMT+8
Etc/GMT+9 Etc/GMT+9
Etc/GMT-0 GMT
Etc/GMT-1 Etc/GMT-1
Etc/GMT-10 Etc/GMT-10
Etc/GMT-11 Etc/GMT-11
Etc/GMT-12 Etc/GMT-12
Etc/GMT-13 Etc/GMT-13
Etc/GMT-14 Etc/GMT-14
Etc/GMT-2 Etc/GMT-2
Etc/GMT-3 Etc/GMT-3
Etc/GMT-4 Etc/GMT-4
Etc/GMT-5 Etc/GMT-5
Etc/GMT-6 Etc/GMT-6
Etc/GMT-7 Etc/GMT-7
Etc/GMT-8 Etc/GMT-8
Etc/GMT-9 Etc/GMT-9
Etc/GMT0 GMT
Etc/Greenwich GMT
Etc/UCT UCT
Etc/UTC UCT
Etc/Universal UCT
Etc/Zulu UCT
Europe/Amsterdam CET
Europe/Andorra Europe/Andorra
Europe/Astrakhan Europe/Astrakhan
Europe/Athens EET
Europe/Belfast GB
Europe/Belgrade Europe/Belgrade
Europe/Berlin Europe/Berlin
Europe/Bratislava Europe/Bratislava
Europe/Brussels CET
Europe/Bucharest Europe/Bucharest
Europe/Budapest Europe/Budapest
Europe/Busingen Europe/Busingen
Europe/Chisinau Europe/Chisinau
Europe/Copenhagen Europe/Copenhagen
Europe/Dublin Eire
Europe/Gibraltar Europe/Gibraltar
Europe/Guernsey GB
Europe/Helsinki Europe/Helsinki
Europe/Isle_of_Man GB
Europe/Istanbul Turkey
Europe/Jersey GB
Europe/Kaliningrad Europe/Kaliningrad
Europe/Kiev Europe/Kiev
Europe/Kirov Europe/Kirov
Europe/Kyiv Europe/Kyiv
Europe/Lisbon WET
Europe/Ljubljana Europe/Ljubljana
Europe/London GB
Europe/Luxembourg CET
Europe/Madrid Europe/Madrid
Europe/Malta Europe/Malta
Europe/Mariehamn Europe/Mariehamn
Europe/Minsk Europe/Minsk
Europe/Monaco ECT
Europe/Moscow W-SU
Europe/Nicosia Europe/Nicosia
Europe/Oslo Europe/Oslo
Europe/Paris ECT
Europe/Podgorica Europe/Podgorica
Europe/Prague Europe/Prague
Europe/Riga Europe/Riga
Europe/Rome Europe/Rome
Europe/Samara Europe/Samara
Europe/San_Marino Europe/San_Marino
Europe/Sarajevo Europe/Sarajevo
Europe/Saratov Europe/Saratov
Europe/Simferopol Europe/Simferopol
Europe/Skopje Europe/Skopje
Europe/Sofia Europe/Sofia
Europe/Stockholm Europe/Stockholm
Europe/Tallinn Europe/Tallinn
Europe/Tirane Europe/Tirane
Europe/Tiraspol Europe/Tiraspol
Europe/Ulyanovsk Europe/Ulyanovsk
Europe/Uzhgorod Europe/Uzhgorod
Europe/Vaduz Europe/Vaduz
Europe/Vatican Europe/Vatican
Europe/Vienna Europe/Vienna
Europe/Vilnius Europe/Vilnius
Europe/Volgograd Europe/Volgograd
Europe/Warsaw Poland
Europe/Zagreb Europe/Zagreb
Europe/Zaporozhye Europe/Zaporozhye
Europe/Zurich Europe/Zurich
Factory Factory
GB GB
GB-Eire GB
GMT GMT
GMT+0 GMT
GMT-0 GMT
GMT0 GMT
Greenwich GMT
HST HST
Hongkong Hongkong
IET IET
IST IST
Iceland Iceland
Indian/Antananarivo EAT
Indian/Chagos Indian/Chagos
Indian/Christmas Indian/Christmas
Indian/Cocos Indian/Cocos
Indian/Comoro EAT
Indian/Kerguelen Indian/Kerguelen
Indian/Mahe Indian/Mahe
Indian/Maldives Indian/Maldives
Indian/Mauritius Indian/Mauritius
Indian/Mayotte EAT
Indian/Reunion Indian/Reunion
Iran Iran
Israel Israel
JST JST
Jamaica Jamaica
Japan JST
Kwajalein Kwajalein
Libya Libya
MET CET
MIT MIT
MST MST
MST7MDT Navajo
Mexico/BajaNorte Mexico/BajaNorte
Mexico/BajaSur Mexico/BajaSur
Mexico/General Mexico/General
NET NET
NST NZ
NZ NZ
NZ-CHAT NZ-CHAT
Navajo Navajo
PLT PLT
PNT MST
PRC CTT
PRT PRT
PST PST
PST8PDT PST
Pacific/Apia MIT
Pacific/Auckland NZ
Pacific/Bougainville Pacific/Bougainville
Pacific/Chatham NZ-CHAT
Pacific/Chuuk Pacific/Chuuk
Pacific/Easter Pacific/Easter
Pacific/Efate Pacific/Efate
Pacific/Enderbury Pacific/Enderbury
Pacific/Fakaofo Pacific/Fakaofo
Pacific/Fiji Pacific/Fiji
Pacific/Funafuti Pacific/Funafuti
Pacific/Galapagos Pacific/Galapagos
Pacific/Gambier Pacific/Gambier
Pacific/Guadalcanal SST
Pacific/Guam Pacific/Guam
Pacific/Honolulu HST
Pacific/Johnston HST
Pacific/Kanton Pacific/Kanton
Pacific/Kiritimati Pacific/Kiritimati
Pacific/Kosrae Pacific/Kosrae
Pacific/Kwajalein Kwajalein
Pacific/Majuro Pacific/Majuro
Pacific/Marquesas Pacific/Marquesas
Pacific/Midway Pacific/Midway
Pacific/Nauru Pacific/Nauru
Pacific/Niue Pacific/Niue
Pacific/Norfolk Pacific/Norfolk
Pacific/Noumea Pacific/Noumea
Pacific/Pago_Pago Pacific/Pago_Pago
Pacific/Palau Pacific/Palau
Pacific/Pitcairn Pacific/Pitcairn
Pacific/Pohnpei SST
Pacific/Ponape SST
Pacific/Port_Moresby Pacific/Port_Moresby
Pacific/Rarotonga Pacific/Rarotonga
Pacific/Saipan Pacific/Saipan
Pacific/Samoa Pacific/Samoa
Pacific/Tahiti Pacific/Tahiti
Pacific/Tarawa Pacific/Tarawa
Pacific/Tongatapu Pacific/Tongatapu
Pacific/Truk Pacific/Truk
Pacific/Wake Pacific/Wake
Pacific/Wallis Pacific/Wallis
Pacific/Yap Pacific/Yap
Poland Poland
Portugal WET
ROC ROC
ROK ROK
SST SST
Singapore Singapore
SystemV/AST4 SystemV/AST4
SystemV/AST4ADT SystemV/AST4ADT
SystemV/CST6 SystemV/CST6
SystemV/CST6CDT SystemV/CST6CDT
SystemV/EST5 SystemV/EST5
SystemV/EST5EDT SystemV/EST5EDT
SystemV/HST10 SystemV/HST10
SystemV/MST7 SystemV/MST7
SystemV/MST7MDT SystemV/MST7MDT
SystemV/PST8 SystemV/PST8
SystemV/PST8PDT SystemV/PST8PDT
SystemV/YST9 SystemV/YST9
SystemV/YST9YDT SystemV/YST9YDT
Turkey Turkey
UCT UCT
US/Alaska AST
US/Aleutian US/Aleutian
US/Arizona MST
US/Central CST
US/East-Indiana IET
US/Eastern EST5EDT
US/Hawaii HST
US/Indiana-Starke US/Indiana-Starke
US/Michigan US/Michigan
US/Mountain Navajo
US/Pacific PST
US/Pacific-New PST
US/Samoa US/Samoa
UTC UCT
Universal UCT
VST VST
W-SU W-SU
WET WET
Zulu UCT
#
# Time Zone Rule updates
#
# These are conventionally named YYYYx, where x is a lower case letter.
#
# 2022e
# Jordan and Syria are abandoning the DST regime and are changing to
# permanent +03, so they will not fall back from +03 to +02 on
# 2022-10-28.
statement ok
SET TimeZone = 'Asia/Amman';
query I
SELECT '2022-10-29 00:00:00+00'::TIMESTAMPTZ;
----
2022-10-29 03:00:00+03
# 2022f
# Chihuahua moves to year-round -06 on 2022-10-30.
statement ok
SET TimeZone = 'America/Chihuahua';
query I
SELECT '2022-11-01 00:00:00+00'::TIMESTAMPTZ;
----
2022-10-31 18:00:00-06
# 2022g
# The northern edge of Chihuahua changes to US timekeeping (DST).
statement ok
SET TimeZone = 'America/Ciudad_Juarez';
query I
SELECT '2023-05-01 12:00:00+00'::TIMESTAMPTZ;
----
2023-05-01 06:00:00-06
# 2023a
# Egypt now uses DST again, from April through October
statement ok
SET TimeZone = 'Egypt';
query I
SELECT '2023-05-15 12:00:00+00'::TIMESTAMPTZ;
----
2023-05-15 15:00:00+03
# 2023b
# This year Lebanon springs forward April 20/21 not March 25/26.
# 2023c
# This release's code and data are identical to 2023a.
# In other words, this release reverts all changes made in 2023b other than commentary,
# as that appears to be the best of a bad set of short-notice choices for modeling
# this week's daylight saving chaos in Lebanon.
statement ok
SET TimeZone = 'Asia/Beirut';
query II
SELECT '2023-03-26 12:00:00+00'::TIMESTAMPTZ, '2023-04-21 12:00:00+00'::TIMESTAMPTZ;
----
2023-03-26 15:00:00+03 2023-04-21 15:00:00+03
# Greenland 2023 time zone change
statement ok
SET TimeZone = 'America/Nuuk';
query II
SELECT '2022-10-30 03:00:00-07'::TIMESTAMPTZ, '2023-10-30 02:00:00-07'::TIMESTAMPTZ;
----
2022-10-30 07:00:00-03 2023-10-30 07:00:00-02
# 2023d
# Ittoqqortoormiit, Greenland changes time zones on 2024-03-31
# from -01/+00 to -02/-01 at the same moment as the spring-forward transition.
statement ok
SET TimeZone = 'America/Scoresbysund';
query II
SELECT '2024-03-31 00:59:00-01'::TIMESTAMPTZ, '2024-03-31 01:00:00-01'::TIMESTAMPTZ;
----
2024-03-31 00:59:00-01 2024-03-31 01:00:00-01
# 2024a
# Kazakhstan unifies on UTC+5 beginning 2024-03-01.
statement ok
SET TimeZone = 'Asia/Almaty';
query II
select '2024-02-29 00:00:00+06'::TIMESTAMPTZ, '2024-03-01 01:00:00+06'::TIMESTAMPTZ;
----
2024-02-29 00:00:00+06 2024-03-01 00:00:00+05
# 2024b
# In particular, the Azores and Madeira did not observe DST from 1977 to 1981.
statement ok
SET TimeZone = 'Atlantic/Azores';
# Last DST error
query I
select '1981-09-27 01:00:00+00'::TIMESTAMPTZ;
----
1981-09-27 00:00:00-01
# 2025a
# Paraguay will stop changing its clocks after the spring-forward
# transition on 2024-10-06, so it is now permanently at -03.
statement ok
set timezone='America/Asuncion';
query I
select '2025-03-23'::timestamptz;
----
2025-03-23 00:00:00-03
query II
select utc_offset, is_dst
from pg_timezone_names()
where name = 'America/Asuncion'
----
-03:00:00 false
# 2025b
# Chile's Aysén Region moves from -04/-03 to -03 year-round, joining
# Magallanes Region. The region will not change its clocks on
# 2025-04-05 at 24:00, diverging from America/Santiago and creating a
# new zone America/Coyhaique
statement ok
set timezone='America/Coyhaique';
query I
select '2025-04-05'::timestamptz;
----
2025-04-05 00:00:00-03
query II
select utc_offset, is_dst
from pg_timezone_names()
where name = 'America/Coyhaique'
----
-03:00:00 false
# 2025c
# Baja California agreed with California’s DST rules in 1953 and in
# 1961 through 1975, instead of observing standard time all year.
statement ok
set timezone='Mexico/BajaNorte';
query I
select '1962-07-31'::timestamptz;
----
1962-07-31 00:00:00-07
# 2026a
# Since 2022 Moldova has observed EU transition times, that is, it
# has sprung forward at 03:00, not 02:00, and has fallen back at
# 04:00, not 03:00. (Thanks to Heitor David Pinto.)
statement ok
set timezone='Europe/Chisinau';
query IIII
select
'2022-03-27 02:00:00+02'::timestamptz,
'2022-03-27 03:00:00+02'::timestamptz,
'2022-10-30 03:00:00+03'::timestamptz,
'2022-10-30 04:00:00+03'::timestamptz,
----
2022-03-27 02:00:00+02 2022-03-27 04:00:00+03 2022-10-30 03:00:00+03 2022-10-30 03:00:00+02
|
