1-- 2-- TIMETZ 3-- 4 5CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone); 6 7INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT'); 8INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT'); 9INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT'); 10INSERT INTO TIMETZ_TBL VALUES ('07:07 PST'); 11INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT'); 12INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT'); 13INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT'); 14INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT'); 15INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT'); 16INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT'); 17 18INSERT INTO TIMETZ_TBL VALUES ('2003-03-07 15:36:39 America/New_York'); 19INSERT INTO TIMETZ_TBL VALUES ('2003-07-07 15:36:39 America/New_York'); 20-- this should fail (the timezone offset is not known) 21INSERT INTO TIMETZ_TBL VALUES ('15:36:39 America/New_York'); 22-- this should fail (timezone not specified without a date) 23INSERT INTO TIMETZ_TBL VALUES ('15:36:39 m2'); 24-- this should fail (dynamic timezone abbreviation without a date) 25INSERT INTO TIMETZ_TBL VALUES ('15:36:39 MSK m2'); 26 27 28SELECT f1 AS "Time TZ" FROM TIMETZ_TBL; 29 30SELECT f1 AS "Three" FROM TIMETZ_TBL WHERE f1 < '05:06:07-07'; 31 32SELECT f1 AS "Seven" FROM TIMETZ_TBL WHERE f1 > '05:06:07-07'; 33 34SELECT f1 AS "None" FROM TIMETZ_TBL WHERE f1 < '00:00-07'; 35 36SELECT f1 AS "Ten" FROM TIMETZ_TBL WHERE f1 >= '00:00-07'; 37 38-- Check edge cases 39SELECT '23:59:59.999999 PDT'::timetz; 40SELECT '23:59:59.9999999 PDT'::timetz; -- rounds up 41SELECT '23:59:60 PDT'::timetz; -- rounds up 42SELECT '24:00:00 PDT'::timetz; -- allowed 43SELECT '24:00:00.01 PDT'::timetz; -- not allowed 44SELECT '23:59:60.01 PDT'::timetz; -- not allowed 45SELECT '24:01:00 PDT'::timetz; -- not allowed 46SELECT '25:00:00 PDT'::timetz; -- not allowed 47 48-- 49-- TIME simple math 50-- 51-- We now make a distinction between time and intervals, 52-- and adding two times together makes no sense at all. 53-- Leave in one query to show that it is rejected, 54-- and do the rest of the testing in horology.sql 55-- where we do mixed-type arithmetic. - thomas 2000-12-02 56 57SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL; 58