3 -- testing built-in time type abstime
4 -- uses reltime and tinterval
7 -- timezones may vary based not only on location but the operating
8 -- system. the main correctness issue is that the OS may not get
9 -- daylight savings time right for times prior to Unix epoch (jan 1 1970).
11 CREATE TABLE ABSTIME_TBL (f1 abstime);
13 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
14 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
15 SELECT count(*) AS two FROM ABSTIME_TBL WHERE f1 = 'now' ;
22 DELETE FROM ABSTIME_TBL;
23 INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
24 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995');
25 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch');
26 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity');
27 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity');
28 INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12');
29 -- what happens if we specify slightly misformatted abstime?
30 INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
31 ERROR: date/time field value out of range: "Feb 35, 1946 10:00:00"
32 HINT: Perhaps you need a different "datestyle" setting.
33 INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
34 ERROR: date/time field value out of range: "Feb 28, 1984 25:08:10"
35 -- badly formatted abstimes: these should result in invalid abstimes
36 INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
37 ERROR: invalid input syntax for type abstime: "bad date format"
38 INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
39 -- test abstime operators
40 SELECT '' AS eight, * FROM ABSTIME_TBL;
42 -------+------------------------------
43 | Sun Jan 14 03:14:21 1973 PST
44 | Mon May 01 00:30:30 1995 PDT
45 | Wed Dec 31 16:00:00 1969 PST
48 | Sat May 10 23:59:12 1947 PST
52 SELECT '' AS six, * FROM ABSTIME_TBL
53 WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001';
55 -----+------------------------------
56 | Sun Jan 14 03:14:21 1973 PST
57 | Mon May 01 00:30:30 1995 PDT
58 | Wed Dec 31 16:00:00 1969 PST
60 | Sat May 10 23:59:12 1947 PST
63 SELECT '' AS six, * FROM ABSTIME_TBL
64 WHERE ABSTIME_TBL.f1 > abstime '-infinity';
66 -----+------------------------------
67 | Sun Jan 14 03:14:21 1973 PST
68 | Mon May 01 00:30:30 1995 PDT
69 | Wed Dec 31 16:00:00 1969 PST
71 | Sat May 10 23:59:12 1947 PST
75 SELECT '' AS six, * FROM ABSTIME_TBL
76 WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1;
78 -----+------------------------------
79 | Sun Jan 14 03:14:21 1973 PST
80 | Mon May 01 00:30:30 1995 PDT
81 | Wed Dec 31 16:00:00 1969 PST
87 SELECT '' AS three, * FROM ABSTIME_TBL
88 WHERE abstime 'epoch' >= ABSTIME_TBL.f1;
90 -------+------------------------------
91 | Wed Dec 31 16:00:00 1969 PST
93 | Sat May 10 23:59:12 1947 PST
96 SELECT '' AS four, * FROM ABSTIME_TBL
97 WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21';
99 ------+------------------------------
100 | Sun Jan 14 03:14:21 1973 PST
101 | Wed Dec 31 16:00:00 1969 PST
103 | Sat May 10 23:59:12 1947 PST
106 SELECT '' AS four, * FROM ABSTIME_TBL
107 WHERE ABSTIME_TBL.f1 <?>
108 tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]';
110 ------+------------------------------
111 | Sun Jan 14 03:14:21 1973 PST
112 | Mon May 01 00:30:30 1995 PDT
113 | Wed Dec 31 16:00:00 1969 PST
116 SELECT '' AS four, f1 AS abstime,
117 date_part('year', f1) AS year, date_part('month', f1) AS month,
118 date_part('day',f1) AS day, date_part('hour', f1) AS hour,
119 date_part('minute', f1) AS minute, date_part('second', f1) AS second
123 four | abstime | year | month | day | hour | minute | second
124 ------+------------------------------+------+-------+-----+------+--------+--------
125 | Sat May 10 23:59:12 1947 PST | 1947 | 5 | 10 | 23 | 59 | 12
126 | Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0
127 | Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 | 3 | 14 | 21
128 | Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 | 0 | 30 | 30