5 CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
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.
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');
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';
29 DELETE FROM TIMESTAMPTZ_TBL;
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
34 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
38 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
40 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
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;
46 TRUNCATE TIMESTAMPTZ_TBL;
49 INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
50 INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
51 INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
53 SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
55 -- Postgres v6.0 standard output format
56 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
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');
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');
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');
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');
92 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
93 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
95 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
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';
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
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
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');
119 -- Check date conversion and date arithmetic
120 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');
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');
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');
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');
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');
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;
168 SELECT d1 FROM TIMESTAMPTZ_TBL;
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
180 -- Demonstrate functions and operators
181 SELECT d1 FROM TIMESTAMPTZ_TBL
182 WHERE d1 > timestamp with time zone '1997-01-02';
184 SELECT d1 FROM TIMESTAMPTZ_TBL
185 WHERE d1 < timestamp with time zone '1997-01-02';
187 SELECT d1 FROM TIMESTAMPTZ_TBL
188 WHERE d1 = timestamp with time zone '1997-01-02';
190 SELECT d1 FROM TIMESTAMPTZ_TBL
191 WHERE d1 != timestamp with time zone '1997-01-02';
193 SELECT d1 FROM TIMESTAMPTZ_TBL
194 WHERE d1 <= timestamp with time zone '1997-01-02';
196 SELECT d1 FROM TIMESTAMPTZ_TBL
197 WHERE d1 >= timestamp with time zone '1997-01-02';
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';
202 SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
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
208 -- verify date_bin behaves the same as date_trunc for relevant intervals
212 date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS equal
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);
224 -- bin timestamps into arbitrary intervals
229 date_bin(interval::interval, ts, origin)
234 ('1 hour 30 minutes'),
237 ('100 milliseconds'),
239 ) intervals (interval),
240 (VALUES (timestamptz '2020-02-11 15:44:17.71393')) ts (ts),
241 (VALUES (timestamptz '2001-01-01')) origin (origin);
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');
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');
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');
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');
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');
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);
264 -- Test casting within a BETWEEN qualifier
265 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
267 WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
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;
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;
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;
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;
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;
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;
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);
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;
322 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
323 FROM TIMESTAMPTZ_TBL;
325 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
326 FROM TIMESTAMPTZ_TBL;
328 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
329 FROM TIMESTAMPTZ_TBL;
331 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
332 FROM TIMESTAMPTZ_TBL;
334 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
335 FROM TIMESTAMPTZ_TBL;
337 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
338 FROM TIMESTAMPTZ_TBL;
340 SELECT to_char(d1, 'HH24--text--MI--text--SS')
341 FROM TIMESTAMPTZ_TBL;
343 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
344 FROM TIMESTAMPTZ_TBL;
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;
349 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
350 FROM TIMESTAMPTZ_TBL;
352 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
353 FROM TIMESTAMPTZ_TBL;
355 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
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')
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";
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";
406 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
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');
416 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
418 DROP TABLE TIMESTAMPTZ_TST;
420 -- test timestamptz constructors
421 set TimeZone to 'America/New_York';
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'),
432 SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
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');
440 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
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');
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');
456 -- generate_series for timestamptz
457 select * from generate_series('2020-01-01 00:00'::timestamptz,
458 '2020-01-02 03:00'::timestamptz,
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;
466 select * from generate_series('2020-01-01 00:00'::timestamptz,
467 '2020-01-02 03:00'::timestamptz,
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');
472 -- Interval crossing time shift for Europe/Warsaw timezone (with DST)
473 SET TimeZone to 'UTC';
475 SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
477 SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
480 SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
482 SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
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,
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.
497 SET TimeZone to 'UTC';
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;
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;
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;
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;
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';
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';
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';
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';
563 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
564 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
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
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
573 SELECT to_timestamp(' Infinity'::float);
574 SELECT to_timestamp('-Infinity'::float);
575 SELECT to_timestamp('NaN'::float);
578 SET TimeZone to 'Europe/Moscow';
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;
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;
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';
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';
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';
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';
625 -- Test LOCAL time zone
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);
642 TABLE timestamp_local_view;
644 DROP VIEW timestamp_local_view;
648 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
650 create temp table tmptz (f1 timestamptz primary key);
651 insert into tmptz values ('2017-01-18 00:00+00');
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';
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';
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');