1-- 2-- ABSTIME 3-- testing built-in time type abstime 4-- uses reltime and tinterval 5-- 6-- 7-- timezones may vary based not only on location but the operating 8-- system. the main correctness issue is that the OS may not get 9-- daylight savings time right for times prior to Unix epoch (jan 1 1970). 10-- 11CREATE TABLE ABSTIME_TBL (f1 abstime); 12BEGIN; 13INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now'); 14INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now'); 15SELECT count(*) AS two FROM ABSTIME_TBL WHERE f1 = 'now' ; 16 two 17----- 18 2 19(1 row) 20 21END; 22DELETE FROM ABSTIME_TBL; 23INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21'); 24INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995'); 25INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch'); 26INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity'); 27INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity'); 28INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12'); 29-- what happens if we specify slightly misformatted abstime? 30INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'); 31ERROR: date/time field value out of range: "Feb 35, 1946 10:00:00" 32LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'... 33 ^ 34HINT: Perhaps you need a different "datestyle" setting. 35INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'); 36ERROR: date/time field value out of range: "Feb 28, 1984 25:08:10" 37LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'... 38 ^ 39-- badly formatted abstimes: these should result in invalid abstimes 40INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format'); 41ERROR: invalid input syntax for type abstime: "bad date format" 42LINE 1: INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format'); 43 ^ 44INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843'); 45-- test abstime operators 46SELECT '' AS eight, * FROM ABSTIME_TBL; 47 eight | f1 48-------+------------------------------ 49 | Sun Jan 14 03:14:21 1973 PST 50 | Mon May 01 00:30:30 1995 PDT 51 | Wed Dec 31 16:00:00 1969 PST 52 | infinity 53 | -infinity 54 | Sat May 10 23:59:12 1947 PST 55 | invalid 56(7 rows) 57 58SELECT '' AS six, * FROM ABSTIME_TBL 59 WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001'; 60 six | f1 61-----+------------------------------ 62 | Sun Jan 14 03:14:21 1973 PST 63 | Mon May 01 00:30:30 1995 PDT 64 | Wed Dec 31 16:00:00 1969 PST 65 | -infinity 66 | Sat May 10 23:59:12 1947 PST 67(5 rows) 68 69SELECT '' AS six, * FROM ABSTIME_TBL 70 WHERE ABSTIME_TBL.f1 > abstime '-infinity'; 71 six | f1 72-----+------------------------------ 73 | Sun Jan 14 03:14:21 1973 PST 74 | Mon May 01 00:30:30 1995 PDT 75 | Wed Dec 31 16:00:00 1969 PST 76 | infinity 77 | Sat May 10 23:59:12 1947 PST 78 | invalid 79(6 rows) 80 81SELECT '' AS six, * FROM ABSTIME_TBL 82 WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1; 83 six | f1 84-----+------------------------------ 85 | Sun Jan 14 03:14:21 1973 PST 86 | Mon May 01 00:30:30 1995 PDT 87 | Wed Dec 31 16:00:00 1969 PST 88 | infinity 89 | -infinity 90 | invalid 91(6 rows) 92 93SELECT '' AS three, * FROM ABSTIME_TBL 94 WHERE abstime 'epoch' >= ABSTIME_TBL.f1; 95 three | f1 96-------+------------------------------ 97 | Wed Dec 31 16:00:00 1969 PST 98 | -infinity 99 | Sat May 10 23:59:12 1947 PST 100(3 rows) 101 102SELECT '' AS four, * FROM ABSTIME_TBL 103 WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21'; 104 four | f1 105------+------------------------------ 106 | Sun Jan 14 03:14:21 1973 PST 107 | Wed Dec 31 16:00:00 1969 PST 108 | -infinity 109 | Sat May 10 23:59:12 1947 PST 110(4 rows) 111 112SELECT '' AS four, * FROM ABSTIME_TBL 113 WHERE ABSTIME_TBL.f1 <?> 114 tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]'; 115 four | f1 116------+------------------------------ 117 | Sun Jan 14 03:14:21 1973 PST 118 | Mon May 01 00:30:30 1995 PDT 119 | Wed Dec 31 16:00:00 1969 PST 120(3 rows) 121 122SELECT '' AS four, f1 AS abstime, 123 date_part('year', f1) AS year, date_part('month', f1) AS month, 124 date_part('day',f1) AS day, date_part('hour', f1) AS hour, 125 date_part('minute', f1) AS minute, date_part('second', f1) AS second 126 FROM ABSTIME_TBL 127 WHERE isfinite(f1) 128 ORDER BY abstime; 129 four | abstime | year | month | day | hour | minute | second 130------+------------------------------+------+-------+-----+------+--------+-------- 131 | Sat May 10 23:59:12 1947 PST | 1947 | 5 | 10 | 23 | 59 | 12 132 | Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0 133 | Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 | 3 | 14 | 21 134 | Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 | 0 | 30 | 30 135(4 rows) 136 137