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