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
LINE 2: VALUES ('["bad time specifications" ""]');
23
INSERT INTO TINTERVAL_TBL (f1)
24
VALUES ('["" "infinity"]');
25
ERROR: invalid input syntax for type abstime: ""
26
LINE 2: VALUES ('["" "infinity"]');
28
-- test tinterval operators
29
SELECT '' AS five, * FROM TINTERVAL_TBL;
31
------+-----------------------------------------------------------------
32
| ["-infinity" "infinity"]
33
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
34
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
35
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
36
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
42
WHERE t.f1 #= '@ 1 months';
44
-----+-----------------------------------------------------------------
45
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
49
SELECT '' AS three, t.*
51
WHERE t.f1 #<> '@ 1 months';
53
-------+-----------------------------------------------------------------
54
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
55
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
56
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
60
SELECT '' AS zero, t.*
62
WHERE t.f1 #< '@ 1 month';
70
WHERE t.f1 #<= '@ 1 month';
72
-----+-----------------------------------------------------------------
73
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
77
SELECT '' AS three, t.*
79
WHERE t.f1 #> '@ 1 year';
81
-------+-----------------------------------------------------------------
82
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
83
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
84
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
88
SELECT '' AS three, t.*
90
WHERE t.f1 #>= '@ 3 years';
92
-------+-----------------------------------------------------------------
93
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
94
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
95
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
99
SELECT '' AS three, t1.*
100
FROM TINTERVAL_TBL t1
102
tinterval '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]';
104
-------+-----------------------------------------------------------------
105
| ["-infinity" "infinity"]
106
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
107
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
110
SELECT '' AS five, t1.f1, t2.f1
111
FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
112
WHERE t1.f1 && t2.f1 and
114
ORDER BY t1.f1, t2.f1;
116
------+-----------------------------------------------------------------+-----------------------------------------------------------------
117
| ["-infinity" "infinity"] | ["-infinity" "infinity"]
118
| ["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"]
119
| ["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"]
120
| ["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"]
121
| ["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"]
124
SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2
125
FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
126
WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1
127
ORDER BY interval1, interval2;
128
fourteen | interval1 | interval2
129
----------+-----------------------------------------------------------------+-----------------------------------------------------------------
130
| ["-infinity" "infinity"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
131
| ["-infinity" "infinity"] | ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
132
| ["-infinity" "infinity"] | ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]
133
| ["-infinity" "infinity"] | ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
134
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["-infinity" "infinity"]
135
| ["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"]
136
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"] | ["-infinity" "infinity"]
137
| ["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"]
138
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"] | ["-infinity" "infinity"]
139
| ["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"]
140
| ["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"]
141
| ["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"]
142
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"] | ["-infinity" "infinity"]
143
| ["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"]
147
SELECT '' AS five, t1.f1
148
FROM TINTERVAL_TBL t1
150
tinterval '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'
153
------+-----------------------------------------------------------------
154
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
155
| ["Thu Feb 15 12:15:03 1990 PST" "Sun Sep 23 11:12:13 2001 PDT"]
156
| ["Sat May 10 23:59:12 1947 PST" "Sun Jan 14 03:14:21 1973 PST"]
159
-- make time interval
160
SELECT '' AS three, t1.f1
161
FROM TINTERVAL_TBL t1
163
(abstime 'Aug 15 14:23:19 1983' <#>
164
abstime 'Sep 16 14:23:19 1983')
167
-------+-----------------------------------------------------------------
168
| ["-infinity" "infinity"]
169
| ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]
170
| ["Wed Dec 31 16:00:00 1969 PST" "Mon May 01 00:30:30 1995 PDT"]