src/test/regress/sql/timestamptz.sql

   1 --
   2 -- TIMESTAMPTZ
   3 --
   4
   5 CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
   6
   7 -- Test shorthand input values
   8 -- We can't just "select" the results since they aren't constants; test for
   9 -- equality instead.  We can do that by running the test inside a transaction
  10 -- block, within which the value of 'now' shouldn't change, and so these
  11 -- related values shouldn't either.
  12
  13 BEGIN;
  14
  15 INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
  16 INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
  17 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
  18 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow EST');
  19 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
  20
  21 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
  22 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
  23 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
  24 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
  25 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
  26
  27 COMMIT;
  28
  29 DELETE FROM TIMESTAMPTZ_TBL;
  30
  31 -- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
  32 -- and that it doesn't change over the same interval within a transaction block
  33
  34 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
  35 SELECT pg_sleep(0.1);
  36
  37 BEGIN;
  38 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
  39 SELECT pg_sleep(0.1);
  40 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
  41 SELECT pg_sleep(0.1);
  42 SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
  43 SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
  44 COMMIT;
  45
  46 TRUNCATE TIMESTAMPTZ_TBL;
  47
  48 -- Special values
  49 INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
  50 INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
  51 INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
  52
  53 SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
  54
  55 -- Postgres v6.0 standard output format
  56 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
  57
  58 -- Variations on Postgres v6.1 standard output format
  59 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
  60 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
  61 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
  62 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
  63 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
  64
  65 -- ISO 8601 format
  66 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02');
  67 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02 03:04:05');
  68 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-08');
  69 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-0800');
  70 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00');
  71 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800');
  72 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00');
  73 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20');
  74
  75 -- POSIX format (note that the timezone abbrev is just decoration here)
  76 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
  77 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
  78 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
  79 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8');
  80 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
  81
  82 -- Variations for acceptable input formats
  83 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
  84 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
  85 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 5:32PM 1997');
  86 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997/02/10 17:32:01-0800');
  87 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST');
  88 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST');
  89 INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST');
  90 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST');
  91 set datestyle to ymd;
  92 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
  93 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
  94 reset datestyle;
  95 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
  96
  97 -- timestamps at different timezones
  98 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 America/New_York');
  99 SELECT '19970210 173201' AT TIME ZONE 'America/New_York';
 100 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/New_York');
 101 SELECT '19970710 173201' AT TIME ZONE 'America/New_York';
 102 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist');
 103 SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist';
 104
 105 -- Daylight saving time for timestamps beyond 32-bit time_t range.
 106 SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST
 107 SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST
 108
 109 SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST
 110 SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST
 111
 112 -- Test non-error-throwing API
 113 SELECT pg_input_is_valid('now', 'timestamptz');
 114 SELECT pg_input_is_valid('garbage', 'timestamptz');
 115 SELECT pg_input_is_valid('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamptz');
 116 SELECT pg_input_error_message('garbage', 'timestamptz');
 117 SELECT pg_input_error_message('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamptz');
 118
 119 -- Check date conversion and date arithmetic
 120 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');
 121
 122 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
 123 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 11 17:32:01 1997');
 124 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 12 17:32:01 1997');
 125 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 13 17:32:01 1997');
 126 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 14 17:32:01 1997');
 127 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 15 17:32:01 1997');
 128 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
 129
 130 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097 BC');
 131 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097');
 132 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0597');
 133 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1097');
 134 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1697');
 135 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1797');
 136 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1897');
 137 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
 138 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 2097');
 139
 140 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1996');
 141 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1996');
 142 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1996');
 143 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1996');
 144 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1996');
 145 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 1997');
 146 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1997');
 147 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997');
 148 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1997');
 149 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1997');
 150 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1997');
 151 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999');
 152 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000');
 153 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000');
 154 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001');
 155
 156 -- Currently unsupported syntax and ranges
 157 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
 158 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');
 159
 160 -- Alternative field order that we've historically supported (sort of)
 161 -- with regular and POSIXy timezone specs
 162 SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
 163 SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
 164 SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
 165 SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
 166 SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
 167
 168 SELECT d1 FROM TIMESTAMPTZ_TBL;
 169
 170 -- Check behavior at the boundaries of the timestamp range
 171 SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
 172 SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
 173 SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
 174 SELECT '4714-11-23 23:59:59+00 BC'::timestamptz;  -- out of range
 175 SELECT '294276-12-31 23:59:59+00'::timestamptz;
 176 SELECT '294276-12-31 15:59:59-08'::timestamptz;
 177 SELECT '294277-01-01 00:00:00+00'::timestamptz;  -- out of range
 178 SELECT '294277-12-31 16:00:00-08'::timestamptz;  -- out of range
 179
 180 -- Demonstrate functions and operators
 181 SELECT d1 FROM TIMESTAMPTZ_TBL
 182    WHERE d1 > timestamp with time zone '1997-01-02';
 183
 184 SELECT d1 FROM TIMESTAMPTZ_TBL
 185    WHERE d1 < timestamp with time zone '1997-01-02';
 186
 187 SELECT d1 FROM TIMESTAMPTZ_TBL
 188    WHERE d1 = timestamp with time zone '1997-01-02';
 189
 190 SELECT d1 FROM TIMESTAMPTZ_TBL
 191    WHERE d1 != timestamp with time zone '1997-01-02';
 192
 193 SELECT d1 FROM TIMESTAMPTZ_TBL
 194    WHERE d1 <= timestamp with time zone '1997-01-02';
 195
 196 SELECT d1 FROM TIMESTAMPTZ_TBL
 197    WHERE d1 >= timestamp with time zone '1997-01-02';
 198
 199 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
 200    FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
 201
 202 SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
 203
 204 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc;  -- zone name
 205 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc;  -- fixed-offset abbreviation
 206 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc;  -- variable-offset abbreviation
 207
 208 -- verify date_bin behaves the same as date_trunc for relevant intervals
 209 SELECT
 210   str,
 211   interval,
 212   date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS equal
 213 FROM (
 214   VALUES
 215   ('day', '1 d'),
 216   ('hour', '1 h'),
 217   ('minute', '1 m'),
 218   ('second', '1 s'),
 219   ('millisecond', '1 ms'),
 220   ('microsecond', '1 us')
 221 ) intervals (str, interval),
 222 (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
 223
 224 -- bin timestamps into arbitrary intervals
 225 SELECT
 226   interval,
 227   ts,
 228   origin,
 229   date_bin(interval::interval, ts, origin)
 230 FROM (
 231   VALUES
 232   ('15 days'),
 233   ('2 hours'),
 234   ('1 hour 30 minutes'),
 235   ('15 minutes'),
 236   ('10 seconds'),
 237   ('100 milliseconds'),
 238   ('250 microseconds')
 239 ) intervals (interval),
 240 (VALUES (timestamptz '2020-02-11 15:44:17.71393')) ts (ts),
 241 (VALUES (timestamptz '2001-01-01')) origin (origin);
 242
 243 -- shift bins using the origin parameter:
 244 SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00');
 245
 246 -- disallow intervals with months or years
 247 SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
 248 SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
 249
 250 -- disallow zero intervals
 251 SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
 252
 253 -- disallow negative intervals
 254 SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
 255
 256 -- Test casting within a BETWEEN qualifier
 257 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
 258   FROM TIMESTAMPTZ_TBL
 259   WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
 260
 261 -- DATE_PART (timestamptz_part)
 262 SELECT d1 as timestamptz,
 263    date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
 264    date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
 265    date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
 266    FROM TIMESTAMPTZ_TBL;
 267
 268 SELECT d1 as timestamptz,
 269    date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
 270    date_part( 'usec', d1) AS usec
 271    FROM TIMESTAMPTZ_TBL;
 272
 273 SELECT d1 as timestamptz,
 274    date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
 275    date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
 276    date_part( 'doy', d1) AS doy
 277    FROM TIMESTAMPTZ_TBL;
 278
 279 SELECT d1 as timestamptz,
 280    date_part( 'decade', d1) AS decade,
 281    date_part( 'century', d1) AS century,
 282    date_part( 'millennium', d1) AS millennium,
 283    round(date_part( 'julian', d1)) AS julian,
 284    date_part( 'epoch', d1) AS epoch
 285    FROM TIMESTAMPTZ_TBL;
 286
 287 SELECT d1 as timestamptz,
 288    date_part( 'timezone', d1) AS timezone,
 289    date_part( 'timezone_hour', d1) AS timezone_hour,
 290    date_part( 'timezone_minute', d1) AS timezone_minute
 291    FROM TIMESTAMPTZ_TBL;
 292
 293 -- extract implementation is mostly the same as date_part, so only
 294 -- test a few cases for additional coverage.
 295 SELECT d1 as "timestamp",
 296    extract(microseconds from d1) AS microseconds,
 297    extract(milliseconds from d1) AS milliseconds,
 298    extract(seconds from d1) AS seconds,
 299    round(extract(julian from d1)) AS julian,
 300    extract(epoch from d1) AS epoch
 301    FROM TIMESTAMPTZ_TBL;
 302
 303 -- value near upper bound uses special case in code
 304 SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz);
 305 SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
 306 -- another internal overflow test case
 307 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
 308
 309 -- test edge-case overflow in timestamp subtraction
 310 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224193 UTC' AS ok;
 311 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224192 UTC' AS overflows;
 312
 313 -- TO_CHAR()
 314 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
 315    FROM TIMESTAMPTZ_TBL;
 316
 317 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
 318    FROM TIMESTAMPTZ_TBL;
 319
 320 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
 321    FROM TIMESTAMPTZ_TBL;
 322
 323 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
 324    FROM TIMESTAMPTZ_TBL;
 325
 326 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
 327    FROM TIMESTAMPTZ_TBL;
 328
 329 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
 330    FROM TIMESTAMPTZ_TBL;
 331
 332 SELECT to_char(d1, 'HH24--text--MI--text--SS')
 333    FROM TIMESTAMPTZ_TBL;
 334
 335 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
 336    FROM TIMESTAMPTZ_TBL;
 337
 338 SELECT to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
 339    FROM TIMESTAMPTZ_TBL;
 340
 341 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
 342    FROM TIMESTAMPTZ_TBL;
 343
 344 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
 345    FROM TIMESTAMPTZ_TBL;
 346
 347 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6  ff1 ff2 ff3 ff4 ff5 ff6  MS US')
 348    FROM (VALUES
 349        ('2018-11-02 12:34:56'::timestamptz),
 350        ('2018-11-02 12:34:56.78'),
 351        ('2018-11-02 12:34:56.78901'),
 352        ('2018-11-02 12:34:56.78901234')
 353    ) d(d);
 354
 355 -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours
 356 SET timezone = '00:00';
 357 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 358 SET timezone = '+02:00';
 359 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 360 SET timezone = '-13:00';
 361 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 362 SET timezone = '-00:30';
 363 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 364 SET timezone = '00:30';
 365 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 366 SET timezone = '-04:30';
 367 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 368 SET timezone = '04:30';
 369 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 370 SET timezone = '-04:15';
 371 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 372 SET timezone = '04:15';
 373 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 374 RESET timezone;
 375
 376 -- Check of, tzh, tzm with various zone offsets.
 377 SET timezone = '00:00';
 378 SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 379 SET timezone = '+02:00';
 380 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 381 SET timezone = '-13:00';
 382 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 383 SET timezone = '-00:30';
 384 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 385 SET timezone = '00:30';
 386 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 387 SET timezone = '-04:30';
 388 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 389 SET timezone = '04:30';
 390 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 391 SET timezone = '-04:15';
 392 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 393 SET timezone = '04:15';
 394 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 395 RESET timezone;
 396
 397
 398 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
 399
 400 -- Test year field value with len > 4
 401 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
 402 INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
 403 INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
 404 INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
 405 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
 406 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
 407 --Verify data
 408 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
 409 --Cleanup
 410 DROP TABLE TIMESTAMPTZ_TST;
 411
 412 -- test timestamptz constructors
 413 set TimeZone to 'America/New_York';
 414
 415 -- numeric timezone
 416 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
 417 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
 418 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2');
 419 WITH tzs (tz) AS (VALUES
 420     ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'),
 421     ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'),
 422     ('+10:00:1'), ('+10:00:01'),
 423     ('+10:00:10'))
 424      SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
 425
 426 -- these should fail
 427 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
 428 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16');
 429 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16');
 430
 431 -- should be true
 432 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
 433
 434 -- full timezone names
 435 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague';
 436 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC';
 437 SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC';
 438 SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC';
 439 SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar');
 440
 441 -- abbreviations
 442 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST');
 443 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT');
 444 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT');
 445
 446 RESET TimeZone;
 447
 448 -- generate_series for timestamptz
 449 select * from generate_series('2020-01-01 00:00'::timestamptz,
 450                               '2020-01-02 03:00'::timestamptz,
 451                               '1 hour'::interval);
 452 -- the LIMIT should allow this to terminate in a reasonable amount of time
 453 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
 454 select generate_series('2022-01-01 00:00'::timestamptz,
 455                        'infinity'::timestamptz,
 456                        '1 month'::interval) limit 10;
 457 -- errors
 458 select * from generate_series('2020-01-01 00:00'::timestamptz,
 459                               '2020-01-02 03:00'::timestamptz,
 460                               '0 hour'::interval);
 461
 462 --
 463 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
 464 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
 465 -- moved forwards in Mar 2011 and backwards again in Oct 2014.
 466 --
 467
 468 SET TimeZone to 'UTC';
 469
 470 SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz;
 471 SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz;
 472 SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz;
 473 SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz;
 474 SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz;
 475 SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz;
 476 SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz;
 477 SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz;
 478 SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz;
 479
 480 SELECT '2011-03-27 00:00:00 MSK'::timestamptz;
 481 SELECT '2011-03-27 01:00:00 MSK'::timestamptz;
 482 SELECT '2011-03-27 01:59:59 MSK'::timestamptz;
 483 SELECT '2011-03-27 02:00:00 MSK'::timestamptz;
 484 SELECT '2011-03-27 02:00:01 MSK'::timestamptz;
 485 SELECT '2011-03-27 02:59:59 MSK'::timestamptz;
 486 SELECT '2011-03-27 03:00:00 MSK'::timestamptz;
 487 SELECT '2011-03-27 03:00:01 MSK'::timestamptz;
 488 SELECT '2011-03-27 04:00:00 MSK'::timestamptz;
 489
 490 SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz;
 491 SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz;
 492 SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz;
 493 SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz;
 494 SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz;
 495
 496 SELECT '2014-10-26 00:00:00 MSK'::timestamptz;
 497 SELECT '2014-10-26 00:59:59 MSK'::timestamptz;
 498 SELECT '2014-10-26 01:00:00 MSK'::timestamptz;
 499 SELECT '2014-10-26 01:00:01 MSK'::timestamptz;
 500 SELECT '2014-10-26 02:00:00 MSK'::timestamptz;
 501
 502 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 503 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 504 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 505 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 506 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 507 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 508 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 509 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 510 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 511
 512 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK';
 513 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK';
 514 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK';
 515 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK';
 516 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK';
 517 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK';
 518 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK';
 519 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK';
 520 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK';
 521
 522 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 523 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 524 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 525 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 526 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 527
 528 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK';
 529 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK';
 530 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK';
 531 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK';
 532 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK';
 533
 534 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
 535 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
 536
 537 SELECT to_timestamp(         0);          -- 1970-01-01 00:00:00+00
 538 SELECT to_timestamp( 946684800);          -- 2000-01-01 00:00:00+00
 539 SELECT to_timestamp(1262349296.7890123);  -- 2010-01-01 12:34:56.789012+00
 540 -- edge cases
 541 SELECT to_timestamp(-210866803200);       --   4714-11-24 00:00:00+00 BC
 542 -- upper limit varies between integer and float timestamps, so hard to test
 543 -- nonfinite values
 544 SELECT to_timestamp(' Infinity'::float);
 545 SELECT to_timestamp('-Infinity'::float);
 546 SELECT to_timestamp('NaN'::float);
 547
 548
 549 SET TimeZone to 'Europe/Moscow';
 550
 551 SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
 552 SELECT '2011-03-26 22:00:00 UTC'::timestamptz;
 553 SELECT '2011-03-26 22:59:59 UTC'::timestamptz;
 554 SELECT '2011-03-26 23:00:00 UTC'::timestamptz;
 555 SELECT '2011-03-26 23:00:01 UTC'::timestamptz;
 556 SELECT '2011-03-26 23:59:59 UTC'::timestamptz;
 557 SELECT '2011-03-27 00:00:00 UTC'::timestamptz;
 558
 559 SELECT '2014-10-25 21:00:00 UTC'::timestamptz;
 560 SELECT '2014-10-25 21:59:59 UTC'::timestamptz;
 561 SELECT '2014-10-25 22:00:00 UTC'::timestamptz;
 562 SELECT '2014-10-25 22:00:01 UTC'::timestamptz;
 563 SELECT '2014-10-25 23:00:00 UTC'::timestamptz;
 564
 565 RESET TimeZone;
 566
 567 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 568 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 569 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 570 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 571 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 572 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 573 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 574
 575 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 576 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 577 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 578 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 579 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 580
 581 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 582 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 583 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 584 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 585 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 586 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 587 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 588
 589 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 590 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 591 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 592 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 593 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 594
 595 --
 596 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
 597 --
 598 create temp table tmptz (f1 timestamptz primary key);
 599 insert into tmptz values ('2017-01-18 00:00+00');
 600 explain (costs off)
 601 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
 602 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';