| blob | b437613ac82e7cb9f12b7c45127af2ec29358292 |
1 --
2 -- TIMESTAMPTZ
3 --
4 CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
5 -- Test shorthand input values
6 -- We can't just "select" the results since they aren't constants; test for
7 -- equality instead. We can do that by running the test inside a transaction
8 -- block, within which the value of 'now' shouldn't change, and so these
9 -- related values shouldn't either.
10 BEGIN;
11 INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
12 INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
13 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
14 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow EST');
15 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
16 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
17 one
18 -----
19 1
20 (1 row)
22 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
23 one
24 -----
25 1
26 (1 row)
28 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
29 one
30 -----
31 1
32 (1 row)
34 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
35 one
36 -----
37 1
38 (1 row)
40 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
41 one
42 -----
43 1
44 (1 row)
46 COMMIT;
47 DELETE FROM TIMESTAMPTZ_TBL;
48 -- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
49 -- and that it doesn't change over the same interval within a transaction block
50 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
51 SELECT pg_sleep(0.1);
52 pg_sleep
53 ----------
55 (1 row)
57 BEGIN;
58 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
59 SELECT pg_sleep(0.1);
60 pg_sleep
61 ----------
63 (1 row)
65 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
66 SELECT pg_sleep(0.1);
67 pg_sleep
68 ----------
70 (1 row)
72 SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
73 two
74 -----
75 2
76 (1 row)
78 SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
79 three | two
80 -------+-----
81 3 | 2
82 (1 row)
84 COMMIT;
85 TRUNCATE TIMESTAMPTZ_TBL;
86 -- Special values
87 INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
88 INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
89 INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
90 SELECT timestamptz 'infinity' = timestamptz '+infinity' AS t;
91 t
92 ---
93 t
94 (1 row)
96 -- Postgres v6.0 standard output format
97 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
98 -- Variations on Postgres v6.1 standard output format
99 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
100 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
101 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
102 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
103 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
104 -- ISO 8601 format
105 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02');
106 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02 03:04:05');
107 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-08');
108 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-0800');
109 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00');
110 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800');
111 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00');
112 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20');
113 -- POSIX format (note that the timezone abbrev is just decoration here)
114 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
115 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
116 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
117 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8');
118 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
119 -- Variations for acceptable input formats
120 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
121 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
122 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 5:32PM 1997');
123 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997/02/10 17:32:01-0800');
124 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST');
125 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST');
126 INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST');
127 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST');
128 set datestyle to ymd;
129 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
130 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
131 reset datestyle;
132 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
133 -- timestamps at different timezones
134 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 America/New_York');
135 SELECT '19970210 173201' AT TIME ZONE 'America/New_York';
136 timezone
137 --------------------------
138 Mon Feb 10 20:32:01 1997
139 (1 row)
141 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/New_York');
142 SELECT '19970710 173201' AT TIME ZONE 'America/New_York';
143 timezone
144 --------------------------
145 Thu Jul 10 20:32:01 1997
146 (1 row)
148 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist');
149 ERROR: time zone "america/does_not_exist" not recognized
150 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America...
151 ^
152 SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist';
153 ERROR: time zone "America/Does_not_exist" not recognized
154 -- Daylight saving time for timestamps beyond 32-bit time_t range.
155 SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST
156 timestamptz
157 ------------------------------
158 Sun Jul 10 07:32:01 2050 PDT
159 (1 row)
161 SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST
162 timestamptz
163 ------------------------------
164 Mon Jan 10 07:32:01 2050 PST
165 (1 row)
167 SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST
168 timestamptz
169 --------------------------------
170 Thu Jul 10 07:32:01 205000 PDT
171 (1 row)
173 SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST
174 timestamptz
175 --------------------------------
176 Fri Jan 10 07:32:01 205000 PST
177 (1 row)
179 -- Test non-error-throwing API
180 SELECT pg_input_is_valid('now', 'timestamptz');
181 pg_input_is_valid
182 -------------------
183 t
184 (1 row)
186 SELECT pg_input_is_valid('garbage', 'timestamptz');
187 pg_input_is_valid
188 -------------------
189 f
190 (1 row)
192 SELECT pg_input_is_valid('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamptz');
193 pg_input_is_valid
194 -------------------
195 f
196 (1 row)
198 SELECT * FROM pg_input_error_info('garbage', 'timestamptz');
199 message | detail | hint | sql_error_code
200 -------------------------------------------------------------------+--------+------+----------------
201 invalid input syntax for type timestamp with time zone: "garbage" | | | 22007
202 (1 row)
204 SELECT * FROM pg_input_error_info('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamptz');
205 message | detail | hint | sql_error_code
206 --------------------------------------------+--------+------+----------------
207 time zone "nehwon/lankhmar" not recognized | | | 22023
208 (1 row)
210 -- Check date conversion and date arithmetic
211 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');
212 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
213 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 11 17:32:01 1997');
214 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 12 17:32:01 1997');
215 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 13 17:32:01 1997');
216 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 14 17:32:01 1997');
217 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 15 17:32:01 1997');
218 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
219 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097 BC');
220 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097');
221 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0597');
222 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1097');
223 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1697');
224 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1797');
225 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1897');
226 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
227 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 2097');
228 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1996');
229 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1996');
230 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1996');
231 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1996');
232 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1996');
233 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 1997');
234 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1997');
235 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997');
236 ERROR: date/time field value out of range: "Feb 29 17:32:01 1997"
237 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997');
238 ^
239 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1997');
240 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1997');
241 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1997');
242 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999');
243 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000');
244 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000');
245 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001');
246 -- Currently unsupported syntax and ranges
247 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
248 ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097"
249 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097')...
250 ^
251 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');
252 ERROR: timestamp out of range: "Feb 16 17:32:01 5097 BC"
253 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC...
254 ^
255 -- Alternative field order that we've historically supported (sort of)
256 -- with regular and POSIXy timezone specs
257 SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
258 timestamptz
259 ------------------------------
260 Wed Jul 11 07:51:14 2001 PDT
261 (1 row)
263 SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
264 timestamptz
265 ------------------------------
266 Tue Jul 10 23:51:14 2001 PDT
267 (1 row)
269 SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
270 timestamptz
271 ------------------------------
272 Wed Jul 11 07:51:14 2001 PDT
273 (1 row)
275 SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
276 timestamptz
277 ------------------------------
278 Wed Jul 11 00:51:14 2001 PDT
279 (1 row)
281 SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
282 timestamptz
283 ------------------------------
284 Wed Jul 11 06:51:14 2001 PDT
285 (1 row)
287 SELECT d1 FROM TIMESTAMPTZ_TBL;
288 d1
289 ---------------------------------
290 -infinity
291 infinity
292 Wed Dec 31 16:00:00 1969 PST
293 Mon Feb 10 17:32:01 1997 PST
294 Mon Feb 10 17:32:01 1997 PST
295 Mon Feb 10 17:32:02 1997 PST
296 Mon Feb 10 17:32:01.4 1997 PST
297 Mon Feb 10 17:32:01.5 1997 PST
298 Mon Feb 10 17:32:01.6 1997 PST
299 Thu Jan 02 00:00:00 1997 PST
300 Thu Jan 02 03:04:05 1997 PST
301 Mon Feb 10 17:32:01 1997 PST
302 Mon Feb 10 17:32:01 1997 PST
303 Mon Feb 10 17:32:01 1997 PST
304 Mon Feb 10 17:32:01 1997 PST
305 Tue Jun 10 17:32:01 1997 PDT
306 Sat Sep 22 18:19:20 2001 PDT
307 Wed Mar 15 08:14:01 2000 PST
308 Wed Mar 15 04:14:02 2000 PST
309 Wed Mar 15 02:14:03 2000 PST
310 Wed Mar 15 03:14:04 2000 PST
311 Wed Mar 15 01:14:05 2000 PST
312 Mon Feb 10 17:32:01 1997 PST
313 Mon Feb 10 17:32:01 1997 PST
314 Mon Feb 10 17:32:00 1997 PST
315 Mon Feb 10 17:32:01 1997 PST
316 Mon Feb 10 17:32:01 1997 PST
317 Mon Feb 10 17:32:01 1997 PST
318 Mon Feb 10 17:32:01 1997 PST
319 Mon Feb 10 17:32:01 1997 PST
320 Mon Feb 10 09:32:01 1997 PST
321 Mon Feb 10 09:32:01 1997 PST
322 Mon Feb 10 09:32:01 1997 PST
323 Mon Feb 10 14:32:01 1997 PST
324 Thu Jul 10 14:32:01 1997 PDT
325 Tue Jun 10 18:32:01 1997 PDT
326 Mon Feb 10 17:32:01 1997 PST
327 Tue Feb 11 17:32:01 1997 PST
328 Wed Feb 12 17:32:01 1997 PST
329 Thu Feb 13 17:32:01 1997 PST
330 Fri Feb 14 17:32:01 1997 PST
331 Sat Feb 15 17:32:01 1997 PST
332 Sun Feb 16 17:32:01 1997 PST
333 Tue Feb 16 17:32:01 0097 LMT BC
334 Sat Feb 16 17:32:01 0097 LMT
335 Thu Feb 16 17:32:01 0597 LMT
336 Tue Feb 16 17:32:01 1097 LMT
337 Sat Feb 16 17:32:01 1697 LMT
338 Thu Feb 16 17:32:01 1797 LMT
339 Tue Feb 16 17:32:01 1897 PST
340 Sun Feb 16 17:32:01 1997 PST
341 Sat Feb 16 17:32:01 2097 PST
342 Wed Feb 28 17:32:01 1996 PST
343 Thu Feb 29 17:32:01 1996 PST
344 Fri Mar 01 17:32:01 1996 PST
345 Mon Dec 30 17:32:01 1996 PST
346 Tue Dec 31 17:32:01 1996 PST
347 Wed Jan 01 17:32:01 1997 PST
348 Fri Feb 28 17:32:01 1997 PST
349 Sat Mar 01 17:32:01 1997 PST
350 Tue Dec 30 17:32:01 1997 PST
351 Wed Dec 31 17:32:01 1997 PST
352 Fri Dec 31 17:32:01 1999 PST
353 Sat Jan 01 17:32:01 2000 PST
354 Sun Dec 31 17:32:01 2000 PST
355 Mon Jan 01 17:32:01 2001 PST
356 (66 rows)
358 -- Check behavior at the boundaries of the timestamp range
359 SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
360 timestamptz
361 ---------------------------------
362 Sun Nov 23 16:07:02 4714 LMT BC
363 (1 row)
365 SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
366 timestamptz
367 ---------------------------------
368 Sun Nov 23 16:07:02 4714 LMT BC
369 (1 row)
371 SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
372 timestamptz
373 ---------------------------------
374 Sun Nov 23 16:07:02 4714 LMT BC
375 (1 row)
377 SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; -- out of range
378 ERROR: timestamp out of range: "4714-11-23 23:59:59+00 BC"
379 LINE 1: SELECT '4714-11-23 23:59:59+00 BC'::timestamptz;
380 ^
381 SELECT '294276-12-31 23:59:59+00'::timestamptz;
382 timestamptz
383 --------------------------------
384 Sun Dec 31 15:59:59 294276 PST
385 (1 row)
387 SELECT '294276-12-31 15:59:59-08'::timestamptz;
388 timestamptz
389 --------------------------------
390 Sun Dec 31 15:59:59 294276 PST
391 (1 row)
393 SELECT '294277-01-01 00:00:00+00'::timestamptz; -- out of range
394 ERROR: timestamp out of range: "294277-01-01 00:00:00+00"
395 LINE 1: SELECT '294277-01-01 00:00:00+00'::timestamptz;
396 ^
397 SELECT '294277-12-31 16:00:00-08'::timestamptz; -- out of range
398 ERROR: timestamp out of range: "294277-12-31 16:00:00-08"
399 LINE 1: SELECT '294277-12-31 16:00:00-08'::timestamptz;
400 ^
401 -- Demonstrate functions and operators
402 SELECT d1 FROM TIMESTAMPTZ_TBL
403 WHERE d1 > timestamp with time zone '1997-01-02';
404 d1
405 --------------------------------
406 infinity
407 Mon Feb 10 17:32:01 1997 PST
408 Mon Feb 10 17:32:01 1997 PST
409 Mon Feb 10 17:32:02 1997 PST
410 Mon Feb 10 17:32:01.4 1997 PST
411 Mon Feb 10 17:32:01.5 1997 PST
412 Mon Feb 10 17:32:01.6 1997 PST
413 Thu Jan 02 03:04:05 1997 PST
414 Mon Feb 10 17:32:01 1997 PST
415 Mon Feb 10 17:32:01 1997 PST
416 Mon Feb 10 17:32:01 1997 PST
417 Mon Feb 10 17:32:01 1997 PST
418 Tue Jun 10 17:32:01 1997 PDT
419 Sat Sep 22 18:19:20 2001 PDT
420 Wed Mar 15 08:14:01 2000 PST
421 Wed Mar 15 04:14:02 2000 PST
422 Wed Mar 15 02:14:03 2000 PST
423 Wed Mar 15 03:14:04 2000 PST
424 Wed Mar 15 01:14:05 2000 PST
425 Mon Feb 10 17:32:01 1997 PST
426 Mon Feb 10 17:32:01 1997 PST
427 Mon Feb 10 17:32:00 1997 PST
428 Mon Feb 10 17:32:01 1997 PST
429 Mon Feb 10 17:32:01 1997 PST
430 Mon Feb 10 17:32:01 1997 PST
431 Mon Feb 10 17:32:01 1997 PST
432 Mon Feb 10 17:32:01 1997 PST
433 Mon Feb 10 09:32:01 1997 PST
434 Mon Feb 10 09:32:01 1997 PST
435 Mon Feb 10 09:32:01 1997 PST
436 Mon Feb 10 14:32:01 1997 PST
437 Thu Jul 10 14:32:01 1997 PDT
438 Tue Jun 10 18:32:01 1997 PDT
439 Mon Feb 10 17:32:01 1997 PST
440 Tue Feb 11 17:32:01 1997 PST
441 Wed Feb 12 17:32:01 1997 PST
442 Thu Feb 13 17:32:01 1997 PST
443 Fri Feb 14 17:32:01 1997 PST
444 Sat Feb 15 17:32:01 1997 PST
445 Sun Feb 16 17:32:01 1997 PST
446 Sun Feb 16 17:32:01 1997 PST
447 Sat Feb 16 17:32:01 2097 PST
448 Fri Feb 28 17:32:01 1997 PST
449 Sat Mar 01 17:32:01 1997 PST
450 Tue Dec 30 17:32:01 1997 PST
451 Wed Dec 31 17:32:01 1997 PST
452 Fri Dec 31 17:32:01 1999 PST
453 Sat Jan 01 17:32:01 2000 PST
454 Sun Dec 31 17:32:01 2000 PST
455 Mon Jan 01 17:32:01 2001 PST
456 (50 rows)
458 SELECT d1 FROM TIMESTAMPTZ_TBL
459 WHERE d1 < timestamp with time zone '1997-01-02';
460 d1
461 ---------------------------------
462 -infinity
463 Wed Dec 31 16:00:00 1969 PST
464 Tue Feb 16 17:32:01 0097 LMT BC
465 Sat Feb 16 17:32:01 0097 LMT
466 Thu Feb 16 17:32:01 0597 LMT
467 Tue Feb 16 17:32:01 1097 LMT
468 Sat Feb 16 17:32:01 1697 LMT
469 Thu Feb 16 17:32:01 1797 LMT
470 Tue Feb 16 17:32:01 1897 PST
471 Wed Feb 28 17:32:01 1996 PST
472 Thu Feb 29 17:32:01 1996 PST
473 Fri Mar 01 17:32:01 1996 PST
474 Mon Dec 30 17:32:01 1996 PST
475 Tue Dec 31 17:32:01 1996 PST
476 Wed Jan 01 17:32:01 1997 PST
477 (15 rows)
479 SELECT d1 FROM TIMESTAMPTZ_TBL
480 WHERE d1 = timestamp with time zone '1997-01-02';
481 d1
482 ------------------------------
483 Thu Jan 02 00:00:00 1997 PST
484 (1 row)
486 SELECT d1 FROM TIMESTAMPTZ_TBL
487 WHERE d1 != timestamp with time zone '1997-01-02';
488 d1
489 ---------------------------------
490 -infinity
491 infinity
492 Wed Dec 31 16:00:00 1969 PST
493 Mon Feb 10 17:32:01 1997 PST
494 Mon Feb 10 17:32:01 1997 PST
495 Mon Feb 10 17:32:02 1997 PST
496 Mon Feb 10 17:32:01.4 1997 PST
497 Mon Feb 10 17:32:01.5 1997 PST
498 Mon Feb 10 17:32:01.6 1997 PST
499 Thu Jan 02 03:04:05 1997 PST
500 Mon Feb 10 17:32:01 1997 PST
501 Mon Feb 10 17:32:01 1997 PST
502 Mon Feb 10 17:32:01 1997 PST
503 Mon Feb 10 17:32:01 1997 PST
504 Tue Jun 10 17:32:01 1997 PDT
505 Sat Sep 22 18:19:20 2001 PDT
506 Wed Mar 15 08:14:01 2000 PST
507 Wed Mar 15 04:14:02 2000 PST
508 Wed Mar 15 02:14:03 2000 PST
509 Wed Mar 15 03:14:04 2000 PST
510 Wed Mar 15 01:14:05 2000 PST
511 Mon Feb 10 17:32:01 1997 PST
512 Mon Feb 10 17:32:01 1997 PST
513 Mon Feb 10 17:32:00 1997 PST
514 Mon Feb 10 17:32:01 1997 PST
515 Mon Feb 10 17:32:01 1997 PST
516 Mon Feb 10 17:32:01 1997 PST
517 Mon Feb 10 17:32:01 1997 PST
518 Mon Feb 10 17:32:01 1997 PST
519 Mon Feb 10 09:32:01 1997 PST
520 Mon Feb 10 09:32:01 1997 PST
521 Mon Feb 10 09:32:01 1997 PST
522 Mon Feb 10 14:32:01 1997 PST
523 Thu Jul 10 14:32:01 1997 PDT
524 Tue Jun 10 18:32:01 1997 PDT
525 Mon Feb 10 17:32:01 1997 PST
526 Tue Feb 11 17:32:01 1997 PST
527 Wed Feb 12 17:32:01 1997 PST
528 Thu Feb 13 17:32:01 1997 PST
529 Fri Feb 14 17:32:01 1997 PST
530 Sat Feb 15 17:32:01 1997 PST
531 Sun Feb 16 17:32:01 1997 PST
532 Tue Feb 16 17:32:01 0097 LMT BC
533 Sat Feb 16 17:32:01 0097 LMT
534 Thu Feb 16 17:32:01 0597 LMT
535 Tue Feb 16 17:32:01 1097 LMT
536 Sat Feb 16 17:32:01 1697 LMT
537 Thu Feb 16 17:32:01 1797 LMT
538 Tue Feb 16 17:32:01 1897 PST
539 Sun Feb 16 17:32:01 1997 PST
540 Sat Feb 16 17:32:01 2097 PST
541 Wed Feb 28 17:32:01 1996 PST
542 Thu Feb 29 17:32:01 1996 PST
543 Fri Mar 01 17:32:01 1996 PST
544 Mon Dec 30 17:32:01 1996 PST
545 Tue Dec 31 17:32:01 1996 PST
546 Wed Jan 01 17:32:01 1997 PST
547 Fri Feb 28 17:32:01 1997 PST
548 Sat Mar 01 17:32:01 1997 PST
549 Tue Dec 30 17:32:01 1997 PST
550 Wed Dec 31 17:32:01 1997 PST
551 Fri Dec 31 17:32:01 1999 PST
552 Sat Jan 01 17:32:01 2000 PST
553 Sun Dec 31 17:32:01 2000 PST
554 Mon Jan 01 17:32:01 2001 PST
555 (65 rows)
557 SELECT d1 FROM TIMESTAMPTZ_TBL
558 WHERE d1 <= timestamp with time zone '1997-01-02';
559 d1
560 ---------------------------------
561 -infinity
562 Wed Dec 31 16:00:00 1969 PST
563 Thu Jan 02 00:00:00 1997 PST
564 Tue Feb 16 17:32:01 0097 LMT BC
565 Sat Feb 16 17:32:01 0097 LMT
566 Thu Feb 16 17:32:01 0597 LMT
567 Tue Feb 16 17:32:01 1097 LMT
568 Sat Feb 16 17:32:01 1697 LMT
569 Thu Feb 16 17:32:01 1797 LMT
570 Tue Feb 16 17:32:01 1897 PST
571 Wed Feb 28 17:32:01 1996 PST
572 Thu Feb 29 17:32:01 1996 PST
573 Fri Mar 01 17:32:01 1996 PST
574 Mon Dec 30 17:32:01 1996 PST
575 Tue Dec 31 17:32:01 1996 PST
576 Wed Jan 01 17:32:01 1997 PST
577 (16 rows)
579 SELECT d1 FROM TIMESTAMPTZ_TBL
580 WHERE d1 >= timestamp with time zone '1997-01-02';
581 d1
582 --------------------------------
583 infinity
584 Mon Feb 10 17:32:01 1997 PST
585 Mon Feb 10 17:32:01 1997 PST
586 Mon Feb 10 17:32:02 1997 PST
587 Mon Feb 10 17:32:01.4 1997 PST
588 Mon Feb 10 17:32:01.5 1997 PST
589 Mon Feb 10 17:32:01.6 1997 PST
590 Thu Jan 02 00:00:00 1997 PST
591 Thu Jan 02 03:04:05 1997 PST
592 Mon Feb 10 17:32:01 1997 PST
593 Mon Feb 10 17:32:01 1997 PST
594 Mon Feb 10 17:32:01 1997 PST
595 Mon Feb 10 17:32:01 1997 PST
596 Tue Jun 10 17:32:01 1997 PDT
597 Sat Sep 22 18:19:20 2001 PDT
598 Wed Mar 15 08:14:01 2000 PST
599 Wed Mar 15 04:14:02 2000 PST
600 Wed Mar 15 02:14:03 2000 PST
601 Wed Mar 15 03:14:04 2000 PST
602 Wed Mar 15 01:14:05 2000 PST
603 Mon Feb 10 17:32:01 1997 PST
604 Mon Feb 10 17:32:01 1997 PST
605 Mon Feb 10 17:32:00 1997 PST
606 Mon Feb 10 17:32:01 1997 PST
607 Mon Feb 10 17:32:01 1997 PST
608 Mon Feb 10 17:32:01 1997 PST
609 Mon Feb 10 17:32:01 1997 PST
610 Mon Feb 10 17:32:01 1997 PST
611 Mon Feb 10 09:32:01 1997 PST
612 Mon Feb 10 09:32:01 1997 PST
613 Mon Feb 10 09:32:01 1997 PST
614 Mon Feb 10 14:32:01 1997 PST
615 Thu Jul 10 14:32:01 1997 PDT
616 Tue Jun 10 18:32:01 1997 PDT
617 Mon Feb 10 17:32:01 1997 PST
618 Tue Feb 11 17:32:01 1997 PST
619 Wed Feb 12 17:32:01 1997 PST
620 Thu Feb 13 17:32:01 1997 PST
621 Fri Feb 14 17:32:01 1997 PST
622 Sat Feb 15 17:32:01 1997 PST
623 Sun Feb 16 17:32:01 1997 PST
624 Sun Feb 16 17:32:01 1997 PST
625 Sat Feb 16 17:32:01 2097 PST
626 Fri Feb 28 17:32:01 1997 PST
627 Sat Mar 01 17:32:01 1997 PST
628 Tue Dec 30 17:32:01 1997 PST
629 Wed Dec 31 17:32:01 1997 PST
630 Fri Dec 31 17:32:01 1999 PST
631 Sat Jan 01 17:32:01 2000 PST
632 Sun Dec 31 17:32:01 2000 PST
633 Mon Jan 01 17:32:01 2001 PST
634 (51 rows)
636 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
637 FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
638 diff
639 ----------------------------------------
640 @ 9863 days 8 hours ago
641 @ 39 days 17 hours 32 mins 1 sec
642 @ 39 days 17 hours 32 mins 1 sec
643 @ 39 days 17 hours 32 mins 2 secs
644 @ 39 days 17 hours 32 mins 1.4 secs
645 @ 39 days 17 hours 32 mins 1.5 secs
646 @ 39 days 17 hours 32 mins 1.6 secs
647 @ 0
648 @ 3 hours 4 mins 5 secs
649 @ 39 days 17 hours 32 mins 1 sec
650 @ 39 days 17 hours 32 mins 1 sec
651 @ 39 days 17 hours 32 mins 1 sec
652 @ 39 days 17 hours 32 mins 1 sec
653 @ 159 days 16 hours 32 mins 1 sec
654 @ 1724 days 17 hours 19 mins 20 secs
655 @ 1168 days 8 hours 14 mins 1 sec
656 @ 1168 days 4 hours 14 mins 2 secs
657 @ 1168 days 2 hours 14 mins 3 secs
658 @ 1168 days 3 hours 14 mins 4 secs
659 @ 1168 days 1 hour 14 mins 5 secs
660 @ 39 days 17 hours 32 mins 1 sec
661 @ 39 days 17 hours 32 mins 1 sec
662 @ 39 days 17 hours 32 mins
663 @ 39 days 17 hours 32 mins 1 sec
664 @ 39 days 17 hours 32 mins 1 sec
665 @ 39 days 17 hours 32 mins 1 sec
666 @ 39 days 17 hours 32 mins 1 sec
667 @ 39 days 17 hours 32 mins 1 sec
668 @ 39 days 9 hours 32 mins 1 sec
669 @ 39 days 9 hours 32 mins 1 sec
670 @ 39 days 9 hours 32 mins 1 sec
671 @ 39 days 14 hours 32 mins 1 sec
672 @ 189 days 13 hours 32 mins 1 sec
673 @ 159 days 17 hours 32 mins 1 sec
674 @ 39 days 17 hours 32 mins 1 sec
675 @ 40 days 17 hours 32 mins 1 sec
676 @ 41 days 17 hours 32 mins 1 sec
677 @ 42 days 17 hours 32 mins 1 sec
678 @ 43 days 17 hours 32 mins 1 sec
679 @ 44 days 17 hours 32 mins 1 sec
680 @ 45 days 17 hours 32 mins 1 sec
681 @ 45 days 17 hours 32 mins 1 sec
682 @ 308 days 6 hours 27 mins 59 secs ago
683 @ 307 days 6 hours 27 mins 59 secs ago
684 @ 306 days 6 hours 27 mins 59 secs ago
685 @ 2 days 6 hours 27 mins 59 secs ago
686 @ 1 day 6 hours 27 mins 59 secs ago
687 @ 6 hours 27 mins 59 secs ago
688 @ 57 days 17 hours 32 mins 1 sec
689 @ 58 days 17 hours 32 mins 1 sec
690 @ 362 days 17 hours 32 mins 1 sec
691 @ 363 days 17 hours 32 mins 1 sec
692 @ 1093 days 17 hours 32 mins 1 sec
693 @ 1094 days 17 hours 32 mins 1 sec
694 @ 1459 days 17 hours 32 mins 1 sec
695 @ 1460 days 17 hours 32 mins 1 sec
696 (56 rows)
698 SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
699 week_trunc
700 ------------------------------
701 Mon Feb 23 00:00:00 2004 PST
702 (1 row)
704 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
705 sydney_trunc
706 ------------------------------
707 Fri Feb 16 05:00:00 2001 PST
708 (1 row)
710 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
711 gmt_trunc
712 ------------------------------
713 Thu Feb 15 16:00:00 2001 PST
714 (1 row)
716 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
717 vet_trunc
718 ------------------------------
719 Thu Feb 15 20:00:00 2001 PST
720 (1 row)
722 -- verify date_bin behaves the same as date_trunc for relevant intervals
723 SELECT
724 str,
725 interval,
726 date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS equal
727 FROM (
728 VALUES
729 ('day', '1 d'),
730 ('hour', '1 h'),
731 ('minute', '1 m'),
732 ('second', '1 s'),
733 ('millisecond', '1 ms'),
734 ('microsecond', '1 us')
735 ) intervals (str, interval),
736 (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
737 str | interval | equal
738 -------------+----------+-------
739 day | 1 d | t
740 hour | 1 h | t
741 minute | 1 m | t
742 second | 1 s | t
743 millisecond | 1 ms | t
744 microsecond | 1 us | t
745 (6 rows)
747 -- bin timestamps into arbitrary intervals
748 SELECT
749 interval,
750 ts,
751 origin,
752 date_bin(interval::interval, ts, origin)
753 FROM (
754 VALUES
755 ('15 days'),
756 ('2 hours'),
757 ('1 hour 30 minutes'),
758 ('15 minutes'),
759 ('10 seconds'),
760 ('100 milliseconds'),
761 ('250 microseconds')
762 ) intervals (interval),
763 (VALUES (timestamptz '2020-02-11 15:44:17.71393')) ts (ts),
764 (VALUES (timestamptz '2001-01-01')) origin (origin);
765 interval | ts | origin | date_bin
766 -------------------+------------------------------------+------------------------------+------------------------------------
767 15 days | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Thu Feb 06 00:00:00 2020 PST
768 2 hours | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 14:00:00 2020 PST
769 1 hour 30 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:00:00 2020 PST
770 15 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:30:00 2020 PST
771 10 seconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:10 2020 PST
772 100 milliseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.7 2020 PST
773 250 microseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.71375 2020 PST
774 (7 rows)
776 -- shift bins using the origin parameter:
777 SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00');
778 date_bin
779 ------------------------------
780 Fri Jan 31 16:57:30 2020 PST
781 (1 row)
783 -- test roundoff edge case when source < origin
784 SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00');
785 date_bin
786 ------------------------------
787 Thu Feb 01 15:00:00 2024 PST
788 (1 row)
790 -- disallow intervals with months or years
791 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');
792 ERROR: timestamps cannot be binned into intervals containing months or years
793 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');
794 ERROR: timestamps cannot be binned into intervals containing months or years
795 -- disallow zero intervals
796 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');
797 ERROR: stride must be greater than zero
798 -- disallow negative intervals
799 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');
800 ERROR: stride must be greater than zero
801 -- test overflow cases
802 select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC');
803 ERROR: interval out of range
804 select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz);
805 ERROR: interval out of range
806 select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz);
807 ERROR: timestamp out of range
808 -- Test casting within a BETWEEN qualifier
809 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
810 FROM TIMESTAMPTZ_TBL
811 WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
812 diff
813 ----------------------------------------
814 @ 9863 days 8 hours ago
815 @ 39 days 17 hours 32 mins 1 sec
816 @ 39 days 17 hours 32 mins 1 sec
817 @ 39 days 17 hours 32 mins 2 secs
818 @ 39 days 17 hours 32 mins 1.4 secs
819 @ 39 days 17 hours 32 mins 1.5 secs
820 @ 39 days 17 hours 32 mins 1.6 secs
821 @ 0
822 @ 3 hours 4 mins 5 secs
823 @ 39 days 17 hours 32 mins 1 sec
824 @ 39 days 17 hours 32 mins 1 sec
825 @ 39 days 17 hours 32 mins 1 sec
826 @ 39 days 17 hours 32 mins 1 sec
827 @ 159 days 16 hours 32 mins 1 sec
828 @ 1724 days 17 hours 19 mins 20 secs
829 @ 1168 days 8 hours 14 mins 1 sec
830 @ 1168 days 4 hours 14 mins 2 secs
831 @ 1168 days 2 hours 14 mins 3 secs
832 @ 1168 days 3 hours 14 mins 4 secs
833 @ 1168 days 1 hour 14 mins 5 secs
834 @ 39 days 17 hours 32 mins 1 sec
835 @ 39 days 17 hours 32 mins 1 sec
836 @ 39 days 17 hours 32 mins
837 @ 39 days 17 hours 32 mins 1 sec
838 @ 39 days 17 hours 32 mins 1 sec
839 @ 39 days 17 hours 32 mins 1 sec
840 @ 39 days 17 hours 32 mins 1 sec
841 @ 39 days 17 hours 32 mins 1 sec
842 @ 39 days 9 hours 32 mins 1 sec
843 @ 39 days 9 hours 32 mins 1 sec
844 @ 39 days 9 hours 32 mins 1 sec
845 @ 39 days 14 hours 32 mins 1 sec
846 @ 189 days 13 hours 32 mins 1 sec
847 @ 159 days 17 hours 32 mins 1 sec
848 @ 39 days 17 hours 32 mins 1 sec
849 @ 40 days 17 hours 32 mins 1 sec
850 @ 41 days 17 hours 32 mins 1 sec
851 @ 42 days 17 hours 32 mins 1 sec
852 @ 43 days 17 hours 32 mins 1 sec
853 @ 44 days 17 hours 32 mins 1 sec
854 @ 45 days 17 hours 32 mins 1 sec
855 @ 45 days 17 hours 32 mins 1 sec
856 @ 308 days 6 hours 27 mins 59 secs ago
857 @ 307 days 6 hours 27 mins 59 secs ago
858 @ 306 days 6 hours 27 mins 59 secs ago
859 @ 2 days 6 hours 27 mins 59 secs ago
860 @ 1 day 6 hours 27 mins 59 secs ago
861 @ 6 hours 27 mins 59 secs ago
862 @ 57 days 17 hours 32 mins 1 sec
863 @ 58 days 17 hours 32 mins 1 sec
864 @ 362 days 17 hours 32 mins 1 sec
865 @ 363 days 17 hours 32 mins 1 sec
866 @ 1093 days 17 hours 32 mins 1 sec
867 @ 1094 days 17 hours 32 mins 1 sec
868 @ 1459 days 17 hours 32 mins 1 sec
869 @ 1460 days 17 hours 32 mins 1 sec
870 (56 rows)
872 -- DATE_PART (timestamptz_part)
873 SELECT d1 as timestamptz,
874 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
875 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
876 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
877 FROM TIMESTAMPTZ_TBL;
878 timestamptz | year | month | day | hour | minute | second
879 ---------------------------------+-----------+-------+-----+------+--------+--------
880 -infinity | -Infinity | | | | |
881 infinity | Infinity | | | | |
882 Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0
883 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
884 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
885 Mon Feb 10 17:32:02 1997 PST | 1997 | 2 | 10 | 17 | 32 | 2
886 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4
887 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5
888 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6
889 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0
890 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5
891 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
892 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
893 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
894 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
895 Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1
896 Sat Sep 22 18:19:20 2001 PDT | 2001 | 9 | 22 | 18 | 19 | 20
897 Wed Mar 15 08:14:01 2000 PST | 2000 | 3 | 15 | 8 | 14 | 1
898 Wed Mar 15 04:14:02 2000 PST | 2000 | 3 | 15 | 4 | 14 | 2
899 Wed Mar 15 02:14:03 2000 PST | 2000 | 3 | 15 | 2 | 14 | 3
900 Wed Mar 15 03:14:04 2000 PST | 2000 | 3 | 15 | 3 | 14 | 4
901 Wed Mar 15 01:14:05 2000 PST | 2000 | 3 | 15 | 1 | 14 | 5
902 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
903 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
904 Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0
905 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
906 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
907 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
908 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
909 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
910 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
911 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
912 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
913 Mon Feb 10 14:32:01 1997 PST | 1997 | 2 | 10 | 14 | 32 | 1
914 Thu Jul 10 14:32:01 1997 PDT | 1997 | 7 | 10 | 14 | 32 | 1
915 Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1
916 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
917 Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1
918 Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1
919 Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1
920 Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1
921 Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1
922 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
923 Tue Feb 16 17:32:01 0097 LMT BC | -97 | 2 | 16 | 17 | 32 | 1
924 Sat Feb 16 17:32:01 0097 LMT | 97 | 2 | 16 | 17 | 32 | 1
925 Thu Feb 16 17:32:01 0597 LMT | 597 | 2 | 16 | 17 | 32 | 1
926 Tue Feb 16 17:32:01 1097 LMT | 1097 | 2 | 16 | 17 | 32 | 1
927 Sat Feb 16 17:32:01 1697 LMT | 1697 | 2 | 16 | 17 | 32 | 1
928 Thu Feb 16 17:32:01 1797 LMT | 1797 | 2 | 16 | 17 | 32 | 1
929 Tue Feb 16 17:32:01 1897 PST | 1897 | 2 | 16 | 17 | 32 | 1
930 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
931 Sat Feb 16 17:32:01 2097 PST | 2097 | 2 | 16 | 17 | 32 | 1
932 Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1
933 Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1
934 Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1
935 Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1
936 Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1
937 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1
938 Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1
939 Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1
940 Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1
941 Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1
942 Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1
943 Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
944 Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
945 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
946 (66 rows)
948 SELECT d1 as timestamptz,
949 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
950 date_part( 'usec', d1) AS usec
951 FROM TIMESTAMPTZ_TBL;
952 timestamptz | quarter | msec | usec
953 ---------------------------------+---------+-------+----------
954 -infinity | | |
955 infinity | | |
956 Wed Dec 31 16:00:00 1969 PST | 4 | 0 | 0
957 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
958 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
959 Mon Feb 10 17:32:02 1997 PST | 1 | 2000 | 2000000
960 Mon Feb 10 17:32:01.4 1997 PST | 1 | 1400 | 1400000
961 Mon Feb 10 17:32:01.5 1997 PST | 1 | 1500 | 1500000
962 Mon Feb 10 17:32:01.6 1997 PST | 1 | 1600 | 1600000
963 Thu Jan 02 00:00:00 1997 PST | 1 | 0 | 0
964 Thu Jan 02 03:04:05 1997 PST | 1 | 5000 | 5000000
965 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
966 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
967 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
968 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
969 Tue Jun 10 17:32:01 1997 PDT | 2 | 1000 | 1000000
970 Sat Sep 22 18:19:20 2001 PDT | 3 | 20000 | 20000000
971 Wed Mar 15 08:14:01 2000 PST | 1 | 1000 | 1000000
972 Wed Mar 15 04:14:02 2000 PST | 1 | 2000 | 2000000
973 Wed Mar 15 02:14:03 2000 PST | 1 | 3000 | 3000000
974 Wed Mar 15 03:14:04 2000 PST | 1 | 4000 | 4000000
975 Wed Mar 15 01:14:05 2000 PST | 1 | 5000 | 5000000
976 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
977 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
978 Mon Feb 10 17:32:00 1997 PST | 1 | 0 | 0
979 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
980 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
981 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
982 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
983 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
984 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
985 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
986 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
987 Mon Feb 10 14:32:01 1997 PST | 1 | 1000 | 1000000
988 Thu Jul 10 14:32:01 1997 PDT | 3 | 1000 | 1000000
989 Tue Jun 10 18:32:01 1997 PDT | 2 | 1000 | 1000000
990 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
991 Tue Feb 11 17:32:01 1997 PST | 1 | 1000 | 1000000
992 Wed Feb 12 17:32:01 1997 PST | 1 | 1000 | 1000000
993 Thu Feb 13 17:32:01 1997 PST | 1 | 1000 | 1000000
994 Fri Feb 14 17:32:01 1997 PST | 1 | 1000 | 1000000
995 Sat Feb 15 17:32:01 1997 PST | 1 | 1000 | 1000000
996 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
997 Tue Feb 16 17:32:01 0097 LMT BC | 1 | 1000 | 1000000
998 Sat Feb 16 17:32:01 0097 LMT | 1 | 1000 | 1000000
999 Thu Feb 16 17:32:01 0597 LMT | 1 | 1000 | 1000000
1000 Tue Feb 16 17:32:01 1097 LMT | 1 | 1000 | 1000000
1001 Sat Feb 16 17:32:01 1697 LMT | 1 | 1000 | 1000000
1002 Thu Feb 16 17:32:01 1797 LMT | 1 | 1000 | 1000000
1003 Tue Feb 16 17:32:01 1897 PST | 1 | 1000 | 1000000
1004 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
1005 Sat Feb 16 17:32:01 2097 PST | 1 | 1000 | 1000000
1006 Wed Feb 28 17:32:01 1996 PST | 1 | 1000 | 1000000
1007 Thu Feb 29 17:32:01 1996 PST | 1 | 1000 | 1000000
1008 Fri Mar 01 17:32:01 1996 PST | 1 | 1000 | 1000000
1009 Mon Dec 30 17:32:01 1996 PST | 4 | 1000 | 1000000
1010 Tue Dec 31 17:32:01 1996 PST | 4 | 1000 | 1000000
1011 Wed Jan 01 17:32:01 1997 PST | 1 | 1000 | 1000000
1012 Fri Feb 28 17:32:01 1997 PST | 1 | 1000 | 1000000
1013 Sat Mar 01 17:32:01 1997 PST | 1 | 1000 | 1000000
1014 Tue Dec 30 17:32:01 1997 PST | 4 | 1000 | 1000000
1015 Wed Dec 31 17:32:01 1997 PST | 4 | 1000 | 1000000
1016 Fri Dec 31 17:32:01 1999 PST | 4 | 1000 | 1000000
1017 Sat Jan 01 17:32:01 2000 PST | 1 | 1000 | 1000000
1018 Sun Dec 31 17:32:01 2000 PST | 4 | 1000 | 1000000
1019 Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000
1020 (66 rows)
1022 SELECT d1 as timestamptz,
1023 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
1024 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
1025 date_part( 'doy', d1) AS doy
1026 FROM TIMESTAMPTZ_TBL;
1027 timestamptz | isoyear | week | isodow | dow | doy
1028 ---------------------------------+-----------+------+--------+-----+-----
1029 -infinity | -Infinity | | | |
1030 infinity | Infinity | | | |
1031 Wed Dec 31 16:00:00 1969 PST | 1970 | 1 | 3 | 3 | 365
1032 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1033 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1034 Mon Feb 10 17:32:02 1997 PST | 1997 | 7 | 1 | 1 | 41
1035 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 7 | 1 | 1 | 41
1036 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 7 | 1 | 1 | 41
1037 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 7 | 1 | 1 | 41
1038 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 4 | 4 | 2
1039 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 4 | 4 | 2
1040 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1041 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1042 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1043 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1044 Tue Jun 10 17:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
1045 Sat Sep 22 18:19:20 2001 PDT | 2001 | 38 | 6 | 6 | 265
1046 Wed Mar 15 08:14:01 2000 PST | 2000 | 11 | 3 | 3 | 75
1047 Wed Mar 15 04:14:02 2000 PST | 2000 | 11 | 3 | 3 | 75
1048 Wed Mar 15 02:14:03 2000 PST | 2000 | 11 | 3 | 3 | 75
1049 Wed Mar 15 03:14:04 2000 PST | 2000 | 11 | 3 | 3 | 75
1050 Wed Mar 15 01:14:05 2000 PST | 2000 | 11 | 3 | 3 | 75
1051 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1052 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1053 Mon Feb 10 17:32:00 1997 PST | 1997 | 7 | 1 | 1 | 41
1054 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1055 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1056 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1057 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1058 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1059 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1060 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1061 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1062 Mon Feb 10 14:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1063 Thu Jul 10 14:32:01 1997 PDT | 1997 | 28 | 4 | 4 | 191
1064 Tue Jun 10 18:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
1065 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1066 Tue Feb 11 17:32:01 1997 PST | 1997 | 7 | 2 | 2 | 42
1067 Wed Feb 12 17:32:01 1997 PST | 1997 | 7 | 3 | 3 | 43
1068 Thu Feb 13 17:32:01 1997 PST | 1997 | 7 | 4 | 4 | 44
1069 Fri Feb 14 17:32:01 1997 PST | 1997 | 7 | 5 | 5 | 45
1070 Sat Feb 15 17:32:01 1997 PST | 1997 | 7 | 6 | 6 | 46
1071 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1072 Tue Feb 16 17:32:01 0097 LMT BC | -97 | 7 | 2 | 2 | 47
1073 Sat Feb 16 17:32:01 0097 LMT | 97 | 7 | 6 | 6 | 47
1074 Thu Feb 16 17:32:01 0597 LMT | 597 | 7 | 4 | 4 | 47
1075 Tue Feb 16 17:32:01 1097 LMT | 1097 | 7 | 2 | 2 | 47
1076 Sat Feb 16 17:32:01 1697 LMT | 1697 | 7 | 6 | 6 | 47
1077 Thu Feb 16 17:32:01 1797 LMT | 1797 | 7 | 4 | 4 | 47
1078 Tue Feb 16 17:32:01 1897 PST | 1897 | 7 | 2 | 2 | 47
1079 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1080 Sat Feb 16 17:32:01 2097 PST | 2097 | 7 | 6 | 6 | 47
1081 Wed Feb 28 17:32:01 1996 PST | 1996 | 9 | 3 | 3 | 59
1082 Thu Feb 29 17:32:01 1996 PST | 1996 | 9 | 4 | 4 | 60
1083 Fri Mar 01 17:32:01 1996 PST | 1996 | 9 | 5 | 5 | 61
1084 Mon Dec 30 17:32:01 1996 PST | 1997 | 1 | 1 | 1 | 365
1085 Tue Dec 31 17:32:01 1996 PST | 1997 | 1 | 2 | 2 | 366
1086 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 3 | 3 | 1
1087 Fri Feb 28 17:32:01 1997 PST | 1997 | 9 | 5 | 5 | 59
1088 Sat Mar 01 17:32:01 1997 PST | 1997 | 9 | 6 | 6 | 60
1089 Tue Dec 30 17:32:01 1997 PST | 1998 | 1 | 2 | 2 | 364
1090 Wed Dec 31 17:32:01 1997 PST | 1998 | 1 | 3 | 3 | 365
1091 Fri Dec 31 17:32:01 1999 PST | 1999 | 52 | 5 | 5 | 365
1092 Sat Jan 01 17:32:01 2000 PST | 1999 | 52 | 6 | 6 | 1
1093 Sun Dec 31 17:32:01 2000 PST | 2000 | 52 | 7 | 0 | 366
1094 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 1 | 1
1095 (66 rows)
1097 SELECT d1 as timestamptz,
1098 date_part( 'decade', d1) AS decade,
1099 date_part( 'century', d1) AS century,
1100 date_part( 'millennium', d1) AS millennium,
1101 round(date_part( 'julian', d1)) AS julian,
1102 date_part( 'epoch', d1) AS epoch
1103 FROM TIMESTAMPTZ_TBL;
1104 timestamptz | decade | century | millennium | julian | epoch
1105 ---------------------------------+-----------+-----------+------------+-----------+--------------
1106 -infinity | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1107 infinity | Infinity | Infinity | Infinity | Infinity | Infinity
1108 Wed Dec 31 16:00:00 1969 PST | 196 | 20 | 2 | 2440588 | 0
1109 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1110 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1111 Mon Feb 10 17:32:02 1997 PST | 199 | 20 | 2 | 2450491 | 855624722
1112 Mon Feb 10 17:32:01.4 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.4
1113 Mon Feb 10 17:32:01.5 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.5
1114 Mon Feb 10 17:32:01.6 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.6
1115 Thu Jan 02 00:00:00 1997 PST | 199 | 20 | 2 | 2450451 | 852192000
1116 Thu Jan 02 03:04:05 1997 PST | 199 | 20 | 2 | 2450451 | 852203045
1117 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1118 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1119 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1120 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1121 Tue Jun 10 17:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865989121
1122 Sat Sep 22 18:19:20 2001 PDT | 200 | 21 | 3 | 2452176 | 1001207960
1123 Wed Mar 15 08:14:01 2000 PST | 200 | 20 | 2 | 2451619 | 953136841
1124 Wed Mar 15 04:14:02 2000 PST | 200 | 20 | 2 | 2451619 | 953122442
1125 Wed Mar 15 02:14:03 2000 PST | 200 | 20 | 2 | 2451619 | 953115243
1126 Wed Mar 15 03:14:04 2000 PST | 200 | 20 | 2 | 2451619 | 953118844
1127 Wed Mar 15 01:14:05 2000 PST | 200 | 20 | 2 | 2451619 | 953111645
1128 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1129 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1130 Mon Feb 10 17:32:00 1997 PST | 199 | 20 | 2 | 2450491 | 855624720
1131 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1132 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1133 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1134 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1135 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1136 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1137 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1138 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1139 Mon Feb 10 14:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855613921
1140 Thu Jul 10 14:32:01 1997 PDT | 199 | 20 | 2 | 2450641 | 868570321
1141 Tue Jun 10 18:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865992721
1142 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1143 Tue Feb 11 17:32:01 1997 PST | 199 | 20 | 2 | 2450492 | 855711121
1144 Wed Feb 12 17:32:01 1997 PST | 199 | 20 | 2 | 2450493 | 855797521
1145 Thu Feb 13 17:32:01 1997 PST | 199 | 20 | 2 | 2450494 | 855883921
1146 Fri Feb 14 17:32:01 1997 PST | 199 | 20 | 2 | 2450495 | 855970321
1147 Sat Feb 15 17:32:01 1997 PST | 199 | 20 | 2 | 2450496 | 856056721
1148 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1149 Tue Feb 16 17:32:01 0097 LMT BC | -10 | -1 | -1 | 1686043 | -65192682901
1150 Sat Feb 16 17:32:01 0097 LMT | 9 | 1 | 1 | 1756537 | -59102001301
1151 Thu Feb 16 17:32:01 0597 LMT | 59 | 6 | 1 | 1939158 | -43323546901
1152 Tue Feb 16 17:32:01 1097 LMT | 109 | 11 | 2 | 2121779 | -27545092501
1153 Sat Feb 16 17:32:01 1697 LMT | 169 | 17 | 2 | 2340925 | -8610878101
1154 Thu Feb 16 17:32:01 1797 LMT | 179 | 18 | 2 | 2377449 | -5455204501
1155 Tue Feb 16 17:32:01 1897 PST | 189 | 19 | 2 | 2413973 | -2299530479
1156 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1157 Sat Feb 16 17:32:01 2097 PST | 209 | 21 | 3 | 2487022 | 4011903121
1158 Wed Feb 28 17:32:01 1996 PST | 199 | 20 | 2 | 2450143 | 825557521
1159 Thu Feb 29 17:32:01 1996 PST | 199 | 20 | 2 | 2450144 | 825643921
1160 Fri Mar 01 17:32:01 1996 PST | 199 | 20 | 2 | 2450145 | 825730321
1161 Mon Dec 30 17:32:01 1996 PST | 199 | 20 | 2 | 2450449 | 851995921
1162 Tue Dec 31 17:32:01 1996 PST | 199 | 20 | 2 | 2450450 | 852082321
1163 Wed Jan 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450451 | 852168721
1164 Fri Feb 28 17:32:01 1997 PST | 199 | 20 | 2 | 2450509 | 857179921
1165 Sat Mar 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450510 | 857266321
1166 Tue Dec 30 17:32:01 1997 PST | 199 | 20 | 2 | 2450814 | 883531921
1167 Wed Dec 31 17:32:01 1997 PST | 199 | 20 | 2 | 2450815 | 883618321
1168 Fri Dec 31 17:32:01 1999 PST | 199 | 20 | 2 | 2451545 | 946690321
1169 Sat Jan 01 17:32:01 2000 PST | 200 | 20 | 2 | 2451546 | 946776721
1170 Sun Dec 31 17:32:01 2000 PST | 200 | 20 | 2 | 2451911 | 978312721
1171 Mon Jan 01 17:32:01 2001 PST | 200 | 21 | 3 | 2451912 | 978399121
1172 (66 rows)
1174 SELECT d1 as timestamptz,
1175 date_part( 'timezone', d1) AS timezone,
1176 date_part( 'timezone_hour', d1) AS timezone_hour,
1177 date_part( 'timezone_minute', d1) AS timezone_minute
1178 FROM TIMESTAMPTZ_TBL;
1179 timestamptz | timezone | timezone_hour | timezone_minute
1180 ---------------------------------+----------+---------------+-----------------
1181 -infinity | | |
1182 infinity | | |
1183 Wed Dec 31 16:00:00 1969 PST | -28800 | -8 | 0
1184 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1185 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1186 Mon Feb 10 17:32:02 1997 PST | -28800 | -8 | 0
1187 Mon Feb 10 17:32:01.4 1997 PST | -28800 | -8 | 0
1188 Mon Feb 10 17:32:01.5 1997 PST | -28800 | -8 | 0
1189 Mon Feb 10 17:32:01.6 1997 PST | -28800 | -8 | 0
1190 Thu Jan 02 00:00:00 1997 PST | -28800 | -8 | 0
1191 Thu Jan 02 03:04:05 1997 PST | -28800 | -8 | 0
1192 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1193 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1194 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1195 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1196 Tue Jun 10 17:32:01 1997 PDT | -25200 | -7 | 0
1197 Sat Sep 22 18:19:20 2001 PDT | -25200 | -7 | 0
1198 Wed Mar 15 08:14:01 2000 PST | -28800 | -8 | 0
1199 Wed Mar 15 04:14:02 2000 PST | -28800 | -8 | 0
1200 Wed Mar 15 02:14:03 2000 PST | -28800 | -8 | 0
1201 Wed Mar 15 03:14:04 2000 PST | -28800 | -8 | 0
1202 Wed Mar 15 01:14:05 2000 PST | -28800 | -8 | 0
1203 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1204 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1205 Mon Feb 10 17:32:00 1997 PST | -28800 | -8 | 0
1206 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1207 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1208 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1209 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1210 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1211 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1212 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1213 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1214 Mon Feb 10 14:32:01 1997 PST | -28800 | -8 | 0
1215 Thu Jul 10 14:32:01 1997 PDT | -25200 | -7 | 0
1216 Tue Jun 10 18:32:01 1997 PDT | -25200 | -7 | 0
1217 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1218 Tue Feb 11 17:32:01 1997 PST | -28800 | -8 | 0
1219 Wed Feb 12 17:32:01 1997 PST | -28800 | -8 | 0
1220 Thu Feb 13 17:32:01 1997 PST | -28800 | -8 | 0
1221 Fri Feb 14 17:32:01 1997 PST | -28800 | -8 | 0
1222 Sat Feb 15 17:32:01 1997 PST | -28800 | -8 | 0
1223 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1224 Tue Feb 16 17:32:01 0097 LMT BC | -28378 | -7 | -52
1225 Sat Feb 16 17:32:01 0097 LMT | -28378 | -7 | -52
1226 Thu Feb 16 17:32:01 0597 LMT | -28378 | -7 | -52
1227 Tue Feb 16 17:32:01 1097 LMT | -28378 | -7 | -52
1228 Sat Feb 16 17:32:01 1697 LMT | -28378 | -7 | -52
1229 Thu Feb 16 17:32:01 1797 LMT | -28378 | -7 | -52
1230 Tue Feb 16 17:32:01 1897 PST | -28800 | -8 | 0
1231 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1232 Sat Feb 16 17:32:01 2097 PST | -28800 | -8 | 0
1233 Wed Feb 28 17:32:01 1996 PST | -28800 | -8 | 0
1234 Thu Feb 29 17:32:01 1996 PST | -28800 | -8 | 0
1235 Fri Mar 01 17:32:01 1996 PST | -28800 | -8 | 0
1236 Mon Dec 30 17:32:01 1996 PST | -28800 | -8 | 0
1237 Tue Dec 31 17:32:01 1996 PST | -28800 | -8 | 0
1238 Wed Jan 01 17:32:01 1997 PST | -28800 | -8 | 0
1239 Fri Feb 28 17:32:01 1997 PST | -28800 | -8 | 0
1240 Sat Mar 01 17:32:01 1997 PST | -28800 | -8 | 0
1241 Tue Dec 30 17:32:01 1997 PST | -28800 | -8 | 0
1242 Wed Dec 31 17:32:01 1997 PST | -28800 | -8 | 0
1243 Fri Dec 31 17:32:01 1999 PST | -28800 | -8 | 0
1244 Sat Jan 01 17:32:01 2000 PST | -28800 | -8 | 0
1245 Sun Dec 31 17:32:01 2000 PST | -28800 | -8 | 0
1246 Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0
1247 (66 rows)
1249 -- extract implementation is mostly the same as date_part, so only
1250 -- test a few cases for additional coverage.
1251 SELECT d1 as "timestamp",
1252 extract(microseconds from d1) AS microseconds,
1253 extract(milliseconds from d1) AS milliseconds,
1254 extract(seconds from d1) AS seconds,
1255 round(extract(julian from d1)) AS julian,
1256 extract(epoch from d1) AS epoch
1257 FROM TIMESTAMPTZ_TBL;
1258 timestamp | microseconds | milliseconds | seconds | julian | epoch
1259 ---------------------------------+--------------+--------------+-----------+-----------+---------------------
1260 -infinity | | | | -Infinity | -Infinity
1261 infinity | | | | Infinity | Infinity
1262 Wed Dec 31 16:00:00 1969 PST | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
1263 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1264 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1265 Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000.000 | 2.000000 | 2450491 | 855624722.000000
1266 Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400.000 | 1.400000 | 2450491 | 855624721.400000
1267 Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500.000 | 1.500000 | 2450491 | 855624721.500000
1268 Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600.000 | 1.600000 | 2450491 | 855624721.600000
1269 Thu Jan 02 00:00:00 1997 PST | 0 | 0.000 | 0.000000 | 2450451 | 852192000.000000
1270 Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000.000 | 5.000000 | 2450451 | 852203045.000000
1271 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1272 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1273 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1274 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1275 Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865989121.000000
1276 Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001207960.000000
1277 Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451619 | 953136841.000000
1278 Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000.000 | 2.000000 | 2451619 | 953122442.000000
1279 Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000.000 | 3.000000 | 2451619 | 953115243.000000
1280 Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000.000 | 4.000000 | 2451619 | 953118844.000000
1281 Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000.000 | 5.000000 | 2451619 | 953111645.000000
1282 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1283 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1284 Mon Feb 10 17:32:00 1997 PST | 0 | 0.000 | 0.000000 | 2450491 | 855624720.000000
1285 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1286 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1287 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1288 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1289 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1290 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1291 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1292 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1293 Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855613921.000000
1294 Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450641 | 868570321.000000
1295 Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865992721.000000
1296 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1297 Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450492 | 855711121.000000
1298 Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450493 | 855797521.000000
1299 Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450494 | 855883921.000000
1300 Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450495 | 855970321.000000
1301 Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450496 | 856056721.000000
1302 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1303 Tue Feb 16 17:32:01 0097 LMT BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192682901.000000
1304 Sat Feb 16 17:32:01 0097 LMT | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102001301.000000
1305 Thu Feb 16 17:32:01 0597 LMT | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323546901.000000
1306 Tue Feb 16 17:32:01 1097 LMT | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545092501.000000
1307 Sat Feb 16 17:32:01 1697 LMT | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610878101.000000
1308 Thu Feb 16 17:32:01 1797 LMT | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455204501.000000
1309 Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299530479.000000
1310 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1311 Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011903121.000000
1312 Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450143 | 825557521.000000
1313 Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450144 | 825643921.000000
1314 Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450145 | 825730321.000000
1315 Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450449 | 851995921.000000
1316 Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450450 | 852082321.000000
1317 Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450451 | 852168721.000000
1318 Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450509 | 857179921.000000
1319 Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450510 | 857266321.000000
1320 Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450814 | 883531921.000000
1321 Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450815 | 883618321.000000
1322 Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000.000 | 1.000000 | 2451545 | 946690321.000000
1323 Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451546 | 946776721.000000
1324 Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451911 | 978312721.000000
1325 Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000.000 | 1.000000 | 2451912 | 978399121.000000
1326 (66 rows)
1328 -- value near upper bound uses special case in code
1329 SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz);
1330 date_part
1331 ---------------
1332 9224097091200
1333 (1 row)
1335 SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
1336 extract
1337 ----------------------
1338 9224097091200.000000
1339 (1 row)
1341 -- another internal overflow test case
1342 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
1343 extract
1344 --------------------
1345 95617584000.000000
1346 (1 row)
1348 -- test edge-case overflow in timestamp subtraction
1349 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224193 UTC' AS ok;
1350 ok
1351 -----------------------------------------
1352 @ 106751991 days 4 hours 54.775807 secs
1353 (1 row)
1355 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224192 UTC' AS overflows;
1356 ERROR: interval out of range
1357 -- TO_CHAR()
1358 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
1359 FROM TIMESTAMPTZ_TBL;
1360 to_char
1361 ------------------------------------------------------------------------------------------
1364 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1365 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1366 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1367 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1368 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1369 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1370 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1371 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1372 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1373 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1374 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1375 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1376 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1377 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1378 SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep
1379 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1380 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1381 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1382 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1383 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1384 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1385 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1386 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1387 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1388 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1389 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1390 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1391 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1392 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1393 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1394 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1395 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1396 THURSDAY Thursday thursday THU Thu thu JULY July july VII JUL Jul jul
1397 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1398 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1399 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1400 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1401 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1402 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1403 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1404 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1405 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1406 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1407 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1408 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1409 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1410 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1411 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1412 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1413 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1414 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1415 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1416 FRIDAY Friday friday FRI Fri fri MARCH March march III MAR Mar mar
1417 MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec
1418 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1419 WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan
1420 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1421 SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar
1422 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1423 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1424 FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec
1425 SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
1426 SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
1427 MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
1428 (66 rows)
1430 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
1431 FROM TIMESTAMPTZ_TBL;
1432 to_char
1433 --------------------------------------------------------------
1436 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1437 MONDAY Monday monday FEBRUARY February february II
1438 MONDAY Monday monday FEBRUARY February february II
1439 MONDAY Monday monday FEBRUARY February february II
1440 MONDAY Monday monday FEBRUARY February february II
1441 MONDAY Monday monday FEBRUARY February february II
1442 MONDAY Monday monday FEBRUARY February february II
1443 THURSDAY Thursday thursday JANUARY January january I
1444 THURSDAY Thursday thursday JANUARY January january I
1445 MONDAY Monday monday FEBRUARY February february II
1446 MONDAY Monday monday FEBRUARY February february II
1447 MONDAY Monday monday FEBRUARY February february II
1448 MONDAY Monday monday FEBRUARY February february II
1449 TUESDAY Tuesday tuesday JUNE June june VI
1450 SATURDAY Saturday saturday SEPTEMBER September september IX
1451 WEDNESDAY Wednesday wednesday MARCH March march III
1452 WEDNESDAY Wednesday wednesday MARCH March march III
1453 WEDNESDAY Wednesday wednesday MARCH March march III
1454 WEDNESDAY Wednesday wednesday MARCH March march III
1455 WEDNESDAY Wednesday wednesday MARCH March march III
1456 MONDAY Monday monday FEBRUARY February february II
1457 MONDAY Monday monday FEBRUARY February february II
1458 MONDAY Monday monday FEBRUARY February february II
1459 MONDAY Monday monday FEBRUARY February february II
1460 MONDAY Monday monday FEBRUARY February february II
1461 MONDAY Monday monday FEBRUARY February february II
1462 MONDAY Monday monday FEBRUARY February february II
1463 MONDAY Monday monday FEBRUARY February february II
1464 MONDAY Monday monday FEBRUARY February february II
1465 MONDAY Monday monday FEBRUARY February february II
1466 MONDAY Monday monday FEBRUARY February february II
1467 MONDAY Monday monday FEBRUARY February february II
1468 THURSDAY Thursday thursday JULY July july VII
1469 TUESDAY Tuesday tuesday JUNE June june VI
1470 MONDAY Monday monday FEBRUARY February february II
1471 TUESDAY Tuesday tuesday FEBRUARY February february II
1472 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1473 THURSDAY Thursday thursday FEBRUARY February february II
1474 FRIDAY Friday friday FEBRUARY February february II
1475 SATURDAY Saturday saturday FEBRUARY February february II
1476 SUNDAY Sunday sunday FEBRUARY February february II
1477 TUESDAY Tuesday tuesday FEBRUARY February february II
1478 SATURDAY Saturday saturday FEBRUARY February february II
1479 THURSDAY Thursday thursday FEBRUARY February february II
1480 TUESDAY Tuesday tuesday FEBRUARY February february II
1481 SATURDAY Saturday saturday FEBRUARY February february II
1482 THURSDAY Thursday thursday FEBRUARY February february II
1483 TUESDAY Tuesday tuesday FEBRUARY February february II
1484 SUNDAY Sunday sunday FEBRUARY February february II
1485 SATURDAY Saturday saturday FEBRUARY February february II
1486 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1487 THURSDAY Thursday thursday FEBRUARY February february II
1488 FRIDAY Friday friday MARCH March march III
1489 MONDAY Monday monday DECEMBER December december XII
1490 TUESDAY Tuesday tuesday DECEMBER December december XII
1491 WEDNESDAY Wednesday wednesday JANUARY January january I
1492 FRIDAY Friday friday FEBRUARY February february II
1493 SATURDAY Saturday saturday MARCH March march III
1494 TUESDAY Tuesday tuesday DECEMBER December december XII
1495 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1496 FRIDAY Friday friday DECEMBER December december XII
1497 SATURDAY Saturday saturday JANUARY January january I
1498 SUNDAY Sunday sunday DECEMBER December december XII
1499 MONDAY Monday monday JANUARY January january I
1500 (66 rows)
1502 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
1503 FROM TIMESTAMPTZ_TBL;
1504 to_char
1505 --------------------------------------------------
1508 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1509 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1510 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1511 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1512 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1513 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1514 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1515 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1516 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1517 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1518 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1519 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1520 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1521 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1522 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
1523 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1524 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1525 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1526 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1527 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1528 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1529 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1530 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1531 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1532 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1533 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1534 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1535 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1536 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1537 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1538 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1539 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1540 1,997 1997 997 97 7 20 3 07 28 191 10 5 2450640
1541 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1542 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1543 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
1544 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1545 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1546 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1547 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1548 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1549 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
1550 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
1551 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1552 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1553 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1554 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1555 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1556 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1557 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
1558 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1559 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1560 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1561 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1562 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1563 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1564 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1565 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1566 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1567 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1568 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1569 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
1570 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
1571 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
1572 (66 rows)
1574 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
1575 FROM TIMESTAMPTZ_TBL;
1576 to_char
1577 -------------------------------------------------
1580 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1581 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1582 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1583 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1584 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1585 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1586 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1587 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1588 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1589 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1590 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1591 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1592 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1593 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1594 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
1595 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1596 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1597 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1598 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1599 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1600 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1601 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1602 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1603 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1604 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1605 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1606 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1607 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1608 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1609 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1610 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1611 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1612 1,997 1997 997 97 7 20 3 7 28 191 10 5 2450640
1613 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1614 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1615 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
1616 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1617 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1618 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1619 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1620 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1621 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
1622 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
1623 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1624 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1625 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1626 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1627 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1628 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1629 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
1630 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1631 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1632 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1633 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1634 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1635 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1636 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1637 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1638 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1639 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1640 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1641 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
1642 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
1643 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
1644 (66 rows)
1646 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
1647 FROM TIMESTAMPTZ_TBL;
1648 to_char
1649 ----------------------
1652 04 04 16 00 00 57600
1653 05 05 17 32 01 63121
1654 05 05 17 32 01 63121
1655 05 05 17 32 02 63122
1656 05 05 17 32 01 63121
1657 05 05 17 32 01 63121
1658 05 05 17 32 01 63121
1659 12 12 00 00 00 0
1660 03 03 03 04 05 11045
1661 05 05 17 32 01 63121
1662 05 05 17 32 01 63121
1663 05 05 17 32 01 63121
1664 05 05 17 32 01 63121
1665 05 05 17 32 01 63121
1666 06 06 18 19 20 65960
1667 08 08 08 14 01 29641
1668 04 04 04 14 02 15242
1669 02 02 02 14 03 8043
1670 03 03 03 14 04 11644
1671 01 01 01 14 05 4445
1672 05 05 17 32 01 63121
1673 05 05 17 32 01 63121
1674 05 05 17 32 00 63120
1675 05 05 17 32 01 63121
1676 05 05 17 32 01 63121
1677 05 05 17 32 01 63121
1678 05 05 17 32 01 63121
1679 05 05 17 32 01 63121
1680 09 09 09 32 01 34321
1681 09 09 09 32 01 34321
1682 09 09 09 32 01 34321
1683 02 02 14 32 01 52321
1684 02 02 14 32 01 52321
1685 06 06 18 32 01 66721
1686 05 05 17 32 01 63121
1687 05 05 17 32 01 63121
1688 05 05 17 32 01 63121
1689 05 05 17 32 01 63121
1690 05 05 17 32 01 63121
1691 05 05 17 32 01 63121
1692 05 05 17 32 01 63121
1693 05 05 17 32 01 63121
1694 05 05 17 32 01 63121
1695 05 05 17 32 01 63121
1696 05 05 17 32 01 63121
1697 05 05 17 32 01 63121
1698 05 05 17 32 01 63121
1699 05 05 17 32 01 63121
1700 05 05 17 32 01 63121
1701 05 05 17 32 01 63121
1702 05 05 17 32 01 63121
1703 05 05 17 32 01 63121
1704 05 05 17 32 01 63121
1705 05 05 17 32 01 63121
1706 05 05 17 32 01 63121
1707 05 05 17 32 01 63121
1708 05 05 17 32 01 63121
1709 05 05 17 32 01 63121
1710 05 05 17 32 01 63121
1711 05 05 17 32 01 63121
1712 05 05 17 32 01 63121
1713 05 05 17 32 01 63121
1714 05 05 17 32 01 63121
1715 05 05 17 32 01 63121
1716 (66 rows)
1718 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
1719 FROM TIMESTAMPTZ_TBL;
1720 to_char
1721 -------------------------------------------------
1724 HH:MI:SS is 04:00:00 "text between quote marks"
1725 HH:MI:SS is 05:32:01 "text between quote marks"
1726 HH:MI:SS is 05:32:01 "text between quote marks"
1727 HH:MI:SS is 05:32:02 "text between quote marks"
1728 HH:MI:SS is 05:32:01 "text between quote marks"
1729 HH:MI:SS is 05:32:01 "text between quote marks"
1730 HH:MI:SS is 05:32:01 "text between quote marks"
1731 HH:MI:SS is 12:00:00 "text between quote marks"
1732 HH:MI:SS is 03:04:05 "text between quote marks"
1733 HH:MI:SS is 05:32:01 "text between quote marks"
1734 HH:MI:SS is 05:32:01 "text between quote marks"
1735 HH:MI:SS is 05:32:01 "text between quote marks"
1736 HH:MI:SS is 05:32:01 "text between quote marks"
1737 HH:MI:SS is 05:32:01 "text between quote marks"
1738 HH:MI:SS is 06:19:20 "text between quote marks"
1739 HH:MI:SS is 08:14:01 "text between quote marks"
1740 HH:MI:SS is 04:14:02 "text between quote marks"
1741 HH:MI:SS is 02:14:03 "text between quote marks"
1742 HH:MI:SS is 03:14:04 "text between quote marks"
1743 HH:MI:SS is 01:14:05 "text between quote marks"
1744 HH:MI:SS is 05:32:01 "text between quote marks"
1745 HH:MI:SS is 05:32:01 "text between quote marks"
1746 HH:MI:SS is 05:32:00 "text between quote marks"
1747 HH:MI:SS is 05:32:01 "text between quote marks"
1748 HH:MI:SS is 05:32:01 "text between quote marks"
1749 HH:MI:SS is 05:32:01 "text between quote marks"
1750 HH:MI:SS is 05:32:01 "text between quote marks"
1751 HH:MI:SS is 05:32:01 "text between quote marks"
1752 HH:MI:SS is 09:32:01 "text between quote marks"
1753 HH:MI:SS is 09:32:01 "text between quote marks"
1754 HH:MI:SS is 09:32:01 "text between quote marks"
1755 HH:MI:SS is 02:32:01 "text between quote marks"
1756 HH:MI:SS is 02:32:01 "text between quote marks"
1757 HH:MI:SS is 06:32:01 "text between quote marks"
1758 HH:MI:SS is 05:32:01 "text between quote marks"
1759 HH:MI:SS is 05:32:01 "text between quote marks"
1760 HH:MI:SS is 05:32:01 "text between quote marks"
1761 HH:MI:SS is 05:32:01 "text between quote marks"
1762 HH:MI:SS is 05:32:01 "text between quote marks"
1763 HH:MI:SS is 05:32:01 "text between quote marks"
1764 HH:MI:SS is 05:32:01 "text between quote marks"
1765 HH:MI:SS is 05:32:01 "text between quote marks"
1766 HH:MI:SS is 05:32:01 "text between quote marks"
1767 HH:MI:SS is 05:32:01 "text between quote marks"
1768 HH:MI:SS is 05:32:01 "text between quote marks"
1769 HH:MI:SS is 05:32:01 "text between quote marks"
1770 HH:MI:SS is 05:32:01 "text between quote marks"
1771 HH:MI:SS is 05:32:01 "text between quote marks"
1772 HH:MI:SS is 05:32:01 "text between quote marks"
1773 HH:MI:SS is 05:32:01 "text between quote marks"
1774 HH:MI:SS is 05:32:01 "text between quote marks"
1775 HH:MI:SS is 05:32:01 "text between quote marks"
1776 HH:MI:SS is 05:32:01 "text between quote marks"
1777 HH:MI:SS is 05:32:01 "text between quote marks"
1778 HH:MI:SS is 05:32:01 "text between quote marks"
1779 HH:MI:SS is 05:32:01 "text between quote marks"
1780 HH:MI:SS is 05:32:01 "text between quote marks"
1781 HH:MI:SS is 05:32:01 "text between quote marks"
1782 HH:MI:SS is 05:32:01 "text between quote marks"
1783 HH:MI:SS is 05:32:01 "text between quote marks"
1784 HH:MI:SS is 05:32:01 "text between quote marks"
1785 HH:MI:SS is 05:32:01 "text between quote marks"
1786 HH:MI:SS is 05:32:01 "text between quote marks"
1787 HH:MI:SS is 05:32:01 "text between quote marks"
1788 (66 rows)
1790 SELECT to_char(d1, 'HH24--text--MI--text--SS')
1791 FROM TIMESTAMPTZ_TBL;
1792 to_char
1793 ------------------------
1796 16--text--00--text--00
1797 17--text--32--text--01
1798 17--text--32--text--01
1799 17--text--32--text--02
1800 17--text--32--text--01
1801 17--text--32--text--01
1802 17--text--32--text--01
1803 00--text--00--text--00
1804 03--text--04--text--05
1805 17--text--32--text--01
1806 17--text--32--text--01
1807 17--text--32--text--01
1808 17--text--32--text--01
1809 17--text--32--text--01
1810 18--text--19--text--20
1811 08--text--14--text--01
1812 04--text--14--text--02
1813 02--text--14--text--03
1814 03--text--14--text--04
1815 01--text--14--text--05
1816 17--text--32--text--01
1817 17--text--32--text--01
1818 17--text--32--text--00
1819 17--text--32--text--01
1820 17--text--32--text--01
1821 17--text--32--text--01
1822 17--text--32--text--01
1823 17--text--32--text--01
1824 09--text--32--text--01
1825 09--text--32--text--01
1826 09--text--32--text--01
1827 14--text--32--text--01
1828 14--text--32--text--01
1829 18--text--32--text--01
1830 17--text--32--text--01
1831 17--text--32--text--01
1832 17--text--32--text--01
1833 17--text--32--text--01
1834 17--text--32--text--01
1835 17--text--32--text--01
1836 17--text--32--text--01
1837 17--text--32--text--01
1838 17--text--32--text--01
1839 17--text--32--text--01
1840 17--text--32--text--01
1841 17--text--32--text--01
1842 17--text--32--text--01
1843 17--text--32--text--01
1844 17--text--32--text--01
1845 17--text--32--text--01
1846 17--text--32--text--01
1847 17--text--32--text--01
1848 17--text--32--text--01
1849 17--text--32--text--01
1850 17--text--32--text--01
1851 17--text--32--text--01
1852 17--text--32--text--01
1853 17--text--32--text--01
1854 17--text--32--text--01
1855 17--text--32--text--01
1856 17--text--32--text--01
1857 17--text--32--text--01
1858 17--text--32--text--01
1859 17--text--32--text--01
1860 (66 rows)
1862 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
1863 FROM TIMESTAMPTZ_TBL;
1864 to_char
1865 -------------------------
1868 1969TH 1969th 2440587th
1869 1997TH 1997th 2450490th
1870 1997TH 1997th 2450490th
1871 1997TH 1997th 2450490th
1872 1997TH 1997th 2450490th
1873 1997TH 1997th 2450490th
1874 1997TH 1997th 2450490th
1875 1997TH 1997th 2450451st
1876 1997TH 1997th 2450451st
1877 1997TH 1997th 2450490th
1878 1997TH 1997th 2450490th
1879 1997TH 1997th 2450490th
1880 1997TH 1997th 2450490th
1881 1997TH 1997th 2450610th
1882 2001ST 2001st 2452175th
1883 2000TH 2000th 2451619th
1884 2000TH 2000th 2451619th
1885 2000TH 2000th 2451619th
1886 2000TH 2000th 2451619th
1887 2000TH 2000th 2451619th
1888 1997TH 1997th 2450490th
1889 1997TH 1997th 2450490th
1890 1997TH 1997th 2450490th
1891 1997TH 1997th 2450490th
1892 1997TH 1997th 2450490th
1893 1997TH 1997th 2450490th
1894 1997TH 1997th 2450490th
1895 1997TH 1997th 2450490th
1896 1997TH 1997th 2450490th
1897 1997TH 1997th 2450490th
1898 1997TH 1997th 2450490th
1899 1997TH 1997th 2450490th
1900 1997TH 1997th 2450640th
1901 1997TH 1997th 2450610th
1902 1997TH 1997th 2450490th
1903 1997TH 1997th 2450491st
1904 1997TH 1997th 2450492nd
1905 1997TH 1997th 2450493rd
1906 1997TH 1997th 2450494th
1907 1997TH 1997th 2450495th
1908 1997TH 1997th 2450496th
1909 0097TH 0097th 1686042nd
1910 0097TH 0097th 1756536th
1911 0597TH 0597th 1939157th
1912 1097TH 1097th 2121778th
1913 1697TH 1697th 2340924th
1914 1797TH 1797th 2377448th
1915 1897TH 1897th 2413972nd
1916 1997TH 1997th 2450496th
1917 2097TH 2097th 2487021st
1918 1996TH 1996th 2450142nd
1919 1996TH 1996th 2450143rd
1920 1996TH 1996th 2450144th
1921 1996TH 1996th 2450448th
1922 1996TH 1996th 2450449th
1923 1997TH 1997th 2450450th
1924 1997TH 1997th 2450508th
1925 1997TH 1997th 2450509th
1926 1997TH 1997th 2450813th
1927 1997TH 1997th 2450814th
1928 1999TH 1999th 2451544th
1929 2000TH 2000th 2451545th
1930 2000TH 2000th 2451910th
1931 2001ST 2001st 2451911th
1932 (66 rows)
1934 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')
1935 FROM TIMESTAMPTZ_TBL;
1936 to_char
1937 ---------------------------------------------------------------------
1940 1969 A.D. 1969 a.d. 1969 ad 04:00:00 P.M. 04:00:00 p.m. 04:00:00 pm
1941 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1942 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1943 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm
1944 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1945 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1946 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1947 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1948 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1949 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1950 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1951 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1952 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1953 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1954 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
1955 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
1956 2000 A.D. 2000 a.d. 2000 ad 04:14:02 A.M. 04:14:02 a.m. 04:14:02 am
1957 2000 A.D. 2000 a.d. 2000 ad 02:14:03 A.M. 02:14:03 a.m. 02:14:03 am
1958 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
1959 2000 A.D. 2000 a.d. 2000 ad 01:14:05 A.M. 01:14:05 a.m. 01:14:05 am
1960 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1961 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1962 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
1963 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1964 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1965 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1966 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1967 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1968 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1969 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1970 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1971 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1972 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1973 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
1974 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1975 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1976 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1977 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1978 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1979 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1980 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1981 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1982 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1983 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1984 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1985 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1986 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1987 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1988 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1989 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1990 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1991 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1992 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1993 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1994 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1995 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1996 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1997 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1998 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1999 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2000 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2001 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2002 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2003 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
2004 (66 rows)
2006 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
2007 FROM TIMESTAMPTZ_TBL;
2008 to_char
2009 ------------------------
2012 1970 970 70 0 01 003 3
2013 1997 997 97 7 07 043 1
2014 1997 997 97 7 07 043 1
2015 1997 997 97 7 07 043 1
2016 1997 997 97 7 07 043 1
2017 1997 997 97 7 07 043 1
2018 1997 997 97 7 07 043 1
2019 1997 997 97 7 01 004 4
2020 1997 997 97 7 01 004 4
2021 1997 997 97 7 07 043 1
2022 1997 997 97 7 07 043 1
2023 1997 997 97 7 07 043 1
2024 1997 997 97 7 07 043 1
2025 1997 997 97 7 24 163 2
2026 2001 001 01 1 38 265 6
2027 2000 000 00 0 11 073 3
2028 2000 000 00 0 11 073 3
2029 2000 000 00 0 11 073 3
2030 2000 000 00 0 11 073 3
2031 2000 000 00 0 11 073 3
2032 1997 997 97 7 07 043 1
2033 1997 997 97 7 07 043 1
2034 1997 997 97 7 07 043 1
2035 1997 997 97 7 07 043 1
2036 1997 997 97 7 07 043 1
2037 1997 997 97 7 07 043 1
2038 1997 997 97 7 07 043 1
2039 1997 997 97 7 07 043 1
2040 1997 997 97 7 07 043 1
2041 1997 997 97 7 07 043 1
2042 1997 997 97 7 07 043 1
2043 1997 997 97 7 07 043 1
2044 1997 997 97 7 28 193 4
2045 1997 997 97 7 24 163 2
2046 1997 997 97 7 07 043 1
2047 1997 997 97 7 07 044 2
2048 1997 997 97 7 07 045 3
2049 1997 997 97 7 07 046 4
2050 1997 997 97 7 07 047 5
2051 1997 997 97 7 07 048 6
2052 1997 997 97 7 07 049 7
2053 0097 097 97 7 07 044 2
2054 0097 097 97 7 07 048 6
2055 0597 597 97 7 07 046 4
2056 1097 097 97 7 07 044 2
2057 1697 697 97 7 07 048 6
2058 1797 797 97 7 07 046 4
2059 1897 897 97 7 07 044 2
2060 1997 997 97 7 07 049 7
2061 2097 097 97 7 07 048 6
2062 1996 996 96 6 09 059 3
2063 1996 996 96 6 09 060 4
2064 1996 996 96 6 09 061 5
2065 1997 997 97 7 01 001 1
2066 1997 997 97 7 01 002 2
2067 1997 997 97 7 01 003 3
2068 1997 997 97 7 09 061 5
2069 1997 997 97 7 09 062 6
2070 1998 998 98 8 01 002 2
2071 1998 998 98 8 01 003 3
2072 1999 999 99 9 52 362 5
2073 1999 999 99 9 52 363 6
2074 2000 000 00 0 52 364 7
2075 2001 001 01 1 01 001 1
2076 (66 rows)
2078 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
2079 FROM TIMESTAMPTZ_TBL;
2080 to_char
2081 ------------------------
2084 1970 970 70 0 1 3 3
2085 1997 997 97 7 7 43 1
2086 1997 997 97 7 7 43 1
2087 1997 997 97 7 7 43 1
2088 1997 997 97 7 7 43 1
2089 1997 997 97 7 7 43 1
2090 1997 997 97 7 7 43 1
2091 1997 997 97 7 1 4 4
2092 1997 997 97 7 1 4 4
2093 1997 997 97 7 7 43 1
2094 1997 997 97 7 7 43 1
2095 1997 997 97 7 7 43 1
2096 1997 997 97 7 7 43 1
2097 1997 997 97 7 24 163 2
2098 2001 1 1 1 38 265 6
2099 2000 0 0 0 11 73 3
2100 2000 0 0 0 11 73 3
2101 2000 0 0 0 11 73 3
2102 2000 0 0 0 11 73 3
2103 2000 0 0 0 11 73 3
2104 1997 997 97 7 7 43 1
2105 1997 997 97 7 7 43 1
2106 1997 997 97 7 7 43 1
2107 1997 997 97 7 7 43 1
2108 1997 997 97 7 7 43 1
2109 1997 997 97 7 7 43 1
2110 1997 997 97 7 7 43 1
2111 1997 997 97 7 7 43 1
2112 1997 997 97 7 7 43 1
2113 1997 997 97 7 7 43 1
2114 1997 997 97 7 7 43 1
2115 1997 997 97 7 7 43 1
2116 1997 997 97 7 28 193 4
2117 1997 997 97 7 24 163 2
2118 1997 997 97 7 7 43 1
2119 1997 997 97 7 7 44 2
2120 1997 997 97 7 7 45 3
2121 1997 997 97 7 7 46 4
2122 1997 997 97 7 7 47 5
2123 1997 997 97 7 7 48 6
2124 1997 997 97 7 7 49 7
2125 97 97 97 7 7 44 2
2126 97 97 97 7 7 48 6
2127 597 597 97 7 7 46 4
2128 1097 97 97 7 7 44 2
2129 1697 697 97 7 7 48 6
2130 1797 797 97 7 7 46 4
2131 1897 897 97 7 7 44 2
2132 1997 997 97 7 7 49 7
2133 2097 97 97 7 7 48 6
2134 1996 996 96 6 9 59 3
2135 1996 996 96 6 9 60 4
2136 1996 996 96 6 9 61 5
2137 1997 997 97 7 1 1 1
2138 1997 997 97 7 1 2 2
2139 1997 997 97 7 1 3 3
2140 1997 997 97 7 9 61 5
2141 1997 997 97 7 9 62 6
2142 1998 998 98 8 1 2 2
2143 1998 998 98 8 1 3 3
2144 1999 999 99 9 52 362 5
2145 1999 999 99 9 52 363 6
2146 2000 0 0 0 52 364 7
2147 2001 1 1 1 1 1 1
2148 (66 rows)
2150 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
2151 FROM (VALUES
2152 ('2018-11-02 12:34:56'::timestamptz),
2153 ('2018-11-02 12:34:56.78'),
2154 ('2018-11-02 12:34:56.78901'),
2155 ('2018-11-02 12:34:56.78901234')
2156 ) d(d);
2157 to_char
2158 --------------------------------------------------------------------
2159 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000
2160 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000
2161 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010
2162 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012
2163 (4 rows)
2165 -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours
2166 SET timezone = '00:00';
2167 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2168 OF | TZH:TZM
2169 -----+---------
2170 +00 | +00:00
2171 (1 row)
2173 SET timezone = '+02:00';
2174 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2175 OF | TZH:TZM
2176 -----+---------
2177 -02 | -02:00
2178 (1 row)
2180 SET timezone = '-13:00';
2181 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2182 OF | TZH:TZM
2183 -----+---------
2184 +13 | +13:00
2185 (1 row)
2187 SET timezone = '-00:30';
2188 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2189 OF | TZH:TZM
2190 --------+---------
2191 +00:30 | +00:30
2192 (1 row)
2194 SET timezone = '00:30';
2195 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2196 OF | TZH:TZM
2197 --------+---------
2198 -00:30 | -00:30
2199 (1 row)
2201 SET timezone = '-04:30';
2202 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2203 OF | TZH:TZM
2204 --------+---------
2205 +04:30 | +04:30
2206 (1 row)
2208 SET timezone = '04:30';
2209 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2210 OF | TZH:TZM
2211 --------+---------
2212 -04:30 | -04:30
2213 (1 row)
2215 SET timezone = '-04:15';
2216 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2217 OF | TZH:TZM
2218 --------+---------
2219 +04:15 | +04:15
2220 (1 row)
2222 SET timezone = '04:15';
2223 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2224 OF | TZH:TZM
2225 --------+---------
2226 -04:15 | -04:15
2227 (1 row)
2229 RESET timezone;
2230 -- Check of, tzh, tzm with various zone offsets.
2231 SET timezone = '00:00';
2232 SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2233 Of | tzh:tzm
2234 -----+---------
2235 +00 | +00:00
2236 (1 row)
2238 SET timezone = '+02:00';
2239 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2240 of | tzh:tzm
2241 -----+---------
2242 -02 | -02:00
2243 (1 row)
2245 SET timezone = '-13:00';
2246 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2247 of | tzh:tzm
2248 -----+---------
2249 +13 | +13:00
2250 (1 row)
2252 SET timezone = '-00:30';
2253 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2254 of | tzh:tzm
2255 --------+---------
2256 +00:30 | +00:30
2257 (1 row)
2259 SET timezone = '00:30';
2260 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2261 of | tzh:tzm
2262 --------+---------
2263 -00:30 | -00:30
2264 (1 row)
2266 SET timezone = '-04:30';
2267 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2268 of | tzh:tzm
2269 --------+---------
2270 +04:30 | +04:30
2271 (1 row)
2273 SET timezone = '04:30';
2274 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2275 of | tzh:tzm
2276 --------+---------
2277 -04:30 | -04:30
2278 (1 row)
2280 SET timezone = '-04:15';
2281 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2282 of | tzh:tzm
2283 --------+---------
2284 +04:15 | +04:15
2285 (1 row)
2287 SET timezone = '04:15';
2288 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2289 of | tzh:tzm
2290 --------+---------
2291 -04:15 | -04:15
2292 (1 row)
2294 RESET timezone;
2295 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
2296 -- Test year field value with len > 4
2297 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
2298 INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
2299 INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
2300 INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
2301 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
2302 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
2303 --Verify data
2304 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
2305 a | b
2306 ---+--------------------------------
2307 1 | Wed Mar 12 14:05:50 1000 LMT
2308 2 | Sun Mar 12 14:58:48 10000 PDT
2309 3 | Sun Mar 12 14:58:48 100000 PDT
2310 3 | Sun Mar 12 14:58:48 10000 PDT
2311 4 | Sun Mar 12 14:58:48 10000 PDT
2312 4 | Sun Mar 12 14:58:48 100000 PDT
2313 (6 rows)
2315 --Cleanup
2316 DROP TABLE TIMESTAMPTZ_TST;
2317 -- test timestamptz constructors
2318 set TimeZone to 'America/New_York';
2319 -- numeric timezone
2320 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
2321 make_timestamptz
2322 ---------------------------------
2323 Sun Jul 15 08:15:55.33 1973 EDT
2324 (1 row)
2326 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
2327 make_timestamptz
2328 ---------------------------------
2329 Sun Jul 15 02:15:55.33 1973 EDT
2330 (1 row)
2332 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2');
2333 make_timestamptz
2334 ---------------------------------
2335 Sun Jul 15 06:15:55.33 1973 EDT
2336 (1 row)
2338 WITH tzs (tz) AS (VALUES
2339 ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'),
2340 ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'),
2341 ('+10:00:1'), ('+10:00:01'),
2342 ('+10:00:10'))
2343 SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
2344 make_timestamptz | tz
2345 ------------------------------+-----------
2346 Fri Feb 26 21:45:00 2010 EST | +1
2347 Fri Feb 26 21:45:00 2010 EST | +1:
2348 Fri Feb 26 21:45:00 2010 EST | +1:0
2349 Fri Feb 26 21:45:00 2010 EST | +100
2350 Fri Feb 26 21:45:00 2010 EST | +1:00
2351 Fri Feb 26 21:45:00 2010 EST | +01:00
2352 Fri Feb 26 12:45:00 2010 EST | +10
2353 Fri Feb 26 12:45:00 2010 EST | +1000
2354 Fri Feb 26 12:45:00 2010 EST | +10:
2355 Fri Feb 26 12:45:00 2010 EST | +10:0
2356 Fri Feb 26 12:45:00 2010 EST | +10:00
2357 Fri Feb 26 12:45:00 2010 EST | +10:00:
2358 Fri Feb 26 12:44:59 2010 EST | +10:00:1
2359 Fri Feb 26 12:44:59 2010 EST | +10:00:01
2360 Fri Feb 26 12:44:50 2010 EST | +10:00:10
2361 (15 rows)
2363 -- these should fail
2364 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
2365 ERROR: invalid input syntax for type numeric time zone: "2"
2366 HINT: Numeric time zones must have "-" or "+" as first character.
2367 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16');
2368 ERROR: numeric time zone "+16" out of range
2369 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16');
2370 ERROR: numeric time zone "-16" out of range
2371 -- should be true
2372 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
2373 ?column?
2374 ----------
2375 t
2376 (1 row)
2378 -- full timezone names
2379 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague';
2380 ?column?
2381 ----------
2382 t
2383 (1 row)
2385 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC';
2386 timezone
2387 --------------------------
2388 Tue Dec 09 23:00:00 2014
2389 (1 row)
2391 SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC';
2392 timezone
2393 --------------------------
2394 Wed Dec 09 15:56:00 1846
2395 (1 row)
2397 SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC';
2398 timezone
2399 --------------------------
2400 Fri Dec 09 23:50:39 1881
2401 (1 row)
2403 SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar');
2404 ERROR: time zone "Nehwon/Lankhmar" not recognized
2405 -- abbreviations
2406 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST');
2407 make_timestamptz
2408 ------------------------------
2409 Wed Dec 10 10:10:10 2008 EST
2410 (1 row)
2412 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT');
2413 make_timestamptz
2414 ------------------------------
2415 Wed Dec 10 09:10:10 2008 EST
2416 (1 row)
2418 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'FOO8BAR');
2419 make_timestamptz
2420 ------------------------------
2421 Wed Dec 10 13:10:10 2014 EST
2422 (1 row)
2424 -- POSIX
2425 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT,M3.2.0,M11.1.0');
2426 make_timestamptz
2427 ------------------------------
2428 Wed Dec 10 13:10:10 2014 EST
2429 (1 row)
2431 RESET TimeZone;
2432 -- generate_series for timestamptz
2433 select * from generate_series('2020-01-01 00:00'::timestamptz,
2434 '2020-01-02 03:00'::timestamptz,
2435 '1 hour'::interval);
2436 generate_series
2437 ------------------------------
2438 Wed Jan 01 00:00:00 2020 PST
2439 Wed Jan 01 01:00:00 2020 PST
2440 Wed Jan 01 02:00:00 2020 PST
2441 Wed Jan 01 03:00:00 2020 PST
2442 Wed Jan 01 04:00:00 2020 PST
2443 Wed Jan 01 05:00:00 2020 PST
2444 Wed Jan 01 06:00:00 2020 PST
2445 Wed Jan 01 07:00:00 2020 PST
2446 Wed Jan 01 08:00:00 2020 PST
2447 Wed Jan 01 09:00:00 2020 PST
2448 Wed Jan 01 10:00:00 2020 PST
2449 Wed Jan 01 11:00:00 2020 PST
2450 Wed Jan 01 12:00:00 2020 PST
2451 Wed Jan 01 13:00:00 2020 PST
2452 Wed Jan 01 14:00:00 2020 PST
2453 Wed Jan 01 15:00:00 2020 PST
2454 Wed Jan 01 16:00:00 2020 PST
2455 Wed Jan 01 17:00:00 2020 PST
2456 Wed Jan 01 18:00:00 2020 PST
2457 Wed Jan 01 19:00:00 2020 PST
2458 Wed Jan 01 20:00:00 2020 PST
2459 Wed Jan 01 21:00:00 2020 PST
2460 Wed Jan 01 22:00:00 2020 PST
2461 Wed Jan 01 23:00:00 2020 PST
2462 Thu Jan 02 00:00:00 2020 PST
2463 Thu Jan 02 01:00:00 2020 PST
2464 Thu Jan 02 02:00:00 2020 PST
2465 Thu Jan 02 03:00:00 2020 PST
2466 (28 rows)
2468 -- the LIMIT should allow this to terminate in a reasonable amount of time
2469 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
2470 select generate_series('2022-01-01 00:00'::timestamptz,
2471 'infinity'::timestamptz,
2472 '1 month'::interval) limit 10;
2473 generate_series
2474 ------------------------------
2475 Sat Jan 01 00:00:00 2022 PST
2476 Tue Feb 01 00:00:00 2022 PST
2477 Tue Mar 01 00:00:00 2022 PST
2478 Fri Apr 01 00:00:00 2022 PDT
2479 Sun May 01 00:00:00 2022 PDT
2480 Wed Jun 01 00:00:00 2022 PDT
2481 Fri Jul 01 00:00:00 2022 PDT
2482 Mon Aug 01 00:00:00 2022 PDT
2483 Thu Sep 01 00:00:00 2022 PDT
2484 Sat Oct 01 00:00:00 2022 PDT
2485 (10 rows)
2487 -- errors
2488 select * from generate_series('2020-01-01 00:00'::timestamptz,
2489 '2020-01-02 03:00'::timestamptz,
2490 '0 hour'::interval);
2491 ERROR: step size cannot equal zero
2492 select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
2493 ERROR: step size cannot be infinite
2494 select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
2495 ERROR: step size cannot be infinite
2496 -- Interval crossing time shift for Europe/Warsaw timezone (with DST)
2497 SET TimeZone to 'UTC';
2498 SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
2499 '1 day'::interval);
2500 date_add
2501 ------------------------------
2502 Sun Oct 30 23:00:00 2022 UTC
2503 (1 row)
2505 SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
2506 '1 day'::interval,
2507 'Europe/Warsaw');
2508 date_add
2509 ------------------------------
2510 Sun Oct 31 23:00:00 2021 UTC
2511 (1 row)
2513 SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
2514 '1 day'::interval);
2515 date_subtract
2516 ------------------------------
2517 Fri Oct 28 23:00:00 2022 UTC
2518 (1 row)
2520 SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
2521 '1 day'::interval,
2522 'Europe/Warsaw');
2523 date_subtract
2524 ------------------------------
2525 Fri Oct 29 22:00:00 2021 UTC
2526 (1 row)
2528 SELECT * FROM generate_series('2021-12-31 23:00:00+00'::timestamptz,
2529 '2020-12-31 23:00:00+00'::timestamptz,
2530 '-1 month'::interval,
2531 'Europe/Warsaw');
2532 generate_series
2533 ------------------------------
2534 Fri Dec 31 23:00:00 2021 UTC
2535 Tue Nov 30 23:00:00 2021 UTC
2536 Sun Oct 31 23:00:00 2021 UTC
2537 Thu Sep 30 22:00:00 2021 UTC
2538 Tue Aug 31 22:00:00 2021 UTC
2539 Sat Jul 31 22:00:00 2021 UTC
2540 Wed Jun 30 22:00:00 2021 UTC
2541 Mon May 31 22:00:00 2021 UTC
2542 Fri Apr 30 22:00:00 2021 UTC
2543 Wed Mar 31 22:00:00 2021 UTC
2544 Sun Feb 28 23:00:00 2021 UTC
2545 Sun Jan 31 23:00:00 2021 UTC
2546 Thu Dec 31 23:00:00 2020 UTC
2547 (13 rows)
2549 RESET TimeZone;
2550 --
2551 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
2552 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
2553 -- moved forwards in Mar 2011 and backwards again in Oct 2014.
2554 --
2555 SET TimeZone to 'UTC';
2556 SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz;
2557 timestamptz
2558 ------------------------------
2559 Sat Mar 26 21:00:00 2011 UTC
2560 (1 row)
2562 SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz;
2563 timestamptz
2564 ------------------------------
2565 Sat Mar 26 22:00:00 2011 UTC
2566 (1 row)
2568 SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz;
2569 timestamptz
2570 ------------------------------
2571 Sat Mar 26 22:59:59 2011 UTC
2572 (1 row)
2574 SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz;
2575 timestamptz
2576 ------------------------------
2577 Sat Mar 26 23:00:00 2011 UTC
2578 (1 row)
2580 SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz;
2581 timestamptz
2582 ------------------------------
2583 Sat Mar 26 23:00:01 2011 UTC
2584 (1 row)
2586 SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz;
2587 timestamptz
2588 ------------------------------
2589 Sat Mar 26 23:59:59 2011 UTC
2590 (1 row)
2592 SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz;
2593 timestamptz
2594 ------------------------------
2595 Sat Mar 26 23:00:00 2011 UTC
2596 (1 row)
2598 SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz;
2599 timestamptz
2600 ------------------------------
2601 Sat Mar 26 23:00:01 2011 UTC
2602 (1 row)
2604 SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz;
2605 timestamptz
2606 ------------------------------
2607 Sun Mar 27 00:00:00 2011 UTC
2608 (1 row)
2610 SELECT '2011-03-27 00:00:00 MSK'::timestamptz;
2611 timestamptz
2612 ------------------------------
2613 Sat Mar 26 21:00:00 2011 UTC
2614 (1 row)
2616 SELECT '2011-03-27 01:00:00 MSK'::timestamptz;
2617 timestamptz
2618 ------------------------------
2619 Sat Mar 26 22:00:00 2011 UTC
2620 (1 row)
2622 SELECT '2011-03-27 01:59:59 MSK'::timestamptz;
2623 timestamptz
2624 ------------------------------
2625 Sat Mar 26 22:59:59 2011 UTC
2626 (1 row)
2628 SELECT '2011-03-27 02:00:00 MSK'::timestamptz;
2629 timestamptz
2630 ------------------------------
2631 Sat Mar 26 22:00:00 2011 UTC
2632 (1 row)
2634 SELECT '2011-03-27 02:00:01 MSK'::timestamptz;
2635 timestamptz
2636 ------------------------------
2637 Sat Mar 26 22:00:01 2011 UTC
2638 (1 row)
2640 SELECT '2011-03-27 02:59:59 MSK'::timestamptz;
2641 timestamptz
2642 ------------------------------
2643 Sat Mar 26 22:59:59 2011 UTC
2644 (1 row)
2646 SELECT '2011-03-27 03:00:00 MSK'::timestamptz;
2647 timestamptz
2648 ------------------------------
2649 Sat Mar 26 23:00:00 2011 UTC
2650 (1 row)
2652 SELECT '2011-03-27 03:00:01 MSK'::timestamptz;
2653 timestamptz
2654 ------------------------------
2655 Sat Mar 26 23:00:01 2011 UTC
2656 (1 row)
2658 SELECT '2011-03-27 04:00:00 MSK'::timestamptz;
2659 timestamptz
2660 ------------------------------
2661 Sun Mar 27 00:00:00 2011 UTC
2662 (1 row)
2664 SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz;
2665 timestamptz
2666 ------------------------------
2667 Sat Oct 25 20:00:00 2014 UTC
2668 (1 row)
2670 SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz;
2671 timestamptz
2672 ------------------------------
2673 Sat Oct 25 20:59:59 2014 UTC
2674 (1 row)
2676 SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz;
2677 timestamptz
2678 ------------------------------
2679 Sat Oct 25 22:00:00 2014 UTC
2680 (1 row)
2682 SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz;
2683 timestamptz
2684 ------------------------------
2685 Sat Oct 25 22:00:01 2014 UTC
2686 (1 row)
2688 SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz;
2689 timestamptz
2690 ------------------------------
2691 Sat Oct 25 23:00:00 2014 UTC
2692 (1 row)
2694 SELECT '2014-10-26 00:00:00 MSK'::timestamptz;
2695 timestamptz
2696 ------------------------------
2697 Sat Oct 25 20:00:00 2014 UTC
2698 (1 row)
2700 SELECT '2014-10-26 00:59:59 MSK'::timestamptz;
2701 timestamptz
2702 ------------------------------
2703 Sat Oct 25 20:59:59 2014 UTC
2704 (1 row)
2706 SELECT '2014-10-26 01:00:00 MSK'::timestamptz;
2707 timestamptz
2708 ------------------------------
2709 Sat Oct 25 22:00:00 2014 UTC
2710 (1 row)
2712 SELECT '2014-10-26 01:00:01 MSK'::timestamptz;
2713 timestamptz
2714 ------------------------------
2715 Sat Oct 25 22:00:01 2014 UTC
2716 (1 row)
2718 SELECT '2014-10-26 02:00:00 MSK'::timestamptz;
2719 timestamptz
2720 ------------------------------
2721 Sat Oct 25 23:00:00 2014 UTC
2722 (1 row)
2724 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2725 timezone
2726 ------------------------------
2727 Sat Mar 26 21:00:00 2011 UTC
2728 (1 row)
2730 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2731 timezone
2732 ------------------------------
2733 Sat Mar 26 22:00:00 2011 UTC
2734 (1 row)
2736 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2737 timezone
2738 ------------------------------
2739 Sat Mar 26 22:59:59 2011 UTC
2740 (1 row)
2742 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2743 timezone
2744 ------------------------------
2745 Sat Mar 26 23:00:00 2011 UTC
2746 (1 row)
2748 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2749 timezone
2750 ------------------------------
2751 Sat Mar 26 23:00:01 2011 UTC
2752 (1 row)
2754 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2755 timezone
2756 ------------------------------
2757 Sat Mar 26 23:59:59 2011 UTC
2758 (1 row)
2760 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2761 timezone
2762 ------------------------------
2763 Sat Mar 26 23:00:00 2011 UTC
2764 (1 row)
2766 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2767 timezone
2768 ------------------------------
2769 Sat Mar 26 23:00:01 2011 UTC
2770 (1 row)
2772 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2773 timezone
2774 ------------------------------
2775 Sun Mar 27 00:00:00 2011 UTC
2776 (1 row)
2778 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK';
2779 timezone
2780 ------------------------------
2781 Sat Mar 26 21:00:00 2011 UTC
2782 (1 row)
2784 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK';
2785 timezone
2786 ------------------------------
2787 Sat Mar 26 22:00:00 2011 UTC
2788 (1 row)
2790 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK';
2791 timezone
2792 ------------------------------
2793 Sat Mar 26 22:59:59 2011 UTC
2794 (1 row)
2796 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK';
2797 timezone
2798 ------------------------------
2799 Sat Mar 26 22:00:00 2011 UTC
2800 (1 row)
2802 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK';
2803 timezone
2804 ------------------------------
2805 Sat Mar 26 22:00:01 2011 UTC
2806 (1 row)
2808 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK';
2809 timezone
2810 ------------------------------
2811 Sat Mar 26 22:59:59 2011 UTC
2812 (1 row)
2814 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK';
2815 timezone
2816 ------------------------------
2817 Sat Mar 26 23:00:00 2011 UTC
2818 (1 row)
2820 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK';
2821 timezone
2822 ------------------------------
2823 Sat Mar 26 23:00:01 2011 UTC
2824 (1 row)
2826 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK';
2827 timezone
2828 ------------------------------
2829 Sun Mar 27 00:00:00 2011 UTC
2830 (1 row)
2832 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2833 timezone
2834 ------------------------------
2835 Sat Oct 25 20:00:00 2014 UTC
2836 (1 row)
2838 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2839 timezone
2840 ------------------------------
2841 Sat Oct 25 20:59:59 2014 UTC
2842 (1 row)
2844 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2845 timezone
2846 ------------------------------
2847 Sat Oct 25 22:00:00 2014 UTC
2848 (1 row)
2850 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2851 timezone
2852 ------------------------------
2853 Sat Oct 25 22:00:01 2014 UTC
2854 (1 row)
2856 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2857 timezone
2858 ------------------------------
2859 Sat Oct 25 23:00:00 2014 UTC
2860 (1 row)
2862 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK';
2863 timezone
2864 ------------------------------
2865 Sat Oct 25 20:00:00 2014 UTC
2866 (1 row)
2868 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK';
2869 timezone
2870 ------------------------------
2871 Sat Oct 25 20:59:59 2014 UTC
2872 (1 row)
2874 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK';
2875 timezone
2876 ------------------------------
2877 Sat Oct 25 22:00:00 2014 UTC
2878 (1 row)
2880 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK';
2881 timezone
2882 ------------------------------
2883 Sat Oct 25 22:00:01 2014 UTC
2884 (1 row)
2886 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK';
2887 timezone
2888 ------------------------------
2889 Sat Oct 25 23:00:00 2014 UTC
2890 (1 row)
2892 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
2893 make_timestamptz
2894 ------------------------------
2895 Sat Oct 25 20:00:00 2014 UTC
2896 (1 row)
2898 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
2899 make_timestamptz
2900 ------------------------------
2901 Sat Oct 25 22:00:00 2014 UTC
2902 (1 row)
2904 SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
2905 to_timestamp
2906 ------------------------------
2907 Thu Jan 01 00:00:00 1970 UTC
2908 (1 row)
2910 SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
2911 to_timestamp
2912 ------------------------------
2913 Sat Jan 01 00:00:00 2000 UTC
2914 (1 row)
2916 SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
2917 to_timestamp
2918 -------------------------------------
2919 Fri Jan 01 12:34:56.789012 2010 UTC
2920 (1 row)
2922 -- edge cases
2923 SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
2924 to_timestamp
2925 ---------------------------------
2926 Mon Nov 24 00:00:00 4714 UTC BC
2927 (1 row)
2929 -- upper limit varies between integer and float timestamps, so hard to test
2930 -- nonfinite values
2931 SELECT to_timestamp(' Infinity'::float);
2932 to_timestamp
2933 --------------
2934 infinity
2935 (1 row)
2937 SELECT to_timestamp('-Infinity'::float);
2938 to_timestamp
2939 --------------
2940 -infinity
2941 (1 row)
2943 SELECT to_timestamp('NaN'::float);
2944 ERROR: timestamp cannot be NaN
2945 SET TimeZone to 'Europe/Moscow';
2946 SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
2947 timestamptz
2948 ------------------------------
2949 Sun Mar 27 00:00:00 2011 MSK
2950 (1 row)
2952 SELECT '2011-03-26 22:00:00 UTC'::timestamptz;
2953 timestamptz
2954 ------------------------------
2955 Sun Mar 27 01:00:00 2011 MSK
2956 (1 row)
2958 SELECT '2011-03-26 22:59:59 UTC'::timestamptz;
2959 timestamptz
2960 ------------------------------
2961 Sun Mar 27 01:59:59 2011 MSK
2962 (1 row)
2964 SELECT '2011-03-26 23:00:00 UTC'::timestamptz;
2965 timestamptz
2966 ------------------------------
2967 Sun Mar 27 03:00:00 2011 MSK
2968 (1 row)
2970 SELECT '2011-03-26 23:00:01 UTC'::timestamptz;
2971 timestamptz
2972 ------------------------------
2973 Sun Mar 27 03:00:01 2011 MSK
2974 (1 row)
2976 SELECT '2011-03-26 23:59:59 UTC'::timestamptz;
2977 timestamptz
2978 ------------------------------
2979 Sun Mar 27 03:59:59 2011 MSK
2980 (1 row)
2982 SELECT '2011-03-27 00:00:00 UTC'::timestamptz;
2983 timestamptz
2984 ------------------------------
2985 Sun Mar 27 04:00:00 2011 MSK
2986 (1 row)
2988 SELECT '2014-10-25 21:00:00 UTC'::timestamptz;
2989 timestamptz
2990 ------------------------------
2991 Sun Oct 26 01:00:00 2014 MSK
2992 (1 row)
2994 SELECT '2014-10-25 21:59:59 UTC'::timestamptz;
2995 timestamptz
2996 ------------------------------
2997 Sun Oct 26 01:59:59 2014 MSK
2998 (1 row)
3000 SELECT '2014-10-25 22:00:00 UTC'::timestamptz;
3001 timestamptz
3002 ------------------------------
3003 Sun Oct 26 01:00:00 2014 MSK
3004 (1 row)
3006 SELECT '2014-10-25 22:00:01 UTC'::timestamptz;
3007 timestamptz
3008 ------------------------------
3009 Sun Oct 26 01:00:01 2014 MSK
3010 (1 row)
3012 SELECT '2014-10-25 23:00:00 UTC'::timestamptz;
3013 timestamptz
3014 ------------------------------
3015 Sun Oct 26 02:00:00 2014 MSK
3016 (1 row)
3018 RESET TimeZone;
3019 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3020 timezone
3021 --------------------------
3022 Sun Mar 27 00:00:00 2011
3023 (1 row)
3025 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3026 timezone
3027 --------------------------
3028 Sun Mar 27 01:00:00 2011
3029 (1 row)
3031 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3032 timezone
3033 --------------------------
3034 Sun Mar 27 01:59:59 2011
3035 (1 row)
3037 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3038 timezone
3039 --------------------------
3040 Sun Mar 27 03:00:00 2011
3041 (1 row)
3043 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3044 timezone
3045 --------------------------
3046 Sun Mar 27 03:00:01 2011
3047 (1 row)
3049 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3050 timezone
3051 --------------------------
3052 Sun Mar 27 03:59:59 2011
3053 (1 row)
3055 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3056 timezone
3057 --------------------------
3058 Sun Mar 27 04:00:00 2011
3059 (1 row)
3061 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3062 timezone
3063 --------------------------
3064 Sun Oct 26 01:00:00 2014
3065 (1 row)
3067 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3068 timezone
3069 --------------------------
3070 Sun Oct 26 01:59:59 2014
3071 (1 row)
3073 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3074 timezone
3075 --------------------------
3076 Sun Oct 26 01:00:00 2014
3077 (1 row)
3079 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3080 timezone
3081 --------------------------
3082 Sun Oct 26 01:00:01 2014
3083 (1 row)
3085 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3086 timezone
3087 --------------------------
3088 Sun Oct 26 02:00:00 2014
3089 (1 row)
3091 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3092 timezone
3093 --------------------------
3094 Sun Mar 27 00:00:00 2011
3095 (1 row)
3097 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3098 timezone
3099 --------------------------
3100 Sun Mar 27 01:00:00 2011
3101 (1 row)
3103 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3104 timezone
3105 --------------------------
3106 Sun Mar 27 01:59:59 2011
3107 (1 row)
3109 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3110 timezone
3111 --------------------------
3112 Sun Mar 27 03:00:00 2011
3113 (1 row)
3115 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
3116 timezone
3117 --------------------------
3118 Sun Mar 27 03:00:01 2011
3119 (1 row)
3121 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3122 timezone
3123 --------------------------
3124 Sun Mar 27 03:59:59 2011
3125 (1 row)
3127 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3128 timezone
3129 --------------------------
3130 Sun Mar 27 04:00:00 2011
3131 (1 row)
3133 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3134 timezone
3135 --------------------------
3136 Sun Oct 26 01:00:00 2014
3137 (1 row)
3139 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3140 timezone
3141 --------------------------
3142 Sun Oct 26 01:59:59 2014
3143 (1 row)
3145 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3146 timezone
3147 --------------------------
3148 Sun Oct 26 01:00:00 2014
3149 (1 row)
3151 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
3152 timezone
3153 --------------------------
3154 Sun Oct 26 01:00:01 2014
3155 (1 row)
3157 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3158 timezone
3159 --------------------------
3160 Sun Oct 26 02:00:00 2014
3161 (1 row)
3163 --
3164 -- Test LOCAL time zone
3165 --
3166 BEGIN;
3167 SET LOCAL TIME ZONE 'Europe/Paris';
3168 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3169 column1
3170 --------------------------
3171 Sat Jul 08 01:38:00 1978
3172 (1 row)
3174 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3175 column1
3176 -------------------------------
3177 Fri Jul 07 19:38:00 1978 CEST
3178 (1 row)
3180 SET LOCAL TIME ZONE 'Australia/Sydney';
3181 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3182 column1
3183 --------------------------
3184 Sat Jul 08 09:38:00 1978
3185 (1 row)
3187 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3188 column1
3189 -------------------------------
3190 Fri Jul 07 19:38:00 1978 AEST
3191 (1 row)
3193 SET LOCAL TimeZone TO 'UTC';
3194 CREATE VIEW timestamp_local_view AS
3195 SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL AS ttz_at_local,
3196 timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)) AS ttz_func,
3197 TIMESTAMP '1978-07-07 19:38' AT LOCAL AS t_at_local,
3198 timezone(TIMESTAMP '1978-07-07 19:38') AS t_func;
3199 SELECT pg_get_viewdef('timestamp_local_view', true);
3200 pg_get_viewdef
3201 ----------------------------------------------------------------------------------------------
3202 SELECT ('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone AT LOCAL) AS ttz_at_local,+
3203 timezone('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone) AS ttz_func, +
3204 ('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL) AS t_at_local, +
3205 timezone('Fri Jul 07 19:38:00 1978'::timestamp without time zone) AS t_func;
3206 (1 row)
3208 \x
3209 TABLE timestamp_local_view;
3210 -[ RECORD 1 ]+-----------------------------
3211 ttz_at_local | Fri Jul 07 23:38:00 1978
3212 ttz_func | Fri Jul 07 23:38:00 1978
3213 t_at_local | Fri Jul 07 19:38:00 1978 UTC
3214 t_func | Fri Jul 07 19:38:00 1978 UTC
3216 \x
3217 DROP VIEW timestamp_local_view;
3218 COMMIT;
3219 --
3220 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
3221 --
3222 create temp table tmptz (f1 timestamptz primary key);
3223 insert into tmptz values ('2017-01-18 00:00+00');
3224 explain (costs off)
3225 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
3226 QUERY PLAN
3227 -----------------------------------------------------------------------------------------------------
3228 Seq Scan on tmptz
3229 Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
3230 (2 rows)
3232 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
3233 f1
3234 ------------------------------
3235 Tue Jan 17 16:00:00 2017 PST
3236 (1 row)
3238 -- test arithmetic with infinite timestamps
3239 SELECT timestamptz 'infinity' - timestamptz 'infinity';
3240 ERROR: interval out of range
3241 SELECT timestamptz 'infinity' - timestamptz '-infinity';
3242 ?column?
3243 ----------
3244 infinity
3245 (1 row)
3247 SELECT timestamptz '-infinity' - timestamptz 'infinity';
3248 ?column?
3249 -----------
3250 -infinity
3251 (1 row)
3253 SELECT timestamptz '-infinity' - timestamptz '-infinity';
3254 ERROR: interval out of range
3255 SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
3256 ?column?
3257 ----------
3258 infinity
3259 (1 row)
3261 SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
3262 ?column?
3263 -----------
3264 -infinity
3265 (1 row)
3267 -- test age() with infinite timestamps
3268 SELECT age(timestamptz 'infinity');
3269 age
3270 -----------
3271 -infinity
3272 (1 row)
3274 SELECT age(timestamptz '-infinity');
3275 age
3276 ----------
3277 infinity
3278 (1 row)
3280 SELECT age(timestamptz 'infinity', timestamptz 'infinity');
3281 ERROR: interval out of range
3282 SELECT age(timestamptz 'infinity', timestamptz '-infinity');
3283 age
3284 ----------
3285 infinity
3286 (1 row)
3288 SELECT age(timestamptz '-infinity', timestamptz 'infinity');
3289 age
3290 -----------
3291 -infinity
3292 (1 row)
3294 SELECT age(timestamptz '-infinity', timestamptz '-infinity');
3295 ERROR: interval out of range
3296 -- test timestamp near POSTGRES_EPOCH_JDATE
3297 select timestamptz '1999-12-31 24:00:00';
3298 timestamptz
3299 ------------------------------
3300 Sat Jan 01 00:00:00 2000 PST
3301 (1 row)
3303 select make_timestamptz(1999, 12, 31, 24, 0, 0);
3304 make_timestamptz
3305 ------------------------------
3306 Sat Jan 01 00:00:00 2000 PST
3307 (1 row)