| blob | 26d7541b720117d609dc34f3aaacf6cf90754b92 |
1 --
2 -- HOROLOGY
3 --
4 SET DateStyle = 'Postgres, MDY';
5 --
6 -- Test various input formats
7 --
8 SELECT timestamp with time zone '20011227 040506+08';
9 timestamptz
10 ------------------------------
11 Wed Dec 26 12:05:06 2001 PST
12 (1 row)
14 SELECT timestamp with time zone '20011227 040506-08';
15 timestamptz
16 ------------------------------
17 Thu Dec 27 04:05:06 2001 PST
18 (1 row)
20 SELECT timestamp with time zone '20011227 040506.789+08';
21 timestamptz
22 ----------------------------------
23 Wed Dec 26 12:05:06.789 2001 PST
24 (1 row)
26 SELECT timestamp with time zone '20011227 040506.789-08';
27 timestamptz
28 ----------------------------------
29 Thu Dec 27 04:05:06.789 2001 PST
30 (1 row)
32 SELECT timestamp with time zone '20011227T040506+08';
33 timestamptz
34 ------------------------------
35 Wed Dec 26 12:05:06 2001 PST
36 (1 row)
38 SELECT timestamp with time zone '20011227T040506-08';
39 timestamptz
40 ------------------------------
41 Thu Dec 27 04:05:06 2001 PST
42 (1 row)
44 SELECT timestamp with time zone '20011227T040506.789+08';
45 timestamptz
46 ----------------------------------
47 Wed Dec 26 12:05:06.789 2001 PST
48 (1 row)
50 SELECT timestamp with time zone '20011227T040506.789-08';
51 timestamptz
52 ----------------------------------
53 Thu Dec 27 04:05:06.789 2001 PST
54 (1 row)
56 SELECT timestamp with time zone '2001-12-27 04:05:06.789-08';
57 timestamptz
58 ----------------------------------
59 Thu Dec 27 04:05:06.789 2001 PST
60 (1 row)
62 SELECT timestamp with time zone '2001.12.27 04:05:06.789-08';
63 timestamptz
64 ----------------------------------
65 Thu Dec 27 04:05:06.789 2001 PST
66 (1 row)
68 SELECT timestamp with time zone '2001/12/27 04:05:06.789-08';
69 timestamptz
70 ----------------------------------
71 Thu Dec 27 04:05:06.789 2001 PST
72 (1 row)
74 SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
75 timestamptz
76 ----------------------------------
77 Thu Dec 27 04:05:06.789 2001 PST
78 (1 row)
80 -- should fail in mdy mode:
81 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
82 ERROR: date/time field value out of range: "27/12/2001 04:05:06.789-08"
83 LINE 1: SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'...
84 ^
85 HINT: Perhaps you need a different "datestyle" setting.
86 set datestyle to dmy;
87 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
88 timestamptz
89 ----------------------------------
90 Thu 27 Dec 04:05:06.789 2001 PST
91 (1 row)
93 reset datestyle;
94 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
95 timestamptz
96 ----------------------------------
97 Wed Dec 26 12:05:06.789 2001 PST
98 (1 row)
100 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08';
101 timestamptz
102 ----------------------------------
103 Thu Dec 27 04:05:06.789 2001 PST
104 (1 row)
106 SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08';
107 timestamptz
108 ----------------------------------
109 Wed Dec 26 12:05:06.789 2001 PST
110 (1 row)
112 SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789-08';
113 timestamptz
114 ----------------------------------
115 Thu Dec 27 04:05:06.789 2001 PST
116 (1 row)
118 SELECT timestamp with time zone 'J2452271+08';
119 timestamptz
120 ------------------------------
121 Wed Dec 26 08:00:00 2001 PST
122 (1 row)
124 SELECT timestamp with time zone 'J2452271-08';
125 timestamptz
126 ------------------------------
127 Thu Dec 27 00:00:00 2001 PST
128 (1 row)
130 SELECT timestamp with time zone 'J2452271.5+08';
131 timestamptz
132 ------------------------------
133 Wed Dec 26 20:00:00 2001 PST
134 (1 row)
136 SELECT timestamp with time zone 'J2452271.5-08';
137 timestamptz
138 ------------------------------
139 Thu Dec 27 12:00:00 2001 PST
140 (1 row)
142 SELECT timestamp with time zone 'J2452271 04:05:06+08';
143 timestamptz
144 ------------------------------
145 Wed Dec 26 12:05:06 2001 PST
146 (1 row)
148 SELECT timestamp with time zone 'J2452271 04:05:06-08';
149 timestamptz
150 ------------------------------
151 Thu Dec 27 04:05:06 2001 PST
152 (1 row)
154 SELECT timestamp with time zone 'J2452271T040506+08';
155 timestamptz
156 ------------------------------
157 Wed Dec 26 12:05:06 2001 PST
158 (1 row)
160 SELECT timestamp with time zone 'J2452271T040506-08';
161 timestamptz
162 ------------------------------
163 Thu Dec 27 04:05:06 2001 PST
164 (1 row)
166 SELECT timestamp with time zone 'J2452271T040506.789+08';
167 timestamptz
168 ----------------------------------
169 Wed Dec 26 12:05:06.789 2001 PST
170 (1 row)
172 SELECT timestamp with time zone 'J2452271T040506.789-08';
173 timestamptz
174 ----------------------------------
175 Thu Dec 27 04:05:06.789 2001 PST
176 (1 row)
178 -- German/European-style dates with periods as delimiters
179 SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
180 timestamptz
181 ----------------------------------
182 Wed Dec 26 12:05:06.789 2001 PST
183 (1 row)
185 SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
186 timestamptz
187 ----------------------------------
188 Thu Dec 27 04:05:06.789 2001 PST
189 (1 row)
191 SET DateStyle = 'German';
192 SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
193 timestamptz
194 -----------------------------
195 26.12.2001 12:05:06.789 PST
196 (1 row)
198 SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
199 timestamptz
200 -----------------------------
201 27.12.2001 04:05:06.789 PST
202 (1 row)
204 SET DateStyle = 'ISO';
205 -- As of 7.4, allow time without time zone having a time zone specified
206 SELECT time without time zone '040506.789+08';
207 time
208 --------------
209 04:05:06.789
210 (1 row)
212 SELECT time without time zone '040506.789-08';
213 time
214 --------------
215 04:05:06.789
216 (1 row)
218 SELECT time without time zone 'T040506.789+08';
219 time
220 --------------
221 04:05:06.789
222 (1 row)
224 SELECT time without time zone 'T040506.789-08';
225 time
226 --------------
227 04:05:06.789
228 (1 row)
230 SELECT time with time zone '040506.789+08';
231 timetz
232 -----------------
233 04:05:06.789+08
234 (1 row)
236 SELECT time with time zone '040506.789-08';
237 timetz
238 -----------------
239 04:05:06.789-08
240 (1 row)
242 SELECT time with time zone 'T040506.789+08';
243 timetz
244 -----------------
245 04:05:06.789+08
246 (1 row)
248 SELECT time with time zone 'T040506.789-08';
249 timetz
250 -----------------
251 04:05:06.789-08
252 (1 row)
254 SELECT time with time zone 'T040506.789 +08';
255 timetz
256 -----------------
257 04:05:06.789+08
258 (1 row)
260 SELECT time with time zone 'T040506.789 -08';
261 timetz
262 -----------------
263 04:05:06.789-08
264 (1 row)
266 SET DateStyle = 'Postgres, MDY';
267 --
268 -- date, time arithmetic
269 --
270 SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
271 Date + Time
272 --------------------------
273 Tue Feb 03 04:05:06 1981
274 (1 row)
276 SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST";
277 Date + Time PST
278 ------------------------------
279 Sun Feb 03 04:05:06 1991 PST
280 (1 row)
282 SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC";
283 Date + Time UTC
284 ------------------------------
285 Fri Feb 02 20:05:06 2001 PST
286 (1 row)
288 SELECT date '1991-02-03' + interval '2 years' AS "Add Two Years";
289 Add Two Years
290 --------------------------
291 Wed Feb 03 00:00:00 1993
292 (1 row)
294 SELECT date '2001-12-13' - interval '2 years' AS "Subtract Two Years";
295 Subtract Two Years
296 --------------------------
297 Mon Dec 13 00:00:00 1999
298 (1 row)
300 -- subtract time from date should not make sense; use interval instead
301 SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time";
302 Subtract Time
303 --------------------------
304 Sat Feb 02 19:54:54 1991
305 (1 row)
307 SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
308 ERROR: operator does not exist: date - time with time zone
309 LINE 1: SELECT date '1991-02-03' - time with time zone '04:05:06 UTC...
310 ^
311 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
312 --
313 -- timestamp, interval arithmetic
314 --
315 SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
316 Feb 29
317 --------------------------
318 Thu Feb 29 23:59:59 1996
319 (1 row)
321 SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
322 Feb 28
323 --------------------------
324 Sun Feb 28 23:59:59 1999
325 (1 row)
327 SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
328 Feb 29
329 --------------------------
330 Tue Feb 29 23:59:59 2000
331 (1 row)
333 SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
334 Dec 31
335 --------------------------
336 Fri Dec 31 23:59:59 1999
337 (1 row)
339 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
340 Feb 23, 285506
341 ----------------------------
342 Fri Feb 23 00:00:00 285506
343 (1 row)
345 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
346 Jan 20, 288244
347 ----------------------------
348 Sat Jan 20 00:00:00 288244
349 (1 row)
351 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
352 Dec 31, 294276
353 ----------------------------
354 Sun Dec 31 00:00:00 294276
355 (1 row)
357 SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
358 106751991 Days
359 ------------------
360 @ 106751991 days
361 (1 row)
363 -- Shorthand values
364 -- Not directly usable for regression testing since these are not constants.
365 -- So, just try to test parser and hope for the best - thomas 97/04/26
366 SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '1 day')) as "True";
367 True
368 ------
369 t
370 (1 row)
372 SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1 day')) as "True";
373 True
374 ------
375 t
376 (1 row)
378 SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interval '2 days')) as "True";
379 True
380 ------
381 t
382 (1 row)
384 SELECT (timestamp without time zone 'tomorrow' > 'now') as "True";
385 True
386 ------
387 t
388 (1 row)
390 -- Convert from date and time to timestamp
391 -- This test used to be timestamp(date,time) but no longer allowed by grammar
392 -- to enable support for SQL99 timestamp type syntax.
393 SELECT date '1994-01-01' + time '11:00' AS "Jan_01_1994_11am";
394 Jan_01_1994_11am
395 --------------------------
396 Sat Jan 01 11:00:00 1994
397 (1 row)
399 SELECT date '1994-01-01' + time '10:00' AS "Jan_01_1994_10am";
400 Jan_01_1994_10am
401 --------------------------
402 Sat Jan 01 10:00:00 1994
403 (1 row)
405 SELECT date '1994-01-01' + timetz '11:00-5' AS "Jan_01_1994_8am";
406 Jan_01_1994_8am
407 ------------------------------
408 Sat Jan 01 08:00:00 1994 PST
409 (1 row)
411 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am";
412 Jan_01_1994_8am
413 ------------------------------
414 Sat Jan 01 08:00:00 1994 PST
415 (1 row)
417 SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
418 64 | one_year
419 ----+-----------------------------
420 | -infinity
421 | infinity
422 | Fri Jan 01 00:00:00 1971
423 | Tue Feb 10 17:32:01 1998
424 | Tue Feb 10 17:32:01 1998
425 | Tue Feb 10 17:32:02 1998
426 | Tue Feb 10 17:32:01.4 1998
427 | Tue Feb 10 17:32:01.5 1998
428 | Tue Feb 10 17:32:01.6 1998
429 | Fri Jan 02 00:00:00 1998
430 | Fri Jan 02 03:04:05 1998
431 | Tue Feb 10 17:32:01 1998
432 | Tue Feb 10 17:32:01 1998
433 | Tue Feb 10 17:32:01 1998
434 | Tue Feb 10 17:32:01 1998
435 | Wed Jun 10 17:32:01 1998
436 | Sun Sep 22 18:19:20 2002
437 | Thu Mar 15 08:14:01 2001
438 | Thu Mar 15 13:14:02 2001
439 | Thu Mar 15 12:14:03 2001
440 | Thu Mar 15 03:14:04 2001
441 | Thu Mar 15 02:14:05 2001
442 | Tue Feb 10 17:32:01 1998
443 | Tue Feb 10 17:32:01 1998
444 | Tue Feb 10 17:32:00 1998
445 | Tue Feb 10 17:32:01 1998
446 | Tue Feb 10 17:32:01 1998
447 | Tue Feb 10 17:32:01 1998
448 | Tue Feb 10 17:32:01 1998
449 | Tue Feb 10 17:32:01 1998
450 | Tue Feb 10 17:32:01 1998
451 | Tue Feb 10 17:32:01 1998
452 | Tue Feb 10 17:32:01 1998
453 | Tue Feb 10 17:32:01 1998
454 | Wed Jun 10 18:32:01 1998
455 | Tue Feb 10 17:32:01 1998
456 | Wed Feb 11 17:32:01 1998
457 | Thu Feb 12 17:32:01 1998
458 | Fri Feb 13 17:32:01 1998
459 | Sat Feb 14 17:32:01 1998
460 | Sun Feb 15 17:32:01 1998
461 | Mon Feb 16 17:32:01 1998
462 | Thu Feb 16 17:32:01 0096 BC
463 | Sun Feb 16 17:32:01 0098
464 | Fri Feb 16 17:32:01 0598
465 | Wed Feb 16 17:32:01 1098
466 | Sun Feb 16 17:32:01 1698
467 | Fri Feb 16 17:32:01 1798
468 | Wed Feb 16 17:32:01 1898
469 | Mon Feb 16 17:32:01 1998
470 | Sun Feb 16 17:32:01 2098
471 | Fri Feb 28 17:32:01 1997
472 | Fri Feb 28 17:32:01 1997
473 | Sat Mar 01 17:32:01 1997
474 | Tue Dec 30 17:32:01 1997
475 | Wed Dec 31 17:32:01 1997
476 | Thu Jan 01 17:32:01 1998
477 | Sat Feb 28 17:32:01 1998
478 | Sun Mar 01 17:32:01 1998
479 | Wed Dec 30 17:32:01 1998
480 | Thu Dec 31 17:32:01 1998
481 | Sun Dec 31 17:32:01 2000
482 | Mon Jan 01 17:32:01 2001
483 | Mon Dec 31 17:32:01 2001
484 | Tue Jan 01 17:32:01 2002
485 (65 rows)
487 SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
488 64 | one_year
489 ----+-----------------------------
490 | -infinity
491 | infinity
492 | Wed Jan 01 00:00:00 1969
493 | Sat Feb 10 17:32:01 1996
494 | Sat Feb 10 17:32:01 1996
495 | Sat Feb 10 17:32:02 1996
496 | Sat Feb 10 17:32:01.4 1996
497 | Sat Feb 10 17:32:01.5 1996
498 | Sat Feb 10 17:32:01.6 1996
499 | Tue Jan 02 00:00:00 1996
500 | Tue Jan 02 03:04:05 1996
501 | Sat Feb 10 17:32:01 1996
502 | Sat Feb 10 17:32:01 1996
503 | Sat Feb 10 17:32:01 1996
504 | Sat Feb 10 17:32:01 1996
505 | Mon Jun 10 17:32:01 1996
506 | Fri Sep 22 18:19:20 2000
507 | Mon Mar 15 08:14:01 1999
508 | Mon Mar 15 13:14:02 1999
509 | Mon Mar 15 12:14:03 1999
510 | Mon Mar 15 03:14:04 1999
511 | Mon Mar 15 02:14:05 1999
512 | Sat Feb 10 17:32:01 1996
513 | Sat Feb 10 17:32:01 1996
514 | Sat Feb 10 17:32:00 1996
515 | Sat Feb 10 17:32:01 1996
516 | Sat Feb 10 17:32:01 1996
517 | Sat Feb 10 17:32:01 1996
518 | Sat Feb 10 17:32:01 1996
519 | Sat Feb 10 17:32:01 1996
520 | Sat Feb 10 17:32:01 1996
521 | Sat Feb 10 17:32:01 1996
522 | Sat Feb 10 17:32:01 1996
523 | Sat Feb 10 17:32:01 1996
524 | Mon Jun 10 18:32:01 1996
525 | Sat Feb 10 17:32:01 1996
526 | Sun Feb 11 17:32:01 1996
527 | Mon Feb 12 17:32:01 1996
528 | Tue Feb 13 17:32:01 1996
529 | Wed Feb 14 17:32:01 1996
530 | Thu Feb 15 17:32:01 1996
531 | Fri Feb 16 17:32:01 1996
532 | Mon Feb 16 17:32:01 0098 BC
533 | Thu Feb 16 17:32:01 0096
534 | Tue Feb 16 17:32:01 0596
535 | Sun Feb 16 17:32:01 1096
536 | Thu Feb 16 17:32:01 1696
537 | Tue Feb 16 17:32:01 1796
538 | Sun Feb 16 17:32:01 1896
539 | Fri Feb 16 17:32:01 1996
540 | Thu Feb 16 17:32:01 2096
541 | Tue Feb 28 17:32:01 1995
542 | Tue Feb 28 17:32:01 1995
543 | Wed Mar 01 17:32:01 1995
544 | Sat Dec 30 17:32:01 1995
545 | Sun Dec 31 17:32:01 1995
546 | Mon Jan 01 17:32:01 1996
547 | Wed Feb 28 17:32:01 1996
548 | Fri Mar 01 17:32:01 1996
549 | Mon Dec 30 17:32:01 1996
550 | Tue Dec 31 17:32:01 1996
551 | Thu Dec 31 17:32:01 1998
552 | Fri Jan 01 17:32:01 1999
553 | Fri Dec 31 17:32:01 1999
554 | Sat Jan 01 17:32:01 2000
555 (65 rows)
557 SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29";
558 Feb 29
559 ------------------------------
560 Thu Feb 29 23:59:59 1996 PST
561 (1 row)
563 SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28";
564 Feb 28
565 ------------------------------
566 Sun Feb 28 23:59:59 1999 PST
567 (1 row)
569 SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29";
570 Feb 29
571 ------------------------------
572 Tue Feb 29 23:59:59 2000 PST
573 (1 row)
575 SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
576 Dec 31
577 ------------------------------
578 Fri Dec 31 23:59:59 1999 PST
579 (1 row)
581 SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True";
582 True
583 ------
584 t
585 (1 row)
587 SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True";
588 True
589 ------
590 t
591 (1 row)
593 SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True";
594 True
595 ------
596 t
597 (1 row)
599 SELECT (timestamp with time zone 'tomorrow' > 'now') as "True";
600 True
601 ------
602 t
603 (1 row)
605 -- timestamp with time zone, interval arithmetic around DST change
606 SET TIME ZONE 'CST7CDT';
607 SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00";
608 Apr 3, 12:00
609 ------------------------------
610 Sun Apr 03 12:00:00 2005 CDT
611 (1 row)
613 SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00";
614 Apr 3, 13:00
615 ------------------------------
616 Sun Apr 03 13:00:00 2005 CDT
617 (1 row)
619 SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00";
620 Apr 2, 12:00
621 ------------------------------
622 Sat Apr 02 12:00:00 2005 CST
623 (1 row)
625 SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00";
626 Apr 2, 11:00
627 ------------------------------
628 Sat Apr 02 11:00:00 2005 CST
629 (1 row)
631 RESET TIME ZONE;
632 SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am";
633 Jan_01_1994_10am
634 ------------------------------
635 Sat Jan 01 11:00:00 1994 PST
636 (1 row)
638 SELECT timestamptz(date '1994-01-01', time '10:00') AS "Jan_01_1994_9am";
639 Jan_01_1994_9am
640 ------------------------------
641 Sat Jan 01 10:00:00 1994 PST
642 (1 row)
644 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-8') AS "Jan_01_1994_11am";
645 Jan_01_1994_11am
646 ------------------------------
647 Sat Jan 01 11:00:00 1994 PST
648 (1 row)
650 SELECT timestamptz(date '1994-01-01', time with time zone '10:00-8') AS "Jan_01_1994_10am";
651 Jan_01_1994_10am
652 ------------------------------
653 Sat Jan 01 10:00:00 1994 PST
654 (1 row)
656 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am";
657 Jan_01_1994_8am
658 ------------------------------
659 Sat Jan 01 08:00:00 1994 PST
660 (1 row)
662 SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL;
663 64 | one_year
664 ----+---------------------------------
665 | -infinity
666 | infinity
667 | Thu Dec 31 16:00:00 1970 PST
668 | Tue Feb 10 17:32:01 1998 PST
669 | Tue Feb 10 17:32:01 1998 PST
670 | Tue Feb 10 17:32:02 1998 PST
671 | Tue Feb 10 17:32:01.4 1998 PST
672 | Tue Feb 10 17:32:01.5 1998 PST
673 | Tue Feb 10 17:32:01.6 1998 PST
674 | Fri Jan 02 00:00:00 1998 PST
675 | Fri Jan 02 03:04:05 1998 PST
676 | Tue Feb 10 17:32:01 1998 PST
677 | Tue Feb 10 17:32:01 1998 PST
678 | Tue Feb 10 17:32:01 1998 PST
679 | Tue Feb 10 17:32:01 1998 PST
680 | Wed Jun 10 17:32:01 1998 PDT
681 | Sun Sep 22 18:19:20 2002 PDT
682 | Thu Mar 15 08:14:01 2001 PST
683 | Thu Mar 15 04:14:02 2001 PST
684 | Thu Mar 15 02:14:03 2001 PST
685 | Thu Mar 15 03:14:04 2001 PST
686 | Thu Mar 15 01:14:05 2001 PST
687 | Tue Feb 10 17:32:01 1998 PST
688 | Tue Feb 10 17:32:01 1998 PST
689 | Tue Feb 10 17:32:00 1998 PST
690 | Tue Feb 10 17:32:01 1998 PST
691 | Tue Feb 10 17:32:01 1998 PST
692 | Tue Feb 10 17:32:01 1998 PST
693 | Tue Feb 10 17:32:01 1998 PST
694 | Tue Feb 10 17:32:01 1998 PST
695 | Tue Feb 10 09:32:01 1998 PST
696 | Tue Feb 10 09:32:01 1998 PST
697 | Tue Feb 10 09:32:01 1998 PST
698 | Tue Feb 10 14:32:01 1998 PST
699 | Fri Jul 10 14:32:01 1998 PDT
700 | Wed Jun 10 18:32:01 1998 PDT
701 | Tue Feb 10 17:32:01 1998 PST
702 | Wed Feb 11 17:32:01 1998 PST
703 | Thu Feb 12 17:32:01 1998 PST
704 | Fri Feb 13 17:32:01 1998 PST
705 | Sat Feb 14 17:32:01 1998 PST
706 | Sun Feb 15 17:32:01 1998 PST
707 | Mon Feb 16 17:32:01 1998 PST
708 | Thu Feb 16 17:32:01 0096 PST BC
709 | Sun Feb 16 17:32:01 0098 PST
710 | Fri Feb 16 17:32:01 0598 PST
711 | Wed Feb 16 17:32:01 1098 PST
712 | Sun Feb 16 17:32:01 1698 PST
713 | Fri Feb 16 17:32:01 1798 PST
714 | Wed Feb 16 17:32:01 1898 PST
715 | Mon Feb 16 17:32:01 1998 PST
716 | Sun Feb 16 17:32:01 2098 PST
717 | Fri Feb 28 17:32:01 1997 PST
718 | Fri Feb 28 17:32:01 1997 PST
719 | Sat Mar 01 17:32:01 1997 PST
720 | Tue Dec 30 17:32:01 1997 PST
721 | Wed Dec 31 17:32:01 1997 PST
722 | Thu Jan 01 17:32:01 1998 PST
723 | Sat Feb 28 17:32:01 1998 PST
724 | Sun Mar 01 17:32:01 1998 PST
725 | Wed Dec 30 17:32:01 1998 PST
726 | Thu Dec 31 17:32:01 1998 PST
727 | Sun Dec 31 17:32:01 2000 PST
728 | Mon Jan 01 17:32:01 2001 PST
729 | Mon Dec 31 17:32:01 2001 PST
730 | Tue Jan 01 17:32:01 2002 PST
731 (66 rows)
733 SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL;
734 64 | one_year
735 ----+---------------------------------
736 | -infinity
737 | infinity
738 | Tue Dec 31 16:00:00 1968 PST
739 | Sat Feb 10 17:32:01 1996 PST
740 | Sat Feb 10 17:32:01 1996 PST
741 | Sat Feb 10 17:32:02 1996 PST
742 | Sat Feb 10 17:32:01.4 1996 PST
743 | Sat Feb 10 17:32:01.5 1996 PST
744 | Sat Feb 10 17:32:01.6 1996 PST
745 | Tue Jan 02 00:00:00 1996 PST
746 | Tue Jan 02 03:04:05 1996 PST
747 | Sat Feb 10 17:32:01 1996 PST
748 | Sat Feb 10 17:32:01 1996 PST
749 | Sat Feb 10 17:32:01 1996 PST
750 | Sat Feb 10 17:32:01 1996 PST
751 | Mon Jun 10 17:32:01 1996 PDT
752 | Fri Sep 22 18:19:20 2000 PDT
753 | Mon Mar 15 08:14:01 1999 PST
754 | Mon Mar 15 04:14:02 1999 PST
755 | Mon Mar 15 02:14:03 1999 PST
756 | Mon Mar 15 03:14:04 1999 PST
757 | Mon Mar 15 01:14:05 1999 PST
758 | Sat Feb 10 17:32:01 1996 PST
759 | Sat Feb 10 17:32:01 1996 PST
760 | Sat Feb 10 17:32:00 1996 PST
761 | Sat Feb 10 17:32:01 1996 PST
762 | Sat Feb 10 17:32:01 1996 PST
763 | Sat Feb 10 17:32:01 1996 PST
764 | Sat Feb 10 17:32:01 1996 PST
765 | Sat Feb 10 17:32:01 1996 PST
766 | Sat Feb 10 09:32:01 1996 PST
767 | Sat Feb 10 09:32:01 1996 PST
768 | Sat Feb 10 09:32:01 1996 PST
769 | Sat Feb 10 14:32:01 1996 PST
770 | Wed Jul 10 14:32:01 1996 PDT
771 | Mon Jun 10 18:32:01 1996 PDT
772 | Sat Feb 10 17:32:01 1996 PST
773 | Sun Feb 11 17:32:01 1996 PST
774 | Mon Feb 12 17:32:01 1996 PST
775 | Tue Feb 13 17:32:01 1996 PST
776 | Wed Feb 14 17:32:01 1996 PST
777 | Thu Feb 15 17:32:01 1996 PST
778 | Fri Feb 16 17:32:01 1996 PST
779 | Mon Feb 16 17:32:01 0098 PST BC
780 | Thu Feb 16 17:32:01 0096 PST
781 | Tue Feb 16 17:32:01 0596 PST
782 | Sun Feb 16 17:32:01 1096 PST
783 | Thu Feb 16 17:32:01 1696 PST
784 | Tue Feb 16 17:32:01 1796 PST
785 | Sun Feb 16 17:32:01 1896 PST
786 | Fri Feb 16 17:32:01 1996 PST
787 | Thu Feb 16 17:32:01 2096 PST
788 | Tue Feb 28 17:32:01 1995 PST
789 | Tue Feb 28 17:32:01 1995 PST
790 | Wed Mar 01 17:32:01 1995 PST
791 | Sat Dec 30 17:32:01 1995 PST
792 | Sun Dec 31 17:32:01 1995 PST
793 | Mon Jan 01 17:32:01 1996 PST
794 | Wed Feb 28 17:32:01 1996 PST
795 | Fri Mar 01 17:32:01 1996 PST
796 | Mon Dec 30 17:32:01 1996 PST
797 | Tue Dec 31 17:32:01 1996 PST
798 | Thu Dec 31 17:32:01 1998 PST
799 | Fri Jan 01 17:32:01 1999 PST
800 | Fri Dec 31 17:32:01 1999 PST
801 | Sat Jan 01 17:32:01 2000 PST
802 (66 rows)
804 --
805 -- time, interval arithmetic
806 --
807 SELECT CAST(time '01:02' AS interval) AS "+01:02";
808 +01:02
809 -----------------
810 @ 1 hour 2 mins
811 (1 row)
813 SELECT CAST(interval '02:03' AS time) AS "02:03:00";
814 02:03:00
815 ----------
816 02:03:00
817 (1 row)
819 SELECT time '01:30' + interval '02:01' AS "03:31:00";
820 03:31:00
821 ----------
822 03:31:00
823 (1 row)
825 SELECT time '01:30' - interval '02:01' AS "23:29:00";
826 23:29:00
827 ----------
828 23:29:00
829 (1 row)
831 SELECT time '02:30' + interval '36:01' AS "14:31:00";
832 14:31:00
833 ----------
834 14:31:00
835 (1 row)
837 SELECT time '03:30' + interval '1 month 04:01' AS "07:31:00";
838 07:31:00
839 ----------
840 07:31:00
841 (1 row)
843 SELECT CAST(time with time zone '01:02-08' AS interval) AS "+00:01";
844 ERROR: cannot cast type time with time zone to interval
845 LINE 1: SELECT CAST(time with time zone '01:02-08' AS interval) AS "...
846 ^
847 SELECT CAST(interval '02:03' AS time with time zone) AS "02:03:00-08";
848 ERROR: cannot cast type interval to time with time zone
849 LINE 1: SELECT CAST(interval '02:03' AS time with time zone) AS "02:...
850 ^
851 SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08";
852 23:29:00-08
853 -------------
854 23:29:00-08
855 (1 row)
857 SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08";
858 14:31:00-08
859 -------------
860 14:31:00-08
861 (1 row)
863 -- These two tests cannot be used because they default to current timezone,
864 -- which may be either -08 or -07 depending on the time of year.
865 -- SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
866 -- SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08";
867 -- Try the following two tests instead, as a poor substitute
868 SELECT CAST(CAST(date 'today' + time with time zone '05:30'
869 + interval '02:01' AS time with time zone) AS time) AS "07:31:00";
870 07:31:00
871 ----------
872 07:31:00
873 (1 row)
875 SELECT CAST(cast(date 'today' + time with time zone '03:30'
876 + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00";
877 07:31:00
878 ----------
879 07:31:00
880 (1 row)
882 SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
883 FROM TIMESTAMP_TBL t, INTERVAL_TBL i
884 WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01'
885 AND i.f1 BETWEEN '00:00' AND '23:00'
886 ORDER BY 1,2;
887 t | i | add | subtract
888 ----------------------------+-----------+----------------------------+----------------------------
889 Wed Feb 28 17:32:01 1996 | @ 1 min | Wed Feb 28 17:33:01 1996 | Wed Feb 28 17:31:01 1996
890 Wed Feb 28 17:32:01 1996 | @ 5 hours | Wed Feb 28 22:32:01 1996 | Wed Feb 28 12:32:01 1996
891 Thu Feb 29 17:32:01 1996 | @ 1 min | Thu Feb 29 17:33:01 1996 | Thu Feb 29 17:31:01 1996
892 Thu Feb 29 17:32:01 1996 | @ 5 hours | Thu Feb 29 22:32:01 1996 | Thu Feb 29 12:32:01 1996
893 Fri Mar 01 17:32:01 1996 | @ 1 min | Fri Mar 01 17:33:01 1996 | Fri Mar 01 17:31:01 1996
894 Fri Mar 01 17:32:01 1996 | @ 5 hours | Fri Mar 01 22:32:01 1996 | Fri Mar 01 12:32:01 1996
895 Mon Dec 30 17:32:01 1996 | @ 1 min | Mon Dec 30 17:33:01 1996 | Mon Dec 30 17:31:01 1996
896 Mon Dec 30 17:32:01 1996 | @ 5 hours | Mon Dec 30 22:32:01 1996 | Mon Dec 30 12:32:01 1996
897 Tue Dec 31 17:32:01 1996 | @ 1 min | Tue Dec 31 17:33:01 1996 | Tue Dec 31 17:31:01 1996
898 Tue Dec 31 17:32:01 1996 | @ 5 hours | Tue Dec 31 22:32:01 1996 | Tue Dec 31 12:32:01 1996
899 Wed Jan 01 17:32:01 1997 | @ 1 min | Wed Jan 01 17:33:01 1997 | Wed Jan 01 17:31:01 1997
900 Wed Jan 01 17:32:01 1997 | @ 5 hours | Wed Jan 01 22:32:01 1997 | Wed Jan 01 12:32:01 1997
901 Thu Jan 02 00:00:00 1997 | @ 1 min | Thu Jan 02 00:01:00 1997 | Wed Jan 01 23:59:00 1997
902 Thu Jan 02 00:00:00 1997 | @ 5 hours | Thu Jan 02 05:00:00 1997 | Wed Jan 01 19:00:00 1997
903 Thu Jan 02 03:04:05 1997 | @ 1 min | Thu Jan 02 03:05:05 1997 | Thu Jan 02 03:03:05 1997
904 Thu Jan 02 03:04:05 1997 | @ 5 hours | Thu Jan 02 08:04:05 1997 | Wed Jan 01 22:04:05 1997
905 Mon Feb 10 17:32:00 1997 | @ 1 min | Mon Feb 10 17:33:00 1997 | Mon Feb 10 17:31:00 1997
906 Mon Feb 10 17:32:00 1997 | @ 5 hours | Mon Feb 10 22:32:00 1997 | Mon Feb 10 12:32:00 1997
907 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
908 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
909 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
910 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
911 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
912 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
913 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
914 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
915 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
916 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
917 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
918 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
919 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
920 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
921 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
922 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
923 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
924 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997
925 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
926 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
927 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
928 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
929 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
930 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
931 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
932 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
933 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
934 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
935 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
936 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
937 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
938 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
939 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
940 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
941 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
942 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997
943 Mon Feb 10 17:32:01.4 1997 | @ 1 min | Mon Feb 10 17:33:01.4 1997 | Mon Feb 10 17:31:01.4 1997
944 Mon Feb 10 17:32:01.4 1997 | @ 5 hours | Mon Feb 10 22:32:01.4 1997 | Mon Feb 10 12:32:01.4 1997
945 Mon Feb 10 17:32:01.5 1997 | @ 1 min | Mon Feb 10 17:33:01.5 1997 | Mon Feb 10 17:31:01.5 1997
946 Mon Feb 10 17:32:01.5 1997 | @ 5 hours | Mon Feb 10 22:32:01.5 1997 | Mon Feb 10 12:32:01.5 1997
947 Mon Feb 10 17:32:01.6 1997 | @ 1 min | Mon Feb 10 17:33:01.6 1997 | Mon Feb 10 17:31:01.6 1997
948 Mon Feb 10 17:32:01.6 1997 | @ 5 hours | Mon Feb 10 22:32:01.6 1997 | Mon Feb 10 12:32:01.6 1997
949 Mon Feb 10 17:32:02 1997 | @ 1 min | Mon Feb 10 17:33:02 1997 | Mon Feb 10 17:31:02 1997
950 Mon Feb 10 17:32:02 1997 | @ 5 hours | Mon Feb 10 22:32:02 1997 | Mon Feb 10 12:32:02 1997
951 Tue Feb 11 17:32:01 1997 | @ 1 min | Tue Feb 11 17:33:01 1997 | Tue Feb 11 17:31:01 1997
952 Tue Feb 11 17:32:01 1997 | @ 5 hours | Tue Feb 11 22:32:01 1997 | Tue Feb 11 12:32:01 1997
953 Wed Feb 12 17:32:01 1997 | @ 1 min | Wed Feb 12 17:33:01 1997 | Wed Feb 12 17:31:01 1997
954 Wed Feb 12 17:32:01 1997 | @ 5 hours | Wed Feb 12 22:32:01 1997 | Wed Feb 12 12:32:01 1997
955 Thu Feb 13 17:32:01 1997 | @ 1 min | Thu Feb 13 17:33:01 1997 | Thu Feb 13 17:31:01 1997
956 Thu Feb 13 17:32:01 1997 | @ 5 hours | Thu Feb 13 22:32:01 1997 | Thu Feb 13 12:32:01 1997
957 Fri Feb 14 17:32:01 1997 | @ 1 min | Fri Feb 14 17:33:01 1997 | Fri Feb 14 17:31:01 1997
958 Fri Feb 14 17:32:01 1997 | @ 5 hours | Fri Feb 14 22:32:01 1997 | Fri Feb 14 12:32:01 1997
959 Sat Feb 15 17:32:01 1997 | @ 1 min | Sat Feb 15 17:33:01 1997 | Sat Feb 15 17:31:01 1997
960 Sat Feb 15 17:32:01 1997 | @ 5 hours | Sat Feb 15 22:32:01 1997 | Sat Feb 15 12:32:01 1997
961 Sun Feb 16 17:32:01 1997 | @ 1 min | Sun Feb 16 17:33:01 1997 | Sun Feb 16 17:31:01 1997
962 Sun Feb 16 17:32:01 1997 | @ 1 min | Sun Feb 16 17:33:01 1997 | Sun Feb 16 17:31:01 1997
963 Sun Feb 16 17:32:01 1997 | @ 5 hours | Sun Feb 16 22:32:01 1997 | Sun Feb 16 12:32:01 1997
964 Sun Feb 16 17:32:01 1997 | @ 5 hours | Sun Feb 16 22:32:01 1997 | Sun Feb 16 12:32:01 1997
965 Fri Feb 28 17:32:01 1997 | @ 1 min | Fri Feb 28 17:33:01 1997 | Fri Feb 28 17:31:01 1997
966 Fri Feb 28 17:32:01 1997 | @ 5 hours | Fri Feb 28 22:32:01 1997 | Fri Feb 28 12:32:01 1997
967 Sat Mar 01 17:32:01 1997 | @ 1 min | Sat Mar 01 17:33:01 1997 | Sat Mar 01 17:31:01 1997
968 Sat Mar 01 17:32:01 1997 | @ 5 hours | Sat Mar 01 22:32:01 1997 | Sat Mar 01 12:32:01 1997
969 Tue Jun 10 17:32:01 1997 | @ 1 min | Tue Jun 10 17:33:01 1997 | Tue Jun 10 17:31:01 1997
970 Tue Jun 10 17:32:01 1997 | @ 5 hours | Tue Jun 10 22:32:01 1997 | Tue Jun 10 12:32:01 1997
971 Tue Jun 10 18:32:01 1997 | @ 1 min | Tue Jun 10 18:33:01 1997 | Tue Jun 10 18:31:01 1997
972 Tue Jun 10 18:32:01 1997 | @ 5 hours | Tue Jun 10 23:32:01 1997 | Tue Jun 10 13:32:01 1997
973 Tue Dec 30 17:32:01 1997 | @ 1 min | Tue Dec 30 17:33:01 1997 | Tue Dec 30 17:31:01 1997
974 Tue Dec 30 17:32:01 1997 | @ 5 hours | Tue Dec 30 22:32:01 1997 | Tue Dec 30 12:32:01 1997
975 Wed Dec 31 17:32:01 1997 | @ 1 min | Wed Dec 31 17:33:01 1997 | Wed Dec 31 17:31:01 1997
976 Wed Dec 31 17:32:01 1997 | @ 5 hours | Wed Dec 31 22:32:01 1997 | Wed Dec 31 12:32:01 1997
977 Fri Dec 31 17:32:01 1999 | @ 1 min | Fri Dec 31 17:33:01 1999 | Fri Dec 31 17:31:01 1999
978 Fri Dec 31 17:32:01 1999 | @ 5 hours | Fri Dec 31 22:32:01 1999 | Fri Dec 31 12:32:01 1999
979 Sat Jan 01 17:32:01 2000 | @ 1 min | Sat Jan 01 17:33:01 2000 | Sat Jan 01 17:31:01 2000
980 Sat Jan 01 17:32:01 2000 | @ 5 hours | Sat Jan 01 22:32:01 2000 | Sat Jan 01 12:32:01 2000
981 Wed Mar 15 02:14:05 2000 | @ 1 min | Wed Mar 15 02:15:05 2000 | Wed Mar 15 02:13:05 2000
982 Wed Mar 15 02:14:05 2000 | @ 5 hours | Wed Mar 15 07:14:05 2000 | Tue Mar 14 21:14:05 2000
983 Wed Mar 15 03:14:04 2000 | @ 1 min | Wed Mar 15 03:15:04 2000 | Wed Mar 15 03:13:04 2000
984 Wed Mar 15 03:14:04 2000 | @ 5 hours | Wed Mar 15 08:14:04 2000 | Tue Mar 14 22:14:04 2000
985 Wed Mar 15 08:14:01 2000 | @ 1 min | Wed Mar 15 08:15:01 2000 | Wed Mar 15 08:13:01 2000
986 Wed Mar 15 08:14:01 2000 | @ 5 hours | Wed Mar 15 13:14:01 2000 | Wed Mar 15 03:14:01 2000
987 Wed Mar 15 12:14:03 2000 | @ 1 min | Wed Mar 15 12:15:03 2000 | Wed Mar 15 12:13:03 2000
988 Wed Mar 15 12:14:03 2000 | @ 5 hours | Wed Mar 15 17:14:03 2000 | Wed Mar 15 07:14:03 2000
989 Wed Mar 15 13:14:02 2000 | @ 1 min | Wed Mar 15 13:15:02 2000 | Wed Mar 15 13:13:02 2000
990 Wed Mar 15 13:14:02 2000 | @ 5 hours | Wed Mar 15 18:14:02 2000 | Wed Mar 15 08:14:02 2000
991 Sun Dec 31 17:32:01 2000 | @ 1 min | Sun Dec 31 17:33:01 2000 | Sun Dec 31 17:31:01 2000
992 Sun Dec 31 17:32:01 2000 | @ 5 hours | Sun Dec 31 22:32:01 2000 | Sun Dec 31 12:32:01 2000
993 (104 rows)
995 SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
996 FROM TIME_TBL t, INTERVAL_TBL i
997 ORDER BY 1,2;
998 t | i | add | subtract
999 -------------+-------------------------------+-------------+-------------
1000 00:00:00 | @ 14 secs ago | 23:59:46 | 00:00:14
1001 00:00:00 | @ 1 min | 00:01:00 | 23:59:00
1002 00:00:00 | @ 5 hours | 05:00:00 | 19:00:00
1003 00:00:00 | @ 1 day 2 hours 3 mins 4 secs | 02:03:04 | 21:56:56
1004 00:00:00 | @ 10 days | 00:00:00 | 00:00:00
1005 00:00:00 | @ 3 mons | 00:00:00 | 00:00:00
1006 00:00:00 | @ 5 mons | 00:00:00 | 00:00:00
1007 00:00:00 | @ 5 mons 12 hours | 12:00:00 | 12:00:00
1008 00:00:00 | @ 6 years | 00:00:00 | 00:00:00
1009 00:00:00 | @ 34 years | 00:00:00 | 00:00:00
1010 01:00:00 | @ 14 secs ago | 00:59:46 | 01:00:14
1011 01:00:00 | @ 1 min | 01:01:00 | 00:59:00
1012 01:00:00 | @ 5 hours | 06:00:00 | 20:00:00
1013 01:00:00 | @ 1 day 2 hours 3 mins 4 secs | 03:03:04 | 22:56:56
1014 01:00:00 | @ 10 days | 01:00:00 | 01:00:00
1015 01:00:00 | @ 3 mons | 01:00:00 | 01:00:00
1016 01:00:00 | @ 5 mons | 01:00:00 | 01:00:00
1017 01:00:00 | @ 5 mons 12 hours | 13:00:00 | 13:00:00
1018 01:00:00 | @ 6 years | 01:00:00 | 01:00:00
1019 01:00:00 | @ 34 years | 01:00:00 | 01:00:00
1020 02:03:00 | @ 14 secs ago | 02:02:46 | 02:03:14
1021 02:03:00 | @ 1 min | 02:04:00 | 02:02:00
1022 02:03:00 | @ 5 hours | 07:03:00 | 21:03:00
1023 02:03:00 | @ 1 day 2 hours 3 mins 4 secs | 04:06:04 | 23:59:56
1024 02:03:00 | @ 10 days | 02:03:00 | 02:03:00
1025 02:03:00 | @ 3 mons | 02:03:00 | 02:03:00
1026 02:03:00 | @ 5 mons | 02:03:00 | 02:03:00
1027 02:03:00 | @ 5 mons 12 hours | 14:03:00 | 14:03:00
1028 02:03:00 | @ 6 years | 02:03:00 | 02:03:00
1029 02:03:00 | @ 34 years | 02:03:00 | 02:03:00
1030 11:59:00 | @ 14 secs ago | 11:58:46 | 11:59:14
1031 11:59:00 | @ 1 min | 12:00:00 | 11:58:00
1032 11:59:00 | @ 5 hours | 16:59:00 | 06:59:00
1033 11:59:00 | @ 1 day 2 hours 3 mins 4 secs | 14:02:04 | 09:55:56
1034 11:59:00 | @ 10 days | 11:59:00 | 11:59:00
1035 11:59:00 | @ 3 mons | 11:59:00 | 11:59:00
1036 11:59:00 | @ 5 mons | 11:59:00 | 11:59:00
1037 11:59:00 | @ 5 mons 12 hours | 23:59:00 | 23:59:00
1038 11:59:00 | @ 6 years | 11:59:00 | 11:59:00
1039 11:59:00 | @ 34 years | 11:59:00 | 11:59:00
1040 12:00:00 | @ 14 secs ago | 11:59:46 | 12:00:14
1041 12:00:00 | @ 1 min | 12:01:00 | 11:59:00
1042 12:00:00 | @ 5 hours | 17:00:00 | 07:00:00
1043 12:00:00 | @ 1 day 2 hours 3 mins 4 secs | 14:03:04 | 09:56:56
1044 12:00:00 | @ 10 days | 12:00:00 | 12:00:00
1045 12:00:00 | @ 3 mons | 12:00:00 | 12:00:00
1046 12:00:00 | @ 5 mons | 12:00:00 | 12:00:00
1047 12:00:00 | @ 5 mons 12 hours | 00:00:00 | 00:00:00
1048 12:00:00 | @ 6 years | 12:00:00 | 12:00:00
1049 12:00:00 | @ 34 years | 12:00:00 | 12:00:00
1050 12:01:00 | @ 14 secs ago | 12:00:46 | 12:01:14
1051 12:01:00 | @ 1 min | 12:02:00 | 12:00:00
1052 12:01:00 | @ 5 hours | 17:01:00 | 07:01:00
1053 12:01:00 | @ 1 day 2 hours 3 mins 4 secs | 14:04:04 | 09:57:56
1054 12:01:00 | @ 10 days | 12:01:00 | 12:01:00
1055 12:01:00 | @ 3 mons | 12:01:00 | 12:01:00
1056 12:01:00 | @ 5 mons | 12:01:00 | 12:01:00
1057 12:01:00 | @ 5 mons 12 hours | 00:01:00 | 00:01:00
1058 12:01:00 | @ 6 years | 12:01:00 | 12:01:00
1059 12:01:00 | @ 34 years | 12:01:00 | 12:01:00
1060 15:36:39 | @ 14 secs ago | 15:36:25 | 15:36:53
1061 15:36:39 | @ 14 secs ago | 15:36:25 | 15:36:53
1062 15:36:39 | @ 1 min | 15:37:39 | 15:35:39
1063 15:36:39 | @ 1 min | 15:37:39 | 15:35:39
1064 15:36:39 | @ 5 hours | 20:36:39 | 10:36:39
1065 15:36:39 | @ 5 hours | 20:36:39 | 10:36:39
1066 15:36:39 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43 | 13:33:35
1067 15:36:39 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43 | 13:33:35
1068 15:36:39 | @ 10 days | 15:36:39 | 15:36:39
1069 15:36:39 | @ 10 days | 15:36:39 | 15:36:39
1070 15:36:39 | @ 3 mons | 15:36:39 | 15:36:39
1071 15:36:39 | @ 3 mons | 15:36:39 | 15:36:39
1072 15:36:39 | @ 5 mons | 15:36:39 | 15:36:39
1073 15:36:39 | @ 5 mons | 15:36:39 | 15:36:39
1074 15:36:39 | @ 5 mons 12 hours | 03:36:39 | 03:36:39
1075 15:36:39 | @ 5 mons 12 hours | 03:36:39 | 03:36:39
1076 15:36:39 | @ 6 years | 15:36:39 | 15:36:39
1077 15:36:39 | @ 6 years | 15:36:39 | 15:36:39
1078 15:36:39 | @ 34 years | 15:36:39 | 15:36:39
1079 15:36:39 | @ 34 years | 15:36:39 | 15:36:39
1080 23:59:00 | @ 14 secs ago | 23:58:46 | 23:59:14
1081 23:59:00 | @ 1 min | 00:00:00 | 23:58:00
1082 23:59:00 | @ 5 hours | 04:59:00 | 18:59:00
1083 23:59:00 | @ 1 day 2 hours 3 mins 4 secs | 02:02:04 | 21:55:56
1084 23:59:00 | @ 10 days | 23:59:00 | 23:59:00
1085 23:59:00 | @ 3 mons | 23:59:00 | 23:59:00
1086 23:59:00 | @ 5 mons | 23:59:00 | 23:59:00
1087 23:59:00 | @ 5 mons 12 hours | 11:59:00 | 11:59:00
1088 23:59:00 | @ 6 years | 23:59:00 | 23:59:00
1089 23:59:00 | @ 34 years | 23:59:00 | 23:59:00
1090 23:59:59.99 | @ 14 secs ago | 23:59:45.99 | 00:00:13.99
1091 23:59:59.99 | @ 1 min | 00:00:59.99 | 23:58:59.99
1092 23:59:59.99 | @ 5 hours | 04:59:59.99 | 18:59:59.99
1093 23:59:59.99 | @ 1 day 2 hours 3 mins 4 secs | 02:03:03.99 | 21:56:55.99
1094 23:59:59.99 | @ 10 days | 23:59:59.99 | 23:59:59.99
1095 23:59:59.99 | @ 3 mons | 23:59:59.99 | 23:59:59.99
1096 23:59:59.99 | @ 5 mons | 23:59:59.99 | 23:59:59.99
1097 23:59:59.99 | @ 5 mons 12 hours | 11:59:59.99 | 11:59:59.99
1098 23:59:59.99 | @ 6 years | 23:59:59.99 | 23:59:59.99
1099 23:59:59.99 | @ 34 years | 23:59:59.99 | 23:59:59.99
1100 (100 rows)
1102 SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
1103 FROM TIMETZ_TBL t, INTERVAL_TBL i
1104 ORDER BY 1,2;
1105 t | i | add | subtract
1106 ----------------+-------------------------------+----------------+----------------
1107 00:01:00-07 | @ 14 secs ago | 00:00:46-07 | 00:01:14-07
1108 00:01:00-07 | @ 1 min | 00:02:00-07 | 00:00:00-07
1109 00:01:00-07 | @ 5 hours | 05:01:00-07 | 19:01:00-07
1110 00:01:00-07 | @ 1 day 2 hours 3 mins 4 secs | 02:04:04-07 | 21:57:56-07
1111 00:01:00-07 | @ 10 days | 00:01:00-07 | 00:01:00-07
1112 00:01:00-07 | @ 3 mons | 00:01:00-07 | 00:01:00-07
1113 00:01:00-07 | @ 5 mons | 00:01:00-07 | 00:01:00-07
1114 00:01:00-07 | @ 5 mons 12 hours | 12:01:00-07 | 12:01:00-07
1115 00:01:00-07 | @ 6 years | 00:01:00-07 | 00:01:00-07
1116 00:01:00-07 | @ 34 years | 00:01:00-07 | 00:01:00-07
1117 01:00:00-07 | @ 14 secs ago | 00:59:46-07 | 01:00:14-07
1118 01:00:00-07 | @ 1 min | 01:01:00-07 | 00:59:00-07
1119 01:00:00-07 | @ 5 hours | 06:00:00-07 | 20:00:00-07
1120 01:00:00-07 | @ 1 day 2 hours 3 mins 4 secs | 03:03:04-07 | 22:56:56-07
1121 01:00:00-07 | @ 10 days | 01:00:00-07 | 01:00:00-07
1122 01:00:00-07 | @ 3 mons | 01:00:00-07 | 01:00:00-07
1123 01:00:00-07 | @ 5 mons | 01:00:00-07 | 01:00:00-07
1124 01:00:00-07 | @ 5 mons 12 hours | 13:00:00-07 | 13:00:00-07
1125 01:00:00-07 | @ 6 years | 01:00:00-07 | 01:00:00-07
1126 01:00:00-07 | @ 34 years | 01:00:00-07 | 01:00:00-07
1127 02:03:00-07 | @ 14 secs ago | 02:02:46-07 | 02:03:14-07
1128 02:03:00-07 | @ 1 min | 02:04:00-07 | 02:02:00-07
1129 02:03:00-07 | @ 5 hours | 07:03:00-07 | 21:03:00-07
1130 02:03:00-07 | @ 1 day 2 hours 3 mins 4 secs | 04:06:04-07 | 23:59:56-07
1131 02:03:00-07 | @ 10 days | 02:03:00-07 | 02:03:00-07
1132 02:03:00-07 | @ 3 mons | 02:03:00-07 | 02:03:00-07
1133 02:03:00-07 | @ 5 mons | 02:03:00-07 | 02:03:00-07
1134 02:03:00-07 | @ 5 mons 12 hours | 14:03:00-07 | 14:03:00-07
1135 02:03:00-07 | @ 6 years | 02:03:00-07 | 02:03:00-07
1136 02:03:00-07 | @ 34 years | 02:03:00-07 | 02:03:00-07
1137 08:08:00-04 | @ 14 secs ago | 08:07:46-04 | 08:08:14-04
1138 08:08:00-04 | @ 1 min | 08:09:00-04 | 08:07:00-04
1139 08:08:00-04 | @ 5 hours | 13:08:00-04 | 03:08:00-04
1140 08:08:00-04 | @ 1 day 2 hours 3 mins 4 secs | 10:11:04-04 | 06:04:56-04
1141 08:08:00-04 | @ 10 days | 08:08:00-04 | 08:08:00-04
1142 08:08:00-04 | @ 3 mons | 08:08:00-04 | 08:08:00-04
1143 08:08:00-04 | @ 5 mons | 08:08:00-04 | 08:08:00-04
1144 08:08:00-04 | @ 5 mons 12 hours | 20:08:00-04 | 20:08:00-04
1145 08:08:00-04 | @ 6 years | 08:08:00-04 | 08:08:00-04
1146 08:08:00-04 | @ 34 years | 08:08:00-04 | 08:08:00-04
1147 07:07:00-08 | @ 14 secs ago | 07:06:46-08 | 07:07:14-08
1148 07:07:00-08 | @ 1 min | 07:08:00-08 | 07:06:00-08
1149 07:07:00-08 | @ 5 hours | 12:07:00-08 | 02:07:00-08
1150 07:07:00-08 | @ 1 day 2 hours 3 mins 4 secs | 09:10:04-08 | 05:03:56-08
1151 07:07:00-08 | @ 10 days | 07:07:00-08 | 07:07:00-08
1152 07:07:00-08 | @ 3 mons | 07:07:00-08 | 07:07:00-08
1153 07:07:00-08 | @ 5 mons | 07:07:00-08 | 07:07:00-08
1154 07:07:00-08 | @ 5 mons 12 hours | 19:07:00-08 | 19:07:00-08
1155 07:07:00-08 | @ 6 years | 07:07:00-08 | 07:07:00-08
1156 07:07:00-08 | @ 34 years | 07:07:00-08 | 07:07:00-08
1157 11:59:00-07 | @ 14 secs ago | 11:58:46-07 | 11:59:14-07
1158 11:59:00-07 | @ 1 min | 12:00:00-07 | 11:58:00-07
1159 11:59:00-07 | @ 5 hours | 16:59:00-07 | 06:59:00-07
1160 11:59:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:02:04-07 | 09:55:56-07
1161 11:59:00-07 | @ 10 days | 11:59:00-07 | 11:59:00-07
1162 11:59:00-07 | @ 3 mons | 11:59:00-07 | 11:59:00-07
1163 11:59:00-07 | @ 5 mons | 11:59:00-07 | 11:59:00-07
1164 11:59:00-07 | @ 5 mons 12 hours | 23:59:00-07 | 23:59:00-07
1165 11:59:00-07 | @ 6 years | 11:59:00-07 | 11:59:00-07
1166 11:59:00-07 | @ 34 years | 11:59:00-07 | 11:59:00-07
1167 12:00:00-07 | @ 14 secs ago | 11:59:46-07 | 12:00:14-07
1168 12:00:00-07 | @ 1 min | 12:01:00-07 | 11:59:00-07
1169 12:00:00-07 | @ 5 hours | 17:00:00-07 | 07:00:00-07
1170 12:00:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:03:04-07 | 09:56:56-07
1171 12:00:00-07 | @ 10 days | 12:00:00-07 | 12:00:00-07
1172 12:00:00-07 | @ 3 mons | 12:00:00-07 | 12:00:00-07
1173 12:00:00-07 | @ 5 mons | 12:00:00-07 | 12:00:00-07
1174 12:00:00-07 | @ 5 mons 12 hours | 00:00:00-07 | 00:00:00-07
1175 12:00:00-07 | @ 6 years | 12:00:00-07 | 12:00:00-07
1176 12:00:00-07 | @ 34 years | 12:00:00-07 | 12:00:00-07
1177 12:01:00-07 | @ 14 secs ago | 12:00:46-07 | 12:01:14-07
1178 12:01:00-07 | @ 1 min | 12:02:00-07 | 12:00:00-07
1179 12:01:00-07 | @ 5 hours | 17:01:00-07 | 07:01:00-07
1180 12:01:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:04:04-07 | 09:57:56-07
1181 12:01:00-07 | @ 10 days | 12:01:00-07 | 12:01:00-07
1182 12:01:00-07 | @ 3 mons | 12:01:00-07 | 12:01:00-07
1183 12:01:00-07 | @ 5 mons | 12:01:00-07 | 12:01:00-07
1184 12:01:00-07 | @ 5 mons 12 hours | 00:01:00-07 | 00:01:00-07
1185 12:01:00-07 | @ 6 years | 12:01:00-07 | 12:01:00-07
1186 12:01:00-07 | @ 34 years | 12:01:00-07 | 12:01:00-07
1187 15:36:39-04 | @ 14 secs ago | 15:36:25-04 | 15:36:53-04
1188 15:36:39-04 | @ 1 min | 15:37:39-04 | 15:35:39-04
1189 15:36:39-04 | @ 5 hours | 20:36:39-04 | 10:36:39-04
1190 15:36:39-04 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43-04 | 13:33:35-04
1191 15:36:39-04 | @ 10 days | 15:36:39-04 | 15:36:39-04
1192 15:36:39-04 | @ 3 mons | 15:36:39-04 | 15:36:39-04
1193 15:36:39-04 | @ 5 mons | 15:36:39-04 | 15:36:39-04
1194 15:36:39-04 | @ 5 mons 12 hours | 03:36:39-04 | 03:36:39-04
1195 15:36:39-04 | @ 6 years | 15:36:39-04 | 15:36:39-04
1196 15:36:39-04 | @ 34 years | 15:36:39-04 | 15:36:39-04
1197 15:36:39-05 | @ 14 secs ago | 15:36:25-05 | 15:36:53-05
1198 15:36:39-05 | @ 1 min | 15:37:39-05 | 15:35:39-05
1199 15:36:39-05 | @ 5 hours | 20:36:39-05 | 10:36:39-05
1200 15:36:39-05 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43-05 | 13:33:35-05
1201 15:36:39-05 | @ 10 days | 15:36:39-05 | 15:36:39-05
1202 15:36:39-05 | @ 3 mons | 15:36:39-05 | 15:36:39-05
1203 15:36:39-05 | @ 5 mons | 15:36:39-05 | 15:36:39-05
1204 15:36:39-05 | @ 5 mons 12 hours | 03:36:39-05 | 03:36:39-05
1205 15:36:39-05 | @ 6 years | 15:36:39-05 | 15:36:39-05
1206 15:36:39-05 | @ 34 years | 15:36:39-05 | 15:36:39-05
1207 23:59:00-07 | @ 14 secs ago | 23:58:46-07 | 23:59:14-07
1208 23:59:00-07 | @ 1 min | 00:00:00-07 | 23:58:00-07
1209 23:59:00-07 | @ 5 hours | 04:59:00-07 | 18:59:00-07
1210 23:59:00-07 | @ 1 day 2 hours 3 mins 4 secs | 02:02:04-07 | 21:55:56-07
1211 23:59:00-07 | @ 10 days | 23:59:00-07 | 23:59:00-07
1212 23:59:00-07 | @ 3 mons | 23:59:00-07 | 23:59:00-07
1213 23:59:00-07 | @ 5 mons | 23:59:00-07 | 23:59:00-07
1214 23:59:00-07 | @ 5 mons 12 hours | 11:59:00-07 | 11:59:00-07
1215 23:59:00-07 | @ 6 years | 23:59:00-07 | 23:59:00-07
1216 23:59:00-07 | @ 34 years | 23:59:00-07 | 23:59:00-07
1217 23:59:59.99-07 | @ 14 secs ago | 23:59:45.99-07 | 00:00:13.99-07
1218 23:59:59.99-07 | @ 1 min | 00:00:59.99-07 | 23:58:59.99-07
1219 23:59:59.99-07 | @ 5 hours | 04:59:59.99-07 | 18:59:59.99-07
1220 23:59:59.99-07 | @ 1 day 2 hours 3 mins 4 secs | 02:03:03.99-07 | 21:56:55.99-07
1221 23:59:59.99-07 | @ 10 days | 23:59:59.99-07 | 23:59:59.99-07
1222 23:59:59.99-07 | @ 3 mons | 23:59:59.99-07 | 23:59:59.99-07
1223 23:59:59.99-07 | @ 5 mons | 23:59:59.99-07 | 23:59:59.99-07
1224 23:59:59.99-07 | @ 5 mons 12 hours | 11:59:59.99-07 | 11:59:59.99-07
1225 23:59:59.99-07 | @ 6 years | 23:59:59.99-07 | 23:59:59.99-07
1226 23:59:59.99-07 | @ 34 years | 23:59:59.99-07 | 23:59:59.99-07
1227 (120 rows)
1229 -- SQL9x OVERLAPS operator
1230 -- test with time zone
1231 SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28')
1232 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "True";
1233 True
1234 ------
1235 t
1236 (1 row)
1238 SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27')
1239 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False";
1240 False
1241 -------
1242 f
1243 (1 row)
1245 SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28')
1246 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '1 day') AS "True";
1247 True
1248 ------
1249 t
1250 (1 row)
1252 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
1253 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False";
1254 False
1255 -------
1256 f
1257 (1 row)
1259 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
1260 OVERLAPS (timestamp with time zone '2000-11-27', interval '12 hours') AS "True";
1261 True
1262 ------
1263 t
1264 (1 row)
1266 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
1267 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '12 hours') AS "False";
1268 False
1269 -------
1270 f
1271 (1 row)
1273 -- test without time zone
1274 SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28')
1275 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "True";
1276 True
1277 ------
1278 t
1279 (1 row)
1281 SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27')
1282 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False";
1283 False
1284 -------
1285 f
1286 (1 row)
1288 SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28')
1289 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '1 day') AS "True";
1290 True
1291 ------
1292 t
1293 (1 row)
1295 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
1296 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False";
1297 False
1298 -------
1299 f
1300 (1 row)
1302 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
1303 OVERLAPS (timestamp without time zone '2000-11-27', interval '12 hours') AS "True";
1304 True
1305 ------
1306 t
1307 (1 row)
1309 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
1310 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '12 hours') AS "False";
1311 False
1312 -------
1313 f
1314 (1 row)
1316 -- test time and interval
1317 SELECT (time '00:00', time '01:00')
1318 OVERLAPS (time '00:30', time '01:30') AS "True";
1319 True
1320 ------
1321 t
1322 (1 row)
1324 SELECT (time '00:00', interval '1 hour')
1325 OVERLAPS (time '00:30', interval '1 hour') AS "True";
1326 True
1327 ------
1328 t
1329 (1 row)
1331 SELECT (time '00:00', interval '1 hour')
1332 OVERLAPS (time '01:30', interval '1 hour') AS "False";
1333 False
1334 -------
1335 f
1336 (1 row)
1338 -- SQL99 seems to want this to be false (and we conform to the spec).
1339 -- istm that this *should* return true, on the theory that time
1340 -- intervals can wrap around the day boundary - thomas 2001-09-25
1341 SELECT (time '00:00', interval '1 hour')
1342 OVERLAPS (time '01:30', interval '1 day') AS "False";
1343 False
1344 -------
1345 f
1346 (1 row)
1348 CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone);
1349 -- get some candidate input values
1350 INSERT INTO TEMP_TIMESTAMP (f1)
1351 SELECT d1 FROM TIMESTAMP_TBL
1352 WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
1353 OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
1354 SELECT '' AS "16", f1 AS "timestamp"
1355 FROM TEMP_TIMESTAMP
1356 ORDER BY "timestamp";
1357 16 | timestamp
1358 ----+------------------------------
1359 | Thu Jan 01 00:00:00 1970 PST
1360 | Wed Feb 28 17:32:01 1996 PST
1361 | Thu Feb 29 17:32:01 1996 PST
1362 | Fri Mar 01 17:32:01 1996 PST
1363 | Mon Dec 30 17:32:01 1996 PST
1364 | Tue Dec 31 17:32:01 1996 PST
1365 | Fri Dec 31 17:32:01 1999 PST
1366 | Sat Jan 01 17:32:01 2000 PST
1367 | Wed Mar 15 02:14:05 2000 PST
1368 | Wed Mar 15 03:14:04 2000 PST
1369 | Wed Mar 15 08:14:01 2000 PST
1370 | Wed Mar 15 12:14:03 2000 PST
1371 | Wed Mar 15 13:14:02 2000 PST
1372 | Sun Dec 31 17:32:01 2000 PST
1373 | Mon Jan 01 17:32:01 2001 PST
1374 | Sat Sep 22 18:19:20 2001 PDT
1375 (16 rows)
1377 SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
1378 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
1379 ORDER BY plus, "timestamp", "interval";
1380 160 | timestamp | interval | plus
1381 -----+------------------------------+-------------------------------+------------------------------
1382 | Thu Jan 01 00:00:00 1970 PST | @ 14 secs ago | Wed Dec 31 23:59:46 1969 PST
1383 | Thu Jan 01 00:00:00 1970 PST | @ 1 min | Thu Jan 01 00:01:00 1970 PST
1384 | Thu Jan 01 00:00:00 1970 PST | @ 5 hours | Thu Jan 01 05:00:00 1970 PST
1385 | Thu Jan 01 00:00:00 1970 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Jan 02 02:03:04 1970 PST
1386 | Thu Jan 01 00:00:00 1970 PST | @ 10 days | Sun Jan 11 00:00:00 1970 PST
1387 | Thu Jan 01 00:00:00 1970 PST | @ 3 mons | Wed Apr 01 00:00:00 1970 PST
1388 | Thu Jan 01 00:00:00 1970 PST | @ 5 mons | Mon Jun 01 00:00:00 1970 PDT
1389 | Thu Jan 01 00:00:00 1970 PST | @ 5 mons 12 hours | Mon Jun 01 12:00:00 1970 PDT
1390 | Thu Jan 01 00:00:00 1970 PST | @ 6 years | Thu Jan 01 00:00:00 1976 PST
1391 | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST
1392 | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST
1393 | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST
1394 | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST
1395 | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST
1396 | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST
1397 | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST
1398 | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST
1399 | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST
1400 | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST
1401 | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST
1402 | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST
1403 | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST
1404 | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST
1405 | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST
1406 | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 17:32:01 1996 PDT
1407 | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 17:32:01 1996 PDT
1408 | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 17:32:01 1996 PDT
1409 | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 17:32:01 1996 PDT
1410 | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 05:32:01 1996 PDT
1411 | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 17:32:01 1996 PDT
1412 | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 05:32:01 1996 PDT
1413 | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 17:32:01 1996 PDT
1414 | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 05:32:01 1996 PDT
1415 | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST
1416 | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST
1417 | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST
1418 | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST
1419 | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST
1420 | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST
1421 | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST
1422 | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST
1423 | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST
1424 | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST
1425 | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST
1426 | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST
1427 | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 17:32:01 1997 PDT
1428 | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 05:32:01 1997 PDT
1429 | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 17:32:01 1997 PDT
1430 | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 05:32:01 1997 PDT
1431 | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST
1432 | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST
1433 | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST
1434 | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST
1435 | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST
1436 | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST
1437 | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST
1438 | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST
1439 | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST
1440 | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST
1441 | Wed Mar 15 02:14:05 2000 PST | @ 14 secs ago | Wed Mar 15 02:13:51 2000 PST
1442 | Wed Mar 15 02:14:05 2000 PST | @ 1 min | Wed Mar 15 02:15:05 2000 PST
1443 | Wed Mar 15 03:14:04 2000 PST | @ 14 secs ago | Wed Mar 15 03:13:50 2000 PST
1444 | Wed Mar 15 03:14:04 2000 PST | @ 1 min | Wed Mar 15 03:15:04 2000 PST
1445 | Wed Mar 15 02:14:05 2000 PST | @ 5 hours | Wed Mar 15 07:14:05 2000 PST
1446 | Wed Mar 15 08:14:01 2000 PST | @ 14 secs ago | Wed Mar 15 08:13:47 2000 PST
1447 | Wed Mar 15 03:14:04 2000 PST | @ 5 hours | Wed Mar 15 08:14:04 2000 PST
1448 | Wed Mar 15 08:14:01 2000 PST | @ 1 min | Wed Mar 15 08:15:01 2000 PST
1449 | Wed Mar 15 12:14:03 2000 PST | @ 14 secs ago | Wed Mar 15 12:13:49 2000 PST
1450 | Wed Mar 15 12:14:03 2000 PST | @ 1 min | Wed Mar 15 12:15:03 2000 PST
1451 | Wed Mar 15 13:14:02 2000 PST | @ 14 secs ago | Wed Mar 15 13:13:48 2000 PST
1452 | Wed Mar 15 08:14:01 2000 PST | @ 5 hours | Wed Mar 15 13:14:01 2000 PST
1453 | Wed Mar 15 13:14:02 2000 PST | @ 1 min | Wed Mar 15 13:15:02 2000 PST
1454 | Wed Mar 15 12:14:03 2000 PST | @ 5 hours | Wed Mar 15 17:14:03 2000 PST
1455 | Wed Mar 15 13:14:02 2000 PST | @ 5 hours | Wed Mar 15 18:14:02 2000 PST
1456 | Wed Mar 15 02:14:05 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 04:17:09 2000 PST
1457 | Wed Mar 15 03:14:04 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 05:17:08 2000 PST
1458 | Wed Mar 15 08:14:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 10:17:05 2000 PST
1459 | Wed Mar 15 12:14:03 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 14:17:07 2000 PST
1460 | Wed Mar 15 13:14:02 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 15:17:06 2000 PST
1461 | Wed Mar 15 02:14:05 2000 PST | @ 10 days | Sat Mar 25 02:14:05 2000 PST
1462 | Wed Mar 15 03:14:04 2000 PST | @ 10 days | Sat Mar 25 03:14:04 2000 PST
1463 | Wed Mar 15 08:14:01 2000 PST | @ 10 days | Sat Mar 25 08:14:01 2000 PST
1464 | Wed Mar 15 12:14:03 2000 PST | @ 10 days | Sat Mar 25 12:14:03 2000 PST
1465 | Wed Mar 15 13:14:02 2000 PST | @ 10 days | Sat Mar 25 13:14:02 2000 PST
1466 | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST
1467 | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST
1468 | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 17:32:01 2000 PDT
1469 | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 05:32:01 2000 PDT
1470 | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 17:32:01 2000 PDT
1471 | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 05:32:01 2000 PDT
1472 | Wed Mar 15 02:14:05 2000 PST | @ 3 mons | Thu Jun 15 02:14:05 2000 PDT
1473 | Wed Mar 15 03:14:04 2000 PST | @ 3 mons | Thu Jun 15 03:14:04 2000 PDT
1474 | Wed Mar 15 08:14:01 2000 PST | @ 3 mons | Thu Jun 15 08:14:01 2000 PDT
1475 | Wed Mar 15 12:14:03 2000 PST | @ 3 mons | Thu Jun 15 12:14:03 2000 PDT
1476 | Wed Mar 15 13:14:02 2000 PST | @ 3 mons | Thu Jun 15 13:14:02 2000 PDT
1477 | Wed Mar 15 02:14:05 2000 PST | @ 5 mons | Tue Aug 15 02:14:05 2000 PDT
1478 | Wed Mar 15 03:14:04 2000 PST | @ 5 mons | Tue Aug 15 03:14:04 2000 PDT
1479 | Wed Mar 15 08:14:01 2000 PST | @ 5 mons | Tue Aug 15 08:14:01 2000 PDT
1480 | Wed Mar 15 12:14:03 2000 PST | @ 5 mons | Tue Aug 15 12:14:03 2000 PDT
1481 | Wed Mar 15 13:14:02 2000 PST | @ 5 mons | Tue Aug 15 13:14:02 2000 PDT
1482 | Wed Mar 15 02:14:05 2000 PST | @ 5 mons 12 hours | Tue Aug 15 14:14:05 2000 PDT
1483 | Wed Mar 15 03:14:04 2000 PST | @ 5 mons 12 hours | Tue Aug 15 15:14:04 2000 PDT
1484 | Wed Mar 15 08:14:01 2000 PST | @ 5 mons 12 hours | Tue Aug 15 20:14:01 2000 PDT
1485 | Wed Mar 15 12:14:03 2000 PST | @ 5 mons 12 hours | Wed Aug 16 00:14:03 2000 PDT
1486 | Wed Mar 15 13:14:02 2000 PST | @ 5 mons 12 hours | Wed Aug 16 01:14:02 2000 PDT
1487 | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST
1488 | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST
1489 | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST
1490 | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST
1491 | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST
1492 | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST
1493 | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST
1494 | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST
1495 | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST
1496 | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST
1497 | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST
1498 | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 17:32:01 2001 PDT
1499 | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 17:32:01 2001 PDT
1500 | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 05:32:01 2001 PDT
1501 | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 17:32:01 2001 PDT
1502 | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 05:32:01 2001 PDT
1503 | Sat Sep 22 18:19:20 2001 PDT | @ 14 secs ago | Sat Sep 22 18:19:06 2001 PDT
1504 | Sat Sep 22 18:19:20 2001 PDT | @ 1 min | Sat Sep 22 18:20:20 2001 PDT
1505 | Sat Sep 22 18:19:20 2001 PDT | @ 5 hours | Sat Sep 22 23:19:20 2001 PDT
1506 | Sat Sep 22 18:19:20 2001 PDT | @ 1 day 2 hours 3 mins 4 secs | Sun Sep 23 20:22:24 2001 PDT
1507 | Sat Sep 22 18:19:20 2001 PDT | @ 10 days | Tue Oct 02 18:19:20 2001 PDT
1508 | Sat Sep 22 18:19:20 2001 PDT | @ 3 mons | Sat Dec 22 18:19:20 2001 PST
1509 | Sat Sep 22 18:19:20 2001 PDT | @ 5 mons | Fri Feb 22 18:19:20 2002 PST
1510 | Sat Sep 22 18:19:20 2001 PDT | @ 5 mons 12 hours | Sat Feb 23 06:19:20 2002 PST
1511 | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
1512 | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
1513 | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST
1514 | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST
1515 | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST
1516 | Thu Jan 01 00:00:00 1970 PST | @ 34 years | Thu Jan 01 00:00:00 2004 PST
1517 | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST
1518 | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST
1519 | Wed Mar 15 02:14:05 2000 PST | @ 6 years | Wed Mar 15 02:14:05 2006 PST
1520 | Wed Mar 15 03:14:04 2000 PST | @ 6 years | Wed Mar 15 03:14:04 2006 PST
1521 | Wed Mar 15 08:14:01 2000 PST | @ 6 years | Wed Mar 15 08:14:01 2006 PST
1522 | Wed Mar 15 12:14:03 2000 PST | @ 6 years | Wed Mar 15 12:14:03 2006 PST
1523 | Wed Mar 15 13:14:02 2000 PST | @ 6 years | Wed Mar 15 13:14:02 2006 PST
1524 | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST
1525 | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST
1526 | Sat Sep 22 18:19:20 2001 PDT | @ 6 years | Sat Sep 22 18:19:20 2007 PDT
1527 | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
1528 | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
1529 | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST
1530 | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST
1531 | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST
1532 | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST
1533 | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST
1534 | Wed Mar 15 02:14:05 2000 PST | @ 34 years | Wed Mar 15 02:14:05 2034 PDT
1535 | Wed Mar 15 03:14:04 2000 PST | @ 34 years | Wed Mar 15 03:14:04 2034 PDT
1536 | Wed Mar 15 08:14:01 2000 PST | @ 34 years | Wed Mar 15 08:14:01 2034 PDT
1537 | Wed Mar 15 12:14:03 2000 PST | @ 34 years | Wed Mar 15 12:14:03 2034 PDT
1538 | Wed Mar 15 13:14:02 2000 PST | @ 34 years | Wed Mar 15 13:14:02 2034 PDT
1539 | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST
1540 | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST
1541 | Sat Sep 22 18:19:20 2001 PDT | @ 34 years | Sat Sep 22 18:19:20 2035 PDT
1542 (160 rows)
1544 SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
1545 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
1546 WHERE isfinite(d.f1)
1547 ORDER BY minus, "timestamp", "interval";
1548 160 | timestamp | interval | minus
1549 -----+------------------------------+-------------------------------+------------------------------
1550 | Thu Jan 01 00:00:00 1970 PST | @ 34 years | Wed Jan 01 00:00:00 1936 PST
1551 | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
1552 | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
1553 | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST
1554 | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST
1555 | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST
1556 | Thu Jan 01 00:00:00 1970 PST | @ 6 years | Wed Jan 01 00:00:00 1964 PST
1557 | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST
1558 | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST
1559 | Wed Mar 15 02:14:05 2000 PST | @ 34 years | Tue Mar 15 02:14:05 1966 PST
1560 | Wed Mar 15 03:14:04 2000 PST | @ 34 years | Tue Mar 15 03:14:04 1966 PST
1561 | Wed Mar 15 08:14:01 2000 PST | @ 34 years | Tue Mar 15 08:14:01 1966 PST
1562 | Wed Mar 15 12:14:03 2000 PST | @ 34 years | Tue Mar 15 12:14:03 1966 PST
1563 | Wed Mar 15 13:14:02 2000 PST | @ 34 years | Tue Mar 15 13:14:02 1966 PST
1564 | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST
1565 | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST
1566 | Sat Sep 22 18:19:20 2001 PDT | @ 34 years | Fri Sep 22 18:19:20 1967 PDT
1567 | Thu Jan 01 00:00:00 1970 PST | @ 5 mons 12 hours | Thu Jul 31 12:00:00 1969 PDT
1568 | Thu Jan 01 00:00:00 1970 PST | @ 5 mons | Fri Aug 01 00:00:00 1969 PDT
1569 | Thu Jan 01 00:00:00 1970 PST | @ 3 mons | Wed Oct 01 00:00:00 1969 PDT
1570 | Thu Jan 01 00:00:00 1970 PST | @ 10 days | Mon Dec 22 00:00:00 1969 PST
1571 | Thu Jan 01 00:00:00 1970 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 21:56:56 1969 PST
1572 | Thu Jan 01 00:00:00 1970 PST | @ 5 hours | Wed Dec 31 19:00:00 1969 PST
1573 | Thu Jan 01 00:00:00 1970 PST | @ 1 min | Wed Dec 31 23:59:00 1969 PST
1574 | Thu Jan 01 00:00:00 1970 PST | @ 14 secs ago | Thu Jan 01 00:00:14 1970 PST
1575 | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
1576 | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
1577 | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST
1578 | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST
1579 | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST
1580 | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST
1581 | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST
1582 | Wed Mar 15 02:14:05 2000 PST | @ 6 years | Tue Mar 15 02:14:05 1994 PST
1583 | Wed Mar 15 03:14:04 2000 PST | @ 6 years | Tue Mar 15 03:14:04 1994 PST
1584 | Wed Mar 15 08:14:01 2000 PST | @ 6 years | Tue Mar 15 08:14:01 1994 PST
1585 | Wed Mar 15 12:14:03 2000 PST | @ 6 years | Tue Mar 15 12:14:03 1994 PST
1586 | Wed Mar 15 13:14:02 2000 PST | @ 6 years | Tue Mar 15 13:14:02 1994 PST
1587 | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST
1588 | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST
1589 | Sat Sep 22 18:19:20 2001 PDT | @ 6 years | Fri Sep 22 18:19:20 1995 PDT
1590 | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 05:32:01 1995 PDT
1591 | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 17:32:01 1995 PDT
1592 | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 05:32:01 1995 PDT
1593 | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 17:32:01 1995 PDT
1594 | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 05:32:01 1995 PDT
1595 | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 17:32:01 1995 PDT
1596 | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST
1597 | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST
1598 | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST
1599 | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST
1600 | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST
1601 | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST
1602 | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST
1603 | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST
1604 | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST
1605 | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST
1606 | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST
1607 | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST
1608 | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST
1609 | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST
1610 | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST
1611 | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST
1612 | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST
1613 | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST
1614 | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 05:32:01 1996 PDT
1615 | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 17:32:01 1996 PDT
1616 | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 05:32:01 1996 PDT
1617 | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 17:32:01 1996 PDT
1618 | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 17:32:01 1996 PDT
1619 | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 17:32:01 1996 PDT
1620 | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST
1621 | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST
1622 | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST
1623 | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST
1624 | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST
1625 | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST
1626 | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST
1627 | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST
1628 | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST
1629 | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST
1630 | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 05:32:01 1999 PDT
1631 | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 17:32:01 1999 PDT
1632 | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 05:32:01 1999 PDT
1633 | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 17:32:01 1999 PDT
1634 | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 17:32:01 1999 PDT
1635 | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 17:32:01 1999 PDT
1636 | Wed Mar 15 02:14:05 2000 PST | @ 5 mons 12 hours | Thu Oct 14 14:14:05 1999 PDT
1637 | Wed Mar 15 03:14:04 2000 PST | @ 5 mons 12 hours | Thu Oct 14 15:14:04 1999 PDT
1638 | Wed Mar 15 08:14:01 2000 PST | @ 5 mons 12 hours | Thu Oct 14 20:14:01 1999 PDT
1639 | Wed Mar 15 12:14:03 2000 PST | @ 5 mons 12 hours | Fri Oct 15 00:14:03 1999 PDT
1640 | Wed Mar 15 13:14:02 2000 PST | @ 5 mons 12 hours | Fri Oct 15 01:14:02 1999 PDT
1641 | Wed Mar 15 02:14:05 2000 PST | @ 5 mons | Fri Oct 15 02:14:05 1999 PDT
1642 | Wed Mar 15 03:14:04 2000 PST | @ 5 mons | Fri Oct 15 03:14:04 1999 PDT
1643 | Wed Mar 15 08:14:01 2000 PST | @ 5 mons | Fri Oct 15 08:14:01 1999 PDT
1644 | Wed Mar 15 12:14:03 2000 PST | @ 5 mons | Fri Oct 15 12:14:03 1999 PDT
1645 | Wed Mar 15 13:14:02 2000 PST | @ 5 mons | Fri Oct 15 13:14:02 1999 PDT
1646 | Wed Mar 15 02:14:05 2000 PST | @ 3 mons | Wed Dec 15 02:14:05 1999 PST
1647 | Wed Mar 15 03:14:04 2000 PST | @ 3 mons | Wed Dec 15 03:14:04 1999 PST
1648 | Wed Mar 15 08:14:01 2000 PST | @ 3 mons | Wed Dec 15 08:14:01 1999 PST
1649 | Wed Mar 15 12:14:03 2000 PST | @ 3 mons | Wed Dec 15 12:14:03 1999 PST
1650 | Wed Mar 15 13:14:02 2000 PST | @ 3 mons | Wed Dec 15 13:14:02 1999 PST
1651 | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST
1652 | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST
1653 | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST
1654 | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST
1655 | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST
1656 | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST
1657 | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST
1658 | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST
1659 | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST
1660 | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST
1661 | Wed Mar 15 02:14:05 2000 PST | @ 10 days | Sun Mar 05 02:14:05 2000 PST
1662 | Wed Mar 15 03:14:04 2000 PST | @ 10 days | Sun Mar 05 03:14:04 2000 PST
1663 | Wed Mar 15 08:14:01 2000 PST | @ 10 days | Sun Mar 05 08:14:01 2000 PST
1664 | Wed Mar 15 12:14:03 2000 PST | @ 10 days | Sun Mar 05 12:14:03 2000 PST
1665 | Wed Mar 15 13:14:02 2000 PST | @ 10 days | Sun Mar 05 13:14:02 2000 PST
1666 | Wed Mar 15 02:14:05 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 00:11:01 2000 PST
1667 | Wed Mar 15 03:14:04 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 01:11:00 2000 PST
1668 | Wed Mar 15 08:14:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 06:10:57 2000 PST
1669 | Wed Mar 15 12:14:03 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 10:10:59 2000 PST
1670 | Wed Mar 15 13:14:02 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 11:10:58 2000 PST
1671 | Wed Mar 15 02:14:05 2000 PST | @ 5 hours | Tue Mar 14 21:14:05 2000 PST
1672 | Wed Mar 15 03:14:04 2000 PST | @ 5 hours | Tue Mar 14 22:14:04 2000 PST
1673 | Wed Mar 15 02:14:05 2000 PST | @ 1 min | Wed Mar 15 02:13:05 2000 PST
1674 | Wed Mar 15 02:14:05 2000 PST | @ 14 secs ago | Wed Mar 15 02:14:19 2000 PST
1675 | Wed Mar 15 03:14:04 2000 PST | @ 1 min | Wed Mar 15 03:13:04 2000 PST
1676 | Wed Mar 15 08:14:01 2000 PST | @ 5 hours | Wed Mar 15 03:14:01 2000 PST
1677 | Wed Mar 15 03:14:04 2000 PST | @ 14 secs ago | Wed Mar 15 03:14:18 2000 PST
1678 | Wed Mar 15 12:14:03 2000 PST | @ 5 hours | Wed Mar 15 07:14:03 2000 PST
1679 | Wed Mar 15 08:14:01 2000 PST | @ 1 min | Wed Mar 15 08:13:01 2000 PST
1680 | Wed Mar 15 13:14:02 2000 PST | @ 5 hours | Wed Mar 15 08:14:02 2000 PST
1681 | Wed Mar 15 08:14:01 2000 PST | @ 14 secs ago | Wed Mar 15 08:14:15 2000 PST
1682 | Wed Mar 15 12:14:03 2000 PST | @ 1 min | Wed Mar 15 12:13:03 2000 PST
1683 | Wed Mar 15 12:14:03 2000 PST | @ 14 secs ago | Wed Mar 15 12:14:17 2000 PST
1684 | Wed Mar 15 13:14:02 2000 PST | @ 1 min | Wed Mar 15 13:13:02 2000 PST
1685 | Wed Mar 15 13:14:02 2000 PST | @ 14 secs ago | Wed Mar 15 13:14:16 2000 PST
1686 | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 05:32:01 2000 PDT
1687 | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 17:32:01 2000 PDT
1688 | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 05:32:01 2000 PDT
1689 | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 17:32:01 2000 PDT
1690 | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 17:32:01 2000 PDT
1691 | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 17:32:01 2000 PDT
1692 | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST
1693 | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST
1694 | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST
1695 | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST
1696 | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST
1697 | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST
1698 | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST
1699 | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST
1700 | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST
1701 | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST
1702 | Sat Sep 22 18:19:20 2001 PDT | @ 5 mons 12 hours | Sun Apr 22 06:19:20 2001 PDT
1703 | Sat Sep 22 18:19:20 2001 PDT | @ 5 mons | Sun Apr 22 18:19:20 2001 PDT
1704 | Sat Sep 22 18:19:20 2001 PDT | @ 3 mons | Fri Jun 22 18:19:20 2001 PDT
1705 | Sat Sep 22 18:19:20 2001 PDT | @ 10 days | Wed Sep 12 18:19:20 2001 PDT
1706 | Sat Sep 22 18:19:20 2001 PDT | @ 1 day 2 hours 3 mins 4 secs | Fri Sep 21 16:16:16 2001 PDT
1707 | Sat Sep 22 18:19:20 2001 PDT | @ 5 hours | Sat Sep 22 13:19:20 2001 PDT
1708 | Sat Sep 22 18:19:20 2001 PDT | @ 1 min | Sat Sep 22 18:18:20 2001 PDT
1709 | Sat Sep 22 18:19:20 2001 PDT | @ 14 secs ago | Sat Sep 22 18:19:34 2001 PDT
1710 (160 rows)
1712 SELECT '' AS "16", d.f1 AS "timestamp",
1713 timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero,
1714 d.f1 - timestamp with time zone '1980-01-06 00:00 GMT' AS difference
1715 FROM TEMP_TIMESTAMP d
1716 ORDER BY difference;
1717 16 | timestamp | gpstime_zero | difference
1718 ----+------------------------------+------------------------------+-------------------------------------
1719 | Thu Jan 01 00:00:00 1970 PST | Sat Jan 05 16:00:00 1980 PST | @ 3656 days 16 hours ago
1720 | Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
1721 | Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
1722 | Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
1723 | Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec
1724 | Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec
1725 | Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec
1726 | Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec
1727 | Wed Mar 15 02:14:05 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 10 hours 14 mins 5 secs
1728 | Wed Mar 15 03:14:04 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 11 hours 14 mins 4 secs
1729 | Wed Mar 15 08:14:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 16 hours 14 mins 1 sec
1730 | Wed Mar 15 12:14:03 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 20 hours 14 mins 3 secs
1731 | Wed Mar 15 13:14:02 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 21 hours 14 mins 2 secs
1732 | Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec
1733 | Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec
1734 | Sat Sep 22 18:19:20 2001 PDT | Sat Jan 05 16:00:00 1980 PST | @ 7931 days 1 hour 19 mins 20 secs
1735 (16 rows)
1737 SELECT '' AS "226", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference
1738 FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
1739 ORDER BY timestamp1, timestamp2, difference;
1740 226 | timestamp1 | timestamp2 | difference
1741 -----+------------------------------+------------------------------+-------------------------------------------
1742 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 PST | @ 0
1743 | Thu Jan 01 00:00:00 1970 PST | Wed Feb 28 17:32:01 1996 PST | @ 9554 days 17 hours 32 mins 1 sec ago
1744 | Thu Jan 01 00:00:00 1970 PST | Thu Feb 29 17:32:01 1996 PST | @ 9555 days 17 hours 32 mins 1 sec ago
1745 | Thu Jan 01 00:00:00 1970 PST | Fri Mar 01 17:32:01 1996 PST | @ 9556 days 17 hours 32 mins 1 sec ago
1746 | Thu Jan 01 00:00:00 1970 PST | Mon Dec 30 17:32:01 1996 PST | @ 9860 days 17 hours 32 mins 1 sec ago
1747 | Thu Jan 01 00:00:00 1970 PST | Tue Dec 31 17:32:01 1996 PST | @ 9861 days 17 hours 32 mins 1 sec ago
1748 | Thu Jan 01 00:00:00 1970 PST | Fri Dec 31 17:32:01 1999 PST | @ 10956 days 17 hours 32 mins 1 sec ago
1749 | Thu Jan 01 00:00:00 1970 PST | Sat Jan 01 17:32:01 2000 PST | @ 10957 days 17 hours 32 mins 1 sec ago
1750 | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 02:14:05 2000 PST | @ 11031 days 2 hours 14 mins 5 secs ago
1751 | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 03:14:04 2000 PST | @ 11031 days 3 hours 14 mins 4 secs ago
1752 | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 08:14:01 2000 PST | @ 11031 days 8 hours 14 mins 1 sec ago
1753 | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 12:14:03 2000 PST | @ 11031 days 12 hours 14 mins 3 secs ago
1754 | Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 13:14:02 2000 PST | @ 11031 days 13 hours 14 mins 2 secs ago
1755 | Thu Jan 01 00:00:00 1970 PST | Sun Dec 31 17:32:01 2000 PST | @ 11322 days 17 hours 32 mins 1 sec ago
1756 | Thu Jan 01 00:00:00 1970 PST | Mon Jan 01 17:32:01 2001 PST | @ 11323 days 17 hours 32 mins 1 sec ago
1757 | Thu Jan 01 00:00:00 1970 PST | Sat Sep 22 18:19:20 2001 PDT | @ 11587 days 17 hours 19 mins 20 secs ago
1758 | Wed Feb 28 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9554 days 17 hours 32 mins 1 sec
1759 | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
1760 | Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
1761 | Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago
1762 | Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago
1763 | Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
1764 | Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
1765 | Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
1766 | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1476 days 8 hours 42 mins 4 secs ago
1767 | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1476 days 9 hours 42 mins 3 secs ago
1768 | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1476 days 14 hours 42 mins ago
1769 | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1476 days 18 hours 42 mins 2 secs ago
1770 | Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1476 days 19 hours 42 mins 1 sec ago
1771 | Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
1772 | Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
1773 | Wed Feb 28 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2032 days 23 hours 47 mins 19 secs ago
1774 | Thu Feb 29 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9555 days 17 hours 32 mins 1 sec
1775 | Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
1776 | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
1777 | Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago
1778 | Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago
1779 | Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
1780 | Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
1781 | Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
1782 | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1475 days 8 hours 42 mins 4 secs ago
1783 | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1475 days 9 hours 42 mins 3 secs ago
1784 | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1475 days 14 hours 42 mins ago
1785 | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1475 days 18 hours 42 mins 2 secs ago
1786 | Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1475 days 19 hours 42 mins 1 sec ago
1787 | Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
1788 | Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
1789 | Thu Feb 29 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2031 days 23 hours 47 mins 19 secs ago
1790 | Fri Mar 01 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9556 days 17 hours 32 mins 1 sec
1791 | Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
1792 | Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
1793 | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0
1794 | Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago
1795 | Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
1796 | Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
1797 | Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
1798 | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1474 days 8 hours 42 mins 4 secs ago
1799 | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1474 days 9 hours 42 mins 3 secs ago
1800 | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1474 days 14 hours 42 mins ago
1801 | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1474 days 18 hours 42 mins 2 secs ago
1802 | Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1474 days 19 hours 42 mins 1 sec ago
1803 | Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
1804 | Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
1805 | Fri Mar 01 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2030 days 23 hours 47 mins 19 secs ago
1806 | Mon Dec 30 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9860 days 17 hours 32 mins 1 sec
1807 | Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
1808 | Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
1809 | Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days
1810 | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0
1811 | Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
1812 | Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
1813 | Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
1814 | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1170 days 8 hours 42 mins 4 secs ago
1815 | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1170 days 9 hours 42 mins 3 secs ago
1816 | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1170 days 14 hours 42 mins ago
1817 | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1170 days 18 hours 42 mins 2 secs ago
1818 | Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1170 days 19 hours 42 mins 1 sec ago
1819 | Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
1820 | Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
1821 | Mon Dec 30 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1726 days 23 hours 47 mins 19 secs ago
1822 | Tue Dec 31 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9861 days 17 hours 32 mins 1 sec
1823 | Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
1824 | Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
1825 | Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days
1826 | Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day
1827 | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
1828 | Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
1829 | Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
1830 | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1169 days 8 hours 42 mins 4 secs ago
1831 | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1169 days 9 hours 42 mins 3 secs ago
1832 | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1169 days 14 hours 42 mins ago
1833 | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1169 days 18 hours 42 mins 2 secs ago
1834 | Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1169 days 19 hours 42 mins 1 sec ago
1835 | Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
1836 | Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
1837 | Tue Dec 31 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1725 days 23 hours 47 mins 19 secs ago
1838 | Fri Dec 31 17:32:01 1999 PST | Thu Jan 01 00:00:00 1970 PST | @ 10956 days 17 hours 32 mins 1 sec
1839 | Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
1840 | Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
1841 | Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days
1842 | Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days
1843 | Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
1844 | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
1845 | Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
1846 | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 02:14:05 2000 PST | @ 74 days 8 hours 42 mins 4 secs ago
1847 | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 03:14:04 2000 PST | @ 74 days 9 hours 42 mins 3 secs ago
1848 | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 08:14:01 2000 PST | @ 74 days 14 hours 42 mins ago
1849 | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 12:14:03 2000 PST | @ 74 days 18 hours 42 mins 2 secs ago
1850 | Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 13:14:02 2000 PST | @ 74 days 19 hours 42 mins 1 sec ago
1851 | Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
1852 | Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
1853 | Fri Dec 31 17:32:01 1999 PST | Sat Sep 22 18:19:20 2001 PDT | @ 630 days 23 hours 47 mins 19 secs ago
1854 | Sat Jan 01 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 10957 days 17 hours 32 mins 1 sec
1855 | Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
1856 | Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
1857 | Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days
1858 | Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days
1859 | Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
1860 | Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
1861 | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
1862 | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 73 days 8 hours 42 mins 4 secs ago
1863 | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 73 days 9 hours 42 mins 3 secs ago
1864 | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 73 days 14 hours 42 mins ago
1865 | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 73 days 18 hours 42 mins 2 secs ago
1866 | Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 73 days 19 hours 42 mins 1 sec ago
1867 | Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
1868 | Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
1869 | Sat Jan 01 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 629 days 23 hours 47 mins 19 secs ago
1870 | Wed Mar 15 02:14:05 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 2 hours 14 mins 5 secs
1871 | Wed Mar 15 02:14:05 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 8 hours 42 mins 4 secs
1872 | Wed Mar 15 02:14:05 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 8 hours 42 mins 4 secs
1873 | Wed Mar 15 02:14:05 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 8 hours 42 mins 4 secs
1874 | Wed Mar 15 02:14:05 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 8 hours 42 mins 4 secs
1875 | Wed Mar 15 02:14:05 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 8 hours 42 mins 4 secs
1876 | Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
1877 | Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
1878 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
1879 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago
1880 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago
1881 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago
1882 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago
1883 | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
1884 | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
1885 | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
1886 | Wed Mar 15 03:14:04 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 3 hours 14 mins 4 secs
1887 | Wed Mar 15 03:14:04 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 9 hours 42 mins 3 secs
1888 | Wed Mar 15 03:14:04 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 9 hours 42 mins 3 secs
1889 | Wed Mar 15 03:14:04 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 9 hours 42 mins 3 secs
1890 | Wed Mar 15 03:14:04 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 9 hours 42 mins 3 secs
1891 | Wed Mar 15 03:14:04 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 9 hours 42 mins 3 secs
1892 | Wed Mar 15 03:14:04 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 9 hours 42 mins 3 secs
1893 | Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
1894 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
1895 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
1896 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago
1897 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago
1898 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago
1899 | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
1900 | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
1901 | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
1902 | Wed Mar 15 08:14:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 8 hours 14 mins 1 sec
1903 | Wed Mar 15 08:14:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 14 hours 42 mins
1904 | Wed Mar 15 08:14:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 14 hours 42 mins
1905 | Wed Mar 15 08:14:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 14 hours 42 mins
1906 | Wed Mar 15 08:14:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 14 hours 42 mins
1907 | Wed Mar 15 08:14:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 14 hours 42 mins
1908 | Wed Mar 15 08:14:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 14 hours 42 mins
1909 | Wed Mar 15 08:14:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 14 hours 42 mins
1910 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
1911 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
1912 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
1913 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago
1914 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago
1915 | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
1916 | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
1917 | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
1918 | Wed Mar 15 12:14:03 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 12 hours 14 mins 3 secs
1919 | Wed Mar 15 12:14:03 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 18 hours 42 mins 2 secs
1920 | Wed Mar 15 12:14:03 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 18 hours 42 mins 2 secs
1921 | Wed Mar 15 12:14:03 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 18 hours 42 mins 2 secs
1922 | Wed Mar 15 12:14:03 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 18 hours 42 mins 2 secs
1923 | Wed Mar 15 12:14:03 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 18 hours 42 mins 2 secs
1924 | Wed Mar 15 12:14:03 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 18 hours 42 mins 2 secs
1925 | Wed Mar 15 12:14:03 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 18 hours 42 mins 2 secs
1926 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 9 hours 59 mins 58 secs
1927 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
1928 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
1929 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
1930 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago
1931 | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
1932 | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
1933 | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago
1934 | Wed Mar 15 13:14:02 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 13 hours 14 mins 2 secs
1935 | Wed Mar 15 13:14:02 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 19 hours 42 mins 1 sec
1936 | Wed Mar 15 13:14:02 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 19 hours 42 mins 1 sec
1937 | Wed Mar 15 13:14:02 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 19 hours 42 mins 1 sec
1938 | Wed Mar 15 13:14:02 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 19 hours 42 mins 1 sec
1939 | Wed Mar 15 13:14:02 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 19 hours 42 mins 1 sec
1940 | Wed Mar 15 13:14:02 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 19 hours 42 mins 1 sec
1941 | Wed Mar 15 13:14:02 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 19 hours 42 mins 1 sec
1942 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 10 hours 59 mins 57 secs
1943 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 9 hours 59 mins 58 secs
1944 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 1 sec
1945 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 59 mins 59 secs
1946 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 0
1947 | Wed Mar 15 13:14:02 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 4 hours 17 mins 59 secs ago
1948 | Wed Mar 15 13:14:02 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 4 hours 17 mins 59 secs ago
1949 | Wed Mar 15 13:14:02 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 4 hours 5 mins 18 secs ago
1950 | Sun Dec 31 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11322 days 17 hours 32 mins 1 sec
1951 | Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
1952 | Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
1953 | Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days
1954 | Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days
1955 | Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days
1956 | Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days
1957 | Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days
1958 | Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 291 days 15 hours 17 mins 56 secs
1959 | Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 291 days 14 hours 17 mins 57 secs
1960 | Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 291 days 9 hours 18 mins
1961 | Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 291 days 5 hours 17 mins 58 secs
1962 | Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 291 days 4 hours 17 mins 59 secs
1963 | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
1964 | Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
1965 | Sun Dec 31 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 264 days 23 hours 47 mins 19 secs ago
1966 | Mon Jan 01 17:32:01 2001 PST | Thu Jan 01 00:00:00 1970 PST | @ 11323 days 17 hours 32 mins 1 sec
1967 | Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
1968 | Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
1969 | Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days
1970 | Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days
1971 | Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days
1972 | Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days
1973 | Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days
1974 | Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 02:14:05 2000 PST | @ 292 days 15 hours 17 mins 56 secs
1975 | Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 03:14:04 2000 PST | @ 292 days 14 hours 17 mins 57 secs
1976 | Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 08:14:01 2000 PST | @ 292 days 9 hours 18 mins
1977 | Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 12:14:03 2000 PST | @ 292 days 5 hours 17 mins 58 secs
1978 | Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 13:14:02 2000 PST | @ 292 days 4 hours 17 mins 59 secs
1979 | Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
1980 | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
1981 | Mon Jan 01 17:32:01 2001 PST | Sat Sep 22 18:19:20 2001 PDT | @ 263 days 23 hours 47 mins 19 secs ago
1982 | Sat Sep 22 18:19:20 2001 PDT | Thu Jan 01 00:00:00 1970 PST | @ 11587 days 17 hours 19 mins 20 secs
1983 | Sat Sep 22 18:19:20 2001 PDT | Wed Feb 28 17:32:01 1996 PST | @ 2032 days 23 hours 47 mins 19 secs
1984 | Sat Sep 22 18:19:20 2001 PDT | Thu Feb 29 17:32:01 1996 PST | @ 2031 days 23 hours 47 mins 19 secs
1985 | Sat Sep 22 18:19:20 2001 PDT | Fri Mar 01 17:32:01 1996 PST | @ 2030 days 23 hours 47 mins 19 secs
1986 | Sat Sep 22 18:19:20 2001 PDT | Mon Dec 30 17:32:01 1996 PST | @ 1726 days 23 hours 47 mins 19 secs
1987 | Sat Sep 22 18:19:20 2001 PDT | Tue Dec 31 17:32:01 1996 PST | @ 1725 days 23 hours 47 mins 19 secs
1988 | Sat Sep 22 18:19:20 2001 PDT | Fri Dec 31 17:32:01 1999 PST | @ 630 days 23 hours 47 mins 19 secs
1989 | Sat Sep 22 18:19:20 2001 PDT | Sat Jan 01 17:32:01 2000 PST | @ 629 days 23 hours 47 mins 19 secs
1990 | Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 02:14:05 2000 PST | @ 556 days 15 hours 5 mins 15 secs
1991 | Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 03:14:04 2000 PST | @ 556 days 14 hours 5 mins 16 secs
1992 | Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 08:14:01 2000 PST | @ 556 days 9 hours 5 mins 19 secs
1993 | Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 12:14:03 2000 PST | @ 556 days 5 hours 5 mins 17 secs
1994 | Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 13:14:02 2000 PST | @ 556 days 4 hours 5 mins 18 secs
1995 | Sat Sep 22 18:19:20 2001 PDT | Sun Dec 31 17:32:01 2000 PST | @ 264 days 23 hours 47 mins 19 secs
1996 | Sat Sep 22 18:19:20 2001 PDT | Mon Jan 01 17:32:01 2001 PST | @ 263 days 23 hours 47 mins 19 secs
1997 | Sat Sep 22 18:19:20 2001 PDT | Sat Sep 22 18:19:20 2001 PDT | @ 0
1998 (256 rows)
2000 --
2001 -- abstime, reltime arithmetic
2002 --
2003 SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime
2004 FROM ABSTIME_TBL, RELTIME_TBL
2005 WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) < abstime 'Jan 14 14:00:00 1971'
2006 ORDER BY abstime, reltime;
2007 ten | abstime | reltime
2008 -----+------------------------------+---------------
2009 | Sat May 10 23:59:12 1947 PST | @ 14 secs ago
2010 | Sat May 10 23:59:12 1947 PST | @ 1 min
2011 | Sat May 10 23:59:12 1947 PST | @ 5 hours
2012 | Sat May 10 23:59:12 1947 PST | @ 10 days
2013 | Sat May 10 23:59:12 1947 PST | @ 3 mons
2014 | Wed Dec 31 16:00:00 1969 PST | @ 14 secs ago
2015 | Wed Dec 31 16:00:00 1969 PST | @ 1 min
2016 | Wed Dec 31 16:00:00 1969 PST | @ 5 hours
2017 | Wed Dec 31 16:00:00 1969 PST | @ 10 days
2018 | Wed Dec 31 16:00:00 1969 PST | @ 3 mons
2019 (10 rows)
2021 -- these four queries should return the same answer
2022 -- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and
2023 -- therefore, should not show up in the results.
2024 SELECT '' AS three, * FROM ABSTIME_TBL
2025 WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year') -- +3 years
2026 < abstime 'Jan 14 14:00:00 1977';
2027 three | f1
2028 -------+------------------------------
2029 | Sun Jan 14 03:14:21 1973 PST
2030 | Wed Dec 31 16:00:00 1969 PST
2031 | Sat May 10 23:59:12 1947 PST
2032 (3 rows)
2034 SELECT '' AS three, * FROM ABSTIME_TBL
2035 WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year ago') -- -3 years
2036 < abstime 'Jan 14 14:00:00 1971';
2037 three | f1
2038 -------+------------------------------
2039 | Sun Jan 14 03:14:21 1973 PST
2040 | Wed Dec 31 16:00:00 1969 PST
2041 | Sat May 10 23:59:12 1947 PST
2042 (3 rows)
2044 SELECT '' AS three, * FROM ABSTIME_TBL
2045 WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year') -- -(+3) years
2046 < abstime 'Jan 14 14:00:00 1971';
2047 three | f1
2048 -------+------------------------------
2049 | Sun Jan 14 03:14:21 1973 PST
2050 | Wed Dec 31 16:00:00 1969 PST
2051 | Sat May 10 23:59:12 1947 PST
2052 (3 rows)
2054 SELECT '' AS three, * FROM ABSTIME_TBL
2055 WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year ago') -- -(-3) years
2056 < abstime 'Jan 14 14:00:00 1977';
2057 three | f1
2058 -------+------------------------------
2059 | Sun Jan 14 03:14:21 1973 PST
2060 | Wed Dec 31 16:00:00 1969 PST
2061 | Sat May 10 23:59:12 1947 PST
2062 (3 rows)
2064 --
2065 -- Conversions
2066 --
2067 SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date
2068 FROM TEMP_TIMESTAMP
2069 WHERE f1 <> timestamp 'now'
2070 ORDER BY date, "timestamp";
2071 16 | timestamp | date
2072 ----+------------------------------+------------
2073 | Thu Jan 01 00:00:00 1970 PST | 01-01-1970
2074 | Wed Feb 28 17:32:01 1996 PST | 02-28-1996
2075 | Thu Feb 29 17:32:01 1996 PST | 02-29-1996
2076 | Fri Mar 01 17:32:01 1996 PST | 03-01-1996
2077 | Mon Dec 30 17:32:01 1996 PST | 12-30-1996
2078 | Tue Dec 31 17:32:01 1996 PST | 12-31-1996
2079 | Fri Dec 31 17:32:01 1999 PST | 12-31-1999
2080 | Sat Jan 01 17:32:01 2000 PST | 01-01-2000
2081 | Wed Mar 15 02:14:05 2000 PST | 03-15-2000
2082 | Wed Mar 15 03:14:04 2000 PST | 03-15-2000
2083 | Wed Mar 15 08:14:01 2000 PST | 03-15-2000
2084 | Wed Mar 15 12:14:03 2000 PST | 03-15-2000
2085 | Wed Mar 15 13:14:02 2000 PST | 03-15-2000
2086 | Sun Dec 31 17:32:01 2000 PST | 12-31-2000
2087 | Mon Jan 01 17:32:01 2001 PST | 01-01-2001
2088 | Sat Sep 22 18:19:20 2001 PDT | 09-22-2001
2089 (16 rows)
2091 SELECT '' AS "16", f1 AS "timestamp", abstime(f1) AS abstime
2092 FROM TEMP_TIMESTAMP
2093 ORDER BY abstime;
2094 16 | timestamp | abstime
2095 ----+------------------------------+------------------------------
2096 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 PST
2097 | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST
2098 | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST
2099 | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST
2100 | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST
2101 | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST
2102 | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST
2103 | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST
2104 | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST
2105 | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST
2106 | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST
2107 | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST
2108 | Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 13:14:02 2000 PST
2109 | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST
2110 | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST
2111 | Sat Sep 22 18:19:20 2001 PDT | Sat Sep 22 18:19:20 2001 PDT
2112 (16 rows)
2114 SELECT '' AS four, f1 AS abstime, date(f1) AS date
2115 FROM ABSTIME_TBL
2116 WHERE isfinite(f1) AND f1 <> abstime 'now'
2117 ORDER BY date, abstime;
2118 four | abstime | date
2119 ------+------------------------------+------------
2120 | Sat May 10 23:59:12 1947 PST | 05-10-1947
2121 | Wed Dec 31 16:00:00 1969 PST | 12-31-1969
2122 | Sun Jan 14 03:14:21 1973 PST | 01-14-1973
2123 | Mon May 01 00:30:30 1995 PDT | 05-01-1995
2124 (4 rows)
2126 SELECT '' AS two, d1 AS "timestamp", abstime(d1) AS abstime
2127 FROM TIMESTAMP_TBL WHERE NOT isfinite(d1);
2128 two | timestamp | abstime
2129 -----+-----------+-----------
2130 | -infinity | -infinity
2131 | infinity | infinity
2132 (2 rows)
2134 SELECT '' AS three, f1 as abstime, cast(f1 as timestamp) AS "timestamp"
2135 FROM ABSTIME_TBL WHERE NOT isfinite(f1);
2136 ERROR: cannot convert abstime "invalid" to timestamp
2137 SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime
2138 FROM INTERVAL_TBL;
2139 ten | interval | reltime
2140 -----+-------------------------------+-------------------------------
2141 | @ 1 min | @ 1 min
2142 | @ 5 hours | @ 5 hours
2143 | @ 10 days | @ 10 days
2144 | @ 34 years | @ 34 years
2145 | @ 3 mons | @ 3 mons
2146 | @ 14 secs ago | @ 14 secs ago
2147 | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs
2148 | @ 6 years | @ 6 years
2149 | @ 5 mons | @ 5 mons
2150 | @ 5 mons 12 hours | @ 5 mons 12 hours
2151 (10 rows)
2153 SELECT '' AS six, f1 as reltime, CAST(f1 AS interval) AS interval
2154 FROM RELTIME_TBL;
2155 six | reltime | interval
2156 -----+---------------+---------------
2157 | @ 1 min | @ 1 min
2158 | @ 5 hours | @ 5 hours
2159 | @ 10 days | @ 10 days
2160 | @ 34 years | @ 34 years
2161 | @ 3 mons | @ 3 mons
2162 | @ 14 secs ago | @ 14 secs ago
2163 (6 rows)
2165 DROP TABLE TEMP_TIMESTAMP;
2166 --
2167 -- Formats
2168 --
2169 SET DateStyle TO 'US,Postgres';
2170 SHOW DateStyle;
2171 DateStyle
2172 ---------------
2173 Postgres, MDY
2174 (1 row)
2176 SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
2177 64 | us_postgres
2178 ----+-----------------------------
2179 | -infinity
2180 | infinity
2181 | Thu Jan 01 00:00:00 1970
2182 | Mon Feb 10 17:32:01 1997
2183 | Mon Feb 10 17:32:01 1997
2184 | Mon Feb 10 17:32:02 1997
2185 | Mon Feb 10 17:32:01.4 1997
2186 | Mon Feb 10 17:32:01.5 1997
2187 | Mon Feb 10 17:32:01.6 1997
2188 | Thu Jan 02 00:00:00 1997
2189 | Thu Jan 02 03:04:05 1997
2190 | Mon Feb 10 17:32:01 1997
2191 | Mon Feb 10 17:32:01 1997
2192 | Mon Feb 10 17:32:01 1997
2193 | Mon Feb 10 17:32:01 1997
2194 | Tue Jun 10 17:32:01 1997
2195 | Sat Sep 22 18:19:20 2001
2196 | Wed Mar 15 08:14:01 2000
2197 | Wed Mar 15 13:14:02 2000
2198 | Wed Mar 15 12:14:03 2000
2199 | Wed Mar 15 03:14:04 2000
2200 | Wed Mar 15 02:14:05 2000
2201 | Mon Feb 10 17:32:01 1997
2202 | Mon Feb 10 17:32:01 1997
2203 | Mon Feb 10 17:32:00 1997
2204 | Mon Feb 10 17:32:01 1997
2205 | Mon Feb 10 17:32:01 1997
2206 | Mon Feb 10 17:32:01 1997
2207 | Mon Feb 10 17:32:01 1997
2208 | Mon Feb 10 17:32:01 1997
2209 | Mon Feb 10 17:32:01 1997
2210 | Mon Feb 10 17:32:01 1997
2211 | Mon Feb 10 17:32:01 1997
2212 | Mon Feb 10 17:32:01 1997
2213 | Tue Jun 10 18:32:01 1997
2214 | Mon Feb 10 17:32:01 1997
2215 | Tue Feb 11 17:32:01 1997
2216 | Wed Feb 12 17:32:01 1997
2217 | Thu Feb 13 17:32:01 1997
2218 | Fri Feb 14 17:32:01 1997
2219 | Sat Feb 15 17:32:01 1997
2220 | Sun Feb 16 17:32:01 1997
2221 | Tue Feb 16 17:32:01 0097 BC
2222 | Sat Feb 16 17:32:01 0097
2223 | Thu Feb 16 17:32:01 0597
2224 | Tue Feb 16 17:32:01 1097
2225 | Sat Feb 16 17:32:01 1697
2226 | Thu Feb 16 17:32:01 1797
2227 | Tue Feb 16 17:32:01 1897
2228 | Sun Feb 16 17:32:01 1997
2229 | Sat Feb 16 17:32:01 2097
2230 | Wed Feb 28 17:32:01 1996
2231 | Thu Feb 29 17:32:01 1996
2232 | Fri Mar 01 17:32:01 1996
2233 | Mon Dec 30 17:32:01 1996
2234 | Tue Dec 31 17:32:01 1996
2235 | Wed Jan 01 17:32:01 1997
2236 | Fri Feb 28 17:32:01 1997
2237 | Sat Mar 01 17:32:01 1997
2238 | Tue Dec 30 17:32:01 1997
2239 | Wed Dec 31 17:32:01 1997
2240 | Fri Dec 31 17:32:01 1999
2241 | Sat Jan 01 17:32:01 2000
2242 | Sun Dec 31 17:32:01 2000
2243 | Mon Jan 01 17:32:01 2001
2244 (65 rows)
2246 SELECT '' AS seven, f1 AS us_postgres FROM ABSTIME_TBL;
2247 seven | us_postgres
2248 -------+------------------------------
2249 | Sun Jan 14 03:14:21 1973 PST
2250 | Mon May 01 00:30:30 1995 PDT
2251 | Wed Dec 31 16:00:00 1969 PST
2252 | infinity
2253 | -infinity
2254 | Sat May 10 23:59:12 1947 PST
2255 | invalid
2256 (7 rows)
2258 SET DateStyle TO 'US,ISO';
2259 SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL;
2260 64 | us_iso
2261 ----+------------------------
2262 | -infinity
2263 | infinity
2264 | 1970-01-01 00:00:00
2265 | 1997-02-10 17:32:01
2266 | 1997-02-10 17:32:01
2267 | 1997-02-10 17:32:02
2268 | 1997-02-10 17:32:01.4
2269 | 1997-02-10 17:32:01.5
2270 | 1997-02-10 17:32:01.6
2271 | 1997-01-02 00:00:00
2272 | 1997-01-02 03:04:05
2273 | 1997-02-10 17:32:01
2274 | 1997-02-10 17:32:01
2275 | 1997-02-10 17:32:01
2276 | 1997-02-10 17:32:01
2277 | 1997-06-10 17:32:01
2278 | 2001-09-22 18:19:20
2279 | 2000-03-15 08:14:01
2280 | 2000-03-15 13:14:02
2281 | 2000-03-15 12:14:03
2282 | 2000-03-15 03:14:04
2283 | 2000-03-15 02:14:05
2284 | 1997-02-10 17:32:01
2285 | 1997-02-10 17:32:01
2286 | 1997-02-10 17:32:00
2287 | 1997-02-10 17:32:01
2288 | 1997-02-10 17:32:01
2289 | 1997-02-10 17:32:01
2290 | 1997-02-10 17:32:01
2291 | 1997-02-10 17:32:01
2292 | 1997-02-10 17:32:01
2293 | 1997-02-10 17:32:01
2294 | 1997-02-10 17:32:01
2295 | 1997-02-10 17:32:01
2296 | 1997-06-10 18:32:01
2297 | 1997-02-10 17:32:01
2298 | 1997-02-11 17:32:01
2299 | 1997-02-12 17:32:01
2300 | 1997-02-13 17:32:01
2301 | 1997-02-14 17:32:01
2302 | 1997-02-15 17:32:01
2303 | 1997-02-16 17:32:01
2304 | 0097-02-16 17:32:01 BC
2305 | 0097-02-16 17:32:01
2306 | 0597-02-16 17:32:01
2307 | 1097-02-16 17:32:01
2308 | 1697-02-16 17:32:01
2309 | 1797-02-16 17:32:01
2310 | 1897-02-16 17:32:01
2311 | 1997-02-16 17:32:01
2312 | 2097-02-16 17:32:01
2313 | 1996-02-28 17:32:01
2314 | 1996-02-29 17:32:01
2315 | 1996-03-01 17:32:01
2316 | 1996-12-30 17:32:01
2317 | 1996-12-31 17:32:01
2318 | 1997-01-01 17:32:01
2319 | 1997-02-28 17:32:01
2320 | 1997-03-01 17:32:01
2321 | 1997-12-30 17:32:01
2322 | 1997-12-31 17:32:01
2323 | 1999-12-31 17:32:01
2324 | 2000-01-01 17:32:01
2325 | 2000-12-31 17:32:01
2326 | 2001-01-01 17:32:01
2327 (65 rows)
2329 SELECT '' AS seven, f1 AS us_iso FROM ABSTIME_TBL;
2330 seven | us_iso
2331 -------+------------------------
2332 | 1973-01-14 03:14:21-08
2333 | 1995-05-01 00:30:30-07
2334 | 1969-12-31 16:00:00-08
2335 | infinity
2336 | -infinity
2337 | 1947-05-10 23:59:12-08
2338 | invalid
2339 (7 rows)
2341 SET DateStyle TO 'US,SQL';
2342 SHOW DateStyle;
2343 DateStyle
2344 -----------
2345 SQL, MDY
2346 (1 row)
2348 SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
2349 64 | us_sql
2350 ----+------------------------
2351 | -infinity
2352 | infinity
2353 | 01/01/1970 00:00:00
2354 | 02/10/1997 17:32:01
2355 | 02/10/1997 17:32:01
2356 | 02/10/1997 17:32:02
2357 | 02/10/1997 17:32:01.4
2358 | 02/10/1997 17:32:01.5
2359 | 02/10/1997 17:32:01.6
2360 | 01/02/1997 00:00:00
2361 | 01/02/1997 03:04:05
2362 | 02/10/1997 17:32:01
2363 | 02/10/1997 17:32:01
2364 | 02/10/1997 17:32:01
2365 | 02/10/1997 17:32:01
2366 | 06/10/1997 17:32:01
2367 | 09/22/2001 18:19:20
2368 | 03/15/2000 08:14:01
2369 | 03/15/2000 13:14:02
2370 | 03/15/2000 12:14:03
2371 | 03/15/2000 03:14:04
2372 | 03/15/2000 02:14:05
2373 | 02/10/1997 17:32:01
2374 | 02/10/1997 17:32:01
2375 | 02/10/1997 17:32:00
2376 | 02/10/1997 17:32:01
2377 | 02/10/1997 17:32:01
2378 | 02/10/1997 17:32:01
2379 | 02/10/1997 17:32:01
2380 | 02/10/1997 17:32:01
2381 | 02/10/1997 17:32:01
2382 | 02/10/1997 17:32:01
2383 | 02/10/1997 17:32:01
2384 | 02/10/1997 17:32:01
2385 | 06/10/1997 18:32:01
2386 | 02/10/1997 17:32:01
2387 | 02/11/1997 17:32:01
2388 | 02/12/1997 17:32:01
2389 | 02/13/1997 17:32:01
2390 | 02/14/1997 17:32:01
2391 | 02/15/1997 17:32:01
2392 | 02/16/1997 17:32:01
2393 | 02/16/0097 17:32:01 BC
2394 | 02/16/0097 17:32:01
2395 | 02/16/0597 17:32:01
2396 | 02/16/1097 17:32:01
2397 | 02/16/1697 17:32:01
2398 | 02/16/1797 17:32:01
2399 | 02/16/1897 17:32:01
2400 | 02/16/1997 17:32:01
2401 | 02/16/2097 17:32:01
2402 | 02/28/1996 17:32:01
2403 | 02/29/1996 17:32:01
2404 | 03/01/1996 17:32:01
2405 | 12/30/1996 17:32:01
2406 | 12/31/1996 17:32:01
2407 | 01/01/1997 17:32:01
2408 | 02/28/1997 17:32:01
2409 | 03/01/1997 17:32:01
2410 | 12/30/1997 17:32:01
2411 | 12/31/1997 17:32:01
2412 | 12/31/1999 17:32:01
2413 | 01/01/2000 17:32:01
2414 | 12/31/2000 17:32:01
2415 | 01/01/2001 17:32:01
2416 (65 rows)
2418 SELECT '' AS seven, f1 AS us_sql FROM ABSTIME_TBL;
2419 seven | us_sql
2420 -------+-------------------------
2421 | 01/14/1973 03:14:21 PST
2422 | 05/01/1995 00:30:30 PDT
2423 | 12/31/1969 16:00:00 PST
2424 | infinity
2425 | -infinity
2426 | 05/10/1947 23:59:12 PST
2427 | invalid
2428 (7 rows)
2430 SET DateStyle TO 'European,Postgres';
2431 SHOW DateStyle;
2432 DateStyle
2433 ---------------
2434 Postgres, DMY
2435 (1 row)
2437 INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
2438 SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957';
2439 one
2440 -----
2441 1
2442 (1 row)
2444 SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL;
2445 65 | european_postgres
2446 ----+-----------------------------
2447 | -infinity
2448 | infinity
2449 | Thu 01 Jan 00:00:00 1970
2450 | Mon 10 Feb 17:32:01 1997
2451 | Mon 10 Feb 17:32:01 1997
2452 | Mon 10 Feb 17:32:02 1997
2453 | Mon 10 Feb 17:32:01.4 1997
2454 | Mon 10 Feb 17:32:01.5 1997
2455 | Mon 10 Feb 17:32:01.6 1997
2456 | Thu 02 Jan 00:00:00 1997
2457 | Thu 02 Jan 03:04:05 1997
2458 | Mon 10 Feb 17:32:01 1997
2459 | Mon 10 Feb 17:32:01 1997
2460 | Mon 10 Feb 17:32:01 1997
2461 | Mon 10 Feb 17:32:01 1997
2462 | Tue 10 Jun 17:32:01 1997
2463 | Sat 22 Sep 18:19:20 2001
2464 | Wed 15 Mar 08:14:01 2000
2465 | Wed 15 Mar 13:14:02 2000
2466 | Wed 15 Mar 12:14:03 2000
2467 | Wed 15 Mar 03:14:04 2000
2468 | Wed 15 Mar 02:14:05 2000
2469 | Mon 10 Feb 17:32:01 1997
2470 | Mon 10 Feb 17:32:01 1997
2471 | Mon 10 Feb 17:32:00 1997
2472 | Mon 10 Feb 17:32:01 1997
2473 | Mon 10 Feb 17:32:01 1997
2474 | Mon 10 Feb 17:32:01 1997
2475 | Mon 10 Feb 17:32:01 1997
2476 | Mon 10 Feb 17:32:01 1997
2477 | Mon 10 Feb 17:32:01 1997
2478 | Mon 10 Feb 17:32:01 1997
2479 | Mon 10 Feb 17:32:01 1997
2480 | Mon 10 Feb 17:32:01 1997
2481 | Tue 10 Jun 18:32:01 1997
2482 | Mon 10 Feb 17:32:01 1997
2483 | Tue 11 Feb 17:32:01 1997
2484 | Wed 12 Feb 17:32:01 1997
2485 | Thu 13 Feb 17:32:01 1997
2486 | Fri 14 Feb 17:32:01 1997
2487 | Sat 15 Feb 17:32:01 1997
2488 | Sun 16 Feb 17:32:01 1997
2489 | Tue 16 Feb 17:32:01 0097 BC
2490 | Sat 16 Feb 17:32:01 0097
2491 | Thu 16 Feb 17:32:01 0597
2492 | Tue 16 Feb 17:32:01 1097
2493 | Sat 16 Feb 17:32:01 1697
2494 | Thu 16 Feb 17:32:01 1797
2495 | Tue 16 Feb 17:32:01 1897
2496 | Sun 16 Feb 17:32:01 1997
2497 | Sat 16 Feb 17:32:01 2097
2498 | Wed 28 Feb 17:32:01 1996
2499 | Thu 29 Feb 17:32:01 1996
2500 | Fri 01 Mar 17:32:01 1996
2501 | Mon 30 Dec 17:32:01 1996
2502 | Tue 31 Dec 17:32:01 1996
2503 | Wed 01 Jan 17:32:01 1997
2504 | Fri 28 Feb 17:32:01 1997
2505 | Sat 01 Mar 17:32:01 1997
2506 | Tue 30 Dec 17:32:01 1997
2507 | Wed 31 Dec 17:32:01 1997
2508 | Fri 31 Dec 17:32:01 1999
2509 | Sat 01 Jan 17:32:01 2000
2510 | Sun 31 Dec 17:32:01 2000
2511 | Mon 01 Jan 17:32:01 2001
2512 | Thu 13 Jun 00:00:00 1957
2513 (66 rows)
2515 SELECT '' AS seven, f1 AS european_postgres FROM ABSTIME_TBL;
2516 seven | european_postgres
2517 -------+------------------------------
2518 | Sun 14 Jan 03:14:21 1973 PST
2519 | Mon 01 May 00:30:30 1995 PDT
2520 | Wed 31 Dec 16:00:00 1969 PST
2521 | infinity
2522 | -infinity
2523 | Sat 10 May 23:59:12 1947 PST
2524 | invalid
2525 (7 rows)
2527 SET DateStyle TO 'European,ISO';
2528 SHOW DateStyle;
2529 DateStyle
2530 -----------
2531 ISO, DMY
2532 (1 row)
2534 SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
2535 65 | european_iso
2536 ----+------------------------
2537 | -infinity
2538 | infinity
2539 | 1970-01-01 00:00:00
2540 | 1997-02-10 17:32:01
2541 | 1997-02-10 17:32:01
2542 | 1997-02-10 17:32:02
2543 | 1997-02-10 17:32:01.4
2544 | 1997-02-10 17:32:01.5
2545 | 1997-02-10 17:32:01.6
2546 | 1997-01-02 00:00:00
2547 | 1997-01-02 03:04:05
2548 | 1997-02-10 17:32:01
2549 | 1997-02-10 17:32:01
2550 | 1997-02-10 17:32:01
2551 | 1997-02-10 17:32:01
2552 | 1997-06-10 17:32:01
2553 | 2001-09-22 18:19:20
2554 | 2000-03-15 08:14:01
2555 | 2000-03-15 13:14:02
2556 | 2000-03-15 12:14:03
2557 | 2000-03-15 03:14:04
2558 | 2000-03-15 02:14:05
2559 | 1997-02-10 17:32:01
2560 | 1997-02-10 17:32:01
2561 | 1997-02-10 17:32:00
2562 | 1997-02-10 17:32:01
2563 | 1997-02-10 17:32:01
2564 | 1997-02-10 17:32:01
2565 | 1997-02-10 17:32:01
2566 | 1997-02-10 17:32:01
2567 | 1997-02-10 17:32:01
2568 | 1997-02-10 17:32:01
2569 | 1997-02-10 17:32:01
2570 | 1997-02-10 17:32:01
2571 | 1997-06-10 18:32:01
2572 | 1997-02-10 17:32:01
2573 | 1997-02-11 17:32:01
2574 | 1997-02-12 17:32:01
2575 | 1997-02-13 17:32:01
2576 | 1997-02-14 17:32:01
2577 | 1997-02-15 17:32:01
2578 | 1997-02-16 17:32:01
2579 | 0097-02-16 17:32:01 BC
2580 | 0097-02-16 17:32:01
2581 | 0597-02-16 17:32:01
2582 | 1097-02-16 17:32:01
2583 | 1697-02-16 17:32:01
2584 | 1797-02-16 17:32:01
2585 | 1897-02-16 17:32:01
2586 | 1997-02-16 17:32:01
2587 | 2097-02-16 17:32:01
2588 | 1996-02-28 17:32:01
2589 | 1996-02-29 17:32:01
2590 | 1996-03-01 17:32:01
2591 | 1996-12-30 17:32:01
2592 | 1996-12-31 17:32:01
2593 | 1997-01-01 17:32:01
2594 | 1997-02-28 17:32:01
2595 | 1997-03-01 17:32:01
2596 | 1997-12-30 17:32:01
2597 | 1997-12-31 17:32:01
2598 | 1999-12-31 17:32:01
2599 | 2000-01-01 17:32:01
2600 | 2000-12-31 17:32:01
2601 | 2001-01-01 17:32:01
2602 | 1957-06-13 00:00:00
2603 (66 rows)
2605 SELECT '' AS seven, f1 AS european_iso FROM ABSTIME_TBL;
2606 seven | european_iso
2607 -------+------------------------
2608 | 1973-01-14 03:14:21-08
2609 | 1995-05-01 00:30:30-07
2610 | 1969-12-31 16:00:00-08
2611 | infinity
2612 | -infinity
2613 | 1947-05-10 23:59:12-08
2614 | invalid
2615 (7 rows)
2617 SET DateStyle TO 'European,SQL';
2618 SHOW DateStyle;
2619 DateStyle
2620 -----------
2621 SQL, DMY
2622 (1 row)
2624 SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
2625 65 | european_sql
2626 ----+------------------------
2627 | -infinity
2628 | infinity
2629 | 01/01/1970 00:00:00
2630 | 10/02/1997 17:32:01
2631 | 10/02/1997 17:32:01
2632 | 10/02/1997 17:32:02
2633 | 10/02/1997 17:32:01.4
2634 | 10/02/1997 17:32:01.5
2635 | 10/02/1997 17:32:01.6
2636 | 02/01/1997 00:00:00
2637 | 02/01/1997 03:04:05
2638 | 10/02/1997 17:32:01
2639 | 10/02/1997 17:32:01
2640 | 10/02/1997 17:32:01
2641 | 10/02/1997 17:32:01
2642 | 10/06/1997 17:32:01
2643 | 22/09/2001 18:19:20
2644 | 15/03/2000 08:14:01
2645 | 15/03/2000 13:14:02
2646 | 15/03/2000 12:14:03
2647 | 15/03/2000 03:14:04
2648 | 15/03/2000 02:14:05
2649 | 10/02/1997 17:32:01
2650 | 10/02/1997 17:32:01
2651 | 10/02/1997 17:32:00
2652 | 10/02/1997 17:32:01
2653 | 10/02/1997 17:32:01
2654 | 10/02/1997 17:32:01
2655 | 10/02/1997 17:32:01
2656 | 10/02/1997 17:32:01
2657 | 10/02/1997 17:32:01
2658 | 10/02/1997 17:32:01
2659 | 10/02/1997 17:32:01
2660 | 10/02/1997 17:32:01
2661 | 10/06/1997 18:32:01
2662 | 10/02/1997 17:32:01
2663 | 11/02/1997 17:32:01
2664 | 12/02/1997 17:32:01
2665 | 13/02/1997 17:32:01
2666 | 14/02/1997 17:32:01
2667 | 15/02/1997 17:32:01
2668 | 16/02/1997 17:32:01
2669 | 16/02/0097 17:32:01 BC
2670 | 16/02/0097 17:32:01
2671 | 16/02/0597 17:32:01
2672 | 16/02/1097 17:32:01
2673 | 16/02/1697 17:32:01
2674 | 16/02/1797 17:32:01
2675 | 16/02/1897 17:32:01
2676 | 16/02/1997 17:32:01
2677 | 16/02/2097 17:32:01
2678 | 28/02/1996 17:32:01
2679 | 29/02/1996 17:32:01
2680 | 01/03/1996 17:32:01
2681 | 30/12/1996 17:32:01
2682 | 31/12/1996 17:32:01
2683 | 01/01/1997 17:32:01
2684 | 28/02/1997 17:32:01
2685 | 01/03/1997 17:32:01
2686 | 30/12/1997 17:32:01
2687 | 31/12/1997 17:32:01
2688 | 31/12/1999 17:32:01
2689 | 01/01/2000 17:32:01
2690 | 31/12/2000 17:32:01
2691 | 01/01/2001 17:32:01
2692 | 13/06/1957 00:00:00
2693 (66 rows)
2695 SELECT '' AS seven, f1 AS european_sql FROM ABSTIME_TBL;
2696 seven | european_sql
2697 -------+-------------------------
2698 | 14/01/1973 03:14:21 PST
2699 | 01/05/1995 00:30:30 PDT
2700 | 31/12/1969 16:00:00 PST
2701 | infinity
2702 | -infinity
2703 | 10/05/1947 23:59:12 PST
2704 | invalid
2705 (7 rows)
2707 RESET DateStyle;
2708 --
2709 -- to_timestamp()
2710 --
2711 SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
2712 to_timestamp
2713 ------------------------------
2714 Sat Feb 16 08:14:30 0097 PST
2715 (1 row)
2717 SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
2718 to_timestamp
2719 ------------------------------
2720 Sat Feb 16 08:14:30 0097 PST
2721 (1 row)
2723 SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
2724 to_timestamp
2725 ------------------------------
2726 Sat Jan 12 00:00:00 1985 PST
2727 (1 row)
2729 SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
2730 '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
2731 to_timestamp
2732 ------------------------------
2733 Sun May 16 00:00:00 1976 PDT
2734 (1 row)
2736 SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
2737 to_timestamp
2738 ------------------------------
2739 Sat Aug 21 00:00:00 1582 PST
2740 (1 row)
2742 SELECT to_timestamp('15 "text between quote marks" 98 54 45',
2743 E'HH24 "\\text between quote marks\\"" YY MI SS');
2744 to_timestamp
2745 ------------------------------
2746 Thu Jan 01 15:54:45 1998 PST
2747 (1 row)
2749 SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
2750 to_timestamp
2751 ------------------------------
2752 Fri May 12 14:45:48 2000 PDT
2753 (1 row)
2755 SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
2756 to_timestamp
2757 ------------------------------
2758 Sun Jan 09 00:00:00 2000 PST
2759 (1 row)
2761 SELECT to_timestamp('97/Feb/16', 'YYMonDD');
2762 ERROR: invalid value "/Fe" for "Mon"
2763 DETAIL: The given value did not match any of the allowed values for this field.
2764 SELECT to_timestamp('19971116', 'YYYYMMDD');
2765 to_timestamp
2766 ------------------------------
2767 Sun Nov 16 00:00:00 1997 PST
2768 (1 row)
2770 SELECT to_timestamp('20000-1116', 'YYYY-MMDD');
2771 to_timestamp
2772 -------------------------------
2773 Thu Nov 16 00:00:00 20000 PST
2774 (1 row)
2776 SELECT to_timestamp('9-1116', 'Y-MMDD');
2777 to_timestamp
2778 ------------------------------
2779 Mon Nov 16 00:00:00 2009 PST
2780 (1 row)
2782 SELECT to_timestamp('95-1116', 'YY-MMDD');
2783 to_timestamp
2784 ------------------------------
2785 Thu Nov 16 00:00:00 1995 PST
2786 (1 row)
2788 SELECT to_timestamp('995-1116', 'YYY-MMDD');
2789 to_timestamp
2790 ------------------------------
2791 Thu Nov 16 00:00:00 1995 PST
2792 (1 row)
2794 SELECT to_timestamp('2005426', 'YYYYWWD');
2795 to_timestamp
2796 ------------------------------
2797 Sat Oct 15 00:00:00 2005 PDT
2798 (1 row)
2800 SELECT to_timestamp('2005300', 'YYYYDDD');
2801 to_timestamp
2802 ------------------------------
2803 Thu Oct 27 00:00:00 2005 PDT
2804 (1 row)
2806 SELECT to_timestamp('2005527', 'IYYYIWID');
2807 to_timestamp
2808 ------------------------------
2809 Sun Jan 01 00:00:00 2006 PST
2810 (1 row)
2812 SELECT to_timestamp('005527', 'IYYIWID');
2813 to_timestamp
2814 ------------------------------
2815 Sun Jan 01 00:00:00 2006 PST
2816 (1 row)
2818 SELECT to_timestamp('05527', 'IYIWID');
2819 to_timestamp
2820 ------------------------------
2821 Sun Jan 01 00:00:00 2006 PST
2822 (1 row)
2824 SELECT to_timestamp('5527', 'IIWID');
2825 to_timestamp
2826 ------------------------------
2827 Sun Jan 01 00:00:00 2006 PST
2828 (1 row)
2830 SELECT to_timestamp('2005364', 'IYYYIDDD');
2831 to_timestamp
2832 ------------------------------
2833 Sun Jan 01 00:00:00 2006 PST
2834 (1 row)
2836 SELECT to_timestamp('20050302', 'YYYYMMDD');
2837 to_timestamp
2838 ------------------------------
2839 Wed Mar 02 00:00:00 2005 PST
2840 (1 row)
2842 SELECT to_timestamp('2005 03 02', 'YYYYMMDD');
2843 to_timestamp
2844 ------------------------------
2845 Wed Mar 02 00:00:00 2005 PST
2846 (1 row)
2848 SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD');
2849 to_timestamp
2850 ------------------------------
2851 Wed Mar 02 00:00:00 2005 PST
2852 (1 row)
2854 SELECT to_timestamp(' 20050302', 'YYYYMMDD');
2855 to_timestamp
2856 ------------------------------
2857 Wed Mar 02 00:00:00 2005 PST
2858 (1 row)
2860 --
2861 -- Check errors for some incorrect usages of to_timestamp()
2862 --
2863 -- Mixture of date conventions (ISO week and Gregorian):
2864 SELECT to_timestamp('2005527', 'YYYYIWID');
2865 ERROR: invalid combination of date conventions
2866 HINT: Do not mix Gregorian and ISO week date conventions in a formatting template.
2867 -- Insufficient characters in the source string:
2868 SELECT to_timestamp('19971', 'YYYYMMDD');
2869 ERROR: source string too short for "MM" formatting field
2870 DETAIL: Field requires 2 characters, but only 1 remain.
2871 HINT: If your source string is not fixed-width, try using the "FM" modifier.
2872 -- Insufficient digit characters for a single node:
2873 SELECT to_timestamp('19971)24', 'YYYYMMDD');
2874 ERROR: invalid value "1)" for "MM"
2875 DETAIL: Field requires 2 characters, but only 1 could be parsed.
2876 HINT: If your source string is not fixed-width, try using the "FM" modifier.
2877 -- Value clobbering:
2878 SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD');
2879 ERROR: conflicting values for "Mon" field in formatting string
2880 DETAIL: This value contradicts a previous setting for the same field type.
2881 -- Non-numeric input:
2882 SELECT to_timestamp('199711xy', 'YYYYMMDD');
2883 ERROR: invalid value "xy" for "DD"
2884 DETAIL: Value must be an integer.
2885 -- Input that doesn't fit in an int:
2886 SELECT to_timestamp('10000000000', 'FMYYYY');
2887 ERROR: value for "YYYY" in source string is out of range
2888 DETAIL: Value must be in the range -2147483648 to 2147483647.