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
|
--
-- ABSTIME
-- testing built-in time type abstime
-- uses reltime and tinterval
--
--
-- timezones may vary based not only on location but the operating
-- system. the main correctness issue is that the OS may not get
-- daylight savings time right for times prior to Unix epoch (jan 1 1970).
--
CREATE TABLE ABSTIME_TBL (f1 abstime);
BEGIN;
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now');
SELECT count(*) AS two FROM ABSTIME_TBL WHERE f1 = 'now' ;
two
-----
2
(1 row)
END;
DELETE FROM ABSTIME_TBL;
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity');
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12');
-- what happens if we specify slightly misformatted abstime?
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
ERROR: date/time field value out of range: "Feb 35, 1946 10:00:00"
HINT: Perhaps you need a different "datestyle" setting.
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
ERROR: date/time field value out of range: "Feb 28, 1984 25:08:10"
-- badly formatted abstimes: these should result in invalid abstimes
INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
ERROR: invalid input syntax for type abstime: "bad date format"
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
-- test abstime operators
SELECT '' AS eight, ABSTIME_TBL.*;
eight | f1
-------+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| infinity
| -infinity
| Sat May 10 23:59:12 1947 PST
| invalid
(7 rows)
SELECT '' AS six, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001';
six | f1
-----+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| -infinity
| Sat May 10 23:59:12 1947 PST
(5 rows)
SELECT '' AS six, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 > abstime '-infinity';
six | f1
-----+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| infinity
| Sat May 10 23:59:12 1947 PST
| invalid
(6 rows)
SELECT '' AS six, ABSTIME_TBL.*
WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1;
six | f1
-----+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
| infinity
| -infinity
| invalid
(6 rows)
SELECT '' AS three, ABSTIME_TBL.*
WHERE abstime 'epoch' >= ABSTIME_TBL.f1;
three | f1
-------+------------------------------
| Wed Dec 31 16:00:00 1969 PST
| -infinity
| Sat May 10 23:59:12 1947 PST
(3 rows)
SELECT '' AS four, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21';
four | f1
------+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Wed Dec 31 16:00:00 1969 PST
| -infinity
| Sat May 10 23:59:12 1947 PST
(4 rows)
SELECT '' AS four, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 <?>
tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]';
four | f1
------+------------------------------
| Sun Jan 14 03:14:21 1973 PST
| Mon May 01 00:30:30 1995 PDT
| Wed Dec 31 16:00:00 1969 PST
(3 rows)
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)
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
| Wed Dec 31 16:00:00 1969 PST | 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)
|