1--
2-- TIMETZ
3--
4CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
5INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
6INSERT INTO TIMETZ_TBL VALUES ('01:00 PDT');
7INSERT INTO TIMETZ_TBL VALUES ('02:03 PDT');
8INSERT INTO TIMETZ_TBL VALUES ('07:07 PST');
9INSERT INTO TIMETZ_TBL VALUES ('08:08 EDT');
10INSERT INTO TIMETZ_TBL VALUES ('11:59 PDT');
11INSERT INTO TIMETZ_TBL VALUES ('12:00 PDT');
12INSERT INTO TIMETZ_TBL VALUES ('12:01 PDT');
13INSERT INTO TIMETZ_TBL VALUES ('23:59 PDT');
14INSERT INTO TIMETZ_TBL VALUES ('11:59:59.99 PM PDT');
15INSERT INTO TIMETZ_TBL VALUES ('2003-03-07 15:36:39 America/New_York');
16INSERT INTO TIMETZ_TBL VALUES ('2003-07-07 15:36:39 America/New_York');
17-- this should fail (the timezone offset is not known)
18INSERT INTO TIMETZ_TBL VALUES ('15:36:39 America/New_York');
19ERROR:  invalid input syntax for type time with time zone: "15:36:39 America/New_York"
20LINE 1: INSERT INTO TIMETZ_TBL VALUES ('15:36:39 America/New_York');
21                                       ^
22-- this should fail (timezone not specified without a date)
23INSERT INTO TIMETZ_TBL VALUES ('15:36:39 m2');
24ERROR:  invalid input syntax for type time with time zone: "15:36:39 m2"
25LINE 1: INSERT INTO TIMETZ_TBL VALUES ('15:36:39 m2');
26                                       ^
27-- this should fail (dynamic timezone abbreviation without a date)
28INSERT INTO TIMETZ_TBL VALUES ('15:36:39 MSK m2');
29ERROR:  invalid input syntax for type time with time zone: "15:36:39 MSK m2"
30LINE 1: INSERT INTO TIMETZ_TBL VALUES ('15:36:39 MSK m2');
31                                       ^
32SELECT f1 AS "Time TZ" FROM TIMETZ_TBL;
33    Time TZ
34----------------
35 00:01:00-07
36 01:00:00-07
37 02:03:00-07
38 07:07:00-08
39 08:08:00-04
40 11:59:00-07
41 12:00:00-07
42 12:01:00-07
43 23:59:00-07
44 23:59:59.99-07
45 15:36:39-05
46 15:36:39-04
47(12 rows)
48
49SELECT f1 AS "Three" FROM TIMETZ_TBL WHERE f1 < '05:06:07-07';
50    Three
51-------------
52 00:01:00-07
53 01:00:00-07
54 02:03:00-07
55(3 rows)
56
57SELECT f1 AS "Seven" FROM TIMETZ_TBL WHERE f1 > '05:06:07-07';
58     Seven
59----------------
60 07:07:00-08
61 08:08:00-04
62 11:59:00-07
63 12:00:00-07
64 12:01:00-07
65 23:59:00-07
66 23:59:59.99-07
67 15:36:39-05
68 15:36:39-04
69(9 rows)
70
71SELECT f1 AS "None" FROM TIMETZ_TBL WHERE f1 < '00:00-07';
72 None
73------
74(0 rows)
75
76SELECT f1 AS "Ten" FROM TIMETZ_TBL WHERE f1 >= '00:00-07';
77      Ten
78----------------
79 00:01:00-07
80 01:00:00-07
81 02:03:00-07
82 07:07:00-08
83 08:08:00-04
84 11:59:00-07
85 12:00:00-07
86 12:01:00-07
87 23:59:00-07
88 23:59:59.99-07
89 15:36:39-05
90 15:36:39-04
91(12 rows)
92
93-- Check edge cases
94SELECT '23:59:59.999999 PDT'::timetz;
95       timetz
96--------------------
97 23:59:59.999999-07
98(1 row)
99
100SELECT '23:59:59.9999999 PDT'::timetz;  -- rounds up
101   timetz
102-------------
103 24:00:00-07
104(1 row)
105
106SELECT '23:59:60 PDT'::timetz;  -- rounds up
107   timetz
108-------------
109 24:00:00-07
110(1 row)
111
112SELECT '24:00:00 PDT'::timetz;  -- allowed
113   timetz
114-------------
115 24:00:00-07
116(1 row)
117
118SELECT '24:00:00.01 PDT'::timetz;  -- not allowed
119ERROR:  date/time field value out of range: "24:00:00.01 PDT"
120LINE 1: SELECT '24:00:00.01 PDT'::timetz;
121               ^
122SELECT '23:59:60.01 PDT'::timetz;  -- not allowed
123ERROR:  date/time field value out of range: "23:59:60.01 PDT"
124LINE 1: SELECT '23:59:60.01 PDT'::timetz;
125               ^
126SELECT '24:01:00 PDT'::timetz;  -- not allowed
127ERROR:  date/time field value out of range: "24:01:00 PDT"
128LINE 1: SELECT '24:01:00 PDT'::timetz;
129               ^
130SELECT '25:00:00 PDT'::timetz;  -- not allowed
131ERROR:  date/time field value out of range: "25:00:00 PDT"
132LINE 1: SELECT '25:00:00 PDT'::timetz;
133               ^
134--
135-- TIME simple math
136--
137-- We now make a distinction between time and intervals,
138-- and adding two times together makes no sense at all.
139-- Leave in one query to show that it is rejected,
140-- and do the rest of the testing in horology.sql
141-- where we do mixed-type arithmetic. - thomas 2000-12-02
142SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL;
143ERROR:  operator does not exist: time with time zone + time with time zone
144LINE 1: SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TI...
145                  ^
146HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
147