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