4
CREATE TABLE TINTERVAL_TBL (f1 tinterval);
5
-- Should accept any abstime,
6
-- so do not bother with extensive testing of values
7
INSERT INTO TINTERVAL_TBL (f1)
8
VALUES ('["-infinity" "infinity"]');
9
INSERT INTO TINTERVAL_TBL (f1)
10
VALUES ('["May 10, 1947 23:59:12" "Jan 14, 1973 03:14:21"]');
11
INSERT INTO TINTERVAL_TBL (f1)
12
VALUES ('["Sep 4, 1983 23:59:12" "Oct 4, 1983 23:59:12"]');
13
INSERT INTO TINTERVAL_TBL (f1)
14
VALUES ('["epoch" "Mon May 1 00:30:30 1995"]');
15
INSERT INTO TINTERVAL_TBL (f1)
16
VALUES ('["Feb 15 1990 12:15:03" "2001-09-23 11:12:13"]');
17
-- badly formatted tintervals
18
INSERT INTO TINTERVAL_TBL (f1)
19
VALUES ('["bad time specifications" ""]');
20
ERROR: invalid input syntax for type abstime: "bad time specifications"
21
INSERT INTO TINTERVAL_TBL (f1)
22
VALUES ('["" "infinity"]');
23
ERROR: invalid input syntax for type abstime: ""
24
-- test tinterval operators
25
SELECT '' AS five, TINTERVAL_TBL.*;
27
------+-----------------------------------------------------------------
28
| ["-infinity" "infinity"]
29
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
30
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
31
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
32
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
38
WHERE t.f1 #= '@ 1 months';
40
-----+-----------------------------------------------------------------
41
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
45
SELECT '' AS three, t.*
47
WHERE t.f1 #<> '@ 1 months';
49
-------+-----------------------------------------------------------------
50
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
51
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
52
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
56
SELECT '' AS zero, t.*
58
WHERE t.f1 #< '@ 1 month';
66
WHERE t.f1 #<= '@ 1 month';
68
-----+-----------------------------------------------------------------
69
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
73
SELECT '' AS three, t.*
75
WHERE t.f1 #> '@ 1 year';
77
-------+-----------------------------------------------------------------
78
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
79
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
80
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
84
SELECT '' AS three, t.*
86
WHERE t.f1 #>= '@ 3 years';
88
-------+-----------------------------------------------------------------
89
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
90
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
91
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
95
SELECT '' AS three, t1.*
98
tinterval '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]';
100
-------+-----------------------------------------------------------------
101
| ["-infinity" "infinity"]
102
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
103
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
107
SELECT '' AS five, t1.f1, t2.f1
108
FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
109
WHERE t1.f1 && t2.f1 and
111
ORDER BY t1.f1, t2.f1;
113
------+-----------------------------------------------------------------+-----------------------------------------------------------------
114
| ["-infinity" "infinity"] | ["-infinity" "infinity"]
115
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
116
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"] | ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
117
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
118
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"] | ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
121
SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2
122
FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
123
WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1
124
ORDER BY interval1, interval2;
125
fourteen | interval1 | interval2
126
----------+-----------------------------------------------------------------+-----------------------------------------------------------------
127
| ["-infinity" "infinity"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
128
| ["-infinity" "infinity"] | ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
129
| ["-infinity" "infinity"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
130
| ["-infinity" "infinity"] | ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
131
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["-infinity" "infinity"]
132
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
133
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"] | ["-infinity" "infinity"]
134
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
135
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["-infinity" "infinity"]
136
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
137
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
138
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
139
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"] | ["-infinity" "infinity"]
140
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
144
SELECT '' AS five, t1.f1
145
FROM TINTERVAL_TBL t1
147
tinterval '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'
150
------+-----------------------------------------------------------------
151
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
152
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
153
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
156
-- make time interval
157
SELECT '' AS three, t1.f1
158
FROM TINTERVAL_TBL t1
160
(abstime 'Aug 15 14:23:19 1983' <#>
161
abstime 'Sep 16 14:23:19 1983')
164
-------+-----------------------------------------------------------------
165
| ["-infinity" "infinity"]
166
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
167
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]