regress/sql/horology.sql

9 SELECT timestamp with time zone '20011227 040506+08';
10 SELECT timestamp with time zone '20011227 040506-08';
11 SELECT timestamp with time zone '20011227 040506.789+08';
12 SELECT timestamp with time zone '20011227 040506.789-08';
13 SELECT timestamp with time zone '20011227T040506+08';
14 SELECT timestamp with time zone '20011227T040506-08';
15 SELECT timestamp with time zone '20011227T040506.789+08';
16 SELECT timestamp with time zone '20011227T040506.789-08';
17 SELECT timestamp with time zone '2001-12-27 04:05:06.789-08';
18 SELECT timestamp with time zone '2001.12.27 04:05:06.789-08';
19 SELECT timestamp with time zone '2001/12/27 04:05:06.789-08';
20 SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
22 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
24 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
26 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
27 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08';
28 SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08';
29 SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789-08';
30 SELECT timestamp with time zone 'J2452271+08';
31 SELECT timestamp with time zone 'J2452271-08';
32 SELECT timestamp with time zone 'J2452271.5+08';
33 SELECT timestamp with time zone 'J2452271.5-08';
34 SELECT timestamp with time zone 'J2452271 04:05:06+08';
35 SELECT timestamp with time zone 'J2452271 04:05:06-08';
36 SELECT timestamp with time zone 'J2452271T040506+08';
37 SELECT timestamp with time zone 'J2452271T040506-08';
38 SELECT timestamp with time zone 'J2452271T040506.789+08';
39 SELECT timestamp with time zone 'J2452271T040506.789-08';
41 SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
42 SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
44 SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
45 SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
48 SELECT time without time zone '040506.789+08';
49 SELECT time without time zone '040506.789-08';
50 SELECT time without time zone 'T040506.789+08';
51 SELECT time without time zone 'T040506.789-08';
52 SELECT time with time zone '040506.789+08';
53 SELECT time with time zone '040506.789-08';
54 SELECT time with time zone 'T040506.789+08';
55 SELECT time with time zone 'T040506.789-08';
56 SELECT time with time zone 'T040506.789 +08';
57 SELECT time with time zone 'T040506.789 -08';
68 SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST";
69 SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC";
74 SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
80 SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
81 SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
82 SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
83 SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
84 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
85 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
86 SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
87 SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "10…
92 SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '…
93 SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1…
94 SELECT (timestamp without time zone 'today 10:30' = (timestamp without time zone 'yesterday' + inte…
95 SELECT (timestamp without time zone '10:30 today' = (timestamp without time zone 'yesterday' + inte…
96 SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interva…
97 SELECT (timestamp without time zone 'tomorrow 16:00:00' = (timestamp without time zone 'today' + in…
98 SELECT (timestamp without time zone '16:00:00 tomorrow' = (timestamp without time zone 'today' + in…
99 SELECT (timestamp without time zone 'yesterday 12:34:56' = (timestamp without time zone 'tomorrow' …
100 SELECT (timestamp without time zone '12:34:56 yesterday' = (timestamp without time zone 'tomorrow' …
101 SELECT (timestamp without time zone 'tomorrow' > 'now') as "True";
109 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am";
114 SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29";
115 SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28";
116 SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29";
117 SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
119 SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day'…
120 SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')…
121 SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 d…
122 SELECT (timestamp with time zone 'tomorrow' > 'now') as "True";
127 SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00";
128 SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00";
129 SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00";
130 SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00";
136 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-8') AS "Jan_01_1994_11am";
137 SELECT timestamptz(date '1994-01-01', time with time zone '10:00-8') AS "Jan_01_1994_10am";
138 SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am";
153 SELECT CAST(time with time zone '01:02-08' AS interval) AS "+00:01";
154 SELECT CAST(interval '02:03' AS time with time zone) AS "02:03:00-08";
155 SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08";
156 SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08";
164 SELECT CAST(CAST(date 'today' + time with time zone '05:30'
165             + interval '02:01' AS time with time zone) AS time) AS "07:31:00";
167 SELECT CAST(cast(date 'today' + time with time zone '03:30'
168   + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00";
186 SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28')
187 …OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "…
189 SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27')
190 …OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "…
192 SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28')
193   OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '1 day') AS "True";
195 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
196 …OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "…
198 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
199   OVERLAPS (timestamp with time zone '2000-11-27', interval '12 hours') AS "True";
201 SELECT (timestamp with time zone '2000-11-27', interval '12 hours')
202   OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '12 hours') AS "False";
205 SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28')
206 …OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30'…
208 SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27')
209 …OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30'…
211 SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28')
212   OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '1 day') AS "True";
214 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
215 …OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30'…
217 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
218   OVERLAPS (timestamp without time zone '2000-11-27', interval '12 hours') AS "True";
220 SELECT (timestamp without time zone '2000-11-27', interval '12 hours')
221   OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '12 hours') AS "False";
239 CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone);
262    timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero,
263    d.f1 - timestamp with time zone '1980-01-06 00:00 GMT' AS difference