1--
2-- ABSTIME
3-- testing built-in time type abstime
4-- uses reltime and tinterval
5--
6--
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).
10--
11CREATE TABLE ABSTIME_TBL (f1 abstime);
12BEGIN;
13INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
14INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
15SELECT count(*) AS two FROM ABSTIME_TBL WHERE f1 = 'now' ;
16 two
17-----
18   2
19(1 row)
20
21END;
22DELETE FROM ABSTIME_TBL;
23INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
24INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May  1 00:30:30 1995');
25INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch');
26INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity');
27INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity');
28INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12');
29-- what happens if we specify slightly misformatted abstime?
30INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
31ERROR:  date/time field value out of range: "Feb 35, 1946 10:00:00"
32LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'...
33                                             ^
34HINT:  Perhaps you need a different "datestyle" setting.
35INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
36ERROR:  date/time field value out of range: "Feb 28, 1984 25:08:10"
37LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'...
38                                             ^
39-- badly formatted abstimes:  these should result in invalid abstimes
40INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
41ERROR:  invalid input syntax for type abstime: "bad date format"
42LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
43                                             ^
44INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
45-- test abstime operators
46SELECT '' AS eight, * FROM ABSTIME_TBL;
47 eight |              f1
48-------+------------------------------
49       | Sun Jan 14 03:14:21 1973 PST
50       | Mon May 01 00:30:30 1995 PDT
51       | Wed Dec 31 16:00:00 1969 PST
52       | infinity
53       | -infinity
54       | Sat May 10 23:59:12 1947 PST
55       | invalid
56(7 rows)
57
58SELECT '' AS six, * FROM ABSTIME_TBL
59   WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001';
60 six |              f1
61-----+------------------------------
62     | Sun Jan 14 03:14:21 1973 PST
63     | Mon May 01 00:30:30 1995 PDT
64     | Wed Dec 31 16:00:00 1969 PST
65     | -infinity
66     | Sat May 10 23:59:12 1947 PST
67(5 rows)
68
69SELECT '' AS six, * FROM ABSTIME_TBL
70   WHERE ABSTIME_TBL.f1 > abstime '-infinity';
71 six |              f1
72-----+------------------------------
73     | Sun Jan 14 03:14:21 1973 PST
74     | Mon May 01 00:30:30 1995 PDT
75     | Wed Dec 31 16:00:00 1969 PST
76     | infinity
77     | Sat May 10 23:59:12 1947 PST
78     | invalid
79(6 rows)
80
81SELECT '' AS six, * FROM ABSTIME_TBL
82   WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1;
83 six |              f1
84-----+------------------------------
85     | Sun Jan 14 03:14:21 1973 PST
86     | Mon May 01 00:30:30 1995 PDT
87     | Wed Dec 31 16:00:00 1969 PST
88     | infinity
89     | -infinity
90     | invalid
91(6 rows)
92
93SELECT '' AS three, * FROM ABSTIME_TBL
94   WHERE abstime 'epoch' >= ABSTIME_TBL.f1;
95 three |              f1
96-------+------------------------------
97       | Wed Dec 31 16:00:00 1969 PST
98       | -infinity
99       | Sat May 10 23:59:12 1947 PST
100(3 rows)
101
102SELECT '' AS four, * FROM ABSTIME_TBL
103   WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21';
104 four |              f1
105------+------------------------------
106      | Sun Jan 14 03:14:21 1973 PST
107      | Wed Dec 31 16:00:00 1969 PST
108      | -infinity
109      | Sat May 10 23:59:12 1947 PST
110(4 rows)
111
112SELECT '' AS four, * FROM ABSTIME_TBL
113  WHERE ABSTIME_TBL.f1 <?>
114	tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]';
115 four |              f1
116------+------------------------------
117      | Sun Jan 14 03:14:21 1973 PST
118      | Mon May 01 00:30:30 1995 PDT
119      | Wed Dec 31 16:00:00 1969 PST
120(3 rows)
121
122SELECT '' AS four, f1 AS abstime,
123  date_part('year', f1) AS year, date_part('month', f1) AS month,
124  date_part('day',f1) AS day, date_part('hour', f1) AS hour,
125  date_part('minute', f1) AS minute, date_part('second', f1) AS second
126  FROM ABSTIME_TBL
127  WHERE isfinite(f1)
128  ORDER BY abstime;
129 four |           abstime            | year | month | day | hour | minute | second
130------+------------------------------+------+-------+-----+------+--------+--------
131      | Sat May 10 23:59:12 1947 PST | 1947 |     5 |  10 |   23 |     59 |     12
132      | Wed Dec 31 16:00:00 1969 PST | 1969 |    12 |  31 |   16 |      0 |      0
133      | Sun Jan 14 03:14:21 1973 PST | 1973 |     1 |  14 |    3 |     14 |     21
134      | Mon May 01 00:30:30 1995 PDT | 1995 |     5 |   1 |    0 |     30 |     30
135(4 rows)
136
137