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
|
-- **** testing built-in time types: abstime, 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);
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
-- was INSERT INTO ABSTIME_TBL (f1) VALUES ('now'::abstime):
INSERT INTO ABSTIME_TBL (f1) VALUES ('Mon May 1 00:30:30 1995'::abstime);
INSERT INTO ABSTIME_TBL (f1) VALUES ('epoch'::abstime);
INSERT INTO ABSTIME_TBL (f1) VALUES ('current'::abstime);
INSERT INTO ABSTIME_TBL (f1) VALUES ('infinity'::abstime);
INSERT INTO ABSTIME_TBL (f1) VALUES ('-infinity'::abstime);
INSERT INTO ABSTIME_TBL (f1) VALUES ('May 10, 1943 23:59:12');
-- what happens if we specify slightly misformatted abstime?
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
-- badly formatted abstimes: these should result in invalid abstimes
INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
-- test abstime operators
SELECT '' AS eight, ABSTIME_TBL.*;
SELECT '' AS six, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 < 'Jun 30, 2001'::abstime;
SELECT '' AS six, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 > '-infinity'::abstime;
SELECT '' AS six, ABSTIME_TBL.*
WHERE 'May 10, 1943 23:59:12'::abstime <> ABSTIME_TBL.f1;
SELECT '' AS one, ABSTIME_TBL.*
WHERE 'current'::abstime = ABSTIME_TBL.f1;
SELECT '' AS three, ABSTIME_TBL.*
WHERE 'epoch'::abstime >= ABSTIME_TBL.f1;
SELECT '' AS four, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 <= 'Jan 14, 1973 03:14:21'::abstime;
SELECT '' AS four, ABSTIME_TBL.*
WHERE ABSTIME_TBL.f1 <?>
'["Apr 1 1945 00:00:00" "Dec 30 1999 23:00:00"]'::tinterval;
-- these four queries should return the same answer
-- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and
-- therefore, should not show up in the results.
SELECT '' AS three, ABSTIME_TBL.*
WHERE (ABSTIME_TBL.f1 + '@ 3 year'::reltime) -- +3 years
< 'Jan 14 14:00:00 1977'::abstime;
SELECT '' AS three, ABSTIME_TBL.*
WHERE (ABSTIME_TBL.f1 + '@ 3 year ago'::reltime) -- -3 years
< 'Jan 14 14:00:00 1971'::abstime;
SELECT '' AS three, ABSTIME_TBL.*
WHERE (ABSTIME_TBL.f1 - '@ 3 year'::reltime) -- -(+3) years
< 'Jan 14 14:00:00 1971'::abstime;
SELECT '' AS three, ABSTIME_TBL.*
WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime) -- -(-3) years
< 'Jan 14 14:00:00 1977'::abstime;
SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime
WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1)
< 'Jan 14 14:00:00 1971'::abstime
ORDER BY abstime, reltime;
|
