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 * FROM pg_input_error_info('garbage', 'timestamptz');
 117 SELECT * FROM pg_input_error_info('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 -- test roundoff edge case when source < origin
 247 SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00');
 248
 249 -- disallow intervals with months or years
 250 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');
 251 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');
 252
 253 -- disallow zero intervals
 254 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');
 255
 256 -- disallow negative intervals
 257 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');
 258
 259 -- test overflow cases
 260 select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC');
 261 select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz);
 262 select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz);
 263
 264 -- Test casting within a BETWEEN qualifier
 265 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
 266   FROM TIMESTAMPTZ_TBL
 267   WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
 268
 269 -- DATE_PART (timestamptz_part)
 270 SELECT d1 as timestamptz,
 271    date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
 272    date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
 273    date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
 274    FROM TIMESTAMPTZ_TBL;
 275
 276 SELECT d1 as timestamptz,
 277    date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
 278    date_part( 'usec', d1) AS usec
 279    FROM TIMESTAMPTZ_TBL;
 280
 281 SELECT d1 as timestamptz,
 282    date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
 283    date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
 284    date_part( 'doy', d1) AS doy
 285    FROM TIMESTAMPTZ_TBL;
 286
 287 SELECT d1 as timestamptz,
 288    date_part( 'decade', d1) AS decade,
 289    date_part( 'century', d1) AS century,
 290    date_part( 'millennium', d1) AS millennium,
 291    round(date_part( 'julian', d1)) AS julian,
 292    date_part( 'epoch', d1) AS epoch
 293    FROM TIMESTAMPTZ_TBL;
 294
 295 SELECT d1 as timestamptz,
 296    date_part( 'timezone', d1) AS timezone,
 297    date_part( 'timezone_hour', d1) AS timezone_hour,
 298    date_part( 'timezone_minute', d1) AS timezone_minute
 299    FROM TIMESTAMPTZ_TBL;
 300
 301 -- extract implementation is mostly the same as date_part, so only
 302 -- test a few cases for additional coverage.
 303 SELECT d1 as "timestamp",
 304    extract(microseconds from d1) AS microseconds,
 305    extract(milliseconds from d1) AS milliseconds,
 306    extract(seconds from d1) AS seconds,
 307    round(extract(julian from d1)) AS julian,
 308    extract(epoch from d1) AS epoch
 309    FROM TIMESTAMPTZ_TBL;
 310
 311 -- value near upper bound uses special case in code
 312 SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz);
 313 SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
 314 -- another internal overflow test case
 315 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
 316
 317 -- test edge-case overflow in timestamp subtraction
 318 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224193 UTC' AS ok;
 319 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224192 UTC' AS overflows;
 320
 321 -- TO_CHAR()
 322 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
 323    FROM TIMESTAMPTZ_TBL;
 324
 325 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
 326    FROM TIMESTAMPTZ_TBL;
 327
 328 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
 329    FROM TIMESTAMPTZ_TBL;
 330
 331 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
 332    FROM TIMESTAMPTZ_TBL;
 333
 334 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
 335    FROM TIMESTAMPTZ_TBL;
 336
 337 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
 338    FROM TIMESTAMPTZ_TBL;
 339
 340 SELECT to_char(d1, 'HH24--text--MI--text--SS')
 341    FROM TIMESTAMPTZ_TBL;
 342
 343 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
 344    FROM TIMESTAMPTZ_TBL;
 345
 346 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')
 347    FROM TIMESTAMPTZ_TBL;
 348
 349 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
 350    FROM TIMESTAMPTZ_TBL;
 351
 352 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
 353    FROM TIMESTAMPTZ_TBL;
 354
 355 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6  ff1 ff2 ff3 ff4 ff5 ff6  MS US')
 356    FROM (VALUES
 357        ('2018-11-02 12:34:56'::timestamptz),
 358        ('2018-11-02 12:34:56.78'),
 359        ('2018-11-02 12:34:56.78901'),
 360        ('2018-11-02 12:34:56.78901234')
 361    ) d(d);
 362
 363 -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours
 364 SET timezone = '00:00';
 365 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 366 SET timezone = '+02:00';
 367 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 368 SET timezone = '-13:00';
 369 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 370 SET timezone = '-00:30';
 371 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 372 SET timezone = '00:30';
 373 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 374 SET timezone = '-04:30';
 375 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 376 SET timezone = '04:30';
 377 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 378 SET timezone = '-04:15';
 379 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 380 SET timezone = '04:15';
 381 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
 382 RESET timezone;
 383
 384 -- Check of, tzh, tzm with various zone offsets.
 385 SET timezone = '00:00';
 386 SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 387 SET timezone = '+02:00';
 388 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 389 SET timezone = '-13:00';
 390 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 391 SET timezone = '-00:30';
 392 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 393 SET timezone = '00:30';
 394 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 395 SET timezone = '-04:30';
 396 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 397 SET timezone = '04:30';
 398 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 399 SET timezone = '-04:15';
 400 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 401 SET timezone = '04:15';
 402 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
 403 RESET timezone;
 404
 405
 406 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
 407
 408 -- Test year field value with len > 4
 409 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
 410 INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
 411 INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
 412 INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
 413 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
 414 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
 415 --Verify data
 416 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
 417 --Cleanup
 418 DROP TABLE TIMESTAMPTZ_TST;
 419
 420 -- test timestamptz constructors
 421 set TimeZone to 'America/New_York';
 422
 423 -- numeric timezone
 424 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
 425 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
 426 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2');
 427 WITH tzs (tz) AS (VALUES
 428     ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'),
 429     ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'),
 430     ('+10:00:1'), ('+10:00:01'),
 431     ('+10:00:10'))
 432      SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
 433
 434 -- these should fail
 435 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
 436 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16');
 437 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16');
 438
 439 -- should be true
 440 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
 441
 442 -- full timezone names
 443 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague';
 444 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC';
 445 SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC';
 446 SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC';
 447 SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar');
 448
 449 -- abbreviations
 450 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST');
 451 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT');
 452 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT');
 453
 454 RESET TimeZone;
 455
 456 -- generate_series for timestamptz
 457 select * from generate_series('2020-01-01 00:00'::timestamptz,
 458                               '2020-01-02 03:00'::timestamptz,
 459                               '1 hour'::interval);
 460 -- the LIMIT should allow this to terminate in a reasonable amount of time
 461 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
 462 select generate_series('2022-01-01 00:00'::timestamptz,
 463                        'infinity'::timestamptz,
 464                        '1 month'::interval) limit 10;
 465 -- errors
 466 select * from generate_series('2020-01-01 00:00'::timestamptz,
 467                               '2020-01-02 03:00'::timestamptz,
 468                               '0 hour'::interval);
 469 select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
 470 select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
 471
 472 -- Interval crossing time shift for Europe/Warsaw timezone (with DST)
 473 SET TimeZone to 'UTC';
 474
 475 SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
 476                 '1 day'::interval);
 477 SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
 478                 '1 day'::interval,
 479                 'Europe/Warsaw');
 480 SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
 481                      '1 day'::interval);
 482 SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
 483                      '1 day'::interval,
 484                      'Europe/Warsaw');
 485 SELECT * FROM generate_series('2021-12-31 23:00:00+00'::timestamptz,
 486                               '2020-12-31 23:00:00+00'::timestamptz,
 487                               '-1 month'::interval,
 488                               'Europe/Warsaw');
 489 RESET TimeZone;
 490
 491 --
 492 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
 493 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
 494 -- moved forwards in Mar 2011 and backwards again in Oct 2014.
 495 --
 496
 497 SET TimeZone to 'UTC';
 498
 499 SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz;
 500 SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz;
 501 SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz;
 502 SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz;
 503 SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz;
 504 SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz;
 505 SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz;
 506 SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz;
 507 SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz;
 508
 509 SELECT '2011-03-27 00:00:00 MSK'::timestamptz;
 510 SELECT '2011-03-27 01:00:00 MSK'::timestamptz;
 511 SELECT '2011-03-27 01:59:59 MSK'::timestamptz;
 512 SELECT '2011-03-27 02:00:00 MSK'::timestamptz;
 513 SELECT '2011-03-27 02:00:01 MSK'::timestamptz;
 514 SELECT '2011-03-27 02:59:59 MSK'::timestamptz;
 515 SELECT '2011-03-27 03:00:00 MSK'::timestamptz;
 516 SELECT '2011-03-27 03:00:01 MSK'::timestamptz;
 517 SELECT '2011-03-27 04:00:00 MSK'::timestamptz;
 518
 519 SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz;
 520 SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz;
 521 SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz;
 522 SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz;
 523 SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz;
 524
 525 SELECT '2014-10-26 00:00:00 MSK'::timestamptz;
 526 SELECT '2014-10-26 00:59:59 MSK'::timestamptz;
 527 SELECT '2014-10-26 01:00:00 MSK'::timestamptz;
 528 SELECT '2014-10-26 01:00:01 MSK'::timestamptz;
 529 SELECT '2014-10-26 02:00:00 MSK'::timestamptz;
 530
 531 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 532 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 533 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 534 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 535 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 536 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 537 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 538 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 539 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 540
 541 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK';
 542 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK';
 543 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK';
 544 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK';
 545 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK';
 546 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK';
 547 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK';
 548 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK';
 549 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK';
 550
 551 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 552 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
 553 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 554 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
 555 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
 556
 557 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK';
 558 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK';
 559 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK';
 560 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK';
 561 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK';
 562
 563 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
 564 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
 565
 566 SELECT to_timestamp(         0);          -- 1970-01-01 00:00:00+00
 567 SELECT to_timestamp( 946684800);          -- 2000-01-01 00:00:00+00
 568 SELECT to_timestamp(1262349296.7890123);  -- 2010-01-01 12:34:56.789012+00
 569 -- edge cases
 570 SELECT to_timestamp(-210866803200);       --   4714-11-24 00:00:00+00 BC
 571 -- upper limit varies between integer and float timestamps, so hard to test
 572 -- nonfinite values
 573 SELECT to_timestamp(' Infinity'::float);
 574 SELECT to_timestamp('-Infinity'::float);
 575 SELECT to_timestamp('NaN'::float);
 576
 577
 578 SET TimeZone to 'Europe/Moscow';
 579
 580 SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
 581 SELECT '2011-03-26 22:00:00 UTC'::timestamptz;
 582 SELECT '2011-03-26 22:59:59 UTC'::timestamptz;
 583 SELECT '2011-03-26 23:00:00 UTC'::timestamptz;
 584 SELECT '2011-03-26 23:00:01 UTC'::timestamptz;
 585 SELECT '2011-03-26 23:59:59 UTC'::timestamptz;
 586 SELECT '2011-03-27 00:00:00 UTC'::timestamptz;
 587
 588 SELECT '2014-10-25 21:00:00 UTC'::timestamptz;
 589 SELECT '2014-10-25 21:59:59 UTC'::timestamptz;
 590 SELECT '2014-10-25 22:00:00 UTC'::timestamptz;
 591 SELECT '2014-10-25 22:00:01 UTC'::timestamptz;
 592 SELECT '2014-10-25 23:00:00 UTC'::timestamptz;
 593
 594 RESET TimeZone;
 595
 596 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 597 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 598 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 599 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 600 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 601 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 602 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 603
 604 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 605 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 606 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 607 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 608 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
 609
 610 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 611 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 612 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 613 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 614 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 615 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 616 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 617
 618 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 619 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
 620 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 621 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
 622 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
 623
 624 --
 625 -- Test LOCAL time zone
 626 --
 627 BEGIN;
 628 SET LOCAL TIME ZONE 'Europe/Paris';
 629 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
 630 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
 631 SET LOCAL TIME ZONE 'Australia/Sydney';
 632 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
 633 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
 634 SET LOCAL TimeZone TO 'UTC';
 635 CREATE VIEW timestamp_local_view AS
 636   SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL AS ttz_at_local,
 637          timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)) AS ttz_func,
 638          TIMESTAMP '1978-07-07 19:38' AT LOCAL AS t_at_local,
 639          timezone(TIMESTAMP '1978-07-07 19:38') AS t_func;
 640 SELECT pg_get_viewdef('timestamp_local_view', true);
 641 \x
 642 TABLE timestamp_local_view;
 643 \x
 644 DROP VIEW timestamp_local_view;
 645 COMMIT;
 646
 647 --
 648 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
 649 --
 650 create temp table tmptz (f1 timestamptz primary key);
 651 insert into tmptz values ('2017-01-18 00:00+00');
 652 explain (costs off)
 653 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
 654 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
 655
 656 -- test arithmetic with infinite timestamps
 657 SELECT timestamptz 'infinity' - timestamptz 'infinity';
 658 SELECT timestamptz 'infinity' - timestamptz '-infinity';
 659 SELECT timestamptz '-infinity' - timestamptz 'infinity';
 660 SELECT timestamptz '-infinity' - timestamptz '-infinity';
 661 SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
 662 SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
 663
 664 -- test age() with infinite timestamps
 665 SELECT age(timestamptz 'infinity');
 666 SELECT age(timestamptz '-infinity');
 667 SELECT age(timestamptz 'infinity', timestamptz 'infinity');
 668 SELECT age(timestamptz 'infinity', timestamptz '-infinity');
 669 SELECT age(timestamptz '-infinity', timestamptz 'infinity');
 670 SELECT age(timestamptz '-infinity', timestamptz '-infinity');