1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
|
--
-- HOROLOGY
--
--
-- timestamp, interval arithmetic
--
SELECT timestamp '1996-03-01' - interval '1 second' AS "Feb 29";
Feb 29
------------------------------
Thu Feb 29 23:59:59 1996 PST
(1 row)
SELECT timestamp '1999-03-01' - interval '1 second' AS "Feb 28";
Feb 28
------------------------------
Sun Feb 28 23:59:59 1999 PST
(1 row)
SELECT timestamp '2000-03-01' - interval '1 second' AS "Feb 29";
Feb 29
------------------------------
Tue Feb 29 23:59:59 2000 PST
(1 row)
SELECT timestamp '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
Dec 31
------------------------------
Fri Dec 31 23:59:59 1999 PST
(1 row)
CREATE TABLE TEMP_TIMESTAMP (f1 timestamp);
-- get some candidate input values
INSERT INTO TEMP_TIMESTAMP (f1)
SELECT d1 FROM TIMESTAMP_TBL
WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
SELECT '' AS "11", f1 AS timestamp
FROM TEMP_TIMESTAMP
ORDER BY timestamp;
11 | timestamp
----+------------------------------
| epoch
| Wed Feb 28 17:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST
| Fri Dec 31 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST
| Tue Feb 15 23:14:30 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
(11 rows)
SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 + t.f1 AS plus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
ORDER BY plus, timestamp, interval;
110 | timestamp | interval | plus
-----+------------------------------+-------------------------------+------------------------------
| epoch | @ 14 secs ago | Wed Dec 31 15:59:46 1969 PST
| epoch | @ 1 min | Wed Dec 31 16:01:00 1969 PST
| epoch | @ 5 hours | Wed Dec 31 21:00:00 1969 PST
| epoch | @ 1 day 2 hours 3 mins 4 secs | Thu Jan 01 18:03:04 1970 PST
| epoch | @ 10 days | Sat Jan 10 16:00:00 1970 PST
| epoch | @ 3 mons | Tue Mar 31 16:00:00 1970 PST
| epoch | @ 5 mons | Sun May 31 17:00:00 1970 PDT
| epoch | @ 5 mons 12 hours | Mon Jun 01 05:00:00 1970 PDT
| epoch | @ 6 years | Wed Dec 31 16:00:00 1975 PST
| Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 18:32:01 1996 PDT
| Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 18:32:01 1996 PDT
| Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 18:32:01 1996 PDT
| Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 18:32:01 1996 PDT
| Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 06:32:01 1996 PDT
| Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 18:32:01 1996 PDT
| Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
| Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 18:32:01 1996 PDT
| Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 06:32:01 1996 PDT
| Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST
| Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST
| Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST
| Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST
| Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST
| Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 18:32:01 1997 PDT
| Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 06:32:01 1997 PDT
| Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 18:32:01 1997 PDT
| Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 06:32:01 1997 PDT
| Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST
| Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST
| Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 14 secs ago | Tue Feb 15 23:14:16 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 1 min | Tue Feb 15 23:15:30 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 5 hours | Wed Feb 16 04:14:30 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 17 01:17:34 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 10 days | Fri Feb 25 23:14:30 2000 PST
| Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 3 mons | Tue May 16 00:14:30 2000 PDT
| Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 18:32:01 2000 PDT
| Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 06:32:01 2000 PDT
| Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 18:32:01 2000 PDT
| Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 06:32:01 2000 PDT
| Tue Feb 15 23:14:30 2000 PST | @ 5 mons | Sun Jul 16 00:14:30 2000 PDT
| Tue Feb 15 23:14:30 2000 PST | @ 5 mons 12 hours | Sun Jul 16 12:14:30 2000 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST
| Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST
| Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST
| Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 18:32:01 2001 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 18:32:01 2001 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 06:32:01 2001 PDT
| Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 18:32:01 2001 PDT
| Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 06:32:01 2001 PDT
| Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
| Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
| Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST
| Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST
| Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST
| epoch | @ 34 years | Wed Dec 31 16:00:00 2003 PST
| Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST
| Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST
| Tue Feb 15 23:14:30 2000 PST | @ 6 years | Wed Feb 15 23:14:30 2006 PST
| Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST
| Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST
| Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
| Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
| Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST
| Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST
| Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST
| Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST
| Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST
| Tue Feb 15 23:14:30 2000 PST | @ 34 years | Wed Feb 15 23:14:30 2034 PST
| Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST
| Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST
(110 rows)
SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 - t.f1 AS minus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
WHERE isfinite(d.f1)
ORDER BY minus, timestamp, interval;
110 | timestamp | interval | minus
-----+------------------------------+-------------------------------+------------------------------
| epoch | @ 34 years | Tue Dec 31 16:00:00 1935 PST
| Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
| Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
| Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST
| Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST
| Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST
| epoch | @ 6 years | Tue Dec 31 16:00:00 1963 PST
| Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST
| Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST
| Tue Feb 15 23:14:30 2000 PST | @ 34 years | Tue Feb 15 23:14:30 1966 PST
| Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST
| Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST
| epoch | @ 5 mons 12 hours | Thu Jul 31 05:00:00 1969 PDT
| epoch | @ 5 mons | Thu Jul 31 17:00:00 1969 PDT
| epoch | @ 3 mons | Tue Sep 30 17:00:00 1969 PDT
| epoch | @ 10 days | Sun Dec 21 16:00:00 1969 PST
| epoch | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST
| epoch | @ 5 hours | Wed Dec 31 11:00:00 1969 PST
| epoch | @ 1 min | Wed Dec 31 15:59:00 1969 PST
| epoch | @ 14 secs ago | Wed Dec 31 16:00:14 1969 PST
| Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
| Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
| Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST
| Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST
| Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST
| Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST
| Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST
| Tue Feb 15 23:14:30 2000 PST | @ 6 years | Tue Feb 15 23:14:30 1994 PST
| Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST
| Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST
| Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 06:32:01 1995 PDT
| Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 18:32:01 1995 PDT
| Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 06:32:01 1995 PDT
| Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 18:32:01 1995 PDT
| Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 06:32:01 1995 PDT
| Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 18:32:01 1995 PDT
| Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST
| Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST
| Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST
| Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST
| Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
| Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 18:32:01 1996 PDT
| Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 06:32:01 1996 PDT
| Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 18:32:01 1996 PDT
| Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
| Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
| Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST
| Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 06:32:01 1999 PDT
| Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 18:32:01 1999 PDT
| Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 06:32:01 1999 PDT
| Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 18:32:01 1999 PDT
| Tue Feb 15 23:14:30 2000 PST | @ 5 mons 12 hours | Wed Sep 15 12:14:30 1999 PDT
| Tue Feb 15 23:14:30 2000 PST | @ 5 mons | Thu Sep 16 00:14:30 1999 PDT
| Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 18:32:01 1999 PDT
| Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 18:32:01 1999 PDT
| Tue Feb 15 23:14:30 2000 PST | @ 3 mons | Mon Nov 15 23:14:30 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST
| Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST
| Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST
| Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 10 days | Sat Feb 05 23:14:30 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Feb 14 21:11:26 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 5 hours | Tue Feb 15 18:14:30 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 1 min | Tue Feb 15 23:13:30 2000 PST
| Tue Feb 15 23:14:30 2000 PST | @ 14 secs ago | Tue Feb 15 23:14:44 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 06:32:01 2000 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 18:32:01 2000 PDT
| Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 06:32:01 2000 PDT
| Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 18:32:01 2000 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 18:32:01 2000 PDT
| Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 18:32:01 2000 PDT
| Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST
| Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST
| Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST
(110 rows)
SELECT '' AS "11", d.f1 AS timestamp, timestamp '1980-01-06 00:00 GMT' AS gpstime_zero,
d.f1 - timestamp '1980-01-06 00:00 GMT' AS difference
FROM TEMP_TIMESTAMP d
ORDER BY difference;
11 | timestamp | gpstime_zero | difference
----+------------------------------+------------------------------+-------------------------------------
| epoch | Sat Jan 05 16:00:00 1980 PST | @ 3657 days ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
| Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec
| Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec
| Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec
| Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec
| Tue Feb 15 23:14:30 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7346 days 7 hours 14 mins 30 secs
| Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec
| Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec
(11 rows)
SELECT '' AS "121", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference
FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
ORDER BY timestamp1, timestamp2, difference;
121 | timestamp1 | timestamp2 | difference
-----+------------------------------+------------------------------+------------------------------------------
| epoch | epoch | @ 0
| epoch | Wed Feb 28 17:32:01 1996 PST | @ 9555 days 1 hour 32 mins 1 sec ago
| epoch | Thu Feb 29 17:32:01 1996 PST | @ 9556 days 1 hour 32 mins 1 sec ago
| epoch | Fri Mar 01 17:32:01 1996 PST | @ 9557 days 1 hour 32 mins 1 sec ago
| epoch | Mon Dec 30 17:32:01 1996 PST | @ 9861 days 1 hour 32 mins 1 sec ago
| epoch | Tue Dec 31 17:32:01 1996 PST | @ 9862 days 1 hour 32 mins 1 sec ago
| epoch | Fri Dec 31 17:32:01 1999 PST | @ 10957 days 1 hour 32 mins 1 sec ago
| epoch | Sat Jan 01 17:32:01 2000 PST | @ 10958 days 1 hour 32 mins 1 sec ago
| epoch | Tue Feb 15 23:14:30 2000 PST | @ 11003 days 7 hours 14 mins 30 secs ago
| epoch | Sun Dec 31 17:32:01 2000 PST | @ 11323 days 1 hour 32 mins 1 sec ago
| epoch | Mon Jan 01 17:32:01 2001 PST | @ 11324 days 1 hour 32 mins 1 sec ago
| Wed Feb 28 17:32:01 1996 PST | epoch | @ 9555 days 1 hour 32 mins 1 sec
| Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
| Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
| Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago
| Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago
| Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
| Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
| Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
| Wed Feb 28 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1448 days 5 hours 42 mins 29 secs ago
| Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
| Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
| Thu Feb 29 17:32:01 1996 PST | epoch | @ 9556 days 1 hour 32 mins 1 sec
| Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
| Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
| Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago
| Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago
| Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
| Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
| Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
| Thu Feb 29 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1447 days 5 hours 42 mins 29 secs ago
| Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
| Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
| Fri Mar 01 17:32:01 1996 PST | epoch | @ 9557 days 1 hour 32 mins 1 sec
| Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
| Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
| Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0
| Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago
| Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
| Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
| Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
| Fri Mar 01 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1446 days 5 hours 42 mins 29 secs ago
| Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
| Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
| Mon Dec 30 17:32:01 1996 PST | epoch | @ 9861 days 1 hour 32 mins 1 sec
| Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
| Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
| Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days
| Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0
| Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
| Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
| Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
| Mon Dec 30 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1142 days 5 hours 42 mins 29 secs ago
| Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
| Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
| Tue Dec 31 17:32:01 1996 PST | epoch | @ 9862 days 1 hour 32 mins 1 sec
| Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
| Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
| Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days
| Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day
| Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
| Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
| Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
| Tue Dec 31 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1141 days 5 hours 42 mins 29 secs ago
| Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
| Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
| Fri Dec 31 17:32:01 1999 PST | epoch | @ 10957 days 1 hour 32 mins 1 sec
| Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
| Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
| Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days
| Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days
| Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
| Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
| Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
| Fri Dec 31 17:32:01 1999 PST | Tue Feb 15 23:14:30 2000 PST | @ 46 days 5 hours 42 mins 29 secs ago
| Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
| Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
| Sat Jan 01 17:32:01 2000 PST | epoch | @ 10958 days 1 hour 32 mins 1 sec
| Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
| Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
| Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days
| Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days
| Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
| Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
| Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
| Sat Jan 01 17:32:01 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 45 days 5 hours 42 mins 29 secs ago
| Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
| Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
| Tue Feb 15 23:14:30 2000 PST | epoch | @ 11003 days 7 hours 14 mins 30 secs
| Tue Feb 15 23:14:30 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1448 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1447 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1446 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1142 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1141 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 46 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 45 days 5 hours 42 mins 29 secs
| Tue Feb 15 23:14:30 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 0
| Tue Feb 15 23:14:30 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 319 days 18 hours 17 mins 31 secs ago
| Tue Feb 15 23:14:30 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 320 days 18 hours 17 mins 31 secs ago
| Sun Dec 31 17:32:01 2000 PST | epoch | @ 11323 days 1 hour 32 mins 1 sec
| Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
| Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
| Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days
| Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days
| Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days
| Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days
| Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days
| Sun Dec 31 17:32:01 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 319 days 18 hours 17 mins 31 secs
| Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
| Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
| Mon Jan 01 17:32:01 2001 PST | epoch | @ 11324 days 1 hour 32 mins 1 sec
| Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
| Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
| Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days
| Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days
| Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days
| Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days
| Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days
| Mon Jan 01 17:32:01 2001 PST | Tue Feb 15 23:14:30 2000 PST | @ 320 days 18 hours 17 mins 31 secs
| Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
| Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
(121 rows)
SELECT '' as fifty, d1 as timestamp,
date_part('year', d1) AS year, date_part('month', d1) AS month,
date_part('day',d1) AS day, date_part('hour', d1) AS hour,
date_part('minute', d1) AS minute, date_part('second', d1) AS second
FROM TIMESTAMP_TBL
WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT'
ORDER BY timestamp;
fifty | timestamp | year | month | day | hour | minute | second
-------+---------------------------------+------+-------+-----+------+--------+----------
| epoch | 1969 | 12 | 31 | 16 | 0 | 0
| Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1
| Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1
| Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1
| Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1
| Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1
| Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1
| Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0
| Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5
| Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
| Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
| Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
| Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
| Mon Feb 10 17:32:01.00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.000001
| Mon Feb 10 17:32:01.40 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4
| Mon Feb 10 17:32:01.50 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5
| Mon Feb 10 17:32:01.60 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6
| Mon Feb 10 17:32:02.00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.999999
| Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1
| Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1
| Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1
| Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1
| Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1
| Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
| Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
| Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1
| Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1
| Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1
| Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1
| Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1
| Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1
| Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1
| Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
| Tue Feb 15 23:14:30 2000 PST | 2000 | 2 | 15 | 23 | 14 | 30
| Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
| Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
| Sat Feb 16 17:32:01 2097 | 2097 | 2 | 16 | 17 | 32 | 1
(50 rows)
--
-- abstime, reltime arithmetic
--
SELECT '' AS four, f1 AS abstime,
date_part('year', f1) AS year, date_part('month', f1) AS month,
date_part('day',f1) AS day, date_part('hour', f1) AS hour,
date_part('minute', f1) AS minute, date_part('second', f1) AS second
FROM ABSTIME_TBL
WHERE isfinite(f1) and f1 <> abstime 'current'
ORDER BY abstime;
four | abstime | year | month | day | hour | minute | second
------+------------------------------+------+-------+-----+------+--------+--------
| Sat May 10 23:59:12 1947 PST | 1947 | 5 | 10 | 23 | 59 | 12
| epoch | 1969 | 12 | 31 | 16 | 0 | 0
| Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 | 3 | 14 | 21
| Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 | 0 | 30 | 30
(4 rows)
--
-- Conversions
--
SELECT '' AS "11", f1 AS timestamp, date( f1) AS date
FROM TEMP_TIMESTAMP
WHERE f1 <> timestamp 'current'
ORDER BY date;
11 | timestamp | date
----+------------------------------+------------
| epoch | 01-01-1970
| Wed Feb 28 17:32:01 1996 PST | 02-28-1996
| Thu Feb 29 17:32:01 1996 PST | 02-29-1996
| Fri Mar 01 17:32:01 1996 PST | 03-01-1996
| Mon Dec 30 17:32:01 1996 PST | 12-30-1996
| Tue Dec 31 17:32:01 1996 PST | 12-31-1996
| Fri Dec 31 17:32:01 1999 PST | 12-31-1999
| Sat Jan 01 17:32:01 2000 PST | 01-01-2000
| Tue Feb 15 23:14:30 2000 PST | 02-15-2000
| Sun Dec 31 17:32:01 2000 PST | 12-31-2000
| Mon Jan 01 17:32:01 2001 PST | 01-01-2001
(11 rows)
SELECT '' AS "11", f1 AS timestamp, abstime( f1) AS abstime
FROM TEMP_TIMESTAMP
ORDER BY abstime;
11 | timestamp | abstime
----+------------------------------+------------------------------
| epoch | epoch
| Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST
| Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST
| Tue Feb 15 23:14:30 2000 PST | Tue Feb 15 23:14:30 2000 PST
| Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST
(11 rows)
SELECT '' AS four, f1 AS abstime, date( f1) AS date
FROM ABSTIME_TBL
WHERE isfinite(f1) AND f1 <> abstime 'current'
ORDER BY date;
four | abstime | date
------+------------------------------+------------
| Sat May 10 23:59:12 1947 PST | 05-10-1947
| epoch | 01-01-1970
| Sun Jan 14 03:14:21 1973 PST | 01-14-1973
| Mon May 01 00:30:30 1995 PDT | 05-01-1995
(4 rows)
SELECT '' AS five, d1 AS timestamp, abstime(d1) AS abstime
FROM TIMESTAMP_TBL WHERE NOT isfinite(d1);
five | timestamp | abstime
------+-----------+-----------
| invalid | invalid
| -infinity | -infinity
| infinity | infinity
| invalid | invalid
| invalid | invalid
(5 rows)
SELECT '' AS three, f1 as abstime, timestamp(f1) AS timestamp
FROM ABSTIME_TBL WHERE NOT isfinite(f1);
three | abstime | timestamp
-------+-----------+-----------
| infinity | infinity
| -infinity | -infinity
| invalid | invalid
(3 rows)
SELECT '' AS ten, f1 AS interval, reltime( f1) AS reltime
FROM INTERVAL_TBL;
ten | interval | reltime
-----+-------------------------------+-------------------------------
| @ 1 min | @ 1 min
| @ 5 hours | @ 5 hours
| @ 10 days | @ 10 days
| @ 34 years | @ 34 years
| @ 3 mons | @ 3 mons
| @ 14 secs ago | @ 14 secs ago
| @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs
| @ 6 years | @ 6 years
| @ 5 mons | @ 5 mons
| @ 5 mons 12 hours | @ 5 mons 12 hours
(10 rows)
SELECT '' AS six, f1 as reltime, interval( f1) AS interval
FROM RELTIME_TBL;
six | reltime | interval
-----+---------------+---------------
| @ 1 min | @ 1 min
| @ 5 hours | @ 5 hours
| @ 10 days | @ 10 days
| @ 34 years | @ 34 years
| @ 3 mons | @ 3 mons
| @ 14 secs ago | @ 14 secs ago
(6 rows)
DROP TABLE TEMP_TIMESTAMP;
--
-- Formats
--
SET DateStyle TO 'US,Postgres';
SHOW DateStyle;
NOTICE: DateStyle is Postgres with US (NonEuropean) conventions
SELECT '' AS sixty_two, d1 AS us_postgres FROM TIMESTAMP_TBL;
sixty_two | us_postgres
-----------+---------------------------------
| invalid
| -infinity
| infinity
| epoch
| Mon Feb 10 17:32:01 1997 PST
| invalid
| invalid
| Mon Feb 10 17:32:01.00 1997 PST
| Mon Feb 10 17:32:02.00 1997 PST
| Mon Feb 10 17:32:01.40 1997 PST
| Mon Feb 10 17:32:01.50 1997 PST
| Mon Feb 10 17:32:01.60 1997 PST
| Thu Jan 02 00:00:00 1997 PST
| Thu Jan 02 03:04:05 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
| Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 09:32:01 1997 PST
| Mon Feb 10 09:32:01 1997 PST
| Mon Feb 10 09:32:01 1997 PST
| Tue Jun 10 18:32:01 1997 PDT
| Mon Feb 10 17:32:01 1997 PST
| Tue Feb 11 17:32:01 1997 PST
| Wed Feb 12 17:32:01 1997 PST
| Thu Feb 13 17:32:01 1997 PST
| Fri Feb 14 17:32:01 1997 PST
| Sat Feb 15 17:32:01 1997 PST
| Sun Feb 16 17:32:01 1997 PST
| Tue Feb 16 17:32 0097 BC
| Sat Feb 16 17:32:01 0097
| Thu Feb 16 17:32:01 0597
| Tue Feb 16 17:32:01 1097
| Sat Feb 16 17:32:01 1697
| Thu Feb 16 17:32:01 1797
| Tue Feb 16 17:32:01 1897
| Sun Feb 16 17:32:01 1997 PST
| Sat Feb 16 17:32:01 2097
| Wed Feb 28 17:32:01 1996 PST
| Thu Feb 29 17:32:01 1996 PST
| Fri Mar 01 17:32:01 1996 PST
| Mon Dec 30 17:32:01 1996 PST
| Tue Dec 31 17:32:01 1996 PST
| Wed Jan 01 17:32:01 1997 PST
| Fri Feb 28 17:32:01 1997 PST
| Sat Mar 01 17:32:01 1997 PST
| Tue Dec 30 17:32:01 1997 PST
| Wed Dec 31 17:32:01 1997 PST
| Fri Dec 31 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
(62 rows)
SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
eight | us_postgres
-------+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| epoch
| current
| infinity
| -infinity
| Sat May 10 23:59:12 1947 PST
| invalid
(8 rows)
SET DateStyle TO 'US,ISO';
SELECT '' AS sixty_two, d1 AS us_iso FROM TIMESTAMP_TBL;
sixty_two | us_iso
-----------+---------------------------
| invalid
| -infinity
| infinity
| epoch
| 1997-02-10 17:32:01-08
| invalid
| invalid
| 1997-02-10 17:32:01.00-08
| 1997-02-10 17:32:02.00-08
| 1997-02-10 17:32:01.40-08
| 1997-02-10 17:32:01.50-08
| 1997-02-10 17:32:01.60-08
| 1997-01-02 00:00:00-08
| 1997-01-02 03:04:05-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-06-10 17:32:01-07
| 2000-02-15 23:14:30-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:00-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 09:32:01-08
| 1997-02-10 09:32:01-08
| 1997-02-10 09:32:01-08
| 1997-06-10 18:32:01-07
| 1997-02-10 17:32:01-08
| 1997-02-11 17:32:01-08
| 1997-02-12 17:32:01-08
| 1997-02-13 17:32:01-08
| 1997-02-14 17:32:01-08
| 1997-02-15 17:32:01-08
| 1997-02-16 17:32:01-08
| 0097-02-16 17:32 BC
| 0097-02-16 17:32:01
| 0597-02-16 17:32:01
| 1097-02-16 17:32:01
| 1697-02-16 17:32:01
| 1797-02-16 17:32:01
| 1897-02-16 17:32:01
| 1997-02-16 17:32:01-08
| 2097-02-16 17:32:01
| 1996-02-28 17:32:01-08
| 1996-02-29 17:32:01-08
| 1996-03-01 17:32:01-08
| 1996-12-30 17:32:01-08
| 1996-12-31 17:32:01-08
| 1997-01-01 17:32:01-08
| 1997-02-28 17:32:01-08
| 1997-03-01 17:32:01-08
| 1997-12-30 17:32:01-08
| 1997-12-31 17:32:01-08
| 1999-12-31 17:32:01-08
| 2000-01-01 17:32:01-08
| 2000-12-31 17:32:01-08
| 2001-01-01 17:32:01-08
(62 rows)
SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
eight | us_iso
-------+------------------------
| 1973-01-14 03:14:21-08
| 1995-05-01 00:30:30-07
| epoch
| current
| infinity
| -infinity
| 1947-05-10 23:59:12-08
| invalid
(8 rows)
SET DateStyle TO 'US,SQL';
SHOW DateStyle;
NOTICE: DateStyle is SQL with US (NonEuropean) conventions
SELECT '' AS sixty_two, d1 AS us_sql FROM TIMESTAMP_TBL;
sixty_two | us_sql
-----------+----------------------------
| invalid
| -infinity
| infinity
| epoch
| 02/10/1997 17:32:01.00 PST
| invalid
| invalid
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:02.00 PST
| 02/10/1997 17:32:01.40 PST
| 02/10/1997 17:32:01.50 PST
| 02/10/1997 17:32:01.60 PST
| 01/02/1997 00:00:00.00 PST
| 01/02/1997 03:04:05.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 06/10/1997 17:32:01.00 PDT
| 02/15/2000 23:14:30.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:00.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 09:32:01.00 PST
| 02/10/1997 09:32:01.00 PST
| 02/10/1997 09:32:01.00 PST
| 06/10/1997 18:32:01.00 PDT
| 02/10/1997 17:32:01.00 PST
| 02/11/1997 17:32:01.00 PST
| 02/12/1997 17:32:01.00 PST
| 02/13/1997 17:32:01.00 PST
| 02/14/1997 17:32:01.00 PST
| 02/15/1997 17:32:01.00 PST
| 02/16/1997 17:32:01.00 PST
| 02/16/0097 17:32 BC
| 02/16/0097 17:32:01.00
| 02/16/0597 17:32:01.00
| 02/16/1097 17:32:01.00
| 02/16/1697 17:32:01.00
| 02/16/1797 17:32:01.00
| 02/16/1897 17:32:01.00
| 02/16/1997 17:32:01.00 PST
| 02/16/2097 17:32:01.00
| 02/28/1996 17:32:01.00 PST
| 02/29/1996 17:32:01.00 PST
| 03/01/1996 17:32:01.00 PST
| 12/30/1996 17:32:01.00 PST
| 12/31/1996 17:32:01.00 PST
| 01/01/1997 17:32:01.00 PST
| 02/28/1997 17:32:01.00 PST
| 03/01/1997 17:32:01.00 PST
| 12/30/1997 17:32:01.00 PST
| 12/31/1997 17:32:01.00 PST
| 12/31/1999 17:32:01.00 PST
| 01/01/2000 17:32:01.00 PST
| 12/31/2000 17:32:01.00 PST
| 01/01/2001 17:32:01.00 PST
(62 rows)
SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
eight | us_sql
-------+----------------------------
| 01/14/1973 03:14:21.00 PST
| 05/01/1995 00:30:30.00 PDT
| epoch
| current
| infinity
| -infinity
| 05/10/1947 23:59:12.00 PST
| invalid
(8 rows)
SET DateStyle TO 'European,Postgres';
SHOW DateStyle;
NOTICE: DateStyle is Postgres with European conventions
INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957';
one
-----
1
(1 row)
SELECT '' AS sixty_three, d1 AS european_postgres FROM TIMESTAMP_TBL;
sixty_three | european_postgres
-------------+---------------------------------
| invalid
| -infinity
| infinity
| epoch
| Mon 10 Feb 17:32:01 1997 PST
| invalid
| invalid
| Mon 10 Feb 17:32:01.00 1997 PST
| Mon 10 Feb 17:32:02.00 1997 PST
| Mon 10 Feb 17:32:01.40 1997 PST
| Mon 10 Feb 17:32:01.50 1997 PST
| Mon 10 Feb 17:32:01.60 1997 PST
| Thu 02 Jan 00:00:00 1997 PST
| Thu 02 Jan 03:04:05 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Tue 10 Jun 17:32:01 1997 PDT
| Tue 15 Feb 23:14:30 2000 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:00 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 09:32:01 1997 PST
| Mon 10 Feb 09:32:01 1997 PST
| Mon 10 Feb 09:32:01 1997 PST
| Tue 10 Jun 18:32:01 1997 PDT
| Mon 10 Feb 17:32:01 1997 PST
| Tue 11 Feb 17:32:01 1997 PST
| Wed 12 Feb 17:32:01 1997 PST
| Thu 13 Feb 17:32:01 1997 PST
| Fri 14 Feb 17:32:01 1997 PST
| Sat 15 Feb 17:32:01 1997 PST
| Sun 16 Feb 17:32:01 1997 PST
| Tue 16 Feb 17:32 0097 BC
| Sat 16 Feb 17:32:01 0097
| Thu 16 Feb 17:32:01 0597
| Tue 16 Feb 17:32:01 1097
| Sat 16 Feb 17:32:01 1697
| Thu 16 Feb 17:32:01 1797
| Tue 16 Feb 17:32:01 1897
| Sun 16 Feb 17:32:01 1997 PST
| Sat 16 Feb 17:32:01 2097
| Wed 28 Feb 17:32:01 1996 PST
| Thu 29 Feb 17:32:01 1996 PST
| Fri 01 Mar 17:32:01 1996 PST
| Mon 30 Dec 17:32:01 1996 PST
| Tue 31 Dec 17:32:01 1996 PST
| Wed 01 Jan 17:32:01 1997 PST
| Fri 28 Feb 17:32:01 1997 PST
| Sat 01 Mar 17:32:01 1997 PST
| Tue 30 Dec 17:32:01 1997 PST
| Wed 31 Dec 17:32:01 1997 PST
| Fri 31 Dec 17:32:01 1999 PST
| Sat 01 Jan 17:32:01 2000 PST
| Sun 31 Dec 17:32:01 2000 PST
| Mon 01 Jan 17:32:01 2001 PST
| Thu 13 Jun 00:00:00 1957 PDT
(63 rows)
SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
eight | european_postgres
-------+------------------------------
| Sun 14 Jan 03:14:21 1973 PST
| Mon 01 May 00:30:30 1995 PDT
| epoch
| current
| infinity
| -infinity
| Sat 10 May 23:59:12 1947 PST
| invalid
(8 rows)
SET DateStyle TO 'European,ISO';
SHOW DateStyle;
NOTICE: DateStyle is ISO with European conventions
SELECT '' AS sixty_three, d1 AS european_iso FROM TIMESTAMP_TBL;
sixty_three | european_iso
-------------+---------------------------
| invalid
| -infinity
| infinity
| epoch
| 1997-02-10 17:32:01-08
| invalid
| invalid
| 1997-02-10 17:32:01.00-08
| 1997-02-10 17:32:02.00-08
| 1997-02-10 17:32:01.40-08
| 1997-02-10 17:32:01.50-08
| 1997-02-10 17:32:01.60-08
| 1997-01-02 00:00:00-08
| 1997-01-02 03:04:05-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-06-10 17:32:01-07
| 2000-02-15 23:14:30-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:00-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 09:32:01-08
| 1997-02-10 09:32:01-08
| 1997-02-10 09:32:01-08
| 1997-06-10 18:32:01-07
| 1997-02-10 17:32:01-08
| 1997-02-11 17:32:01-08
| 1997-02-12 17:32:01-08
| 1997-02-13 17:32:01-08
| 1997-02-14 17:32:01-08
| 1997-02-15 17:32:01-08
| 1997-02-16 17:32:01-08
| 0097-02-16 17:32 BC
| 0097-02-16 17:32:01
| 0597-02-16 17:32:01
| 1097-02-16 17:32:01
| 1697-02-16 17:32:01
| 1797-02-16 17:32:01
| 1897-02-16 17:32:01
| 1997-02-16 17:32:01-08
| 2097-02-16 17:32:01
| 1996-02-28 17:32:01-08
| 1996-02-29 17:32:01-08
| 1996-03-01 17:32:01-08
| 1996-12-30 17:32:01-08
| 1996-12-31 17:32:01-08
| 1997-01-01 17:32:01-08
| 1997-02-28 17:32:01-08
| 1997-03-01 17:32:01-08
| 1997-12-30 17:32:01-08
| 1997-12-31 17:32:01-08
| 1999-12-31 17:32:01-08
| 2000-01-01 17:32:01-08
| 2000-12-31 17:32:01-08
| 2001-01-01 17:32:01-08
| 1957-06-13 00:00:00-07
(63 rows)
SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
eight | european_iso
-------+------------------------
| 1973-01-14 03:14:21-08
| 1995-05-01 00:30:30-07
| epoch
| current
| infinity
| -infinity
| 1947-05-10 23:59:12-08
| invalid
(8 rows)
SET DateStyle TO 'European,SQL';
SHOW DateStyle;
NOTICE: DateStyle is SQL with European conventions
SELECT '' AS sixty_three, d1 AS european_sql FROM TIMESTAMP_TBL;
sixty_three | european_sql
-------------+----------------------------
| invalid
| -infinity
| infinity
| epoch
| 10/02/1997 17:32:01.00 PST
| invalid
| invalid
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:02.00 PST
| 10/02/1997 17:32:01.40 PST
| 10/02/1997 17:32:01.50 PST
| 10/02/1997 17:32:01.60 PST
| 02/01/1997 00:00:00.00 PST
| 02/01/1997 03:04:05.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/06/1997 17:32:01.00 PDT
| 15/02/2000 23:14:30.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:00.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 09:32:01.00 PST
| 10/02/1997 09:32:01.00 PST
| 10/02/1997 09:32:01.00 PST
| 10/06/1997 18:32:01.00 PDT
| 10/02/1997 17:32:01.00 PST
| 11/02/1997 17:32:01.00 PST
| 12/02/1997 17:32:01.00 PST
| 13/02/1997 17:32:01.00 PST
| 14/02/1997 17:32:01.00 PST
| 15/02/1997 17:32:01.00 PST
| 16/02/1997 17:32:01.00 PST
| 16/02/0097 17:32 BC
| 16/02/0097 17:32:01.00
| 16/02/0597 17:32:01.00
| 16/02/1097 17:32:01.00
| 16/02/1697 17:32:01.00
| 16/02/1797 17:32:01.00
| 16/02/1897 17:32:01.00
| 16/02/1997 17:32:01.00 PST
| 16/02/2097 17:32:01.00
| 28/02/1996 17:32:01.00 PST
| 29/02/1996 17:32:01.00 PST
| 01/03/1996 17:32:01.00 PST
| 30/12/1996 17:32:01.00 PST
| 31/12/1996 17:32:01.00 PST
| 01/01/1997 17:32:01.00 PST
| 28/02/1997 17:32:01.00 PST
| 01/03/1997 17:32:01.00 PST
| 30/12/1997 17:32:01.00 PST
| 31/12/1997 17:32:01.00 PST
| 31/12/1999 17:32:01.00 PST
| 01/01/2000 17:32:01.00 PST
| 31/12/2000 17:32:01.00 PST
| 01/01/2001 17:32:01.00 PST
| 13/06/1957 00:00:00.00 PDT
(63 rows)
SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
eight | european_sql
-------+----------------------------
| 14/01/1973 03:14:21.00 PST
| 01/05/1995 00:30:30.00 PDT
| epoch
| current
| infinity
| -infinity
| 10/05/1947 23:59:12.00 PST
| invalid
(8 rows)
RESET DateStyle;
SHOW DateStyle;
NOTICE: DateStyle is Postgres with US (NonEuropean) conventions
|
