| blob | 2ca2101dd42f12425e2ef844a87ae8d8e6219c70 |
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 PST BC
334 Sat Feb 16 17:32:01 0097 PST
335 Thu Feb 16 17:32:01 0597 PST
336 Tue Feb 16 17:32:01 1097 PST
337 Sat Feb 16 17:32:01 1697 PST
338 Thu Feb 16 17:32:01 1797 PST
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:00:00 4714 PST BC
363 (1 row)
365 SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
366 timestamptz
367 ---------------------------------
368 Sun Nov 23 16:00:00 4714 PST BC
369 (1 row)
371 SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
372 timestamptz
373 ---------------------------------
374 Sun Nov 23 16:00:00 4714 PST 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 PST BC
465 Sat Feb 16 17:32:01 0097 PST
466 Thu Feb 16 17:32:01 0597 PST
467 Tue Feb 16 17:32:01 1097 PST
468 Sat Feb 16 17:32:01 1697 PST
469 Thu Feb 16 17:32:01 1797 PST
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 PST BC
533 Sat Feb 16 17:32:01 0097 PST
534 Thu Feb 16 17:32:01 0597 PST
535 Tue Feb 16 17:32:01 1097 PST
536 Sat Feb 16 17:32:01 1697 PST
537 Thu Feb 16 17:32:01 1797 PST
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 PST BC
565 Sat Feb 16 17:32:01 0097 PST
566 Thu Feb 16 17:32:01 0597 PST
567 Tue Feb 16 17:32:01 1097 PST
568 Sat Feb 16 17:32:01 1697 PST
569 Thu Feb 16 17:32:01 1797 PST
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 -- disallow intervals with months or years
784 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');
785 ERROR: timestamps cannot be binned into intervals containing months or years
786 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');
787 ERROR: timestamps cannot be binned into intervals containing months or years
788 -- disallow zero intervals
789 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');
790 ERROR: stride must be greater than zero
791 -- disallow negative intervals
792 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');
793 ERROR: stride must be greater than zero
794 -- Test casting within a BETWEEN qualifier
795 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
796 FROM TIMESTAMPTZ_TBL
797 WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
798 diff
799 ----------------------------------------
800 @ 9863 days 8 hours ago
801 @ 39 days 17 hours 32 mins 1 sec
802 @ 39 days 17 hours 32 mins 1 sec
803 @ 39 days 17 hours 32 mins 2 secs
804 @ 39 days 17 hours 32 mins 1.4 secs
805 @ 39 days 17 hours 32 mins 1.5 secs
806 @ 39 days 17 hours 32 mins 1.6 secs
807 @ 0
808 @ 3 hours 4 mins 5 secs
809 @ 39 days 17 hours 32 mins 1 sec
810 @ 39 days 17 hours 32 mins 1 sec
811 @ 39 days 17 hours 32 mins 1 sec
812 @ 39 days 17 hours 32 mins 1 sec
813 @ 159 days 16 hours 32 mins 1 sec
814 @ 1724 days 17 hours 19 mins 20 secs
815 @ 1168 days 8 hours 14 mins 1 sec
816 @ 1168 days 4 hours 14 mins 2 secs
817 @ 1168 days 2 hours 14 mins 3 secs
818 @ 1168 days 3 hours 14 mins 4 secs
819 @ 1168 days 1 hour 14 mins 5 secs
820 @ 39 days 17 hours 32 mins 1 sec
821 @ 39 days 17 hours 32 mins 1 sec
822 @ 39 days 17 hours 32 mins
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 @ 39 days 17 hours 32 mins 1 sec
828 @ 39 days 9 hours 32 mins 1 sec
829 @ 39 days 9 hours 32 mins 1 sec
830 @ 39 days 9 hours 32 mins 1 sec
831 @ 39 days 14 hours 32 mins 1 sec
832 @ 189 days 13 hours 32 mins 1 sec
833 @ 159 days 17 hours 32 mins 1 sec
834 @ 39 days 17 hours 32 mins 1 sec
835 @ 40 days 17 hours 32 mins 1 sec
836 @ 41 days 17 hours 32 mins 1 sec
837 @ 42 days 17 hours 32 mins 1 sec
838 @ 43 days 17 hours 32 mins 1 sec
839 @ 44 days 17 hours 32 mins 1 sec
840 @ 45 days 17 hours 32 mins 1 sec
841 @ 45 days 17 hours 32 mins 1 sec
842 @ 308 days 6 hours 27 mins 59 secs ago
843 @ 307 days 6 hours 27 mins 59 secs ago
844 @ 306 days 6 hours 27 mins 59 secs ago
845 @ 2 days 6 hours 27 mins 59 secs ago
846 @ 1 day 6 hours 27 mins 59 secs ago
847 @ 6 hours 27 mins 59 secs ago
848 @ 57 days 17 hours 32 mins 1 sec
849 @ 58 days 17 hours 32 mins 1 sec
850 @ 362 days 17 hours 32 mins 1 sec
851 @ 363 days 17 hours 32 mins 1 sec
852 @ 1093 days 17 hours 32 mins 1 sec
853 @ 1094 days 17 hours 32 mins 1 sec
854 @ 1459 days 17 hours 32 mins 1 sec
855 @ 1460 days 17 hours 32 mins 1 sec
856 (56 rows)
858 -- DATE_PART (timestamptz_part)
859 SELECT d1 as timestamptz,
860 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
861 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
862 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
863 FROM TIMESTAMPTZ_TBL;
864 timestamptz | year | month | day | hour | minute | second
865 ---------------------------------+-----------+-------+-----+------+--------+--------
866 -infinity | -Infinity | | | | |
867 infinity | Infinity | | | | |
868 Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0
869 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
870 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
871 Mon Feb 10 17:32:02 1997 PST | 1997 | 2 | 10 | 17 | 32 | 2
872 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4
873 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5
874 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6
875 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0
876 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5
877 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
878 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
879 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
880 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
881 Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1
882 Sat Sep 22 18:19:20 2001 PDT | 2001 | 9 | 22 | 18 | 19 | 20
883 Wed Mar 15 08:14:01 2000 PST | 2000 | 3 | 15 | 8 | 14 | 1
884 Wed Mar 15 04:14:02 2000 PST | 2000 | 3 | 15 | 4 | 14 | 2
885 Wed Mar 15 02:14:03 2000 PST | 2000 | 3 | 15 | 2 | 14 | 3
886 Wed Mar 15 03:14:04 2000 PST | 2000 | 3 | 15 | 3 | 14 | 4
887 Wed Mar 15 01:14:05 2000 PST | 2000 | 3 | 15 | 1 | 14 | 5
888 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
889 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
890 Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0
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 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
896 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
897 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
898 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
899 Mon Feb 10 14:32:01 1997 PST | 1997 | 2 | 10 | 14 | 32 | 1
900 Thu Jul 10 14:32:01 1997 PDT | 1997 | 7 | 10 | 14 | 32 | 1
901 Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1
902 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
903 Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1
904 Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1
905 Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1
906 Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1
907 Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1
908 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
909 Tue Feb 16 17:32:01 0097 PST BC | -97 | 2 | 16 | 17 | 32 | 1
910 Sat Feb 16 17:32:01 0097 PST | 97 | 2 | 16 | 17 | 32 | 1
911 Thu Feb 16 17:32:01 0597 PST | 597 | 2 | 16 | 17 | 32 | 1
912 Tue Feb 16 17:32:01 1097 PST | 1097 | 2 | 16 | 17 | 32 | 1
913 Sat Feb 16 17:32:01 1697 PST | 1697 | 2 | 16 | 17 | 32 | 1
914 Thu Feb 16 17:32:01 1797 PST | 1797 | 2 | 16 | 17 | 32 | 1
915 Tue Feb 16 17:32:01 1897 PST | 1897 | 2 | 16 | 17 | 32 | 1
916 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
917 Sat Feb 16 17:32:01 2097 PST | 2097 | 2 | 16 | 17 | 32 | 1
918 Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1
919 Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1
920 Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1
921 Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1
922 Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1
923 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1
924 Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1
925 Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1
926 Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1
927 Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1
928 Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1
929 Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
930 Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
931 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
932 (66 rows)
934 SELECT d1 as timestamptz,
935 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
936 date_part( 'usec', d1) AS usec
937 FROM TIMESTAMPTZ_TBL;
938 timestamptz | quarter | msec | usec
939 ---------------------------------+---------+-------+----------
940 -infinity | | |
941 infinity | | |
942 Wed Dec 31 16:00:00 1969 PST | 4 | 0 | 0
943 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
944 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
945 Mon Feb 10 17:32:02 1997 PST | 1 | 2000 | 2000000
946 Mon Feb 10 17:32:01.4 1997 PST | 1 | 1400 | 1400000
947 Mon Feb 10 17:32:01.5 1997 PST | 1 | 1500 | 1500000
948 Mon Feb 10 17:32:01.6 1997 PST | 1 | 1600 | 1600000
949 Thu Jan 02 00:00:00 1997 PST | 1 | 0 | 0
950 Thu Jan 02 03:04:05 1997 PST | 1 | 5000 | 5000000
951 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
952 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
953 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
954 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
955 Tue Jun 10 17:32:01 1997 PDT | 2 | 1000 | 1000000
956 Sat Sep 22 18:19:20 2001 PDT | 3 | 20000 | 20000000
957 Wed Mar 15 08:14:01 2000 PST | 1 | 1000 | 1000000
958 Wed Mar 15 04:14:02 2000 PST | 1 | 2000 | 2000000
959 Wed Mar 15 02:14:03 2000 PST | 1 | 3000 | 3000000
960 Wed Mar 15 03:14:04 2000 PST | 1 | 4000 | 4000000
961 Wed Mar 15 01:14:05 2000 PST | 1 | 5000 | 5000000
962 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
963 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
964 Mon Feb 10 17:32:00 1997 PST | 1 | 0 | 0
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 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
970 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
971 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
972 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
973 Mon Feb 10 14:32:01 1997 PST | 1 | 1000 | 1000000
974 Thu Jul 10 14:32:01 1997 PDT | 3 | 1000 | 1000000
975 Tue Jun 10 18:32:01 1997 PDT | 2 | 1000 | 1000000
976 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
977 Tue Feb 11 17:32:01 1997 PST | 1 | 1000 | 1000000
978 Wed Feb 12 17:32:01 1997 PST | 1 | 1000 | 1000000
979 Thu Feb 13 17:32:01 1997 PST | 1 | 1000 | 1000000
980 Fri Feb 14 17:32:01 1997 PST | 1 | 1000 | 1000000
981 Sat Feb 15 17:32:01 1997 PST | 1 | 1000 | 1000000
982 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
983 Tue Feb 16 17:32:01 0097 PST BC | 1 | 1000 | 1000000
984 Sat Feb 16 17:32:01 0097 PST | 1 | 1000 | 1000000
985 Thu Feb 16 17:32:01 0597 PST | 1 | 1000 | 1000000
986 Tue Feb 16 17:32:01 1097 PST | 1 | 1000 | 1000000
987 Sat Feb 16 17:32:01 1697 PST | 1 | 1000 | 1000000
988 Thu Feb 16 17:32:01 1797 PST | 1 | 1000 | 1000000
989 Tue Feb 16 17:32:01 1897 PST | 1 | 1000 | 1000000
990 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
991 Sat Feb 16 17:32:01 2097 PST | 1 | 1000 | 1000000
992 Wed Feb 28 17:32:01 1996 PST | 1 | 1000 | 1000000
993 Thu Feb 29 17:32:01 1996 PST | 1 | 1000 | 1000000
994 Fri Mar 01 17:32:01 1996 PST | 1 | 1000 | 1000000
995 Mon Dec 30 17:32:01 1996 PST | 4 | 1000 | 1000000
996 Tue Dec 31 17:32:01 1996 PST | 4 | 1000 | 1000000
997 Wed Jan 01 17:32:01 1997 PST | 1 | 1000 | 1000000
998 Fri Feb 28 17:32:01 1997 PST | 1 | 1000 | 1000000
999 Sat Mar 01 17:32:01 1997 PST | 1 | 1000 | 1000000
1000 Tue Dec 30 17:32:01 1997 PST | 4 | 1000 | 1000000
1001 Wed Dec 31 17:32:01 1997 PST | 4 | 1000 | 1000000
1002 Fri Dec 31 17:32:01 1999 PST | 4 | 1000 | 1000000
1003 Sat Jan 01 17:32:01 2000 PST | 1 | 1000 | 1000000
1004 Sun Dec 31 17:32:01 2000 PST | 4 | 1000 | 1000000
1005 Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000
1006 (66 rows)
1008 SELECT d1 as timestamptz,
1009 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
1010 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
1011 date_part( 'doy', d1) AS doy
1012 FROM TIMESTAMPTZ_TBL;
1013 timestamptz | isoyear | week | isodow | dow | doy
1014 ---------------------------------+-----------+------+--------+-----+-----
1015 -infinity | -Infinity | | | |
1016 infinity | Infinity | | | |
1017 Wed Dec 31 16:00:00 1969 PST | 1970 | 1 | 3 | 3 | 365
1018 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1019 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1020 Mon Feb 10 17:32:02 1997 PST | 1997 | 7 | 1 | 1 | 41
1021 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 7 | 1 | 1 | 41
1022 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 7 | 1 | 1 | 41
1023 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 7 | 1 | 1 | 41
1024 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 4 | 4 | 2
1025 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 4 | 4 | 2
1026 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1027 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1028 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1029 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1030 Tue Jun 10 17:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
1031 Sat Sep 22 18:19:20 2001 PDT | 2001 | 38 | 6 | 6 | 265
1032 Wed Mar 15 08:14:01 2000 PST | 2000 | 11 | 3 | 3 | 75
1033 Wed Mar 15 04:14:02 2000 PST | 2000 | 11 | 3 | 3 | 75
1034 Wed Mar 15 02:14:03 2000 PST | 2000 | 11 | 3 | 3 | 75
1035 Wed Mar 15 03:14:04 2000 PST | 2000 | 11 | 3 | 3 | 75
1036 Wed Mar 15 01:14:05 2000 PST | 2000 | 11 | 3 | 3 | 75
1037 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1038 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1039 Mon Feb 10 17:32:00 1997 PST | 1997 | 7 | 1 | 1 | 41
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 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1045 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1046 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1047 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1048 Mon Feb 10 14:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1049 Thu Jul 10 14:32:01 1997 PDT | 1997 | 28 | 4 | 4 | 191
1050 Tue Jun 10 18:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
1051 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1052 Tue Feb 11 17:32:01 1997 PST | 1997 | 7 | 2 | 2 | 42
1053 Wed Feb 12 17:32:01 1997 PST | 1997 | 7 | 3 | 3 | 43
1054 Thu Feb 13 17:32:01 1997 PST | 1997 | 7 | 4 | 4 | 44
1055 Fri Feb 14 17:32:01 1997 PST | 1997 | 7 | 5 | 5 | 45
1056 Sat Feb 15 17:32:01 1997 PST | 1997 | 7 | 6 | 6 | 46
1057 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1058 Tue Feb 16 17:32:01 0097 PST BC | -97 | 7 | 2 | 2 | 47
1059 Sat Feb 16 17:32:01 0097 PST | 97 | 7 | 6 | 6 | 47
1060 Thu Feb 16 17:32:01 0597 PST | 597 | 7 | 4 | 4 | 47
1061 Tue Feb 16 17:32:01 1097 PST | 1097 | 7 | 2 | 2 | 47
1062 Sat Feb 16 17:32:01 1697 PST | 1697 | 7 | 6 | 6 | 47
1063 Thu Feb 16 17:32:01 1797 PST | 1797 | 7 | 4 | 4 | 47
1064 Tue Feb 16 17:32:01 1897 PST | 1897 | 7 | 2 | 2 | 47
1065 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1066 Sat Feb 16 17:32:01 2097 PST | 2097 | 7 | 6 | 6 | 47
1067 Wed Feb 28 17:32:01 1996 PST | 1996 | 9 | 3 | 3 | 59
1068 Thu Feb 29 17:32:01 1996 PST | 1996 | 9 | 4 | 4 | 60
1069 Fri Mar 01 17:32:01 1996 PST | 1996 | 9 | 5 | 5 | 61
1070 Mon Dec 30 17:32:01 1996 PST | 1997 | 1 | 1 | 1 | 365
1071 Tue Dec 31 17:32:01 1996 PST | 1997 | 1 | 2 | 2 | 366
1072 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 3 | 3 | 1
1073 Fri Feb 28 17:32:01 1997 PST | 1997 | 9 | 5 | 5 | 59
1074 Sat Mar 01 17:32:01 1997 PST | 1997 | 9 | 6 | 6 | 60
1075 Tue Dec 30 17:32:01 1997 PST | 1998 | 1 | 2 | 2 | 364
1076 Wed Dec 31 17:32:01 1997 PST | 1998 | 1 | 3 | 3 | 365
1077 Fri Dec 31 17:32:01 1999 PST | 1999 | 52 | 5 | 5 | 365
1078 Sat Jan 01 17:32:01 2000 PST | 1999 | 52 | 6 | 6 | 1
1079 Sun Dec 31 17:32:01 2000 PST | 2000 | 52 | 7 | 0 | 366
1080 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 1 | 1
1081 (66 rows)
1083 SELECT d1 as timestamptz,
1084 date_part( 'decade', d1) AS decade,
1085 date_part( 'century', d1) AS century,
1086 date_part( 'millennium', d1) AS millennium,
1087 round(date_part( 'julian', d1)) AS julian,
1088 date_part( 'epoch', d1) AS epoch
1089 FROM TIMESTAMPTZ_TBL;
1090 timestamptz | decade | century | millennium | julian | epoch
1091 ---------------------------------+-----------+-----------+------------+-----------+--------------
1092 -infinity | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1093 infinity | Infinity | Infinity | Infinity | Infinity | Infinity
1094 Wed Dec 31 16:00:00 1969 PST | 196 | 20 | 2 | 2440588 | 0
1095 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1096 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1097 Mon Feb 10 17:32:02 1997 PST | 199 | 20 | 2 | 2450491 | 855624722
1098 Mon Feb 10 17:32:01.4 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.4
1099 Mon Feb 10 17:32:01.5 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.5
1100 Mon Feb 10 17:32:01.6 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.6
1101 Thu Jan 02 00:00:00 1997 PST | 199 | 20 | 2 | 2450451 | 852192000
1102 Thu Jan 02 03:04:05 1997 PST | 199 | 20 | 2 | 2450451 | 852203045
1103 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1104 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1105 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1106 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1107 Tue Jun 10 17:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865989121
1108 Sat Sep 22 18:19:20 2001 PDT | 200 | 21 | 3 | 2452176 | 1001207960
1109 Wed Mar 15 08:14:01 2000 PST | 200 | 20 | 2 | 2451619 | 953136841
1110 Wed Mar 15 04:14:02 2000 PST | 200 | 20 | 2 | 2451619 | 953122442
1111 Wed Mar 15 02:14:03 2000 PST | 200 | 20 | 2 | 2451619 | 953115243
1112 Wed Mar 15 03:14:04 2000 PST | 200 | 20 | 2 | 2451619 | 953118844
1113 Wed Mar 15 01:14:05 2000 PST | 200 | 20 | 2 | 2451619 | 953111645
1114 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1115 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1116 Mon Feb 10 17:32:00 1997 PST | 199 | 20 | 2 | 2450491 | 855624720
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 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1122 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1123 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1124 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1125 Mon Feb 10 14:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855613921
1126 Thu Jul 10 14:32:01 1997 PDT | 199 | 20 | 2 | 2450641 | 868570321
1127 Tue Jun 10 18:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865992721
1128 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1129 Tue Feb 11 17:32:01 1997 PST | 199 | 20 | 2 | 2450492 | 855711121
1130 Wed Feb 12 17:32:01 1997 PST | 199 | 20 | 2 | 2450493 | 855797521
1131 Thu Feb 13 17:32:01 1997 PST | 199 | 20 | 2 | 2450494 | 855883921
1132 Fri Feb 14 17:32:01 1997 PST | 199 | 20 | 2 | 2450495 | 855970321
1133 Sat Feb 15 17:32:01 1997 PST | 199 | 20 | 2 | 2450496 | 856056721
1134 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1135 Tue Feb 16 17:32:01 0097 PST BC | -10 | -1 | -1 | 1686043 | -65192682479
1136 Sat Feb 16 17:32:01 0097 PST | 9 | 1 | 1 | 1756537 | -59102000879
1137 Thu Feb 16 17:32:01 0597 PST | 59 | 6 | 1 | 1939158 | -43323546479
1138 Tue Feb 16 17:32:01 1097 PST | 109 | 11 | 2 | 2121779 | -27545092079
1139 Sat Feb 16 17:32:01 1697 PST | 169 | 17 | 2 | 2340925 | -8610877679
1140 Thu Feb 16 17:32:01 1797 PST | 179 | 18 | 2 | 2377449 | -5455204079
1141 Tue Feb 16 17:32:01 1897 PST | 189 | 19 | 2 | 2413973 | -2299530479
1142 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1143 Sat Feb 16 17:32:01 2097 PST | 209 | 21 | 3 | 2487022 | 4011903121
1144 Wed Feb 28 17:32:01 1996 PST | 199 | 20 | 2 | 2450143 | 825557521
1145 Thu Feb 29 17:32:01 1996 PST | 199 | 20 | 2 | 2450144 | 825643921
1146 Fri Mar 01 17:32:01 1996 PST | 199 | 20 | 2 | 2450145 | 825730321
1147 Mon Dec 30 17:32:01 1996 PST | 199 | 20 | 2 | 2450449 | 851995921
1148 Tue Dec 31 17:32:01 1996 PST | 199 | 20 | 2 | 2450450 | 852082321
1149 Wed Jan 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450451 | 852168721
1150 Fri Feb 28 17:32:01 1997 PST | 199 | 20 | 2 | 2450509 | 857179921
1151 Sat Mar 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450510 | 857266321
1152 Tue Dec 30 17:32:01 1997 PST | 199 | 20 | 2 | 2450814 | 883531921
1153 Wed Dec 31 17:32:01 1997 PST | 199 | 20 | 2 | 2450815 | 883618321
1154 Fri Dec 31 17:32:01 1999 PST | 199 | 20 | 2 | 2451545 | 946690321
1155 Sat Jan 01 17:32:01 2000 PST | 200 | 20 | 2 | 2451546 | 946776721
1156 Sun Dec 31 17:32:01 2000 PST | 200 | 20 | 2 | 2451911 | 978312721
1157 Mon Jan 01 17:32:01 2001 PST | 200 | 21 | 3 | 2451912 | 978399121
1158 (66 rows)
1160 SELECT d1 as timestamptz,
1161 date_part( 'timezone', d1) AS timezone,
1162 date_part( 'timezone_hour', d1) AS timezone_hour,
1163 date_part( 'timezone_minute', d1) AS timezone_minute
1164 FROM TIMESTAMPTZ_TBL;
1165 timestamptz | timezone | timezone_hour | timezone_minute
1166 ---------------------------------+----------+---------------+-----------------
1167 -infinity | | |
1168 infinity | | |
1169 Wed Dec 31 16:00:00 1969 PST | -28800 | -8 | 0
1170 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1171 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1172 Mon Feb 10 17:32:02 1997 PST | -28800 | -8 | 0
1173 Mon Feb 10 17:32:01.4 1997 PST | -28800 | -8 | 0
1174 Mon Feb 10 17:32:01.5 1997 PST | -28800 | -8 | 0
1175 Mon Feb 10 17:32:01.6 1997 PST | -28800 | -8 | 0
1176 Thu Jan 02 00:00:00 1997 PST | -28800 | -8 | 0
1177 Thu Jan 02 03:04:05 1997 PST | -28800 | -8 | 0
1178 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1179 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1180 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1181 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1182 Tue Jun 10 17:32:01 1997 PDT | -25200 | -7 | 0
1183 Sat Sep 22 18:19:20 2001 PDT | -25200 | -7 | 0
1184 Wed Mar 15 08:14:01 2000 PST | -28800 | -8 | 0
1185 Wed Mar 15 04:14:02 2000 PST | -28800 | -8 | 0
1186 Wed Mar 15 02:14:03 2000 PST | -28800 | -8 | 0
1187 Wed Mar 15 03:14:04 2000 PST | -28800 | -8 | 0
1188 Wed Mar 15 01:14:05 2000 PST | -28800 | -8 | 0
1189 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1190 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1191 Mon Feb 10 17:32:00 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 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1197 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1198 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1199 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1200 Mon Feb 10 14:32:01 1997 PST | -28800 | -8 | 0
1201 Thu Jul 10 14:32:01 1997 PDT | -25200 | -7 | 0
1202 Tue Jun 10 18:32:01 1997 PDT | -25200 | -7 | 0
1203 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1204 Tue Feb 11 17:32:01 1997 PST | -28800 | -8 | 0
1205 Wed Feb 12 17:32:01 1997 PST | -28800 | -8 | 0
1206 Thu Feb 13 17:32:01 1997 PST | -28800 | -8 | 0
1207 Fri Feb 14 17:32:01 1997 PST | -28800 | -8 | 0
1208 Sat Feb 15 17:32:01 1997 PST | -28800 | -8 | 0
1209 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1210 Tue Feb 16 17:32:01 0097 PST BC | -28800 | -8 | 0
1211 Sat Feb 16 17:32:01 0097 PST | -28800 | -8 | 0
1212 Thu Feb 16 17:32:01 0597 PST | -28800 | -8 | 0
1213 Tue Feb 16 17:32:01 1097 PST | -28800 | -8 | 0
1214 Sat Feb 16 17:32:01 1697 PST | -28800 | -8 | 0
1215 Thu Feb 16 17:32:01 1797 PST | -28800 | -8 | 0
1216 Tue Feb 16 17:32:01 1897 PST | -28800 | -8 | 0
1217 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1218 Sat Feb 16 17:32:01 2097 PST | -28800 | -8 | 0
1219 Wed Feb 28 17:32:01 1996 PST | -28800 | -8 | 0
1220 Thu Feb 29 17:32:01 1996 PST | -28800 | -8 | 0
1221 Fri Mar 01 17:32:01 1996 PST | -28800 | -8 | 0
1222 Mon Dec 30 17:32:01 1996 PST | -28800 | -8 | 0
1223 Tue Dec 31 17:32:01 1996 PST | -28800 | -8 | 0
1224 Wed Jan 01 17:32:01 1997 PST | -28800 | -8 | 0
1225 Fri Feb 28 17:32:01 1997 PST | -28800 | -8 | 0
1226 Sat Mar 01 17:32:01 1997 PST | -28800 | -8 | 0
1227 Tue Dec 30 17:32:01 1997 PST | -28800 | -8 | 0
1228 Wed Dec 31 17:32:01 1997 PST | -28800 | -8 | 0
1229 Fri Dec 31 17:32:01 1999 PST | -28800 | -8 | 0
1230 Sat Jan 01 17:32:01 2000 PST | -28800 | -8 | 0
1231 Sun Dec 31 17:32:01 2000 PST | -28800 | -8 | 0
1232 Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0
1233 (66 rows)
1235 -- extract implementation is mostly the same as date_part, so only
1236 -- test a few cases for additional coverage.
1237 SELECT d1 as "timestamp",
1238 extract(microseconds from d1) AS microseconds,
1239 extract(milliseconds from d1) AS milliseconds,
1240 extract(seconds from d1) AS seconds,
1241 round(extract(julian from d1)) AS julian,
1242 extract(epoch from d1) AS epoch
1243 FROM TIMESTAMPTZ_TBL;
1244 timestamp | microseconds | milliseconds | seconds | julian | epoch
1245 ---------------------------------+--------------+--------------+-----------+-----------+---------------------
1246 -infinity | | | | -Infinity | -Infinity
1247 infinity | | | | Infinity | Infinity
1248 Wed Dec 31 16:00:00 1969 PST | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
1249 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1250 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1251 Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000.000 | 2.000000 | 2450491 | 855624722.000000
1252 Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400.000 | 1.400000 | 2450491 | 855624721.400000
1253 Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500.000 | 1.500000 | 2450491 | 855624721.500000
1254 Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600.000 | 1.600000 | 2450491 | 855624721.600000
1255 Thu Jan 02 00:00:00 1997 PST | 0 | 0.000 | 0.000000 | 2450451 | 852192000.000000
1256 Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000.000 | 5.000000 | 2450451 | 852203045.000000
1257 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1258 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1259 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1260 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1261 Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865989121.000000
1262 Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001207960.000000
1263 Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451619 | 953136841.000000
1264 Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000.000 | 2.000000 | 2451619 | 953122442.000000
1265 Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000.000 | 3.000000 | 2451619 | 953115243.000000
1266 Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000.000 | 4.000000 | 2451619 | 953118844.000000
1267 Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000.000 | 5.000000 | 2451619 | 953111645.000000
1268 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1269 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1270 Mon Feb 10 17:32:00 1997 PST | 0 | 0.000 | 0.000000 | 2450491 | 855624720.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 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1276 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1277 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1278 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1279 Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855613921.000000
1280 Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450641 | 868570321.000000
1281 Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865992721.000000
1282 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1283 Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450492 | 855711121.000000
1284 Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450493 | 855797521.000000
1285 Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450494 | 855883921.000000
1286 Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450495 | 855970321.000000
1287 Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450496 | 856056721.000000
1288 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1289 Tue Feb 16 17:32:01 0097 PST BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192682479.000000
1290 Sat Feb 16 17:32:01 0097 PST | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102000879.000000
1291 Thu Feb 16 17:32:01 0597 PST | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323546479.000000
1292 Tue Feb 16 17:32:01 1097 PST | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545092079.000000
1293 Sat Feb 16 17:32:01 1697 PST | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610877679.000000
1294 Thu Feb 16 17:32:01 1797 PST | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455204079.000000
1295 Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299530479.000000
1296 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1297 Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011903121.000000
1298 Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450143 | 825557521.000000
1299 Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450144 | 825643921.000000
1300 Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450145 | 825730321.000000
1301 Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450449 | 851995921.000000
1302 Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450450 | 852082321.000000
1303 Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450451 | 852168721.000000
1304 Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450509 | 857179921.000000
1305 Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450510 | 857266321.000000
1306 Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450814 | 883531921.000000
1307 Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450815 | 883618321.000000
1308 Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000.000 | 1.000000 | 2451545 | 946690321.000000
1309 Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451546 | 946776721.000000
1310 Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451911 | 978312721.000000
1311 Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000.000 | 1.000000 | 2451912 | 978399121.000000
1312 (66 rows)
1314 -- value near upper bound uses special case in code
1315 SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz);
1316 date_part
1317 ---------------
1318 9224097091200
1319 (1 row)
1321 SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
1322 extract
1323 ----------------------
1324 9224097091200.000000
1325 (1 row)
1327 -- another internal overflow test case
1328 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
1329 extract
1330 --------------------
1331 95617584000.000000
1332 (1 row)
1334 -- test edge-case overflow in timestamp subtraction
1335 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224193 UTC' AS ok;
1336 ok
1337 -----------------------------------------
1338 @ 106751991 days 4 hours 54.775807 secs
1339 (1 row)
1341 SELECT timestamptz '294276-12-31 23:59:59 UTC' - timestamptz '1999-12-23 19:59:04.224192 UTC' AS overflows;
1342 ERROR: interval out of range
1343 -- TO_CHAR()
1344 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
1345 FROM TIMESTAMPTZ_TBL;
1346 to_char
1347 ------------------------------------------------------------------------------------------
1350 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1351 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1352 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1353 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1354 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1355 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1356 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1357 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1358 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1359 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1360 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1361 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1362 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1363 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1364 SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep
1365 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1366 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1367 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1368 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1369 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1370 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1371 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1372 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
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 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1378 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1379 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1380 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1381 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1382 THURSDAY Thursday thursday THU Thu thu JULY July july VII JUL Jul jul
1383 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1384 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1385 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1386 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1387 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1388 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1389 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1390 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1391 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1392 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1393 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1394 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1395 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1396 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1397 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1398 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1399 SATURDAY Saturday saturday SAT Sat sat 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 MARCH March march III MAR Mar mar
1403 MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec
1404 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1405 WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan
1406 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1407 SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar
1408 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1409 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1410 FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec
1411 SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
1412 SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
1413 MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
1414 (66 rows)
1416 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
1417 FROM TIMESTAMPTZ_TBL;
1418 to_char
1419 --------------------------------------------------------------
1422 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1423 MONDAY Monday monday FEBRUARY February february II
1424 MONDAY Monday monday FEBRUARY February february II
1425 MONDAY Monday monday FEBRUARY February february II
1426 MONDAY Monday monday FEBRUARY February february II
1427 MONDAY Monday monday FEBRUARY February february II
1428 MONDAY Monday monday FEBRUARY February february II
1429 THURSDAY Thursday thursday JANUARY January january I
1430 THURSDAY Thursday thursday JANUARY January january I
1431 MONDAY Monday monday FEBRUARY February february II
1432 MONDAY Monday monday FEBRUARY February february II
1433 MONDAY Monday monday FEBRUARY February february II
1434 MONDAY Monday monday FEBRUARY February february II
1435 TUESDAY Tuesday tuesday JUNE June june VI
1436 SATURDAY Saturday saturday SEPTEMBER September september IX
1437 WEDNESDAY Wednesday wednesday MARCH March march III
1438 WEDNESDAY Wednesday wednesday MARCH March march III
1439 WEDNESDAY Wednesday wednesday MARCH March march III
1440 WEDNESDAY Wednesday wednesday MARCH March march III
1441 WEDNESDAY Wednesday wednesday MARCH March march III
1442 MONDAY Monday monday FEBRUARY February february II
1443 MONDAY Monday monday FEBRUARY February february II
1444 MONDAY Monday monday FEBRUARY February february II
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 MONDAY Monday monday FEBRUARY February february II
1450 MONDAY Monday monday FEBRUARY February february II
1451 MONDAY Monday monday FEBRUARY February february II
1452 MONDAY Monday monday FEBRUARY February february II
1453 MONDAY Monday monday FEBRUARY February february II
1454 THURSDAY Thursday thursday JULY July july VII
1455 TUESDAY Tuesday tuesday JUNE June june VI
1456 MONDAY Monday monday FEBRUARY February february II
1457 TUESDAY Tuesday tuesday FEBRUARY February february II
1458 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1459 THURSDAY Thursday thursday FEBRUARY February february II
1460 FRIDAY Friday friday FEBRUARY February february II
1461 SATURDAY Saturday saturday FEBRUARY February february II
1462 SUNDAY Sunday sunday FEBRUARY February february II
1463 TUESDAY Tuesday tuesday FEBRUARY February february II
1464 SATURDAY Saturday saturday FEBRUARY February february II
1465 THURSDAY Thursday thursday FEBRUARY February february II
1466 TUESDAY Tuesday tuesday FEBRUARY February february II
1467 SATURDAY Saturday saturday FEBRUARY February february II
1468 THURSDAY Thursday thursday FEBRUARY February february II
1469 TUESDAY Tuesday tuesday FEBRUARY February february II
1470 SUNDAY Sunday sunday FEBRUARY February february II
1471 SATURDAY Saturday saturday FEBRUARY February february II
1472 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1473 THURSDAY Thursday thursday FEBRUARY February february II
1474 FRIDAY Friday friday MARCH March march III
1475 MONDAY Monday monday DECEMBER December december XII
1476 TUESDAY Tuesday tuesday DECEMBER December december XII
1477 WEDNESDAY Wednesday wednesday JANUARY January january I
1478 FRIDAY Friday friday FEBRUARY February february II
1479 SATURDAY Saturday saturday MARCH March march III
1480 TUESDAY Tuesday tuesday DECEMBER December december XII
1481 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1482 FRIDAY Friday friday DECEMBER December december XII
1483 SATURDAY Saturday saturday JANUARY January january I
1484 SUNDAY Sunday sunday DECEMBER December december XII
1485 MONDAY Monday monday JANUARY January january I
1486 (66 rows)
1488 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
1489 FROM TIMESTAMPTZ_TBL;
1490 to_char
1491 --------------------------------------------------
1494 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1495 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1496 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1497 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1498 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1499 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1500 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1501 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1502 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1503 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1504 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1505 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1506 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1507 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1508 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
1509 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1510 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1511 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1512 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1513 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1514 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1515 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1516 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
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 1 02 06 041 10 2 2450490
1522 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1523 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1524 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1525 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1526 1,997 1997 997 97 7 20 3 07 28 191 10 5 2450640
1527 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
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 042 11 3 2450491
1530 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1531 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1532 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1533 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1534 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1535 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
1536 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
1537 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1538 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1539 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1540 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1541 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1542 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1543 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
1544 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1545 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1546 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1547 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1548 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1549 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1550 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1551 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1552 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1553 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1554 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1555 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
1556 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
1557 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
1558 (66 rows)
1560 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
1561 FROM TIMESTAMPTZ_TBL;
1562 to_char
1563 -------------------------------------------------
1566 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1567 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1568 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1569 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1570 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1571 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1572 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1573 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1574 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1575 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1576 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1577 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1578 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1579 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1580 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
1581 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1582 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1583 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1584 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1585 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1586 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1587 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1588 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
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 1 2 6 41 10 2 2450490
1594 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1595 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1596 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1597 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1598 1,997 1997 997 97 7 20 3 7 28 191 10 5 2450640
1599 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
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 42 11 3 2450491
1602 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1603 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1604 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1605 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1606 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1607 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
1608 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
1609 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1610 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1611 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1612 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1613 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1614 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1615 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
1616 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1617 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1618 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1619 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1620 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1621 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1622 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1623 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1624 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1625 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1626 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1627 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
1628 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
1629 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
1630 (66 rows)
1632 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
1633 FROM TIMESTAMPTZ_TBL;
1634 to_char
1635 ----------------------
1638 04 04 16 00 00 57600
1639 05 05 17 32 01 63121
1640 05 05 17 32 01 63121
1641 05 05 17 32 02 63122
1642 05 05 17 32 01 63121
1643 05 05 17 32 01 63121
1644 05 05 17 32 01 63121
1645 12 12 00 00 00 0
1646 03 03 03 04 05 11045
1647 05 05 17 32 01 63121
1648 05 05 17 32 01 63121
1649 05 05 17 32 01 63121
1650 05 05 17 32 01 63121
1651 05 05 17 32 01 63121
1652 06 06 18 19 20 65960
1653 08 08 08 14 01 29641
1654 04 04 04 14 02 15242
1655 02 02 02 14 03 8043
1656 03 03 03 14 04 11644
1657 01 01 01 14 05 4445
1658 05 05 17 32 01 63121
1659 05 05 17 32 01 63121
1660 05 05 17 32 00 63120
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 09 09 09 32 01 34321
1667 09 09 09 32 01 34321
1668 09 09 09 32 01 34321
1669 02 02 14 32 01 52321
1670 02 02 14 32 01 52321
1671 06 06 18 32 01 66721
1672 05 05 17 32 01 63121
1673 05 05 17 32 01 63121
1674 05 05 17 32 01 63121
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 05 05 17 32 01 63121
1681 05 05 17 32 01 63121
1682 05 05 17 32 01 63121
1683 05 05 17 32 01 63121
1684 05 05 17 32 01 63121
1685 05 05 17 32 01 63121
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 (66 rows)
1704 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
1705 FROM TIMESTAMPTZ_TBL;
1706 to_char
1707 -------------------------------------------------
1710 HH:MI:SS is 04:00:00 "text between quote marks"
1711 HH:MI:SS is 05:32:01 "text between quote marks"
1712 HH:MI:SS is 05:32:01 "text between quote marks"
1713 HH:MI:SS is 05:32:02 "text between quote marks"
1714 HH:MI:SS is 05:32:01 "text between quote marks"
1715 HH:MI:SS is 05:32:01 "text between quote marks"
1716 HH:MI:SS is 05:32:01 "text between quote marks"
1717 HH:MI:SS is 12:00:00 "text between quote marks"
1718 HH:MI:SS is 03:04:05 "text between quote marks"
1719 HH:MI:SS is 05:32:01 "text between quote marks"
1720 HH:MI:SS is 05:32:01 "text between quote marks"
1721 HH:MI:SS is 05:32:01 "text between quote marks"
1722 HH:MI:SS is 05:32:01 "text between quote marks"
1723 HH:MI:SS is 05:32:01 "text between quote marks"
1724 HH:MI:SS is 06:19:20 "text between quote marks"
1725 HH:MI:SS is 08:14:01 "text between quote marks"
1726 HH:MI:SS is 04:14:02 "text between quote marks"
1727 HH:MI:SS is 02:14:03 "text between quote marks"
1728 HH:MI:SS is 03:14:04 "text between quote marks"
1729 HH:MI:SS is 01:14:05 "text between quote marks"
1730 HH:MI:SS is 05:32:01 "text between quote marks"
1731 HH:MI:SS is 05:32:01 "text between quote marks"
1732 HH:MI:SS is 05:32:00 "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 09:32:01 "text between quote marks"
1739 HH:MI:SS is 09:32:01 "text between quote marks"
1740 HH:MI:SS is 09:32:01 "text between quote marks"
1741 HH:MI:SS is 02:32:01 "text between quote marks"
1742 HH:MI:SS is 02:32:01 "text between quote marks"
1743 HH:MI:SS is 06:32:01 "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:01 "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 05:32:01 "text between quote marks"
1753 HH:MI:SS is 05:32:01 "text between quote marks"
1754 HH:MI:SS is 05:32:01 "text between quote marks"
1755 HH:MI:SS is 05:32:01 "text between quote marks"
1756 HH:MI:SS is 05:32:01 "text between quote marks"
1757 HH:MI:SS is 05: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 (66 rows)
1776 SELECT to_char(d1, 'HH24--text--MI--text--SS')
1777 FROM TIMESTAMPTZ_TBL;
1778 to_char
1779 ------------------------
1782 16--text--00--text--00
1783 17--text--32--text--01
1784 17--text--32--text--01
1785 17--text--32--text--02
1786 17--text--32--text--01
1787 17--text--32--text--01
1788 17--text--32--text--01
1789 00--text--00--text--00
1790 03--text--04--text--05
1791 17--text--32--text--01
1792 17--text--32--text--01
1793 17--text--32--text--01
1794 17--text--32--text--01
1795 17--text--32--text--01
1796 18--text--19--text--20
1797 08--text--14--text--01
1798 04--text--14--text--02
1799 02--text--14--text--03
1800 03--text--14--text--04
1801 01--text--14--text--05
1802 17--text--32--text--01
1803 17--text--32--text--01
1804 17--text--32--text--00
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 09--text--32--text--01
1811 09--text--32--text--01
1812 09--text--32--text--01
1813 14--text--32--text--01
1814 14--text--32--text--01
1815 18--text--32--text--01
1816 17--text--32--text--01
1817 17--text--32--text--01
1818 17--text--32--text--01
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 17--text--32--text--01
1825 17--text--32--text--01
1826 17--text--32--text--01
1827 17--text--32--text--01
1828 17--text--32--text--01
1829 17--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 (66 rows)
1848 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
1849 FROM TIMESTAMPTZ_TBL;
1850 to_char
1851 -------------------------
1854 1969TH 1969th 2440587th
1855 1997TH 1997th 2450490th
1856 1997TH 1997th 2450490th
1857 1997TH 1997th 2450490th
1858 1997TH 1997th 2450490th
1859 1997TH 1997th 2450490th
1860 1997TH 1997th 2450490th
1861 1997TH 1997th 2450451st
1862 1997TH 1997th 2450451st
1863 1997TH 1997th 2450490th
1864 1997TH 1997th 2450490th
1865 1997TH 1997th 2450490th
1866 1997TH 1997th 2450490th
1867 1997TH 1997th 2450610th
1868 2001ST 2001st 2452175th
1869 2000TH 2000th 2451619th
1870 2000TH 2000th 2451619th
1871 2000TH 2000th 2451619th
1872 2000TH 2000th 2451619th
1873 2000TH 2000th 2451619th
1874 1997TH 1997th 2450490th
1875 1997TH 1997th 2450490th
1876 1997TH 1997th 2450490th
1877 1997TH 1997th 2450490th
1878 1997TH 1997th 2450490th
1879 1997TH 1997th 2450490th
1880 1997TH 1997th 2450490th
1881 1997TH 1997th 2450490th
1882 1997TH 1997th 2450490th
1883 1997TH 1997th 2450490th
1884 1997TH 1997th 2450490th
1885 1997TH 1997th 2450490th
1886 1997TH 1997th 2450640th
1887 1997TH 1997th 2450610th
1888 1997TH 1997th 2450490th
1889 1997TH 1997th 2450491st
1890 1997TH 1997th 2450492nd
1891 1997TH 1997th 2450493rd
1892 1997TH 1997th 2450494th
1893 1997TH 1997th 2450495th
1894 1997TH 1997th 2450496th
1895 0097TH 0097th 1686042nd
1896 0097TH 0097th 1756536th
1897 0597TH 0597th 1939157th
1898 1097TH 1097th 2121778th
1899 1697TH 1697th 2340924th
1900 1797TH 1797th 2377448th
1901 1897TH 1897th 2413972nd
1902 1997TH 1997th 2450496th
1903 2097TH 2097th 2487021st
1904 1996TH 1996th 2450142nd
1905 1996TH 1996th 2450143rd
1906 1996TH 1996th 2450144th
1907 1996TH 1996th 2450448th
1908 1996TH 1996th 2450449th
1909 1997TH 1997th 2450450th
1910 1997TH 1997th 2450508th
1911 1997TH 1997th 2450509th
1912 1997TH 1997th 2450813th
1913 1997TH 1997th 2450814th
1914 1999TH 1999th 2451544th
1915 2000TH 2000th 2451545th
1916 2000TH 2000th 2451910th
1917 2001ST 2001st 2451911th
1918 (66 rows)
1920 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')
1921 FROM TIMESTAMPTZ_TBL;
1922 to_char
1923 ---------------------------------------------------------------------
1926 1969 A.D. 1969 a.d. 1969 ad 04:00:00 P.M. 04:00:00 p.m. 04:00:00 pm
1927 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1928 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1929 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm
1930 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1931 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1932 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1933 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1934 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1935 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1936 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1937 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1938 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1939 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1940 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
1941 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
1942 2000 A.D. 2000 a.d. 2000 ad 04:14:02 A.M. 04:14:02 a.m. 04:14:02 am
1943 2000 A.D. 2000 a.d. 2000 ad 02:14:03 A.M. 02:14:03 a.m. 02:14:03 am
1944 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
1945 2000 A.D. 2000 a.d. 2000 ad 01:14:05 A.M. 01:14:05 a.m. 01:14:05 am
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 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1948 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
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 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1955 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1956 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1957 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1958 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1959 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
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:01 P.M. 05:32:01 p.m. 05:32:01 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 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1968 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1969 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1970 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1971 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1972 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1973 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05: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 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1976 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1977 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1978 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1979 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1980 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1981 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1982 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1983 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1984 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1985 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1986 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1987 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1988 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1989 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1990 (66 rows)
1992 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
1993 FROM TIMESTAMPTZ_TBL;
1994 to_char
1995 ------------------------
1998 1970 970 70 0 01 003 3
1999 1997 997 97 7 07 043 1
2000 1997 997 97 7 07 043 1
2001 1997 997 97 7 07 043 1
2002 1997 997 97 7 07 043 1
2003 1997 997 97 7 07 043 1
2004 1997 997 97 7 07 043 1
2005 1997 997 97 7 01 004 4
2006 1997 997 97 7 01 004 4
2007 1997 997 97 7 07 043 1
2008 1997 997 97 7 07 043 1
2009 1997 997 97 7 07 043 1
2010 1997 997 97 7 07 043 1
2011 1997 997 97 7 24 163 2
2012 2001 001 01 1 38 265 6
2013 2000 000 00 0 11 073 3
2014 2000 000 00 0 11 073 3
2015 2000 000 00 0 11 073 3
2016 2000 000 00 0 11 073 3
2017 2000 000 00 0 11 073 3
2018 1997 997 97 7 07 043 1
2019 1997 997 97 7 07 043 1
2020 1997 997 97 7 07 043 1
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 07 043 1
2026 1997 997 97 7 07 043 1
2027 1997 997 97 7 07 043 1
2028 1997 997 97 7 07 043 1
2029 1997 997 97 7 07 043 1
2030 1997 997 97 7 28 193 4
2031 1997 997 97 7 24 163 2
2032 1997 997 97 7 07 043 1
2033 1997 997 97 7 07 044 2
2034 1997 997 97 7 07 045 3
2035 1997 997 97 7 07 046 4
2036 1997 997 97 7 07 047 5
2037 1997 997 97 7 07 048 6
2038 1997 997 97 7 07 049 7
2039 0097 097 97 7 07 044 2
2040 0097 097 97 7 07 048 6
2041 0597 597 97 7 07 046 4
2042 1097 097 97 7 07 044 2
2043 1697 697 97 7 07 048 6
2044 1797 797 97 7 07 046 4
2045 1897 897 97 7 07 044 2
2046 1997 997 97 7 07 049 7
2047 2097 097 97 7 07 048 6
2048 1996 996 96 6 09 059 3
2049 1996 996 96 6 09 060 4
2050 1996 996 96 6 09 061 5
2051 1997 997 97 7 01 001 1
2052 1997 997 97 7 01 002 2
2053 1997 997 97 7 01 003 3
2054 1997 997 97 7 09 061 5
2055 1997 997 97 7 09 062 6
2056 1998 998 98 8 01 002 2
2057 1998 998 98 8 01 003 3
2058 1999 999 99 9 52 362 5
2059 1999 999 99 9 52 363 6
2060 2000 000 00 0 52 364 7
2061 2001 001 01 1 01 001 1
2062 (66 rows)
2064 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
2065 FROM TIMESTAMPTZ_TBL;
2066 to_char
2067 ------------------------
2070 1970 970 70 0 1 3 3
2071 1997 997 97 7 7 43 1
2072 1997 997 97 7 7 43 1
2073 1997 997 97 7 7 43 1
2074 1997 997 97 7 7 43 1
2075 1997 997 97 7 7 43 1
2076 1997 997 97 7 7 43 1
2077 1997 997 97 7 1 4 4
2078 1997 997 97 7 1 4 4
2079 1997 997 97 7 7 43 1
2080 1997 997 97 7 7 43 1
2081 1997 997 97 7 7 43 1
2082 1997 997 97 7 7 43 1
2083 1997 997 97 7 24 163 2
2084 2001 1 1 1 38 265 6
2085 2000 0 0 0 11 73 3
2086 2000 0 0 0 11 73 3
2087 2000 0 0 0 11 73 3
2088 2000 0 0 0 11 73 3
2089 2000 0 0 0 11 73 3
2090 1997 997 97 7 7 43 1
2091 1997 997 97 7 7 43 1
2092 1997 997 97 7 7 43 1
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 7 43 1
2098 1997 997 97 7 7 43 1
2099 1997 997 97 7 7 43 1
2100 1997 997 97 7 7 43 1
2101 1997 997 97 7 7 43 1
2102 1997 997 97 7 28 193 4
2103 1997 997 97 7 24 163 2
2104 1997 997 97 7 7 43 1
2105 1997 997 97 7 7 44 2
2106 1997 997 97 7 7 45 3
2107 1997 997 97 7 7 46 4
2108 1997 997 97 7 7 47 5
2109 1997 997 97 7 7 48 6
2110 1997 997 97 7 7 49 7
2111 97 97 97 7 7 44 2
2112 97 97 97 7 7 48 6
2113 597 597 97 7 7 46 4
2114 1097 97 97 7 7 44 2
2115 1697 697 97 7 7 48 6
2116 1797 797 97 7 7 46 4
2117 1897 897 97 7 7 44 2
2118 1997 997 97 7 7 49 7
2119 2097 97 97 7 7 48 6
2120 1996 996 96 6 9 59 3
2121 1996 996 96 6 9 60 4
2122 1996 996 96 6 9 61 5
2123 1997 997 97 7 1 1 1
2124 1997 997 97 7 1 2 2
2125 1997 997 97 7 1 3 3
2126 1997 997 97 7 9 61 5
2127 1997 997 97 7 9 62 6
2128 1998 998 98 8 1 2 2
2129 1998 998 98 8 1 3 3
2130 1999 999 99 9 52 362 5
2131 1999 999 99 9 52 363 6
2132 2000 0 0 0 52 364 7
2133 2001 1 1 1 1 1 1
2134 (66 rows)
2136 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
2137 FROM (VALUES
2138 ('2018-11-02 12:34:56'::timestamptz),
2139 ('2018-11-02 12:34:56.78'),
2140 ('2018-11-02 12:34:56.78901'),
2141 ('2018-11-02 12:34:56.78901234')
2142 ) d(d);
2143 to_char
2144 --------------------------------------------------------------------
2145 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000
2146 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000
2147 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010
2148 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012
2149 (4 rows)
2151 -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours
2152 SET timezone = '00:00';
2153 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2154 OF | TZH:TZM
2155 -----+---------
2156 +00 | +00:00
2157 (1 row)
2159 SET timezone = '+02:00';
2160 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2161 OF | TZH:TZM
2162 -----+---------
2163 -02 | -02:00
2164 (1 row)
2166 SET timezone = '-13:00';
2167 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2168 OF | TZH:TZM
2169 -----+---------
2170 +13 | +13:00
2171 (1 row)
2173 SET timezone = '-00:30';
2174 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2175 OF | TZH:TZM
2176 --------+---------
2177 +00:30 | +00:30
2178 (1 row)
2180 SET timezone = '00:30';
2181 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2182 OF | TZH:TZM
2183 --------+---------
2184 -00:30 | -00:30
2185 (1 row)
2187 SET timezone = '-04:30';
2188 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2189 OF | TZH:TZM
2190 --------+---------
2191 +04:30 | +04:30
2192 (1 row)
2194 SET timezone = '04:30';
2195 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2196 OF | TZH:TZM
2197 --------+---------
2198 -04:30 | -04:30
2199 (1 row)
2201 SET timezone = '-04:15';
2202 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2203 OF | TZH:TZM
2204 --------+---------
2205 +04:15 | +04:15
2206 (1 row)
2208 SET timezone = '04:15';
2209 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2210 OF | TZH:TZM
2211 --------+---------
2212 -04:15 | -04:15
2213 (1 row)
2215 RESET timezone;
2216 -- Check of, tzh, tzm with various zone offsets.
2217 SET timezone = '00:00';
2218 SELECT to_char(now(), 'of') as "Of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2219 Of | tzh:tzm
2220 -----+---------
2221 +00 | +00:00
2222 (1 row)
2224 SET timezone = '+02:00';
2225 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2226 of | tzh:tzm
2227 -----+---------
2228 -02 | -02:00
2229 (1 row)
2231 SET timezone = '-13:00';
2232 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2233 of | tzh:tzm
2234 -----+---------
2235 +13 | +13:00
2236 (1 row)
2238 SET timezone = '-00:30';
2239 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2240 of | tzh:tzm
2241 --------+---------
2242 +00:30 | +00:30
2243 (1 row)
2245 SET timezone = '00:30';
2246 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2247 of | tzh:tzm
2248 --------+---------
2249 -00:30 | -00:30
2250 (1 row)
2252 SET timezone = '-04:30';
2253 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2254 of | tzh:tzm
2255 --------+---------
2256 +04:30 | +04:30
2257 (1 row)
2259 SET timezone = '04:30';
2260 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2261 of | tzh:tzm
2262 --------+---------
2263 -04:30 | -04:30
2264 (1 row)
2266 SET timezone = '-04:15';
2267 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2268 of | tzh:tzm
2269 --------+---------
2270 +04:15 | +04:15
2271 (1 row)
2273 SET timezone = '04:15';
2274 SELECT to_char(now(), 'of') as "of", to_char(now(), 'tzh:tzm') as "tzh:tzm";
2275 of | tzh:tzm
2276 --------+---------
2277 -04:15 | -04:15
2278 (1 row)
2280 RESET timezone;
2281 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
2282 -- Test year field value with len > 4
2283 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
2284 INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
2285 INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
2286 INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
2287 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
2288 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
2289 --Verify data
2290 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
2291 a | b
2292 ---+--------------------------------
2293 1 | Wed Mar 12 13:58:48 1000 PST
2294 2 | Sun Mar 12 14:58:48 10000 PDT
2295 3 | Sun Mar 12 14:58:48 100000 PDT
2296 3 | Sun Mar 12 14:58:48 10000 PDT
2297 4 | Sun Mar 12 14:58:48 10000 PDT
2298 4 | Sun Mar 12 14:58:48 100000 PDT
2299 (6 rows)
2301 --Cleanup
2302 DROP TABLE TIMESTAMPTZ_TST;
2303 -- test timestamptz constructors
2304 set TimeZone to 'America/New_York';
2305 -- numeric timezone
2306 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
2307 make_timestamptz
2308 ---------------------------------
2309 Sun Jul 15 08:15:55.33 1973 EDT
2310 (1 row)
2312 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
2313 make_timestamptz
2314 ---------------------------------
2315 Sun Jul 15 02:15:55.33 1973 EDT
2316 (1 row)
2318 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2');
2319 make_timestamptz
2320 ---------------------------------
2321 Sun Jul 15 06:15:55.33 1973 EDT
2322 (1 row)
2324 WITH tzs (tz) AS (VALUES
2325 ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'),
2326 ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'),
2327 ('+10:00:1'), ('+10:00:01'),
2328 ('+10:00:10'))
2329 SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
2330 make_timestamptz | tz
2331 ------------------------------+-----------
2332 Fri Feb 26 21:45:00 2010 EST | +1
2333 Fri Feb 26 21:45:00 2010 EST | +1:
2334 Fri Feb 26 21:45:00 2010 EST | +1:0
2335 Fri Feb 26 21:45:00 2010 EST | +100
2336 Fri Feb 26 21:45:00 2010 EST | +1:00
2337 Fri Feb 26 21:45:00 2010 EST | +01:00
2338 Fri Feb 26 12:45:00 2010 EST | +10
2339 Fri Feb 26 12:45:00 2010 EST | +1000
2340 Fri Feb 26 12:45:00 2010 EST | +10:
2341 Fri Feb 26 12:45:00 2010 EST | +10:0
2342 Fri Feb 26 12:45:00 2010 EST | +10:00
2343 Fri Feb 26 12:45:00 2010 EST | +10:00:
2344 Fri Feb 26 12:44:59 2010 EST | +10:00:1
2345 Fri Feb 26 12:44:59 2010 EST | +10:00:01
2346 Fri Feb 26 12:44:50 2010 EST | +10:00:10
2347 (15 rows)
2349 -- these should fail
2350 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
2351 ERROR: invalid input syntax for type numeric time zone: "2"
2352 HINT: Numeric time zones must have "-" or "+" as first character.
2353 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16');
2354 ERROR: numeric time zone "+16" out of range
2355 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16');
2356 ERROR: numeric time zone "-16" out of range
2357 -- should be true
2358 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
2359 ?column?
2360 ----------
2361 t
2362 (1 row)
2364 -- full timezone names
2365 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague';
2366 ?column?
2367 ----------
2368 t
2369 (1 row)
2371 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC';
2372 timezone
2373 --------------------------
2374 Tue Dec 09 23:00:00 2014
2375 (1 row)
2377 SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC';
2378 timezone
2379 --------------------------
2380 Wed Dec 09 15:56:00 1846
2381 (1 row)
2383 SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC';
2384 timezone
2385 --------------------------
2386 Fri Dec 09 23:50:39 1881
2387 (1 row)
2389 SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar');
2390 ERROR: time zone "Nehwon/Lankhmar" not recognized
2391 -- abbreviations
2392 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST');
2393 make_timestamptz
2394 ------------------------------
2395 Wed Dec 10 10:10:10 2008 EST
2396 (1 row)
2398 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT');
2399 make_timestamptz
2400 ------------------------------
2401 Wed Dec 10 09:10:10 2008 EST
2402 (1 row)
2404 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT');
2405 make_timestamptz
2406 ------------------------------
2407 Wed Dec 10 13:10:10 2014 EST
2408 (1 row)
2410 RESET TimeZone;
2411 -- generate_series for timestamptz
2412 select * from generate_series('2020-01-01 00:00'::timestamptz,
2413 '2020-01-02 03:00'::timestamptz,
2414 '1 hour'::interval);
2415 generate_series
2416 ------------------------------
2417 Wed Jan 01 00:00:00 2020 PST
2418 Wed Jan 01 01:00:00 2020 PST
2419 Wed Jan 01 02:00:00 2020 PST
2420 Wed Jan 01 03:00:00 2020 PST
2421 Wed Jan 01 04:00:00 2020 PST
2422 Wed Jan 01 05:00:00 2020 PST
2423 Wed Jan 01 06:00:00 2020 PST
2424 Wed Jan 01 07:00:00 2020 PST
2425 Wed Jan 01 08:00:00 2020 PST
2426 Wed Jan 01 09:00:00 2020 PST
2427 Wed Jan 01 10:00:00 2020 PST
2428 Wed Jan 01 11:00:00 2020 PST
2429 Wed Jan 01 12:00:00 2020 PST
2430 Wed Jan 01 13:00:00 2020 PST
2431 Wed Jan 01 14:00:00 2020 PST
2432 Wed Jan 01 15:00:00 2020 PST
2433 Wed Jan 01 16:00:00 2020 PST
2434 Wed Jan 01 17:00:00 2020 PST
2435 Wed Jan 01 18:00:00 2020 PST
2436 Wed Jan 01 19:00:00 2020 PST
2437 Wed Jan 01 20:00:00 2020 PST
2438 Wed Jan 01 21:00:00 2020 PST
2439 Wed Jan 01 22:00:00 2020 PST
2440 Wed Jan 01 23:00:00 2020 PST
2441 Thu Jan 02 00:00:00 2020 PST
2442 Thu Jan 02 01:00:00 2020 PST
2443 Thu Jan 02 02:00:00 2020 PST
2444 Thu Jan 02 03:00:00 2020 PST
2445 (28 rows)
2447 -- the LIMIT should allow this to terminate in a reasonable amount of time
2448 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
2449 select generate_series('2022-01-01 00:00'::timestamptz,
2450 'infinity'::timestamptz,
2451 '1 month'::interval) limit 10;
2452 generate_series
2453 ------------------------------
2454 Sat Jan 01 00:00:00 2022 PST
2455 Tue Feb 01 00:00:00 2022 PST
2456 Tue Mar 01 00:00:00 2022 PST
2457 Fri Apr 01 00:00:00 2022 PDT
2458 Sun May 01 00:00:00 2022 PDT
2459 Wed Jun 01 00:00:00 2022 PDT
2460 Fri Jul 01 00:00:00 2022 PDT
2461 Mon Aug 01 00:00:00 2022 PDT
2462 Thu Sep 01 00:00:00 2022 PDT
2463 Sat Oct 01 00:00:00 2022 PDT
2464 (10 rows)
2466 -- errors
2467 select * from generate_series('2020-01-01 00:00'::timestamptz,
2468 '2020-01-02 03:00'::timestamptz,
2469 '0 hour'::interval);
2470 ERROR: step size cannot equal zero
2471 -- Interval crossing time shift for Europe/Warsaw timezone (with DST)
2472 SET TimeZone to 'UTC';
2473 SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
2474 '1 day'::interval);
2475 date_add
2476 ------------------------------
2477 Sun Oct 30 23:00:00 2022 UTC
2478 (1 row)
2480 SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
2481 '1 day'::interval,
2482 'Europe/Warsaw');
2483 date_add
2484 ------------------------------
2485 Sun Oct 31 23:00:00 2021 UTC
2486 (1 row)
2488 SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
2489 '1 day'::interval);
2490 date_subtract
2491 ------------------------------
2492 Fri Oct 28 23:00:00 2022 UTC
2493 (1 row)
2495 SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
2496 '1 day'::interval,
2497 'Europe/Warsaw');
2498 date_subtract
2499 ------------------------------
2500 Fri Oct 29 22:00:00 2021 UTC
2501 (1 row)
2503 SELECT * FROM generate_series('2021-12-31 23:00:00+00'::timestamptz,
2504 '2020-12-31 23:00:00+00'::timestamptz,
2505 '-1 month'::interval,
2506 'Europe/Warsaw');
2507 generate_series
2508 ------------------------------
2509 Fri Dec 31 23:00:00 2021 UTC
2510 Tue Nov 30 23:00:00 2021 UTC
2511 Sun Oct 31 23:00:00 2021 UTC
2512 Thu Sep 30 22:00:00 2021 UTC
2513 Tue Aug 31 22:00:00 2021 UTC
2514 Sat Jul 31 22:00:00 2021 UTC
2515 Wed Jun 30 22:00:00 2021 UTC
2516 Mon May 31 22:00:00 2021 UTC
2517 Fri Apr 30 22:00:00 2021 UTC
2518 Wed Mar 31 22:00:00 2021 UTC
2519 Sun Feb 28 23:00:00 2021 UTC
2520 Sun Jan 31 23:00:00 2021 UTC
2521 Thu Dec 31 23:00:00 2020 UTC
2522 (13 rows)
2524 RESET TimeZone;
2525 --
2526 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
2527 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
2528 -- moved forwards in Mar 2011 and backwards again in Oct 2014.
2529 --
2530 SET TimeZone to 'UTC';
2531 SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz;
2532 timestamptz
2533 ------------------------------
2534 Sat Mar 26 21:00:00 2011 UTC
2535 (1 row)
2537 SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz;
2538 timestamptz
2539 ------------------------------
2540 Sat Mar 26 22:00:00 2011 UTC
2541 (1 row)
2543 SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz;
2544 timestamptz
2545 ------------------------------
2546 Sat Mar 26 22:59:59 2011 UTC
2547 (1 row)
2549 SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz;
2550 timestamptz
2551 ------------------------------
2552 Sat Mar 26 23:00:00 2011 UTC
2553 (1 row)
2555 SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz;
2556 timestamptz
2557 ------------------------------
2558 Sat Mar 26 23:00:01 2011 UTC
2559 (1 row)
2561 SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz;
2562 timestamptz
2563 ------------------------------
2564 Sat Mar 26 23:59:59 2011 UTC
2565 (1 row)
2567 SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz;
2568 timestamptz
2569 ------------------------------
2570 Sat Mar 26 23:00:00 2011 UTC
2571 (1 row)
2573 SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz;
2574 timestamptz
2575 ------------------------------
2576 Sat Mar 26 23:00:01 2011 UTC
2577 (1 row)
2579 SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz;
2580 timestamptz
2581 ------------------------------
2582 Sun Mar 27 00:00:00 2011 UTC
2583 (1 row)
2585 SELECT '2011-03-27 00:00:00 MSK'::timestamptz;
2586 timestamptz
2587 ------------------------------
2588 Sat Mar 26 21:00:00 2011 UTC
2589 (1 row)
2591 SELECT '2011-03-27 01:00:00 MSK'::timestamptz;
2592 timestamptz
2593 ------------------------------
2594 Sat Mar 26 22:00:00 2011 UTC
2595 (1 row)
2597 SELECT '2011-03-27 01:59:59 MSK'::timestamptz;
2598 timestamptz
2599 ------------------------------
2600 Sat Mar 26 22:59:59 2011 UTC
2601 (1 row)
2603 SELECT '2011-03-27 02:00:00 MSK'::timestamptz;
2604 timestamptz
2605 ------------------------------
2606 Sat Mar 26 22:00:00 2011 UTC
2607 (1 row)
2609 SELECT '2011-03-27 02:00:01 MSK'::timestamptz;
2610 timestamptz
2611 ------------------------------
2612 Sat Mar 26 22:00:01 2011 UTC
2613 (1 row)
2615 SELECT '2011-03-27 02:59:59 MSK'::timestamptz;
2616 timestamptz
2617 ------------------------------
2618 Sat Mar 26 22:59:59 2011 UTC
2619 (1 row)
2621 SELECT '2011-03-27 03:00:00 MSK'::timestamptz;
2622 timestamptz
2623 ------------------------------
2624 Sat Mar 26 23:00:00 2011 UTC
2625 (1 row)
2627 SELECT '2011-03-27 03:00:01 MSK'::timestamptz;
2628 timestamptz
2629 ------------------------------
2630 Sat Mar 26 23:00:01 2011 UTC
2631 (1 row)
2633 SELECT '2011-03-27 04:00:00 MSK'::timestamptz;
2634 timestamptz
2635 ------------------------------
2636 Sun Mar 27 00:00:00 2011 UTC
2637 (1 row)
2639 SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz;
2640 timestamptz
2641 ------------------------------
2642 Sat Oct 25 20:00:00 2014 UTC
2643 (1 row)
2645 SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz;
2646 timestamptz
2647 ------------------------------
2648 Sat Oct 25 20:59:59 2014 UTC
2649 (1 row)
2651 SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz;
2652 timestamptz
2653 ------------------------------
2654 Sat Oct 25 22:00:00 2014 UTC
2655 (1 row)
2657 SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz;
2658 timestamptz
2659 ------------------------------
2660 Sat Oct 25 22:00:01 2014 UTC
2661 (1 row)
2663 SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz;
2664 timestamptz
2665 ------------------------------
2666 Sat Oct 25 23:00:00 2014 UTC
2667 (1 row)
2669 SELECT '2014-10-26 00:00:00 MSK'::timestamptz;
2670 timestamptz
2671 ------------------------------
2672 Sat Oct 25 20:00:00 2014 UTC
2673 (1 row)
2675 SELECT '2014-10-26 00:59:59 MSK'::timestamptz;
2676 timestamptz
2677 ------------------------------
2678 Sat Oct 25 20:59:59 2014 UTC
2679 (1 row)
2681 SELECT '2014-10-26 01:00:00 MSK'::timestamptz;
2682 timestamptz
2683 ------------------------------
2684 Sat Oct 25 22:00:00 2014 UTC
2685 (1 row)
2687 SELECT '2014-10-26 01:00:01 MSK'::timestamptz;
2688 timestamptz
2689 ------------------------------
2690 Sat Oct 25 22:00:01 2014 UTC
2691 (1 row)
2693 SELECT '2014-10-26 02:00:00 MSK'::timestamptz;
2694 timestamptz
2695 ------------------------------
2696 Sat Oct 25 23:00:00 2014 UTC
2697 (1 row)
2699 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2700 timezone
2701 ------------------------------
2702 Sat Mar 26 21:00:00 2011 UTC
2703 (1 row)
2705 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2706 timezone
2707 ------------------------------
2708 Sat Mar 26 22:00:00 2011 UTC
2709 (1 row)
2711 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2712 timezone
2713 ------------------------------
2714 Sat Mar 26 22:59:59 2011 UTC
2715 (1 row)
2717 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2718 timezone
2719 ------------------------------
2720 Sat Mar 26 23:00:00 2011 UTC
2721 (1 row)
2723 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2724 timezone
2725 ------------------------------
2726 Sat Mar 26 23:00:01 2011 UTC
2727 (1 row)
2729 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2730 timezone
2731 ------------------------------
2732 Sat Mar 26 23:59:59 2011 UTC
2733 (1 row)
2735 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2736 timezone
2737 ------------------------------
2738 Sat Mar 26 23:00:00 2011 UTC
2739 (1 row)
2741 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2742 timezone
2743 ------------------------------
2744 Sat Mar 26 23:00:01 2011 UTC
2745 (1 row)
2747 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2748 timezone
2749 ------------------------------
2750 Sun Mar 27 00:00:00 2011 UTC
2751 (1 row)
2753 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK';
2754 timezone
2755 ------------------------------
2756 Sat Mar 26 21:00:00 2011 UTC
2757 (1 row)
2759 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK';
2760 timezone
2761 ------------------------------
2762 Sat Mar 26 22:00:00 2011 UTC
2763 (1 row)
2765 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK';
2766 timezone
2767 ------------------------------
2768 Sat Mar 26 22:59:59 2011 UTC
2769 (1 row)
2771 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK';
2772 timezone
2773 ------------------------------
2774 Sat Mar 26 22:00:00 2011 UTC
2775 (1 row)
2777 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK';
2778 timezone
2779 ------------------------------
2780 Sat Mar 26 22:00:01 2011 UTC
2781 (1 row)
2783 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK';
2784 timezone
2785 ------------------------------
2786 Sat Mar 26 22:59:59 2011 UTC
2787 (1 row)
2789 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK';
2790 timezone
2791 ------------------------------
2792 Sat Mar 26 23:00:00 2011 UTC
2793 (1 row)
2795 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK';
2796 timezone
2797 ------------------------------
2798 Sat Mar 26 23:00:01 2011 UTC
2799 (1 row)
2801 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK';
2802 timezone
2803 ------------------------------
2804 Sun Mar 27 00:00:00 2011 UTC
2805 (1 row)
2807 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2808 timezone
2809 ------------------------------
2810 Sat Oct 25 20:00:00 2014 UTC
2811 (1 row)
2813 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2814 timezone
2815 ------------------------------
2816 Sat Oct 25 20:59:59 2014 UTC
2817 (1 row)
2819 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2820 timezone
2821 ------------------------------
2822 Sat Oct 25 22:00:00 2014 UTC
2823 (1 row)
2825 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2826 timezone
2827 ------------------------------
2828 Sat Oct 25 22:00:01 2014 UTC
2829 (1 row)
2831 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2832 timezone
2833 ------------------------------
2834 Sat Oct 25 23:00:00 2014 UTC
2835 (1 row)
2837 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK';
2838 timezone
2839 ------------------------------
2840 Sat Oct 25 20:00:00 2014 UTC
2841 (1 row)
2843 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK';
2844 timezone
2845 ------------------------------
2846 Sat Oct 25 20:59:59 2014 UTC
2847 (1 row)
2849 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK';
2850 timezone
2851 ------------------------------
2852 Sat Oct 25 22:00:00 2014 UTC
2853 (1 row)
2855 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK';
2856 timezone
2857 ------------------------------
2858 Sat Oct 25 22:00:01 2014 UTC
2859 (1 row)
2861 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK';
2862 timezone
2863 ------------------------------
2864 Sat Oct 25 23:00:00 2014 UTC
2865 (1 row)
2867 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
2868 make_timestamptz
2869 ------------------------------
2870 Sat Oct 25 20:00:00 2014 UTC
2871 (1 row)
2873 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
2874 make_timestamptz
2875 ------------------------------
2876 Sat Oct 25 22:00:00 2014 UTC
2877 (1 row)
2879 SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
2880 to_timestamp
2881 ------------------------------
2882 Thu Jan 01 00:00:00 1970 UTC
2883 (1 row)
2885 SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
2886 to_timestamp
2887 ------------------------------
2888 Sat Jan 01 00:00:00 2000 UTC
2889 (1 row)
2891 SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
2892 to_timestamp
2893 -------------------------------------
2894 Fri Jan 01 12:34:56.789012 2010 UTC
2895 (1 row)
2897 -- edge cases
2898 SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
2899 to_timestamp
2900 ---------------------------------
2901 Mon Nov 24 00:00:00 4714 UTC BC
2902 (1 row)
2904 -- upper limit varies between integer and float timestamps, so hard to test
2905 -- nonfinite values
2906 SELECT to_timestamp(' Infinity'::float);
2907 to_timestamp
2908 --------------
2909 infinity
2910 (1 row)
2912 SELECT to_timestamp('-Infinity'::float);
2913 to_timestamp
2914 --------------
2915 -infinity
2916 (1 row)
2918 SELECT to_timestamp('NaN'::float);
2919 ERROR: timestamp cannot be NaN
2920 SET TimeZone to 'Europe/Moscow';
2921 SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
2922 timestamptz
2923 ------------------------------
2924 Sun Mar 27 00:00:00 2011 MSK
2925 (1 row)
2927 SELECT '2011-03-26 22:00:00 UTC'::timestamptz;
2928 timestamptz
2929 ------------------------------
2930 Sun Mar 27 01:00:00 2011 MSK
2931 (1 row)
2933 SELECT '2011-03-26 22:59:59 UTC'::timestamptz;
2934 timestamptz
2935 ------------------------------
2936 Sun Mar 27 01:59:59 2011 MSK
2937 (1 row)
2939 SELECT '2011-03-26 23:00:00 UTC'::timestamptz;
2940 timestamptz
2941 ------------------------------
2942 Sun Mar 27 03:00:00 2011 MSK
2943 (1 row)
2945 SELECT '2011-03-26 23:00:01 UTC'::timestamptz;
2946 timestamptz
2947 ------------------------------
2948 Sun Mar 27 03:00:01 2011 MSK
2949 (1 row)
2951 SELECT '2011-03-26 23:59:59 UTC'::timestamptz;
2952 timestamptz
2953 ------------------------------
2954 Sun Mar 27 03:59:59 2011 MSK
2955 (1 row)
2957 SELECT '2011-03-27 00:00:00 UTC'::timestamptz;
2958 timestamptz
2959 ------------------------------
2960 Sun Mar 27 04:00:00 2011 MSK
2961 (1 row)
2963 SELECT '2014-10-25 21:00:00 UTC'::timestamptz;
2964 timestamptz
2965 ------------------------------
2966 Sun Oct 26 01:00:00 2014 MSK
2967 (1 row)
2969 SELECT '2014-10-25 21:59:59 UTC'::timestamptz;
2970 timestamptz
2971 ------------------------------
2972 Sun Oct 26 01:59:59 2014 MSK
2973 (1 row)
2975 SELECT '2014-10-25 22:00:00 UTC'::timestamptz;
2976 timestamptz
2977 ------------------------------
2978 Sun Oct 26 01:00:00 2014 MSK
2979 (1 row)
2981 SELECT '2014-10-25 22:00:01 UTC'::timestamptz;
2982 timestamptz
2983 ------------------------------
2984 Sun Oct 26 01:00:01 2014 MSK
2985 (1 row)
2987 SELECT '2014-10-25 23:00:00 UTC'::timestamptz;
2988 timestamptz
2989 ------------------------------
2990 Sun Oct 26 02:00:00 2014 MSK
2991 (1 row)
2993 RESET TimeZone;
2994 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2995 timezone
2996 --------------------------
2997 Sun Mar 27 00:00:00 2011
2998 (1 row)
3000 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3001 timezone
3002 --------------------------
3003 Sun Mar 27 01:00:00 2011
3004 (1 row)
3006 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3007 timezone
3008 --------------------------
3009 Sun Mar 27 01:59:59 2011
3010 (1 row)
3012 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3013 timezone
3014 --------------------------
3015 Sun Mar 27 03:00:00 2011
3016 (1 row)
3018 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3019 timezone
3020 --------------------------
3021 Sun Mar 27 03:00:01 2011
3022 (1 row)
3024 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3025 timezone
3026 --------------------------
3027 Sun Mar 27 03:59:59 2011
3028 (1 row)
3030 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3031 timezone
3032 --------------------------
3033 Sun Mar 27 04:00:00 2011
3034 (1 row)
3036 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3037 timezone
3038 --------------------------
3039 Sun Oct 26 01:00:00 2014
3040 (1 row)
3042 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3043 timezone
3044 --------------------------
3045 Sun Oct 26 01:59:59 2014
3046 (1 row)
3048 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3049 timezone
3050 --------------------------
3051 Sun Oct 26 01:00:00 2014
3052 (1 row)
3054 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3055 timezone
3056 --------------------------
3057 Sun Oct 26 01:00:01 2014
3058 (1 row)
3060 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
3061 timezone
3062 --------------------------
3063 Sun Oct 26 02:00:00 2014
3064 (1 row)
3066 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3067 timezone
3068 --------------------------
3069 Sun Mar 27 00:00:00 2011
3070 (1 row)
3072 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3073 timezone
3074 --------------------------
3075 Sun Mar 27 01:00:00 2011
3076 (1 row)
3078 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3079 timezone
3080 --------------------------
3081 Sun Mar 27 01:59:59 2011
3082 (1 row)
3084 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3085 timezone
3086 --------------------------
3087 Sun Mar 27 03:00:00 2011
3088 (1 row)
3090 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
3091 timezone
3092 --------------------------
3093 Sun Mar 27 03:00:01 2011
3094 (1 row)
3096 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3097 timezone
3098 --------------------------
3099 Sun Mar 27 03:59:59 2011
3100 (1 row)
3102 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3103 timezone
3104 --------------------------
3105 Sun Mar 27 04:00:00 2011
3106 (1 row)
3108 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3109 timezone
3110 --------------------------
3111 Sun Oct 26 01:00:00 2014
3112 (1 row)
3114 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
3115 timezone
3116 --------------------------
3117 Sun Oct 26 01:59:59 2014
3118 (1 row)
3120 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3121 timezone
3122 --------------------------
3123 Sun Oct 26 01:00:00 2014
3124 (1 row)
3126 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
3127 timezone
3128 --------------------------
3129 Sun Oct 26 01:00:01 2014
3130 (1 row)
3132 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
3133 timezone
3134 --------------------------
3135 Sun Oct 26 02:00:00 2014
3136 (1 row)
3138 --
3139 -- Test LOCAL time zone
3140 --
3141 BEGIN;
3142 SET LOCAL TIME ZONE 'Europe/Paris';
3143 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3144 column1
3145 --------------------------
3146 Sat Jul 08 01:38:00 1978
3147 (1 row)
3149 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3150 column1
3151 -------------------------------
3152 Fri Jul 07 19:38:00 1978 CEST
3153 (1 row)
3155 SET LOCAL TIME ZONE 'Australia/Sydney';
3156 VALUES (CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL);
3157 column1
3158 --------------------------
3159 Sat Jul 08 09:38:00 1978
3160 (1 row)
3162 VALUES (TIMESTAMP '1978-07-07 19:38' AT LOCAL);
3163 column1
3164 -------------------------------
3165 Fri Jul 07 19:38:00 1978 AEST
3166 (1 row)
3168 SET LOCAL TimeZone TO 'UTC';
3169 CREATE VIEW timestamp_local_view AS
3170 SELECT CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE) AT LOCAL AS ttz_at_local,
3171 timezone(CAST('1978-07-07 19:38 America/New_York' AS TIMESTAMP WITH TIME ZONE)) AS ttz_func,
3172 TIMESTAMP '1978-07-07 19:38' AT LOCAL AS t_at_local,
3173 timezone(TIMESTAMP '1978-07-07 19:38') AS t_func;
3174 SELECT pg_get_viewdef('timestamp_local_view', true);
3175 pg_get_viewdef
3176 ----------------------------------------------------------------------------------------------
3177 SELECT ('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone AT LOCAL) AS ttz_at_local,+
3178 timezone('Fri Jul 07 23:38:00 1978 UTC'::timestamp with time zone) AS ttz_func, +
3179 ('Fri Jul 07 19:38:00 1978'::timestamp without time zone AT LOCAL) AS t_at_local, +
3180 timezone('Fri Jul 07 19:38:00 1978'::timestamp without time zone) AS t_func;
3181 (1 row)
3183 \x
3184 TABLE timestamp_local_view;
3185 -[ RECORD 1 ]+-----------------------------
3186 ttz_at_local | Fri Jul 07 23:38:00 1978
3187 ttz_func | Fri Jul 07 23:38:00 1978
3188 t_at_local | Fri Jul 07 19:38:00 1978 UTC
3189 t_func | Fri Jul 07 19:38:00 1978 UTC
3191 \x
3192 DROP VIEW timestamp_local_view;
3193 COMMIT;
3194 --
3195 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
3196 --
3197 create temp table tmptz (f1 timestamptz primary key);
3198 insert into tmptz values ('2017-01-18 00:00+00');
3199 explain (costs off)
3200 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
3201 QUERY PLAN
3202 -----------------------------------------------------------------------------------------------------
3203 Seq Scan on tmptz
3204 Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
3205 (2 rows)
3207 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
3208 f1
3209 ------------------------------
3210 Tue Jan 17 16:00:00 2017 PST
3211 (1 row)