1--
2-- TINTERVAL
3--
4
5CREATE TABLE TINTERVAL_TBL (f1  tinterval);
6
7-- Should accept any abstime,
8-- so do not bother with extensive testing of values
9
10INSERT INTO TINTERVAL_TBL (f1)
11   VALUES ('["-infinity" "infinity"]');
12
13INSERT INTO TINTERVAL_TBL (f1)
14   VALUES ('["May 10, 1947 23:59:12" "Jan 14, 1973 03:14:21"]');
15
16INSERT INTO TINTERVAL_TBL (f1)
17   VALUES ('["Sep 4, 1983 23:59:12" "Oct 4, 1983 23:59:12"]');
18
19INSERT INTO TINTERVAL_TBL (f1)
20   VALUES ('["epoch" "Mon May  1 00:30:30 1995"]');
21
22INSERT INTO TINTERVAL_TBL (f1)
23   VALUES ('["Feb 15 1990 12:15:03" "2001-09-23 11:12:13"]');
24
25
26-- badly formatted tintervals
27INSERT INTO TINTERVAL_TBL (f1)
28   VALUES ('["bad time specifications" ""]');
29
30INSERT INTO TINTERVAL_TBL (f1)
31   VALUES ('["" "infinity"]');
32
33-- test tinterval operators
34
35SELECT '' AS five, * FROM TINTERVAL_TBL;
36
37-- length ==
38SELECT '' AS one, t.*
39   FROM TINTERVAL_TBL t
40   WHERE t.f1 #= '@ 1 months';
41
42-- length <>
43SELECT '' AS three, t.*
44   FROM TINTERVAL_TBL t
45   WHERE t.f1 #<> '@ 1 months';
46
47-- length <
48SELECT '' AS zero, t.*
49   FROM TINTERVAL_TBL t
50   WHERE t.f1 #< '@ 1 month';
51
52-- length <=
53SELECT '' AS one, t.*
54   FROM TINTERVAL_TBL t
55   WHERE t.f1 #<= '@ 1 month';
56
57-- length >
58SELECT '' AS three, t.*
59   FROM TINTERVAL_TBL t
60   WHERE t.f1 #> '@ 1 year';
61
62-- length >=
63SELECT '' AS three, t.*
64   FROM TINTERVAL_TBL t
65   WHERE t.f1 #>= '@ 3 years';
66
67-- overlaps
68SELECT '' AS three, t1.*
69   FROM TINTERVAL_TBL t1
70   WHERE t1.f1 &&
71        tinterval '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]';
72
73SELECT '' AS five, t1.f1, t2.f1
74   FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
75   WHERE t1.f1 && t2.f1 and
76         t1.f1 = t2.f1
77   ORDER BY t1.f1, t2.f1;
78
79SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2
80   FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2
81   WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1
82   ORDER BY interval1, interval2;
83
84-- contains
85SELECT '' AS five, t1.f1
86   FROM TINTERVAL_TBL t1
87   WHERE not t1.f1 <<
88        tinterval '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'
89   ORDER BY t1.f1;
90
91-- make time interval
92SELECT '' AS three, t1.f1
93   FROM TINTERVAL_TBL t1
94   WHERE t1.f1 &&
95        (abstime 'Aug 15 14:23:19 1983' <#>
96         abstime 'Sep 16 14:23:19 1983')
97   ORDER BY t1.f1;
98