| blob | 990c4eddf13654a2b58e78188abbdd3c8e072c1b |
1 --
2 -- TIMESTAMPTZ
3 --
4 CREATE TABLE TIMESTAMPTZ_TBL (d1 timestamp(2) with time zone);
5 -- Test shorthand input values
6 -- We can't just "select" the results since they aren't constants; test for
7 -- equality instead. We can do that by running the test inside a transaction
8 -- block, within which the value of 'now' shouldn't change, and so these
9 -- related values shouldn't either.
10 BEGIN;
11 INSERT INTO TIMESTAMPTZ_TBL VALUES ('today');
12 INSERT INTO TIMESTAMPTZ_TBL VALUES ('yesterday');
13 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow');
14 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow EST');
15 INSERT INTO TIMESTAMPTZ_TBL VALUES ('tomorrow zulu');
16 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'today';
17 one
18 -----
19 1
20 (1 row)
22 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow';
23 one
24 -----
25 1
26 (1 row)
28 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'yesterday';
29 one
30 -----
31 1
32 (1 row)
34 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow EST';
35 one
36 -----
37 1
38 (1 row)
40 SELECT count(*) AS One FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp with time zone 'tomorrow zulu';
41 one
42 -----
43 1
44 (1 row)
46 COMMIT;
47 DELETE FROM TIMESTAMPTZ_TBL;
48 -- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
49 -- and that it doesn't change over the same interval within a transaction block
50 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
51 SELECT pg_sleep(0.1);
52 pg_sleep
53 ----------
55 (1 row)
57 BEGIN;
58 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
59 SELECT pg_sleep(0.1);
60 pg_sleep
61 ----------
63 (1 row)
65 INSERT INTO TIMESTAMPTZ_TBL VALUES ('now');
66 SELECT pg_sleep(0.1);
67 pg_sleep
68 ----------
70 (1 row)
72 SELECT count(*) AS two FROM TIMESTAMPTZ_TBL WHERE d1 = timestamp(2) with time zone 'now';
73 two
74 -----
75 2
76 (1 row)
78 SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMPTZ_TBL;
79 three | two
80 -------+-----
81 3 | 2
82 (1 row)
84 COMMIT;
85 TRUNCATE TIMESTAMPTZ_TBL;
86 -- Special values
87 INSERT INTO TIMESTAMPTZ_TBL VALUES ('-infinity');
88 INSERT INTO TIMESTAMPTZ_TBL VALUES ('infinity');
89 INSERT INTO TIMESTAMPTZ_TBL VALUES ('epoch');
90 -- Postgres v6.0 standard output format
91 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
92 -- Variations on Postgres v6.1 standard output format
93 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
94 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
95 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
96 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
97 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
98 -- ISO 8601 format
99 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02');
100 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-01-02 03:04:05');
101 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-08');
102 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01-0800');
103 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 -08:00');
104 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 -0800');
105 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 17:32:01 -07:00');
106 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2001-09-22T18:19:20');
107 -- POSIX format (note that the timezone abbrev is just decoration here)
108 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
109 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
110 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
111 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 03:14:04 PST+8');
112 INSERT INTO TIMESTAMPTZ_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
113 -- Variations for acceptable input formats
114 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
115 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
116 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 5:32PM 1997');
117 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997/02/10 17:32:01-0800');
118 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST');
119 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST');
120 INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST');
121 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST');
122 set datestyle to ymd;
123 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
124 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
125 reset datestyle;
126 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
127 -- timestamps at different timezones
128 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 America/New_York');
129 SELECT '19970210 173201' AT TIME ZONE 'America/New_York';
130 timezone
131 --------------------------
132 Mon Feb 10 20:32:01 1997
133 (1 row)
135 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/New_York');
136 SELECT '19970710 173201' AT TIME ZONE 'America/New_York';
137 timezone
138 --------------------------
139 Thu Jul 10 20:32:01 1997
140 (1 row)
142 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America/Does_not_exist');
143 ERROR: time zone "america/does_not_exist" not recognized
144 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970710 173201 America...
145 ^
146 SELECT '19970710 173201' AT TIME ZONE 'America/Does_not_exist';
147 ERROR: time zone "America/Does_not_exist" not recognized
148 -- Daylight saving time for timestamps beyond 32-bit time_t range.
149 SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST
150 timestamptz
151 ------------------------------
152 Sun Jul 10 07:32:01 2050 PDT
153 (1 row)
155 SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST
156 timestamptz
157 ------------------------------
158 Mon Jan 10 07:32:01 2050 PST
159 (1 row)
161 SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST
162 timestamptz
163 --------------------------------
164 Thu Jul 10 07:32:01 205000 PDT
165 (1 row)
167 SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST
168 timestamptz
169 --------------------------------
170 Fri Jan 10 07:32:01 205000 PST
171 (1 row)
173 -- Check date conversion and date arithmetic
174 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');
175 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
176 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 11 17:32:01 1997');
177 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 12 17:32:01 1997');
178 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 13 17:32:01 1997');
179 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 14 17:32:01 1997');
180 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 15 17:32:01 1997');
181 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
182 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097 BC');
183 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0097');
184 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 0597');
185 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1097');
186 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1697');
187 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1797');
188 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1897');
189 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 1997');
190 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 2097');
191 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1996');
192 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1996');
193 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1996');
194 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1996');
195 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1996');
196 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 1997');
197 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 28 17:32:01 1997');
198 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997');
199 ERROR: date/time field value out of range: "Feb 29 17:32:01 1997"
200 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 29 17:32:01 1997');
201 ^
202 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Mar 01 17:32:01 1997');
203 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 30 17:32:01 1997');
204 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1997');
205 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999');
206 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000');
207 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000');
208 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001');
209 -- Currently unsupported syntax and ranges
210 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
211 ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097"
212 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097')...
213 ^
214 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC');
215 ERROR: timestamp out of range: "Feb 16 17:32:01 5097 BC"
216 LINE 1: INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 5097 BC...
217 ^
218 -- Alternative field order that we've historically supported (sort of)
219 -- with regular and POSIXy timezone specs
220 SELECT 'Wed Jul 11 10:51:14 America/New_York 2001'::timestamptz;
221 timestamptz
222 ------------------------------
223 Wed Jul 11 07:51:14 2001 PDT
224 (1 row)
226 SELECT 'Wed Jul 11 10:51:14 GMT-4 2001'::timestamptz;
227 timestamptz
228 ------------------------------
229 Tue Jul 10 23:51:14 2001 PDT
230 (1 row)
232 SELECT 'Wed Jul 11 10:51:14 GMT+4 2001'::timestamptz;
233 timestamptz
234 ------------------------------
235 Wed Jul 11 07:51:14 2001 PDT
236 (1 row)
238 SELECT 'Wed Jul 11 10:51:14 PST-03:00 2001'::timestamptz;
239 timestamptz
240 ------------------------------
241 Wed Jul 11 00:51:14 2001 PDT
242 (1 row)
244 SELECT 'Wed Jul 11 10:51:14 PST+03:00 2001'::timestamptz;
245 timestamptz
246 ------------------------------
247 Wed Jul 11 06:51:14 2001 PDT
248 (1 row)
250 SELECT d1 FROM TIMESTAMPTZ_TBL;
251 d1
252 ---------------------------------
253 -infinity
254 infinity
255 Wed Dec 31 16:00:00 1969 PST
256 Mon Feb 10 17:32:01 1997 PST
257 Mon Feb 10 17:32:01 1997 PST
258 Mon Feb 10 17:32:02 1997 PST
259 Mon Feb 10 17:32:01.4 1997 PST
260 Mon Feb 10 17:32:01.5 1997 PST
261 Mon Feb 10 17:32:01.6 1997 PST
262 Thu Jan 02 00:00:00 1997 PST
263 Thu Jan 02 03:04:05 1997 PST
264 Mon Feb 10 17:32:01 1997 PST
265 Mon Feb 10 17:32:01 1997 PST
266 Mon Feb 10 17:32:01 1997 PST
267 Mon Feb 10 17:32:01 1997 PST
268 Tue Jun 10 17:32:01 1997 PDT
269 Sat Sep 22 18:19:20 2001 PDT
270 Wed Mar 15 08:14:01 2000 PST
271 Wed Mar 15 04:14:02 2000 PST
272 Wed Mar 15 02:14:03 2000 PST
273 Wed Mar 15 03:14:04 2000 PST
274 Wed Mar 15 01:14:05 2000 PST
275 Mon Feb 10 17:32:01 1997 PST
276 Mon Feb 10 17:32:01 1997 PST
277 Mon Feb 10 17:32:00 1997 PST
278 Mon Feb 10 17:32:01 1997 PST
279 Mon Feb 10 17:32:01 1997 PST
280 Mon Feb 10 17:32:01 1997 PST
281 Mon Feb 10 17:32:01 1997 PST
282 Mon Feb 10 17:32:01 1997 PST
283 Mon Feb 10 09:32:01 1997 PST
284 Mon Feb 10 09:32:01 1997 PST
285 Mon Feb 10 09:32:01 1997 PST
286 Mon Feb 10 14:32:01 1997 PST
287 Thu Jul 10 14:32:01 1997 PDT
288 Tue Jun 10 18:32:01 1997 PDT
289 Mon Feb 10 17:32:01 1997 PST
290 Tue Feb 11 17:32:01 1997 PST
291 Wed Feb 12 17:32:01 1997 PST
292 Thu Feb 13 17:32:01 1997 PST
293 Fri Feb 14 17:32:01 1997 PST
294 Sat Feb 15 17:32:01 1997 PST
295 Sun Feb 16 17:32:01 1997 PST
296 Tue Feb 16 17:32:01 0097 PST BC
297 Sat Feb 16 17:32:01 0097 PST
298 Thu Feb 16 17:32:01 0597 PST
299 Tue Feb 16 17:32:01 1097 PST
300 Sat Feb 16 17:32:01 1697 PST
301 Thu Feb 16 17:32:01 1797 PST
302 Tue Feb 16 17:32:01 1897 PST
303 Sun Feb 16 17:32:01 1997 PST
304 Sat Feb 16 17:32:01 2097 PST
305 Wed Feb 28 17:32:01 1996 PST
306 Thu Feb 29 17:32:01 1996 PST
307 Fri Mar 01 17:32:01 1996 PST
308 Mon Dec 30 17:32:01 1996 PST
309 Tue Dec 31 17:32:01 1996 PST
310 Wed Jan 01 17:32:01 1997 PST
311 Fri Feb 28 17:32:01 1997 PST
312 Sat Mar 01 17:32:01 1997 PST
313 Tue Dec 30 17:32:01 1997 PST
314 Wed Dec 31 17:32:01 1997 PST
315 Fri Dec 31 17:32:01 1999 PST
316 Sat Jan 01 17:32:01 2000 PST
317 Sun Dec 31 17:32:01 2000 PST
318 Mon Jan 01 17:32:01 2001 PST
319 (66 rows)
321 -- Check behavior at the boundaries of the timestamp range
322 SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
323 timestamptz
324 ---------------------------------
325 Sun Nov 23 16:00:00 4714 PST BC
326 (1 row)
328 SELECT '4714-11-23 16:00:00-08 BC'::timestamptz;
329 timestamptz
330 ---------------------------------
331 Sun Nov 23 16:00:00 4714 PST BC
332 (1 row)
334 SELECT 'Sun Nov 23 16:00:00 4714 PST BC'::timestamptz;
335 timestamptz
336 ---------------------------------
337 Sun Nov 23 16:00:00 4714 PST BC
338 (1 row)
340 SELECT '4714-11-23 23:59:59+00 BC'::timestamptz; -- out of range
341 ERROR: timestamp out of range: "4714-11-23 23:59:59+00 BC"
342 LINE 1: SELECT '4714-11-23 23:59:59+00 BC'::timestamptz;
343 ^
344 SELECT '294276-12-31 23:59:59+00'::timestamptz;
345 timestamptz
346 --------------------------------
347 Sun Dec 31 15:59:59 294276 PST
348 (1 row)
350 SELECT '294276-12-31 15:59:59-08'::timestamptz;
351 timestamptz
352 --------------------------------
353 Sun Dec 31 15:59:59 294276 PST
354 (1 row)
356 SELECT '294277-01-01 00:00:00+00'::timestamptz; -- out of range
357 ERROR: timestamp out of range: "294277-01-01 00:00:00+00"
358 LINE 1: SELECT '294277-01-01 00:00:00+00'::timestamptz;
359 ^
360 SELECT '294277-12-31 16:00:00-08'::timestamptz; -- out of range
361 ERROR: timestamp out of range: "294277-12-31 16:00:00-08"
362 LINE 1: SELECT '294277-12-31 16:00:00-08'::timestamptz;
363 ^
364 -- Demonstrate functions and operators
365 SELECT d1 FROM TIMESTAMPTZ_TBL
366 WHERE d1 > timestamp with time zone '1997-01-02';
367 d1
368 --------------------------------
369 infinity
370 Mon Feb 10 17:32:01 1997 PST
371 Mon Feb 10 17:32:01 1997 PST
372 Mon Feb 10 17:32:02 1997 PST
373 Mon Feb 10 17:32:01.4 1997 PST
374 Mon Feb 10 17:32:01.5 1997 PST
375 Mon Feb 10 17:32:01.6 1997 PST
376 Thu Jan 02 03:04:05 1997 PST
377 Mon Feb 10 17:32:01 1997 PST
378 Mon Feb 10 17:32:01 1997 PST
379 Mon Feb 10 17:32:01 1997 PST
380 Mon Feb 10 17:32:01 1997 PST
381 Tue Jun 10 17:32:01 1997 PDT
382 Sat Sep 22 18:19:20 2001 PDT
383 Wed Mar 15 08:14:01 2000 PST
384 Wed Mar 15 04:14:02 2000 PST
385 Wed Mar 15 02:14:03 2000 PST
386 Wed Mar 15 03:14:04 2000 PST
387 Wed Mar 15 01:14:05 2000 PST
388 Mon Feb 10 17:32:01 1997 PST
389 Mon Feb 10 17:32:01 1997 PST
390 Mon Feb 10 17:32:00 1997 PST
391 Mon Feb 10 17:32:01 1997 PST
392 Mon Feb 10 17:32:01 1997 PST
393 Mon Feb 10 17:32:01 1997 PST
394 Mon Feb 10 17:32:01 1997 PST
395 Mon Feb 10 17:32:01 1997 PST
396 Mon Feb 10 09:32:01 1997 PST
397 Mon Feb 10 09:32:01 1997 PST
398 Mon Feb 10 09:32:01 1997 PST
399 Mon Feb 10 14:32:01 1997 PST
400 Thu Jul 10 14:32:01 1997 PDT
401 Tue Jun 10 18:32:01 1997 PDT
402 Mon Feb 10 17:32:01 1997 PST
403 Tue Feb 11 17:32:01 1997 PST
404 Wed Feb 12 17:32:01 1997 PST
405 Thu Feb 13 17:32:01 1997 PST
406 Fri Feb 14 17:32:01 1997 PST
407 Sat Feb 15 17:32:01 1997 PST
408 Sun Feb 16 17:32:01 1997 PST
409 Sun Feb 16 17:32:01 1997 PST
410 Sat Feb 16 17:32:01 2097 PST
411 Fri Feb 28 17:32:01 1997 PST
412 Sat Mar 01 17:32:01 1997 PST
413 Tue Dec 30 17:32:01 1997 PST
414 Wed Dec 31 17:32:01 1997 PST
415 Fri Dec 31 17:32:01 1999 PST
416 Sat Jan 01 17:32:01 2000 PST
417 Sun Dec 31 17:32:01 2000 PST
418 Mon Jan 01 17:32:01 2001 PST
419 (50 rows)
421 SELECT d1 FROM TIMESTAMPTZ_TBL
422 WHERE d1 < timestamp with time zone '1997-01-02';
423 d1
424 ---------------------------------
425 -infinity
426 Wed Dec 31 16:00:00 1969 PST
427 Tue Feb 16 17:32:01 0097 PST BC
428 Sat Feb 16 17:32:01 0097 PST
429 Thu Feb 16 17:32:01 0597 PST
430 Tue Feb 16 17:32:01 1097 PST
431 Sat Feb 16 17:32:01 1697 PST
432 Thu Feb 16 17:32:01 1797 PST
433 Tue Feb 16 17:32:01 1897 PST
434 Wed Feb 28 17:32:01 1996 PST
435 Thu Feb 29 17:32:01 1996 PST
436 Fri Mar 01 17:32:01 1996 PST
437 Mon Dec 30 17:32:01 1996 PST
438 Tue Dec 31 17:32:01 1996 PST
439 Wed Jan 01 17:32:01 1997 PST
440 (15 rows)
442 SELECT d1 FROM TIMESTAMPTZ_TBL
443 WHERE d1 = timestamp with time zone '1997-01-02';
444 d1
445 ------------------------------
446 Thu Jan 02 00:00:00 1997 PST
447 (1 row)
449 SELECT d1 FROM TIMESTAMPTZ_TBL
450 WHERE d1 != timestamp with time zone '1997-01-02';
451 d1
452 ---------------------------------
453 -infinity
454 infinity
455 Wed Dec 31 16:00:00 1969 PST
456 Mon Feb 10 17:32:01 1997 PST
457 Mon Feb 10 17:32:01 1997 PST
458 Mon Feb 10 17:32:02 1997 PST
459 Mon Feb 10 17:32:01.4 1997 PST
460 Mon Feb 10 17:32:01.5 1997 PST
461 Mon Feb 10 17:32:01.6 1997 PST
462 Thu Jan 02 03:04:05 1997 PST
463 Mon Feb 10 17:32:01 1997 PST
464 Mon Feb 10 17:32:01 1997 PST
465 Mon Feb 10 17:32:01 1997 PST
466 Mon Feb 10 17:32:01 1997 PST
467 Tue Jun 10 17:32:01 1997 PDT
468 Sat Sep 22 18:19:20 2001 PDT
469 Wed Mar 15 08:14:01 2000 PST
470 Wed Mar 15 04:14:02 2000 PST
471 Wed Mar 15 02:14:03 2000 PST
472 Wed Mar 15 03:14:04 2000 PST
473 Wed Mar 15 01:14:05 2000 PST
474 Mon Feb 10 17:32:01 1997 PST
475 Mon Feb 10 17:32:01 1997 PST
476 Mon Feb 10 17:32:00 1997 PST
477 Mon Feb 10 17:32:01 1997 PST
478 Mon Feb 10 17:32:01 1997 PST
479 Mon Feb 10 17:32:01 1997 PST
480 Mon Feb 10 17:32:01 1997 PST
481 Mon Feb 10 17:32:01 1997 PST
482 Mon Feb 10 09:32:01 1997 PST
483 Mon Feb 10 09:32:01 1997 PST
484 Mon Feb 10 09:32:01 1997 PST
485 Mon Feb 10 14:32:01 1997 PST
486 Thu Jul 10 14:32:01 1997 PDT
487 Tue Jun 10 18:32:01 1997 PDT
488 Mon Feb 10 17:32:01 1997 PST
489 Tue Feb 11 17:32:01 1997 PST
490 Wed Feb 12 17:32:01 1997 PST
491 Thu Feb 13 17:32:01 1997 PST
492 Fri Feb 14 17:32:01 1997 PST
493 Sat Feb 15 17:32:01 1997 PST
494 Sun Feb 16 17:32:01 1997 PST
495 Tue Feb 16 17:32:01 0097 PST BC
496 Sat Feb 16 17:32:01 0097 PST
497 Thu Feb 16 17:32:01 0597 PST
498 Tue Feb 16 17:32:01 1097 PST
499 Sat Feb 16 17:32:01 1697 PST
500 Thu Feb 16 17:32:01 1797 PST
501 Tue Feb 16 17:32:01 1897 PST
502 Sun Feb 16 17:32:01 1997 PST
503 Sat Feb 16 17:32:01 2097 PST
504 Wed Feb 28 17:32:01 1996 PST
505 Thu Feb 29 17:32:01 1996 PST
506 Fri Mar 01 17:32:01 1996 PST
507 Mon Dec 30 17:32:01 1996 PST
508 Tue Dec 31 17:32:01 1996 PST
509 Wed Jan 01 17:32:01 1997 PST
510 Fri Feb 28 17:32:01 1997 PST
511 Sat Mar 01 17:32:01 1997 PST
512 Tue Dec 30 17:32:01 1997 PST
513 Wed Dec 31 17:32:01 1997 PST
514 Fri Dec 31 17:32:01 1999 PST
515 Sat Jan 01 17:32:01 2000 PST
516 Sun Dec 31 17:32:01 2000 PST
517 Mon Jan 01 17:32:01 2001 PST
518 (65 rows)
520 SELECT d1 FROM TIMESTAMPTZ_TBL
521 WHERE d1 <= timestamp with time zone '1997-01-02';
522 d1
523 ---------------------------------
524 -infinity
525 Wed Dec 31 16:00:00 1969 PST
526 Thu Jan 02 00:00:00 1997 PST
527 Tue Feb 16 17:32:01 0097 PST BC
528 Sat Feb 16 17:32:01 0097 PST
529 Thu Feb 16 17:32:01 0597 PST
530 Tue Feb 16 17:32:01 1097 PST
531 Sat Feb 16 17:32:01 1697 PST
532 Thu Feb 16 17:32:01 1797 PST
533 Tue Feb 16 17:32:01 1897 PST
534 Wed Feb 28 17:32:01 1996 PST
535 Thu Feb 29 17:32:01 1996 PST
536 Fri Mar 01 17:32:01 1996 PST
537 Mon Dec 30 17:32:01 1996 PST
538 Tue Dec 31 17:32:01 1996 PST
539 Wed Jan 01 17:32:01 1997 PST
540 (16 rows)
542 SELECT d1 FROM TIMESTAMPTZ_TBL
543 WHERE d1 >= timestamp with time zone '1997-01-02';
544 d1
545 --------------------------------
546 infinity
547 Mon Feb 10 17:32:01 1997 PST
548 Mon Feb 10 17:32:01 1997 PST
549 Mon Feb 10 17:32:02 1997 PST
550 Mon Feb 10 17:32:01.4 1997 PST
551 Mon Feb 10 17:32:01.5 1997 PST
552 Mon Feb 10 17:32:01.6 1997 PST
553 Thu Jan 02 00:00:00 1997 PST
554 Thu Jan 02 03:04:05 1997 PST
555 Mon Feb 10 17:32:01 1997 PST
556 Mon Feb 10 17:32:01 1997 PST
557 Mon Feb 10 17:32:01 1997 PST
558 Mon Feb 10 17:32:01 1997 PST
559 Tue Jun 10 17:32:01 1997 PDT
560 Sat Sep 22 18:19:20 2001 PDT
561 Wed Mar 15 08:14:01 2000 PST
562 Wed Mar 15 04:14:02 2000 PST
563 Wed Mar 15 02:14:03 2000 PST
564 Wed Mar 15 03:14:04 2000 PST
565 Wed Mar 15 01:14:05 2000 PST
566 Mon Feb 10 17:32:01 1997 PST
567 Mon Feb 10 17:32:01 1997 PST
568 Mon Feb 10 17:32:00 1997 PST
569 Mon Feb 10 17:32:01 1997 PST
570 Mon Feb 10 17:32:01 1997 PST
571 Mon Feb 10 17:32:01 1997 PST
572 Mon Feb 10 17:32:01 1997 PST
573 Mon Feb 10 17:32:01 1997 PST
574 Mon Feb 10 09:32:01 1997 PST
575 Mon Feb 10 09:32:01 1997 PST
576 Mon Feb 10 09:32:01 1997 PST
577 Mon Feb 10 14:32:01 1997 PST
578 Thu Jul 10 14:32:01 1997 PDT
579 Tue Jun 10 18:32:01 1997 PDT
580 Mon Feb 10 17:32:01 1997 PST
581 Tue Feb 11 17:32:01 1997 PST
582 Wed Feb 12 17:32:01 1997 PST
583 Thu Feb 13 17:32:01 1997 PST
584 Fri Feb 14 17:32:01 1997 PST
585 Sat Feb 15 17:32:01 1997 PST
586 Sun Feb 16 17:32:01 1997 PST
587 Sun Feb 16 17:32:01 1997 PST
588 Sat Feb 16 17:32:01 2097 PST
589 Fri Feb 28 17:32:01 1997 PST
590 Sat Mar 01 17:32:01 1997 PST
591 Tue Dec 30 17:32:01 1997 PST
592 Wed Dec 31 17:32:01 1997 PST
593 Fri Dec 31 17:32:01 1999 PST
594 Sat Jan 01 17:32:01 2000 PST
595 Sun Dec 31 17:32:01 2000 PST
596 Mon Jan 01 17:32:01 2001 PST
597 (51 rows)
599 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
600 FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
601 diff
602 ----------------------------------------
603 @ 9863 days 8 hours ago
604 @ 39 days 17 hours 32 mins 1 sec
605 @ 39 days 17 hours 32 mins 1 sec
606 @ 39 days 17 hours 32 mins 2 secs
607 @ 39 days 17 hours 32 mins 1.4 secs
608 @ 39 days 17 hours 32 mins 1.5 secs
609 @ 39 days 17 hours 32 mins 1.6 secs
610 @ 0
611 @ 3 hours 4 mins 5 secs
612 @ 39 days 17 hours 32 mins 1 sec
613 @ 39 days 17 hours 32 mins 1 sec
614 @ 39 days 17 hours 32 mins 1 sec
615 @ 39 days 17 hours 32 mins 1 sec
616 @ 159 days 16 hours 32 mins 1 sec
617 @ 1724 days 17 hours 19 mins 20 secs
618 @ 1168 days 8 hours 14 mins 1 sec
619 @ 1168 days 4 hours 14 mins 2 secs
620 @ 1168 days 2 hours 14 mins 3 secs
621 @ 1168 days 3 hours 14 mins 4 secs
622 @ 1168 days 1 hour 14 mins 5 secs
623 @ 39 days 17 hours 32 mins 1 sec
624 @ 39 days 17 hours 32 mins 1 sec
625 @ 39 days 17 hours 32 mins
626 @ 39 days 17 hours 32 mins 1 sec
627 @ 39 days 17 hours 32 mins 1 sec
628 @ 39 days 17 hours 32 mins 1 sec
629 @ 39 days 17 hours 32 mins 1 sec
630 @ 39 days 17 hours 32 mins 1 sec
631 @ 39 days 9 hours 32 mins 1 sec
632 @ 39 days 9 hours 32 mins 1 sec
633 @ 39 days 9 hours 32 mins 1 sec
634 @ 39 days 14 hours 32 mins 1 sec
635 @ 189 days 13 hours 32 mins 1 sec
636 @ 159 days 17 hours 32 mins 1 sec
637 @ 39 days 17 hours 32 mins 1 sec
638 @ 40 days 17 hours 32 mins 1 sec
639 @ 41 days 17 hours 32 mins 1 sec
640 @ 42 days 17 hours 32 mins 1 sec
641 @ 43 days 17 hours 32 mins 1 sec
642 @ 44 days 17 hours 32 mins 1 sec
643 @ 45 days 17 hours 32 mins 1 sec
644 @ 45 days 17 hours 32 mins 1 sec
645 @ 308 days 6 hours 27 mins 59 secs ago
646 @ 307 days 6 hours 27 mins 59 secs ago
647 @ 306 days 6 hours 27 mins 59 secs ago
648 @ 2 days 6 hours 27 mins 59 secs ago
649 @ 1 day 6 hours 27 mins 59 secs ago
650 @ 6 hours 27 mins 59 secs ago
651 @ 57 days 17 hours 32 mins 1 sec
652 @ 58 days 17 hours 32 mins 1 sec
653 @ 362 days 17 hours 32 mins 1 sec
654 @ 363 days 17 hours 32 mins 1 sec
655 @ 1093 days 17 hours 32 mins 1 sec
656 @ 1094 days 17 hours 32 mins 1 sec
657 @ 1459 days 17 hours 32 mins 1 sec
658 @ 1460 days 17 hours 32 mins 1 sec
659 (56 rows)
661 SELECT date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
662 week_trunc
663 ------------------------------
664 Mon Feb 23 00:00:00 2004 PST
665 (1 row)
667 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
668 sydney_trunc
669 ------------------------------
670 Fri Feb 16 05:00:00 2001 PST
671 (1 row)
673 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
674 gmt_trunc
675 ------------------------------
676 Thu Feb 15 16:00:00 2001 PST
677 (1 row)
679 SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
680 vet_trunc
681 ------------------------------
682 Thu Feb 15 20:00:00 2001 PST
683 (1 row)
685 -- verify date_bin behaves the same as date_trunc for relevant intervals
686 SELECT
687 str,
688 interval,
689 date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS equal
690 FROM (
691 VALUES
692 ('day', '1 d'),
693 ('hour', '1 h'),
694 ('minute', '1 m'),
695 ('second', '1 s'),
696 ('millisecond', '1 ms'),
697 ('microsecond', '1 us')
698 ) intervals (str, interval),
699 (VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
700 str | interval | equal
701 -------------+----------+-------
702 day | 1 d | t
703 hour | 1 h | t
704 minute | 1 m | t
705 second | 1 s | t
706 millisecond | 1 ms | t
707 microsecond | 1 us | t
708 (6 rows)
710 -- bin timestamps into arbitrary intervals
711 SELECT
712 interval,
713 ts,
714 origin,
715 date_bin(interval::interval, ts, origin)
716 FROM (
717 VALUES
718 ('15 days'),
719 ('2 hours'),
720 ('1 hour 30 minutes'),
721 ('15 minutes'),
722 ('10 seconds'),
723 ('100 milliseconds'),
724 ('250 microseconds')
725 ) intervals (interval),
726 (VALUES (timestamptz '2020-02-11 15:44:17.71393')) ts (ts),
727 (VALUES (timestamptz '2001-01-01')) origin (origin);
728 interval | ts | origin | date_bin
729 -------------------+------------------------------------+------------------------------+------------------------------------
730 15 days | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Thu Feb 06 00:00:00 2020 PST
731 2 hours | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 14:00:00 2020 PST
732 1 hour 30 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:00:00 2020 PST
733 15 minutes | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:30:00 2020 PST
734 10 seconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:10 2020 PST
735 100 milliseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.7 2020 PST
736 250 microseconds | Tue Feb 11 15:44:17.71393 2020 PST | Mon Jan 01 00:00:00 2001 PST | Tue Feb 11 15:44:17.71375 2020 PST
737 (7 rows)
739 -- shift bins using the origin parameter:
740 SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00');
741 date_bin
742 ------------------------------
743 Fri Jan 31 16:57:30 2020 PST
744 (1 row)
746 -- disallow intervals with months or years
747 SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
748 ERROR: timestamps cannot be binned into intervals containing months or years
749 SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
750 ERROR: timestamps cannot be binned into intervals containing months or years
751 -- disallow zero intervals
752 SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
753 ERROR: stride must be greater than zero
754 -- disallow negative intervals
755 SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
756 ERROR: stride must be greater than zero
757 -- Test casting within a BETWEEN qualifier
758 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
759 FROM TIMESTAMPTZ_TBL
760 WHERE d1 BETWEEN timestamp with time zone '1902-01-01' AND timestamp with time zone '2038-01-01';
761 diff
762 ----------------------------------------
763 @ 9863 days 8 hours ago
764 @ 39 days 17 hours 32 mins 1 sec
765 @ 39 days 17 hours 32 mins 1 sec
766 @ 39 days 17 hours 32 mins 2 secs
767 @ 39 days 17 hours 32 mins 1.4 secs
768 @ 39 days 17 hours 32 mins 1.5 secs
769 @ 39 days 17 hours 32 mins 1.6 secs
770 @ 0
771 @ 3 hours 4 mins 5 secs
772 @ 39 days 17 hours 32 mins 1 sec
773 @ 39 days 17 hours 32 mins 1 sec
774 @ 39 days 17 hours 32 mins 1 sec
775 @ 39 days 17 hours 32 mins 1 sec
776 @ 159 days 16 hours 32 mins 1 sec
777 @ 1724 days 17 hours 19 mins 20 secs
778 @ 1168 days 8 hours 14 mins 1 sec
779 @ 1168 days 4 hours 14 mins 2 secs
780 @ 1168 days 2 hours 14 mins 3 secs
781 @ 1168 days 3 hours 14 mins 4 secs
782 @ 1168 days 1 hour 14 mins 5 secs
783 @ 39 days 17 hours 32 mins 1 sec
784 @ 39 days 17 hours 32 mins 1 sec
785 @ 39 days 17 hours 32 mins
786 @ 39 days 17 hours 32 mins 1 sec
787 @ 39 days 17 hours 32 mins 1 sec
788 @ 39 days 17 hours 32 mins 1 sec
789 @ 39 days 17 hours 32 mins 1 sec
790 @ 39 days 17 hours 32 mins 1 sec
791 @ 39 days 9 hours 32 mins 1 sec
792 @ 39 days 9 hours 32 mins 1 sec
793 @ 39 days 9 hours 32 mins 1 sec
794 @ 39 days 14 hours 32 mins 1 sec
795 @ 189 days 13 hours 32 mins 1 sec
796 @ 159 days 17 hours 32 mins 1 sec
797 @ 39 days 17 hours 32 mins 1 sec
798 @ 40 days 17 hours 32 mins 1 sec
799 @ 41 days 17 hours 32 mins 1 sec
800 @ 42 days 17 hours 32 mins 1 sec
801 @ 43 days 17 hours 32 mins 1 sec
802 @ 44 days 17 hours 32 mins 1 sec
803 @ 45 days 17 hours 32 mins 1 sec
804 @ 45 days 17 hours 32 mins 1 sec
805 @ 308 days 6 hours 27 mins 59 secs ago
806 @ 307 days 6 hours 27 mins 59 secs ago
807 @ 306 days 6 hours 27 mins 59 secs ago
808 @ 2 days 6 hours 27 mins 59 secs ago
809 @ 1 day 6 hours 27 mins 59 secs ago
810 @ 6 hours 27 mins 59 secs ago
811 @ 57 days 17 hours 32 mins 1 sec
812 @ 58 days 17 hours 32 mins 1 sec
813 @ 362 days 17 hours 32 mins 1 sec
814 @ 363 days 17 hours 32 mins 1 sec
815 @ 1093 days 17 hours 32 mins 1 sec
816 @ 1094 days 17 hours 32 mins 1 sec
817 @ 1459 days 17 hours 32 mins 1 sec
818 @ 1460 days 17 hours 32 mins 1 sec
819 (56 rows)
821 -- DATE_PART (timestamptz_part)
822 SELECT d1 as timestamptz,
823 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
824 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
825 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
826 FROM TIMESTAMPTZ_TBL;
827 timestamptz | year | month | day | hour | minute | second
828 ---------------------------------+-----------+-------+-----+------+--------+--------
829 -infinity | -Infinity | | | | |
830 infinity | Infinity | | | | |
831 Wed Dec 31 16:00:00 1969 PST | 1969 | 12 | 31 | 16 | 0 | 0
832 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
833 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
834 Mon Feb 10 17:32:02 1997 PST | 1997 | 2 | 10 | 17 | 32 | 2
835 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4
836 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5
837 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6
838 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0
839 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5
840 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
841 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
842 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
843 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
844 Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1
845 Sat Sep 22 18:19:20 2001 PDT | 2001 | 9 | 22 | 18 | 19 | 20
846 Wed Mar 15 08:14:01 2000 PST | 2000 | 3 | 15 | 8 | 14 | 1
847 Wed Mar 15 04:14:02 2000 PST | 2000 | 3 | 15 | 4 | 14 | 2
848 Wed Mar 15 02:14:03 2000 PST | 2000 | 3 | 15 | 2 | 14 | 3
849 Wed Mar 15 03:14:04 2000 PST | 2000 | 3 | 15 | 3 | 14 | 4
850 Wed Mar 15 01:14:05 2000 PST | 2000 | 3 | 15 | 1 | 14 | 5
851 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
852 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
853 Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0
854 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
855 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
856 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
857 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
858 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
859 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
860 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
861 Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1
862 Mon Feb 10 14:32:01 1997 PST | 1997 | 2 | 10 | 14 | 32 | 1
863 Thu Jul 10 14:32:01 1997 PDT | 1997 | 7 | 10 | 14 | 32 | 1
864 Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1
865 Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1
866 Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1
867 Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1
868 Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1
869 Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1
870 Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1
871 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
872 Tue Feb 16 17:32:01 0097 PST BC | -97 | 2 | 16 | 17 | 32 | 1
873 Sat Feb 16 17:32:01 0097 PST | 97 | 2 | 16 | 17 | 32 | 1
874 Thu Feb 16 17:32:01 0597 PST | 597 | 2 | 16 | 17 | 32 | 1
875 Tue Feb 16 17:32:01 1097 PST | 1097 | 2 | 16 | 17 | 32 | 1
876 Sat Feb 16 17:32:01 1697 PST | 1697 | 2 | 16 | 17 | 32 | 1
877 Thu Feb 16 17:32:01 1797 PST | 1797 | 2 | 16 | 17 | 32 | 1
878 Tue Feb 16 17:32:01 1897 PST | 1897 | 2 | 16 | 17 | 32 | 1
879 Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1
880 Sat Feb 16 17:32:01 2097 PST | 2097 | 2 | 16 | 17 | 32 | 1
881 Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1
882 Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1
883 Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1
884 Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1
885 Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1
886 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1
887 Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1
888 Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1
889 Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1
890 Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1
891 Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1
892 Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
893 Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
894 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
895 (66 rows)
897 SELECT d1 as timestamptz,
898 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
899 date_part( 'usec', d1) AS usec
900 FROM TIMESTAMPTZ_TBL;
901 timestamptz | quarter | msec | usec
902 ---------------------------------+---------+-------+----------
903 -infinity | | |
904 infinity | | |
905 Wed Dec 31 16:00:00 1969 PST | 4 | 0 | 0
906 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
907 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
908 Mon Feb 10 17:32:02 1997 PST | 1 | 2000 | 2000000
909 Mon Feb 10 17:32:01.4 1997 PST | 1 | 1400 | 1400000
910 Mon Feb 10 17:32:01.5 1997 PST | 1 | 1500 | 1500000
911 Mon Feb 10 17:32:01.6 1997 PST | 1 | 1600 | 1600000
912 Thu Jan 02 00:00:00 1997 PST | 1 | 0 | 0
913 Thu Jan 02 03:04:05 1997 PST | 1 | 5000 | 5000000
914 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
915 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
916 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
917 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
918 Tue Jun 10 17:32:01 1997 PDT | 2 | 1000 | 1000000
919 Sat Sep 22 18:19:20 2001 PDT | 3 | 20000 | 20000000
920 Wed Mar 15 08:14:01 2000 PST | 1 | 1000 | 1000000
921 Wed Mar 15 04:14:02 2000 PST | 1 | 2000 | 2000000
922 Wed Mar 15 02:14:03 2000 PST | 1 | 3000 | 3000000
923 Wed Mar 15 03:14:04 2000 PST | 1 | 4000 | 4000000
924 Wed Mar 15 01:14:05 2000 PST | 1 | 5000 | 5000000
925 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
926 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
927 Mon Feb 10 17:32:00 1997 PST | 1 | 0 | 0
928 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
929 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
930 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
931 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
932 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
933 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
934 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
935 Mon Feb 10 09:32:01 1997 PST | 1 | 1000 | 1000000
936 Mon Feb 10 14:32:01 1997 PST | 1 | 1000 | 1000000
937 Thu Jul 10 14:32:01 1997 PDT | 3 | 1000 | 1000000
938 Tue Jun 10 18:32:01 1997 PDT | 2 | 1000 | 1000000
939 Mon Feb 10 17:32:01 1997 PST | 1 | 1000 | 1000000
940 Tue Feb 11 17:32:01 1997 PST | 1 | 1000 | 1000000
941 Wed Feb 12 17:32:01 1997 PST | 1 | 1000 | 1000000
942 Thu Feb 13 17:32:01 1997 PST | 1 | 1000 | 1000000
943 Fri Feb 14 17:32:01 1997 PST | 1 | 1000 | 1000000
944 Sat Feb 15 17:32:01 1997 PST | 1 | 1000 | 1000000
945 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
946 Tue Feb 16 17:32:01 0097 PST BC | 1 | 1000 | 1000000
947 Sat Feb 16 17:32:01 0097 PST | 1 | 1000 | 1000000
948 Thu Feb 16 17:32:01 0597 PST | 1 | 1000 | 1000000
949 Tue Feb 16 17:32:01 1097 PST | 1 | 1000 | 1000000
950 Sat Feb 16 17:32:01 1697 PST | 1 | 1000 | 1000000
951 Thu Feb 16 17:32:01 1797 PST | 1 | 1000 | 1000000
952 Tue Feb 16 17:32:01 1897 PST | 1 | 1000 | 1000000
953 Sun Feb 16 17:32:01 1997 PST | 1 | 1000 | 1000000
954 Sat Feb 16 17:32:01 2097 PST | 1 | 1000 | 1000000
955 Wed Feb 28 17:32:01 1996 PST | 1 | 1000 | 1000000
956 Thu Feb 29 17:32:01 1996 PST | 1 | 1000 | 1000000
957 Fri Mar 01 17:32:01 1996 PST | 1 | 1000 | 1000000
958 Mon Dec 30 17:32:01 1996 PST | 4 | 1000 | 1000000
959 Tue Dec 31 17:32:01 1996 PST | 4 | 1000 | 1000000
960 Wed Jan 01 17:32:01 1997 PST | 1 | 1000 | 1000000
961 Fri Feb 28 17:32:01 1997 PST | 1 | 1000 | 1000000
962 Sat Mar 01 17:32:01 1997 PST | 1 | 1000 | 1000000
963 Tue Dec 30 17:32:01 1997 PST | 4 | 1000 | 1000000
964 Wed Dec 31 17:32:01 1997 PST | 4 | 1000 | 1000000
965 Fri Dec 31 17:32:01 1999 PST | 4 | 1000 | 1000000
966 Sat Jan 01 17:32:01 2000 PST | 1 | 1000 | 1000000
967 Sun Dec 31 17:32:01 2000 PST | 4 | 1000 | 1000000
968 Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000
969 (66 rows)
971 SELECT d1 as timestamptz,
972 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
973 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
974 date_part( 'doy', d1) AS doy
975 FROM TIMESTAMPTZ_TBL;
976 timestamptz | isoyear | week | isodow | dow | doy
977 ---------------------------------+-----------+------+--------+-----+-----
978 -infinity | -Infinity | | | |
979 infinity | Infinity | | | |
980 Wed Dec 31 16:00:00 1969 PST | 1970 | 1 | 3 | 3 | 365
981 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
982 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
983 Mon Feb 10 17:32:02 1997 PST | 1997 | 7 | 1 | 1 | 41
984 Mon Feb 10 17:32:01.4 1997 PST | 1997 | 7 | 1 | 1 | 41
985 Mon Feb 10 17:32:01.5 1997 PST | 1997 | 7 | 1 | 1 | 41
986 Mon Feb 10 17:32:01.6 1997 PST | 1997 | 7 | 1 | 1 | 41
987 Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 4 | 4 | 2
988 Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 4 | 4 | 2
989 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
990 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
991 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
992 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
993 Tue Jun 10 17:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
994 Sat Sep 22 18:19:20 2001 PDT | 2001 | 38 | 6 | 6 | 265
995 Wed Mar 15 08:14:01 2000 PST | 2000 | 11 | 3 | 3 | 75
996 Wed Mar 15 04:14:02 2000 PST | 2000 | 11 | 3 | 3 | 75
997 Wed Mar 15 02:14:03 2000 PST | 2000 | 11 | 3 | 3 | 75
998 Wed Mar 15 03:14:04 2000 PST | 2000 | 11 | 3 | 3 | 75
999 Wed Mar 15 01:14:05 2000 PST | 2000 | 11 | 3 | 3 | 75
1000 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1001 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1002 Mon Feb 10 17:32:00 1997 PST | 1997 | 7 | 1 | 1 | 41
1003 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1004 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1005 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1006 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1007 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1008 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1009 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1010 Mon Feb 10 09:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1011 Mon Feb 10 14:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1012 Thu Jul 10 14:32:01 1997 PDT | 1997 | 28 | 4 | 4 | 191
1013 Tue Jun 10 18:32:01 1997 PDT | 1997 | 24 | 2 | 2 | 161
1014 Mon Feb 10 17:32:01 1997 PST | 1997 | 7 | 1 | 1 | 41
1015 Tue Feb 11 17:32:01 1997 PST | 1997 | 7 | 2 | 2 | 42
1016 Wed Feb 12 17:32:01 1997 PST | 1997 | 7 | 3 | 3 | 43
1017 Thu Feb 13 17:32:01 1997 PST | 1997 | 7 | 4 | 4 | 44
1018 Fri Feb 14 17:32:01 1997 PST | 1997 | 7 | 5 | 5 | 45
1019 Sat Feb 15 17:32:01 1997 PST | 1997 | 7 | 6 | 6 | 46
1020 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1021 Tue Feb 16 17:32:01 0097 PST BC | -97 | 7 | 2 | 2 | 47
1022 Sat Feb 16 17:32:01 0097 PST | 97 | 7 | 6 | 6 | 47
1023 Thu Feb 16 17:32:01 0597 PST | 597 | 7 | 4 | 4 | 47
1024 Tue Feb 16 17:32:01 1097 PST | 1097 | 7 | 2 | 2 | 47
1025 Sat Feb 16 17:32:01 1697 PST | 1697 | 7 | 6 | 6 | 47
1026 Thu Feb 16 17:32:01 1797 PST | 1797 | 7 | 4 | 4 | 47
1027 Tue Feb 16 17:32:01 1897 PST | 1897 | 7 | 2 | 2 | 47
1028 Sun Feb 16 17:32:01 1997 PST | 1997 | 7 | 7 | 0 | 47
1029 Sat Feb 16 17:32:01 2097 PST | 2097 | 7 | 6 | 6 | 47
1030 Wed Feb 28 17:32:01 1996 PST | 1996 | 9 | 3 | 3 | 59
1031 Thu Feb 29 17:32:01 1996 PST | 1996 | 9 | 4 | 4 | 60
1032 Fri Mar 01 17:32:01 1996 PST | 1996 | 9 | 5 | 5 | 61
1033 Mon Dec 30 17:32:01 1996 PST | 1997 | 1 | 1 | 1 | 365
1034 Tue Dec 31 17:32:01 1996 PST | 1997 | 1 | 2 | 2 | 366
1035 Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 3 | 3 | 1
1036 Fri Feb 28 17:32:01 1997 PST | 1997 | 9 | 5 | 5 | 59
1037 Sat Mar 01 17:32:01 1997 PST | 1997 | 9 | 6 | 6 | 60
1038 Tue Dec 30 17:32:01 1997 PST | 1998 | 1 | 2 | 2 | 364
1039 Wed Dec 31 17:32:01 1997 PST | 1998 | 1 | 3 | 3 | 365
1040 Fri Dec 31 17:32:01 1999 PST | 1999 | 52 | 5 | 5 | 365
1041 Sat Jan 01 17:32:01 2000 PST | 1999 | 52 | 6 | 6 | 1
1042 Sun Dec 31 17:32:01 2000 PST | 2000 | 52 | 7 | 0 | 366
1043 Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 1 | 1
1044 (66 rows)
1046 SELECT d1 as timestamptz,
1047 date_part( 'decade', d1) AS decade,
1048 date_part( 'century', d1) AS century,
1049 date_part( 'millennium', d1) AS millennium,
1050 round(date_part( 'julian', d1)) AS julian,
1051 date_part( 'epoch', d1) AS epoch
1052 FROM TIMESTAMPTZ_TBL;
1053 timestamptz | decade | century | millennium | julian | epoch
1054 ---------------------------------+-----------+-----------+------------+-----------+--------------
1055 -infinity | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1056 infinity | Infinity | Infinity | Infinity | Infinity | Infinity
1057 Wed Dec 31 16:00:00 1969 PST | 196 | 20 | 2 | 2440588 | 0
1058 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1059 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1060 Mon Feb 10 17:32:02 1997 PST | 199 | 20 | 2 | 2450491 | 855624722
1061 Mon Feb 10 17:32:01.4 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.4
1062 Mon Feb 10 17:32:01.5 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.5
1063 Mon Feb 10 17:32:01.6 1997 PST | 199 | 20 | 2 | 2450491 | 855624721.6
1064 Thu Jan 02 00:00:00 1997 PST | 199 | 20 | 2 | 2450451 | 852192000
1065 Thu Jan 02 03:04:05 1997 PST | 199 | 20 | 2 | 2450451 | 852203045
1066 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1067 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1068 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1069 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1070 Tue Jun 10 17:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865989121
1071 Sat Sep 22 18:19:20 2001 PDT | 200 | 21 | 3 | 2452176 | 1001207960
1072 Wed Mar 15 08:14:01 2000 PST | 200 | 20 | 2 | 2451619 | 953136841
1073 Wed Mar 15 04:14:02 2000 PST | 200 | 20 | 2 | 2451619 | 953122442
1074 Wed Mar 15 02:14:03 2000 PST | 200 | 20 | 2 | 2451619 | 953115243
1075 Wed Mar 15 03:14:04 2000 PST | 200 | 20 | 2 | 2451619 | 953118844
1076 Wed Mar 15 01:14:05 2000 PST | 200 | 20 | 2 | 2451619 | 953111645
1077 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1078 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1079 Mon Feb 10 17:32:00 1997 PST | 199 | 20 | 2 | 2450491 | 855624720
1080 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1081 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1082 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1083 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1084 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1085 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1086 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1087 Mon Feb 10 09:32:01 1997 PST | 199 | 20 | 2 | 2450490 | 855595921
1088 Mon Feb 10 14:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855613921
1089 Thu Jul 10 14:32:01 1997 PDT | 199 | 20 | 2 | 2450641 | 868570321
1090 Tue Jun 10 18:32:01 1997 PDT | 199 | 20 | 2 | 2450611 | 865992721
1091 Mon Feb 10 17:32:01 1997 PST | 199 | 20 | 2 | 2450491 | 855624721
1092 Tue Feb 11 17:32:01 1997 PST | 199 | 20 | 2 | 2450492 | 855711121
1093 Wed Feb 12 17:32:01 1997 PST | 199 | 20 | 2 | 2450493 | 855797521
1094 Thu Feb 13 17:32:01 1997 PST | 199 | 20 | 2 | 2450494 | 855883921
1095 Fri Feb 14 17:32:01 1997 PST | 199 | 20 | 2 | 2450495 | 855970321
1096 Sat Feb 15 17:32:01 1997 PST | 199 | 20 | 2 | 2450496 | 856056721
1097 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1098 Tue Feb 16 17:32:01 0097 PST BC | -10 | -1 | -1 | 1686043 | -65192682479
1099 Sat Feb 16 17:32:01 0097 PST | 9 | 1 | 1 | 1756537 | -59102000879
1100 Thu Feb 16 17:32:01 0597 PST | 59 | 6 | 1 | 1939158 | -43323546479
1101 Tue Feb 16 17:32:01 1097 PST | 109 | 11 | 2 | 2121779 | -27545092079
1102 Sat Feb 16 17:32:01 1697 PST | 169 | 17 | 2 | 2340925 | -8610877679
1103 Thu Feb 16 17:32:01 1797 PST | 179 | 18 | 2 | 2377449 | -5455204079
1104 Tue Feb 16 17:32:01 1897 PST | 189 | 19 | 2 | 2413973 | -2299530479
1105 Sun Feb 16 17:32:01 1997 PST | 199 | 20 | 2 | 2450497 | 856143121
1106 Sat Feb 16 17:32:01 2097 PST | 209 | 21 | 3 | 2487022 | 4011903121
1107 Wed Feb 28 17:32:01 1996 PST | 199 | 20 | 2 | 2450143 | 825557521
1108 Thu Feb 29 17:32:01 1996 PST | 199 | 20 | 2 | 2450144 | 825643921
1109 Fri Mar 01 17:32:01 1996 PST | 199 | 20 | 2 | 2450145 | 825730321
1110 Mon Dec 30 17:32:01 1996 PST | 199 | 20 | 2 | 2450449 | 851995921
1111 Tue Dec 31 17:32:01 1996 PST | 199 | 20 | 2 | 2450450 | 852082321
1112 Wed Jan 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450451 | 852168721
1113 Fri Feb 28 17:32:01 1997 PST | 199 | 20 | 2 | 2450509 | 857179921
1114 Sat Mar 01 17:32:01 1997 PST | 199 | 20 | 2 | 2450510 | 857266321
1115 Tue Dec 30 17:32:01 1997 PST | 199 | 20 | 2 | 2450814 | 883531921
1116 Wed Dec 31 17:32:01 1997 PST | 199 | 20 | 2 | 2450815 | 883618321
1117 Fri Dec 31 17:32:01 1999 PST | 199 | 20 | 2 | 2451545 | 946690321
1118 Sat Jan 01 17:32:01 2000 PST | 200 | 20 | 2 | 2451546 | 946776721
1119 Sun Dec 31 17:32:01 2000 PST | 200 | 20 | 2 | 2451911 | 978312721
1120 Mon Jan 01 17:32:01 2001 PST | 200 | 21 | 3 | 2451912 | 978399121
1121 (66 rows)
1123 SELECT d1 as timestamptz,
1124 date_part( 'timezone', d1) AS timezone,
1125 date_part( 'timezone_hour', d1) AS timezone_hour,
1126 date_part( 'timezone_minute', d1) AS timezone_minute
1127 FROM TIMESTAMPTZ_TBL;
1128 timestamptz | timezone | timezone_hour | timezone_minute
1129 ---------------------------------+----------+---------------+-----------------
1130 -infinity | | |
1131 infinity | | |
1132 Wed Dec 31 16:00:00 1969 PST | -28800 | -8 | 0
1133 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1134 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1135 Mon Feb 10 17:32:02 1997 PST | -28800 | -8 | 0
1136 Mon Feb 10 17:32:01.4 1997 PST | -28800 | -8 | 0
1137 Mon Feb 10 17:32:01.5 1997 PST | -28800 | -8 | 0
1138 Mon Feb 10 17:32:01.6 1997 PST | -28800 | -8 | 0
1139 Thu Jan 02 00:00:00 1997 PST | -28800 | -8 | 0
1140 Thu Jan 02 03:04:05 1997 PST | -28800 | -8 | 0
1141 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1142 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1143 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1144 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1145 Tue Jun 10 17:32:01 1997 PDT | -25200 | -7 | 0
1146 Sat Sep 22 18:19:20 2001 PDT | -25200 | -7 | 0
1147 Wed Mar 15 08:14:01 2000 PST | -28800 | -8 | 0
1148 Wed Mar 15 04:14:02 2000 PST | -28800 | -8 | 0
1149 Wed Mar 15 02:14:03 2000 PST | -28800 | -8 | 0
1150 Wed Mar 15 03:14:04 2000 PST | -28800 | -8 | 0
1151 Wed Mar 15 01:14:05 2000 PST | -28800 | -8 | 0
1152 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1153 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1154 Mon Feb 10 17:32:00 1997 PST | -28800 | -8 | 0
1155 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1156 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1157 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1158 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1159 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1160 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1161 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1162 Mon Feb 10 09:32:01 1997 PST | -28800 | -8 | 0
1163 Mon Feb 10 14:32:01 1997 PST | -28800 | -8 | 0
1164 Thu Jul 10 14:32:01 1997 PDT | -25200 | -7 | 0
1165 Tue Jun 10 18:32:01 1997 PDT | -25200 | -7 | 0
1166 Mon Feb 10 17:32:01 1997 PST | -28800 | -8 | 0
1167 Tue Feb 11 17:32:01 1997 PST | -28800 | -8 | 0
1168 Wed Feb 12 17:32:01 1997 PST | -28800 | -8 | 0
1169 Thu Feb 13 17:32:01 1997 PST | -28800 | -8 | 0
1170 Fri Feb 14 17:32:01 1997 PST | -28800 | -8 | 0
1171 Sat Feb 15 17:32:01 1997 PST | -28800 | -8 | 0
1172 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1173 Tue Feb 16 17:32:01 0097 PST BC | -28800 | -8 | 0
1174 Sat Feb 16 17:32:01 0097 PST | -28800 | -8 | 0
1175 Thu Feb 16 17:32:01 0597 PST | -28800 | -8 | 0
1176 Tue Feb 16 17:32:01 1097 PST | -28800 | -8 | 0
1177 Sat Feb 16 17:32:01 1697 PST | -28800 | -8 | 0
1178 Thu Feb 16 17:32:01 1797 PST | -28800 | -8 | 0
1179 Tue Feb 16 17:32:01 1897 PST | -28800 | -8 | 0
1180 Sun Feb 16 17:32:01 1997 PST | -28800 | -8 | 0
1181 Sat Feb 16 17:32:01 2097 PST | -28800 | -8 | 0
1182 Wed Feb 28 17:32:01 1996 PST | -28800 | -8 | 0
1183 Thu Feb 29 17:32:01 1996 PST | -28800 | -8 | 0
1184 Fri Mar 01 17:32:01 1996 PST | -28800 | -8 | 0
1185 Mon Dec 30 17:32:01 1996 PST | -28800 | -8 | 0
1186 Tue Dec 31 17:32:01 1996 PST | -28800 | -8 | 0
1187 Wed Jan 01 17:32:01 1997 PST | -28800 | -8 | 0
1188 Fri Feb 28 17:32:01 1997 PST | -28800 | -8 | 0
1189 Sat Mar 01 17:32:01 1997 PST | -28800 | -8 | 0
1190 Tue Dec 30 17:32:01 1997 PST | -28800 | -8 | 0
1191 Wed Dec 31 17:32:01 1997 PST | -28800 | -8 | 0
1192 Fri Dec 31 17:32:01 1999 PST | -28800 | -8 | 0
1193 Sat Jan 01 17:32:01 2000 PST | -28800 | -8 | 0
1194 Sun Dec 31 17:32:01 2000 PST | -28800 | -8 | 0
1195 Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0
1196 (66 rows)
1198 -- extract implementation is mostly the same as date_part, so only
1199 -- test a few cases for additional coverage.
1200 SELECT d1 as "timestamp",
1201 extract(microseconds from d1) AS microseconds,
1202 extract(milliseconds from d1) AS milliseconds,
1203 extract(seconds from d1) AS seconds,
1204 round(extract(julian from d1)) AS julian,
1205 extract(epoch from d1) AS epoch
1206 FROM TIMESTAMPTZ_TBL;
1207 timestamp | microseconds | milliseconds | seconds | julian | epoch
1208 ---------------------------------+--------------+--------------+-----------+-----------+---------------------
1209 -infinity | | | | -Infinity | -Infinity
1210 infinity | | | | Infinity | Infinity
1211 Wed Dec 31 16:00:00 1969 PST | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
1212 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1213 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1214 Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000.000 | 2.000000 | 2450491 | 855624722.000000
1215 Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400.000 | 1.400000 | 2450491 | 855624721.400000
1216 Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500.000 | 1.500000 | 2450491 | 855624721.500000
1217 Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600.000 | 1.600000 | 2450491 | 855624721.600000
1218 Thu Jan 02 00:00:00 1997 PST | 0 | 0.000 | 0.000000 | 2450451 | 852192000.000000
1219 Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000.000 | 5.000000 | 2450451 | 852203045.000000
1220 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1221 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1222 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1223 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1224 Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865989121.000000
1225 Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001207960.000000
1226 Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451619 | 953136841.000000
1227 Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000.000 | 2.000000 | 2451619 | 953122442.000000
1228 Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000.000 | 3.000000 | 2451619 | 953115243.000000
1229 Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000.000 | 4.000000 | 2451619 | 953118844.000000
1230 Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000.000 | 5.000000 | 2451619 | 953111645.000000
1231 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1232 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1233 Mon Feb 10 17:32:00 1997 PST | 0 | 0.000 | 0.000000 | 2450491 | 855624720.000000
1234 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1235 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1236 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1237 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1238 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1239 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1240 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1241 Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
1242 Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855613921.000000
1243 Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450641 | 868570321.000000
1244 Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865992721.000000
1245 Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
1246 Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450492 | 855711121.000000
1247 Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450493 | 855797521.000000
1248 Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450494 | 855883921.000000
1249 Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450495 | 855970321.000000
1250 Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450496 | 856056721.000000
1251 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1252 Tue Feb 16 17:32:01 0097 PST BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192682479.000000
1253 Sat Feb 16 17:32:01 0097 PST | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102000879.000000
1254 Thu Feb 16 17:32:01 0597 PST | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323546479.000000
1255 Tue Feb 16 17:32:01 1097 PST | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545092079.000000
1256 Sat Feb 16 17:32:01 1697 PST | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610877679.000000
1257 Thu Feb 16 17:32:01 1797 PST | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455204079.000000
1258 Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299530479.000000
1259 Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
1260 Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011903121.000000
1261 Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450143 | 825557521.000000
1262 Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450144 | 825643921.000000
1263 Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450145 | 825730321.000000
1264 Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450449 | 851995921.000000
1265 Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450450 | 852082321.000000
1266 Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450451 | 852168721.000000
1267 Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450509 | 857179921.000000
1268 Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450510 | 857266321.000000
1269 Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450814 | 883531921.000000
1270 Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450815 | 883618321.000000
1271 Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000.000 | 1.000000 | 2451545 | 946690321.000000
1272 Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451546 | 946776721.000000
1273 Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451911 | 978312721.000000
1274 Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000.000 | 1.000000 | 2451912 | 978399121.000000
1275 (66 rows)
1277 -- value near upper bound uses special case in code
1278 SELECT date_part('epoch', '294270-01-01 00:00:00+00'::timestamptz);
1279 date_part
1280 ---------------
1281 9224097091200
1282 (1 row)
1284 SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
1285 extract
1286 ----------------------
1287 9224097091200.000000
1288 (1 row)
1290 -- another internal overflow test case
1291 SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
1292 extract
1293 --------------------
1294 95617584000.000000
1295 (1 row)
1297 -- TO_CHAR()
1298 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
1299 FROM TIMESTAMPTZ_TBL;
1300 to_char
1301 ------------------------------------------------------------------------------------------
1304 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1305 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1306 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1307 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1308 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1309 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1310 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1311 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1312 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1313 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1314 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1315 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1316 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1317 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1318 SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep
1319 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1320 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1321 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1322 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1323 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1324 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1325 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1326 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1327 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1328 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1329 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1330 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1331 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1332 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1333 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1334 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1335 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1336 THURSDAY Thursday thursday THU Thu thu JULY July july VII JUL Jul jul
1337 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1338 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1339 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1340 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1341 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1342 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1343 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1344 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1345 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1346 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1347 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1348 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1349 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1350 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1351 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1352 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1353 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1354 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1355 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1356 FRIDAY Friday friday FRI Fri fri MARCH March march III MAR Mar mar
1357 MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec
1358 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1359 WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan
1360 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1361 SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar
1362 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1363 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1364 FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec
1365 SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
1366 SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
1367 MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
1368 (66 rows)
1370 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
1371 FROM TIMESTAMPTZ_TBL;
1372 to_char
1373 --------------------------------------------------------------
1376 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1377 MONDAY Monday monday FEBRUARY February february II
1378 MONDAY Monday monday FEBRUARY February february II
1379 MONDAY Monday monday FEBRUARY February february II
1380 MONDAY Monday monday FEBRUARY February february II
1381 MONDAY Monday monday FEBRUARY February february II
1382 MONDAY Monday monday FEBRUARY February february II
1383 THURSDAY Thursday thursday JANUARY January january I
1384 THURSDAY Thursday thursday JANUARY January january I
1385 MONDAY Monday monday FEBRUARY February february II
1386 MONDAY Monday monday FEBRUARY February february II
1387 MONDAY Monday monday FEBRUARY February february II
1388 MONDAY Monday monday FEBRUARY February february II
1389 TUESDAY Tuesday tuesday JUNE June june VI
1390 SATURDAY Saturday saturday SEPTEMBER September september IX
1391 WEDNESDAY Wednesday wednesday MARCH March march III
1392 WEDNESDAY Wednesday wednesday MARCH March march III
1393 WEDNESDAY Wednesday wednesday MARCH March march III
1394 WEDNESDAY Wednesday wednesday MARCH March march III
1395 WEDNESDAY Wednesday wednesday MARCH March march III
1396 MONDAY Monday monday FEBRUARY February february II
1397 MONDAY Monday monday FEBRUARY February february II
1398 MONDAY Monday monday FEBRUARY February february II
1399 MONDAY Monday monday FEBRUARY February february II
1400 MONDAY Monday monday FEBRUARY February february II
1401 MONDAY Monday monday FEBRUARY February february II
1402 MONDAY Monday monday FEBRUARY February february II
1403 MONDAY Monday monday FEBRUARY February february II
1404 MONDAY Monday monday FEBRUARY February february II
1405 MONDAY Monday monday FEBRUARY February february II
1406 MONDAY Monday monday FEBRUARY February february II
1407 MONDAY Monday monday FEBRUARY February february II
1408 THURSDAY Thursday thursday JULY July july VII
1409 TUESDAY Tuesday tuesday JUNE June june VI
1410 MONDAY Monday monday FEBRUARY February february II
1411 TUESDAY Tuesday tuesday FEBRUARY February february II
1412 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1413 THURSDAY Thursday thursday FEBRUARY February february II
1414 FRIDAY Friday friday FEBRUARY February february II
1415 SATURDAY Saturday saturday FEBRUARY February february II
1416 SUNDAY Sunday sunday FEBRUARY February february II
1417 TUESDAY Tuesday tuesday FEBRUARY February february II
1418 SATURDAY Saturday saturday FEBRUARY February february II
1419 THURSDAY Thursday thursday FEBRUARY February february II
1420 TUESDAY Tuesday tuesday FEBRUARY February february II
1421 SATURDAY Saturday saturday FEBRUARY February february II
1422 THURSDAY Thursday thursday FEBRUARY February february II
1423 TUESDAY Tuesday tuesday FEBRUARY February february II
1424 SUNDAY Sunday sunday FEBRUARY February february II
1425 SATURDAY Saturday saturday FEBRUARY February february II
1426 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1427 THURSDAY Thursday thursday FEBRUARY February february II
1428 FRIDAY Friday friday MARCH March march III
1429 MONDAY Monday monday DECEMBER December december XII
1430 TUESDAY Tuesday tuesday DECEMBER December december XII
1431 WEDNESDAY Wednesday wednesday JANUARY January january I
1432 FRIDAY Friday friday FEBRUARY February february II
1433 SATURDAY Saturday saturday MARCH March march III
1434 TUESDAY Tuesday tuesday DECEMBER December december XII
1435 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1436 FRIDAY Friday friday DECEMBER December december XII
1437 SATURDAY Saturday saturday JANUARY January january I
1438 SUNDAY Sunday sunday DECEMBER December december XII
1439 MONDAY Monday monday JANUARY January january I
1440 (66 rows)
1442 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
1443 FROM TIMESTAMPTZ_TBL;
1444 to_char
1445 --------------------------------------------------
1448 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1449 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1450 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1451 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1452 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1453 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1454 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1455 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1456 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1457 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1458 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1459 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1460 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1461 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1462 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
1463 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1464 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1465 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1466 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1467 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1468 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1469 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1470 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1471 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1472 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1473 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1474 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1475 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1476 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1477 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1478 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1479 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1480 1,997 1997 997 97 7 20 3 07 28 191 10 5 2450640
1481 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1482 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1483 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
1484 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1485 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1486 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1487 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1488 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1489 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
1490 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
1491 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1492 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1493 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1494 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1495 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1496 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1497 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
1498 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1499 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1500 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1501 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1502 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1503 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1504 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1505 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1506 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1507 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1508 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1509 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
1510 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
1511 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
1512 (66 rows)
1514 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
1515 FROM TIMESTAMPTZ_TBL;
1516 to_char
1517 -------------------------------------------------
1520 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
1521 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1522 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1523 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1524 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1525 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1526 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1527 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1528 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1529 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1530 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1531 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1532 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1533 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1534 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
1535 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1536 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1537 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1538 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1539 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1540 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1541 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1542 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1543 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1544 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1545 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1546 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1547 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1548 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1549 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1550 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1551 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1552 1,997 1997 997 97 7 20 3 7 28 191 10 5 2450640
1553 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1554 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1555 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
1556 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1557 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1558 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1559 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1560 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1561 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
1562 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
1563 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1564 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1565 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1566 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1567 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1568 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1569 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
1570 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1571 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1572 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1573 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1574 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1575 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1576 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1577 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1578 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1579 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1580 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1581 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
1582 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
1583 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
1584 (66 rows)
1586 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
1587 FROM TIMESTAMPTZ_TBL;
1588 to_char
1589 ----------------------
1592 04 04 16 00 00 57600
1593 05 05 17 32 01 63121
1594 05 05 17 32 01 63121
1595 05 05 17 32 02 63122
1596 05 05 17 32 01 63121
1597 05 05 17 32 01 63121
1598 05 05 17 32 01 63121
1599 12 12 00 00 00 0
1600 03 03 03 04 05 11045
1601 05 05 17 32 01 63121
1602 05 05 17 32 01 63121
1603 05 05 17 32 01 63121
1604 05 05 17 32 01 63121
1605 05 05 17 32 01 63121
1606 06 06 18 19 20 65960
1607 08 08 08 14 01 29641
1608 04 04 04 14 02 15242
1609 02 02 02 14 03 8043
1610 03 03 03 14 04 11644
1611 01 01 01 14 05 4445
1612 05 05 17 32 01 63121
1613 05 05 17 32 01 63121
1614 05 05 17 32 00 63120
1615 05 05 17 32 01 63121
1616 05 05 17 32 01 63121
1617 05 05 17 32 01 63121
1618 05 05 17 32 01 63121
1619 05 05 17 32 01 63121
1620 09 09 09 32 01 34321
1621 09 09 09 32 01 34321
1622 09 09 09 32 01 34321
1623 02 02 14 32 01 52321
1624 02 02 14 32 01 52321
1625 06 06 18 32 01 66721
1626 05 05 17 32 01 63121
1627 05 05 17 32 01 63121
1628 05 05 17 32 01 63121
1629 05 05 17 32 01 63121
1630 05 05 17 32 01 63121
1631 05 05 17 32 01 63121
1632 05 05 17 32 01 63121
1633 05 05 17 32 01 63121
1634 05 05 17 32 01 63121
1635 05 05 17 32 01 63121
1636 05 05 17 32 01 63121
1637 05 05 17 32 01 63121
1638 05 05 17 32 01 63121
1639 05 05 17 32 01 63121
1640 05 05 17 32 01 63121
1641 05 05 17 32 01 63121
1642 05 05 17 32 01 63121
1643 05 05 17 32 01 63121
1644 05 05 17 32 01 63121
1645 05 05 17 32 01 63121
1646 05 05 17 32 01 63121
1647 05 05 17 32 01 63121
1648 05 05 17 32 01 63121
1649 05 05 17 32 01 63121
1650 05 05 17 32 01 63121
1651 05 05 17 32 01 63121
1652 05 05 17 32 01 63121
1653 05 05 17 32 01 63121
1654 05 05 17 32 01 63121
1655 05 05 17 32 01 63121
1656 (66 rows)
1658 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
1659 FROM TIMESTAMPTZ_TBL;
1660 to_char
1661 -------------------------------------------------
1664 HH:MI:SS is 04:00:00 "text between quote marks"
1665 HH:MI:SS is 05:32:01 "text between quote marks"
1666 HH:MI:SS is 05:32:01 "text between quote marks"
1667 HH:MI:SS is 05:32:02 "text between quote marks"
1668 HH:MI:SS is 05:32:01 "text between quote marks"
1669 HH:MI:SS is 05:32:01 "text between quote marks"
1670 HH:MI:SS is 05:32:01 "text between quote marks"
1671 HH:MI:SS is 12:00:00 "text between quote marks"
1672 HH:MI:SS is 03:04:05 "text between quote marks"
1673 HH:MI:SS is 05:32:01 "text between quote marks"
1674 HH:MI:SS is 05:32:01 "text between quote marks"
1675 HH:MI:SS is 05:32:01 "text between quote marks"
1676 HH:MI:SS is 05:32:01 "text between quote marks"
1677 HH:MI:SS is 05:32:01 "text between quote marks"
1678 HH:MI:SS is 06:19:20 "text between quote marks"
1679 HH:MI:SS is 08:14:01 "text between quote marks"
1680 HH:MI:SS is 04:14:02 "text between quote marks"
1681 HH:MI:SS is 02:14:03 "text between quote marks"
1682 HH:MI:SS is 03:14:04 "text between quote marks"
1683 HH:MI:SS is 01:14:05 "text between quote marks"
1684 HH:MI:SS is 05:32:01 "text between quote marks"
1685 HH:MI:SS is 05:32:01 "text between quote marks"
1686 HH:MI:SS is 05:32:00 "text between quote marks"
1687 HH:MI:SS is 05:32:01 "text between quote marks"
1688 HH:MI:SS is 05:32:01 "text between quote marks"
1689 HH:MI:SS is 05:32:01 "text between quote marks"
1690 HH:MI:SS is 05:32:01 "text between quote marks"
1691 HH:MI:SS is 05:32:01 "text between quote marks"
1692 HH:MI:SS is 09:32:01 "text between quote marks"
1693 HH:MI:SS is 09:32:01 "text between quote marks"
1694 HH:MI:SS is 09:32:01 "text between quote marks"
1695 HH:MI:SS is 02:32:01 "text between quote marks"
1696 HH:MI:SS is 02:32:01 "text between quote marks"
1697 HH:MI:SS is 06:32:01 "text between quote marks"
1698 HH:MI:SS is 05:32:01 "text between quote marks"
1699 HH:MI:SS is 05:32:01 "text between quote marks"
1700 HH:MI:SS is 05:32:01 "text between quote marks"
1701 HH:MI:SS is 05:32:01 "text between quote marks"
1702 HH:MI:SS is 05:32:01 "text between quote marks"
1703 HH:MI:SS is 05:32:01 "text between quote marks"
1704 HH:MI:SS is 05:32:01 "text between quote marks"
1705 HH:MI:SS is 05:32:01 "text between quote marks"
1706 HH:MI:SS is 05:32:01 "text between quote marks"
1707 HH:MI:SS is 05:32:01 "text between quote marks"
1708 HH:MI:SS is 05:32:01 "text between quote marks"
1709 HH:MI:SS is 05:32:01 "text between quote marks"
1710 HH:MI:SS is 05:32:01 "text between quote marks"
1711 HH:MI:SS is 05:32:01 "text between quote marks"
1712 HH:MI:SS is 05:32:01 "text between quote marks"
1713 HH:MI:SS is 05:32:01 "text between quote marks"
1714 HH:MI:SS is 05:32:01 "text between quote marks"
1715 HH:MI:SS is 05:32:01 "text between quote marks"
1716 HH:MI:SS is 05:32:01 "text between quote marks"
1717 HH:MI:SS is 05:32:01 "text between quote marks"
1718 HH:MI:SS is 05:32:01 "text between quote marks"
1719 HH:MI:SS is 05:32:01 "text between quote marks"
1720 HH:MI:SS is 05:32:01 "text between quote marks"
1721 HH:MI:SS is 05:32:01 "text between quote marks"
1722 HH:MI:SS is 05:32:01 "text between quote marks"
1723 HH:MI:SS is 05:32:01 "text between quote marks"
1724 HH:MI:SS is 05:32:01 "text between quote marks"
1725 HH:MI:SS is 05:32:01 "text between quote marks"
1726 HH:MI:SS is 05:32:01 "text between quote marks"
1727 HH:MI:SS is 05:32:01 "text between quote marks"
1728 (66 rows)
1730 SELECT to_char(d1, 'HH24--text--MI--text--SS')
1731 FROM TIMESTAMPTZ_TBL;
1732 to_char
1733 ------------------------
1736 16--text--00--text--00
1737 17--text--32--text--01
1738 17--text--32--text--01
1739 17--text--32--text--02
1740 17--text--32--text--01
1741 17--text--32--text--01
1742 17--text--32--text--01
1743 00--text--00--text--00
1744 03--text--04--text--05
1745 17--text--32--text--01
1746 17--text--32--text--01
1747 17--text--32--text--01
1748 17--text--32--text--01
1749 17--text--32--text--01
1750 18--text--19--text--20
1751 08--text--14--text--01
1752 04--text--14--text--02
1753 02--text--14--text--03
1754 03--text--14--text--04
1755 01--text--14--text--05
1756 17--text--32--text--01
1757 17--text--32--text--01
1758 17--text--32--text--00
1759 17--text--32--text--01
1760 17--text--32--text--01
1761 17--text--32--text--01
1762 17--text--32--text--01
1763 17--text--32--text--01
1764 09--text--32--text--01
1765 09--text--32--text--01
1766 09--text--32--text--01
1767 14--text--32--text--01
1768 14--text--32--text--01
1769 18--text--32--text--01
1770 17--text--32--text--01
1771 17--text--32--text--01
1772 17--text--32--text--01
1773 17--text--32--text--01
1774 17--text--32--text--01
1775 17--text--32--text--01
1776 17--text--32--text--01
1777 17--text--32--text--01
1778 17--text--32--text--01
1779 17--text--32--text--01
1780 17--text--32--text--01
1781 17--text--32--text--01
1782 17--text--32--text--01
1783 17--text--32--text--01
1784 17--text--32--text--01
1785 17--text--32--text--01
1786 17--text--32--text--01
1787 17--text--32--text--01
1788 17--text--32--text--01
1789 17--text--32--text--01
1790 17--text--32--text--01
1791 17--text--32--text--01
1792 17--text--32--text--01
1793 17--text--32--text--01
1794 17--text--32--text--01
1795 17--text--32--text--01
1796 17--text--32--text--01
1797 17--text--32--text--01
1798 17--text--32--text--01
1799 17--text--32--text--01
1800 (66 rows)
1802 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
1803 FROM TIMESTAMPTZ_TBL;
1804 to_char
1805 -------------------------
1808 1969TH 1969th 2440587th
1809 1997TH 1997th 2450490th
1810 1997TH 1997th 2450490th
1811 1997TH 1997th 2450490th
1812 1997TH 1997th 2450490th
1813 1997TH 1997th 2450490th
1814 1997TH 1997th 2450490th
1815 1997TH 1997th 2450451st
1816 1997TH 1997th 2450451st
1817 1997TH 1997th 2450490th
1818 1997TH 1997th 2450490th
1819 1997TH 1997th 2450490th
1820 1997TH 1997th 2450490th
1821 1997TH 1997th 2450610th
1822 2001ST 2001st 2452175th
1823 2000TH 2000th 2451619th
1824 2000TH 2000th 2451619th
1825 2000TH 2000th 2451619th
1826 2000TH 2000th 2451619th
1827 2000TH 2000th 2451619th
1828 1997TH 1997th 2450490th
1829 1997TH 1997th 2450490th
1830 1997TH 1997th 2450490th
1831 1997TH 1997th 2450490th
1832 1997TH 1997th 2450490th
1833 1997TH 1997th 2450490th
1834 1997TH 1997th 2450490th
1835 1997TH 1997th 2450490th
1836 1997TH 1997th 2450490th
1837 1997TH 1997th 2450490th
1838 1997TH 1997th 2450490th
1839 1997TH 1997th 2450490th
1840 1997TH 1997th 2450640th
1841 1997TH 1997th 2450610th
1842 1997TH 1997th 2450490th
1843 1997TH 1997th 2450491st
1844 1997TH 1997th 2450492nd
1845 1997TH 1997th 2450493rd
1846 1997TH 1997th 2450494th
1847 1997TH 1997th 2450495th
1848 1997TH 1997th 2450496th
1849 0097TH 0097th 1686042nd
1850 0097TH 0097th 1756536th
1851 0597TH 0597th 1939157th
1852 1097TH 1097th 2121778th
1853 1697TH 1697th 2340924th
1854 1797TH 1797th 2377448th
1855 1897TH 1897th 2413972nd
1856 1997TH 1997th 2450496th
1857 2097TH 2097th 2487021st
1858 1996TH 1996th 2450142nd
1859 1996TH 1996th 2450143rd
1860 1996TH 1996th 2450144th
1861 1996TH 1996th 2450448th
1862 1996TH 1996th 2450449th
1863 1997TH 1997th 2450450th
1864 1997TH 1997th 2450508th
1865 1997TH 1997th 2450509th
1866 1997TH 1997th 2450813th
1867 1997TH 1997th 2450814th
1868 1999TH 1999th 2451544th
1869 2000TH 2000th 2451545th
1870 2000TH 2000th 2451910th
1871 2001ST 2001st 2451911th
1872 (66 rows)
1874 SELECT to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
1875 FROM TIMESTAMPTZ_TBL;
1876 to_char
1877 ---------------------------------------------------------------------
1880 1969 A.D. 1969 a.d. 1969 ad 04:00:00 P.M. 04:00:00 p.m. 04:00:00 pm
1881 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1882 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1883 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm
1884 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1885 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1886 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1887 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1888 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1889 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1890 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1891 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1892 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1893 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1894 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
1895 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
1896 2000 A.D. 2000 a.d. 2000 ad 04:14:02 A.M. 04:14:02 a.m. 04:14:02 am
1897 2000 A.D. 2000 a.d. 2000 ad 02:14:03 A.M. 02:14:03 a.m. 02:14:03 am
1898 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
1899 2000 A.D. 2000 a.d. 2000 ad 01:14:05 A.M. 01:14:05 a.m. 01:14:05 am
1900 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1901 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1902 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
1903 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1904 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1905 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1906 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1907 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1908 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1909 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1910 1997 A.D. 1997 a.d. 1997 ad 09:32:01 A.M. 09:32:01 a.m. 09:32:01 am
1911 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1912 1997 A.D. 1997 a.d. 1997 ad 02:32:01 P.M. 02:32:01 p.m. 02:32:01 pm
1913 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
1914 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1915 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1916 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1917 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1918 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1919 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1920 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1921 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1922 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1923 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1924 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1925 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1926 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1927 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1928 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1929 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1930 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1931 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1932 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1933 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1934 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1935 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1936 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1937 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1938 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1939 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1940 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1941 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1942 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1943 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1944 (66 rows)
1946 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
1947 FROM TIMESTAMPTZ_TBL;
1948 to_char
1949 ------------------------
1952 1970 970 70 0 01 003 3
1953 1997 997 97 7 07 043 1
1954 1997 997 97 7 07 043 1
1955 1997 997 97 7 07 043 1
1956 1997 997 97 7 07 043 1
1957 1997 997 97 7 07 043 1
1958 1997 997 97 7 07 043 1
1959 1997 997 97 7 01 004 4
1960 1997 997 97 7 01 004 4
1961 1997 997 97 7 07 043 1
1962 1997 997 97 7 07 043 1
1963 1997 997 97 7 07 043 1
1964 1997 997 97 7 07 043 1
1965 1997 997 97 7 24 163 2
1966 2001 001 01 1 38 265 6
1967 2000 000 00 0 11 073 3
1968 2000 000 00 0 11 073 3
1969 2000 000 00 0 11 073 3
1970 2000 000 00 0 11 073 3
1971 2000 000 00 0 11 073 3
1972 1997 997 97 7 07 043 1
1973 1997 997 97 7 07 043 1
1974 1997 997 97 7 07 043 1
1975 1997 997 97 7 07 043 1
1976 1997 997 97 7 07 043 1
1977 1997 997 97 7 07 043 1
1978 1997 997 97 7 07 043 1
1979 1997 997 97 7 07 043 1
1980 1997 997 97 7 07 043 1
1981 1997 997 97 7 07 043 1
1982 1997 997 97 7 07 043 1
1983 1997 997 97 7 07 043 1
1984 1997 997 97 7 28 193 4
1985 1997 997 97 7 24 163 2
1986 1997 997 97 7 07 043 1
1987 1997 997 97 7 07 044 2
1988 1997 997 97 7 07 045 3
1989 1997 997 97 7 07 046 4
1990 1997 997 97 7 07 047 5
1991 1997 997 97 7 07 048 6
1992 1997 997 97 7 07 049 7
1993 0097 097 97 7 07 044 2
1994 0097 097 97 7 07 048 6
1995 0597 597 97 7 07 046 4
1996 1097 097 97 7 07 044 2
1997 1697 697 97 7 07 048 6
1998 1797 797 97 7 07 046 4
1999 1897 897 97 7 07 044 2
2000 1997 997 97 7 07 049 7
2001 2097 097 97 7 07 048 6
2002 1996 996 96 6 09 059 3
2003 1996 996 96 6 09 060 4
2004 1996 996 96 6 09 061 5
2005 1997 997 97 7 01 001 1
2006 1997 997 97 7 01 002 2
2007 1997 997 97 7 01 003 3
2008 1997 997 97 7 09 061 5
2009 1997 997 97 7 09 062 6
2010 1998 998 98 8 01 002 2
2011 1998 998 98 8 01 003 3
2012 1999 999 99 9 52 362 5
2013 1999 999 99 9 52 363 6
2014 2000 000 00 0 52 364 7
2015 2001 001 01 1 01 001 1
2016 (66 rows)
2018 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
2019 FROM TIMESTAMPTZ_TBL;
2020 to_char
2021 ------------------------
2024 1970 970 70 0 1 3 3
2025 1997 997 97 7 7 43 1
2026 1997 997 97 7 7 43 1
2027 1997 997 97 7 7 43 1
2028 1997 997 97 7 7 43 1
2029 1997 997 97 7 7 43 1
2030 1997 997 97 7 7 43 1
2031 1997 997 97 7 1 4 4
2032 1997 997 97 7 1 4 4
2033 1997 997 97 7 7 43 1
2034 1997 997 97 7 7 43 1
2035 1997 997 97 7 7 43 1
2036 1997 997 97 7 7 43 1
2037 1997 997 97 7 24 163 2
2038 2001 1 1 1 38 265 6
2039 2000 0 0 0 11 73 3
2040 2000 0 0 0 11 73 3
2041 2000 0 0 0 11 73 3
2042 2000 0 0 0 11 73 3
2043 2000 0 0 0 11 73 3
2044 1997 997 97 7 7 43 1
2045 1997 997 97 7 7 43 1
2046 1997 997 97 7 7 43 1
2047 1997 997 97 7 7 43 1
2048 1997 997 97 7 7 43 1
2049 1997 997 97 7 7 43 1
2050 1997 997 97 7 7 43 1
2051 1997 997 97 7 7 43 1
2052 1997 997 97 7 7 43 1
2053 1997 997 97 7 7 43 1
2054 1997 997 97 7 7 43 1
2055 1997 997 97 7 7 43 1
2056 1997 997 97 7 28 193 4
2057 1997 997 97 7 24 163 2
2058 1997 997 97 7 7 43 1
2059 1997 997 97 7 7 44 2
2060 1997 997 97 7 7 45 3
2061 1997 997 97 7 7 46 4
2062 1997 997 97 7 7 47 5
2063 1997 997 97 7 7 48 6
2064 1997 997 97 7 7 49 7
2065 97 97 97 7 7 44 2
2066 97 97 97 7 7 48 6
2067 597 597 97 7 7 46 4
2068 1097 97 97 7 7 44 2
2069 1697 697 97 7 7 48 6
2070 1797 797 97 7 7 46 4
2071 1897 897 97 7 7 44 2
2072 1997 997 97 7 7 49 7
2073 2097 97 97 7 7 48 6
2074 1996 996 96 6 9 59 3
2075 1996 996 96 6 9 60 4
2076 1996 996 96 6 9 61 5
2077 1997 997 97 7 1 1 1
2078 1997 997 97 7 1 2 2
2079 1997 997 97 7 1 3 3
2080 1997 997 97 7 9 61 5
2081 1997 997 97 7 9 62 6
2082 1998 998 98 8 1 2 2
2083 1998 998 98 8 1 3 3
2084 1999 999 99 9 52 362 5
2085 1999 999 99 9 52 363 6
2086 2000 0 0 0 52 364 7
2087 2001 1 1 1 1 1 1
2088 (66 rows)
2090 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
2091 FROM (VALUES
2092 ('2018-11-02 12:34:56'::timestamptz),
2093 ('2018-11-02 12:34:56.78'),
2094 ('2018-11-02 12:34:56.78901'),
2095 ('2018-11-02 12:34:56.78901234')
2096 ) d(d);
2097 to_char
2098 --------------------------------------------------------------------
2099 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000
2100 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000
2101 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010
2102 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012
2103 (4 rows)
2105 -- Check OF, TZH, TZM with various zone offsets, particularly fractional hours
2106 SET timezone = '00:00';
2107 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2108 OF | TZH:TZM
2109 -----+---------
2110 +00 | +00:00
2111 (1 row)
2113 SET timezone = '+02:00';
2114 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2115 OF | TZH:TZM
2116 -----+---------
2117 -02 | -02:00
2118 (1 row)
2120 SET timezone = '-13:00';
2121 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2122 OF | TZH:TZM
2123 -----+---------
2124 +13 | +13:00
2125 (1 row)
2127 SET timezone = '-00:30';
2128 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2129 OF | TZH:TZM
2130 --------+---------
2131 +00:30 | +00:30
2132 (1 row)
2134 SET timezone = '00:30';
2135 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2136 OF | TZH:TZM
2137 --------+---------
2138 -00:30 | -00:30
2139 (1 row)
2141 SET timezone = '-04:30';
2142 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2143 OF | TZH:TZM
2144 --------+---------
2145 +04:30 | +04:30
2146 (1 row)
2148 SET timezone = '04:30';
2149 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2150 OF | TZH:TZM
2151 --------+---------
2152 -04:30 | -04:30
2153 (1 row)
2155 SET timezone = '-04:15';
2156 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2157 OF | TZH:TZM
2158 --------+---------
2159 +04:15 | +04:15
2160 (1 row)
2162 SET timezone = '04:15';
2163 SELECT to_char(now(), 'OF') as "OF", to_char(now(), 'TZH:TZM') as "TZH:TZM";
2164 OF | TZH:TZM
2165 --------+---------
2166 -04:15 | -04:15
2167 (1 row)
2169 RESET timezone;
2170 CREATE TABLE TIMESTAMPTZ_TST (a int , b timestamptz);
2171 -- Test year field value with len > 4
2172 INSERT INTO TIMESTAMPTZ_TST VALUES(1, 'Sat Mar 12 23:58:48 1000 IST');
2173 INSERT INTO TIMESTAMPTZ_TST VALUES(2, 'Sat Mar 12 23:58:48 10000 IST');
2174 INSERT INTO TIMESTAMPTZ_TST VALUES(3, 'Sat Mar 12 23:58:48 100000 IST');
2175 INSERT INTO TIMESTAMPTZ_TST VALUES(3, '10000 Mar 12 23:58:48 IST');
2176 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '100000312 23:58:48 IST');
2177 INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
2178 --Verify data
2179 SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
2180 a | b
2181 ---+--------------------------------
2182 1 | Wed Mar 12 13:58:48 1000 PST
2183 2 | Sun Mar 12 14:58:48 10000 PDT
2184 3 | Sun Mar 12 14:58:48 100000 PDT
2185 3 | Sun Mar 12 14:58:48 10000 PDT
2186 4 | Sun Mar 12 14:58:48 10000 PDT
2187 4 | Sun Mar 12 14:58:48 100000 PDT
2188 (6 rows)
2190 --Cleanup
2191 DROP TABLE TIMESTAMPTZ_TST;
2192 -- test timestamptz constructors
2193 set TimeZone to 'America/New_York';
2194 -- numeric timezone
2195 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
2196 make_timestamptz
2197 ---------------------------------
2198 Sun Jul 15 08:15:55.33 1973 EDT
2199 (1 row)
2201 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
2202 make_timestamptz
2203 ---------------------------------
2204 Sun Jul 15 02:15:55.33 1973 EDT
2205 (1 row)
2207 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '-2');
2208 make_timestamptz
2209 ---------------------------------
2210 Sun Jul 15 06:15:55.33 1973 EDT
2211 (1 row)
2213 WITH tzs (tz) AS (VALUES
2214 ('+1'), ('+1:'), ('+1:0'), ('+100'), ('+1:00'), ('+01:00'),
2215 ('+10'), ('+1000'), ('+10:'), ('+10:0'), ('+10:00'), ('+10:00:'),
2216 ('+10:00:1'), ('+10:00:01'),
2217 ('+10:00:10'))
2218 SELECT make_timestamptz(2010, 2, 27, 3, 45, 00, tz), tz FROM tzs;
2219 make_timestamptz | tz
2220 ------------------------------+-----------
2221 Fri Feb 26 21:45:00 2010 EST | +1
2222 Fri Feb 26 21:45:00 2010 EST | +1:
2223 Fri Feb 26 21:45:00 2010 EST | +1:0
2224 Fri Feb 26 21:45:00 2010 EST | +100
2225 Fri Feb 26 21:45:00 2010 EST | +1:00
2226 Fri Feb 26 21:45:00 2010 EST | +01:00
2227 Fri Feb 26 12:45:00 2010 EST | +10
2228 Fri Feb 26 12:45:00 2010 EST | +1000
2229 Fri Feb 26 12:45:00 2010 EST | +10:
2230 Fri Feb 26 12:45:00 2010 EST | +10:0
2231 Fri Feb 26 12:45:00 2010 EST | +10:00
2232 Fri Feb 26 12:45:00 2010 EST | +10:00:
2233 Fri Feb 26 12:44:59 2010 EST | +10:00:1
2234 Fri Feb 26 12:44:59 2010 EST | +10:00:01
2235 Fri Feb 26 12:44:50 2010 EST | +10:00:10
2236 (15 rows)
2238 -- these should fail
2239 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
2240 ERROR: invalid input syntax for type numeric time zone: "2"
2241 HINT: Numeric time zones must have "-" or "+" as first character.
2242 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '+16');
2243 ERROR: numeric time zone "+16" out of range
2244 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, '-16');
2245 ERROR: numeric time zone "-16" out of range
2246 -- should be true
2247 SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
2248 ?column?
2249 ----------
2250 t
2251 (1 row)
2253 -- full timezone names
2254 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') = timestamptz '2014-12-10 00:00:00 Europe/Prague';
2255 ?column?
2256 ----------
2257 t
2258 (1 row)
2260 SELECT make_timestamptz(2014, 12, 10, 0, 0, 0, 'Europe/Prague') AT TIME ZONE 'UTC';
2261 timezone
2262 --------------------------
2263 Tue Dec 09 23:00:00 2014
2264 (1 row)
2266 SELECT make_timestamptz(1846, 12, 10, 0, 0, 0, 'Asia/Manila') AT TIME ZONE 'UTC';
2267 timezone
2268 --------------------------
2269 Wed Dec 09 15:56:00 1846
2270 (1 row)
2272 SELECT make_timestamptz(1881, 12, 10, 0, 0, 0, 'Europe/Paris') AT TIME ZONE 'UTC';
2273 timezone
2274 --------------------------
2275 Fri Dec 09 23:50:39 1881
2276 (1 row)
2278 SELECT make_timestamptz(1910, 12, 24, 0, 0, 0, 'Nehwon/Lankhmar');
2279 ERROR: time zone "Nehwon/Lankhmar" not recognized
2280 -- abbreviations
2281 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EST');
2282 make_timestamptz
2283 ------------------------------
2284 Wed Dec 10 10:10:10 2008 EST
2285 (1 row)
2287 SELECT make_timestamptz(2008, 12, 10, 10, 10, 10, 'EDT');
2288 make_timestamptz
2289 ------------------------------
2290 Wed Dec 10 09:10:10 2008 EST
2291 (1 row)
2293 SELECT make_timestamptz(2014, 12, 10, 10, 10, 10, 'PST8PDT');
2294 make_timestamptz
2295 ------------------------------
2296 Wed Dec 10 13:10:10 2014 EST
2297 (1 row)
2299 RESET TimeZone;
2300 --
2301 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
2302 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
2303 -- moved forwards in Mar 2011 and backwards again in Oct 2014.
2304 --
2305 SET TimeZone to 'UTC';
2306 SELECT '2011-03-27 00:00:00 Europe/Moscow'::timestamptz;
2307 timestamptz
2308 ------------------------------
2309 Sat Mar 26 21:00:00 2011 UTC
2310 (1 row)
2312 SELECT '2011-03-27 01:00:00 Europe/Moscow'::timestamptz;
2313 timestamptz
2314 ------------------------------
2315 Sat Mar 26 22:00:00 2011 UTC
2316 (1 row)
2318 SELECT '2011-03-27 01:59:59 Europe/Moscow'::timestamptz;
2319 timestamptz
2320 ------------------------------
2321 Sat Mar 26 22:59:59 2011 UTC
2322 (1 row)
2324 SELECT '2011-03-27 02:00:00 Europe/Moscow'::timestamptz;
2325 timestamptz
2326 ------------------------------
2327 Sat Mar 26 23:00:00 2011 UTC
2328 (1 row)
2330 SELECT '2011-03-27 02:00:01 Europe/Moscow'::timestamptz;
2331 timestamptz
2332 ------------------------------
2333 Sat Mar 26 23:00:01 2011 UTC
2334 (1 row)
2336 SELECT '2011-03-27 02:59:59 Europe/Moscow'::timestamptz;
2337 timestamptz
2338 ------------------------------
2339 Sat Mar 26 23:59:59 2011 UTC
2340 (1 row)
2342 SELECT '2011-03-27 03:00:00 Europe/Moscow'::timestamptz;
2343 timestamptz
2344 ------------------------------
2345 Sat Mar 26 23:00:00 2011 UTC
2346 (1 row)
2348 SELECT '2011-03-27 03:00:01 Europe/Moscow'::timestamptz;
2349 timestamptz
2350 ------------------------------
2351 Sat Mar 26 23:00:01 2011 UTC
2352 (1 row)
2354 SELECT '2011-03-27 04:00:00 Europe/Moscow'::timestamptz;
2355 timestamptz
2356 ------------------------------
2357 Sun Mar 27 00:00:00 2011 UTC
2358 (1 row)
2360 SELECT '2011-03-27 00:00:00 MSK'::timestamptz;
2361 timestamptz
2362 ------------------------------
2363 Sat Mar 26 21:00:00 2011 UTC
2364 (1 row)
2366 SELECT '2011-03-27 01:00:00 MSK'::timestamptz;
2367 timestamptz
2368 ------------------------------
2369 Sat Mar 26 22:00:00 2011 UTC
2370 (1 row)
2372 SELECT '2011-03-27 01:59:59 MSK'::timestamptz;
2373 timestamptz
2374 ------------------------------
2375 Sat Mar 26 22:59:59 2011 UTC
2376 (1 row)
2378 SELECT '2011-03-27 02:00:00 MSK'::timestamptz;
2379 timestamptz
2380 ------------------------------
2381 Sat Mar 26 22:00:00 2011 UTC
2382 (1 row)
2384 SELECT '2011-03-27 02:00:01 MSK'::timestamptz;
2385 timestamptz
2386 ------------------------------
2387 Sat Mar 26 22:00:01 2011 UTC
2388 (1 row)
2390 SELECT '2011-03-27 02:59:59 MSK'::timestamptz;
2391 timestamptz
2392 ------------------------------
2393 Sat Mar 26 22:59:59 2011 UTC
2394 (1 row)
2396 SELECT '2011-03-27 03:00:00 MSK'::timestamptz;
2397 timestamptz
2398 ------------------------------
2399 Sat Mar 26 23:00:00 2011 UTC
2400 (1 row)
2402 SELECT '2011-03-27 03:00:01 MSK'::timestamptz;
2403 timestamptz
2404 ------------------------------
2405 Sat Mar 26 23:00:01 2011 UTC
2406 (1 row)
2408 SELECT '2011-03-27 04:00:00 MSK'::timestamptz;
2409 timestamptz
2410 ------------------------------
2411 Sun Mar 27 00:00:00 2011 UTC
2412 (1 row)
2414 SELECT '2014-10-26 00:00:00 Europe/Moscow'::timestamptz;
2415 timestamptz
2416 ------------------------------
2417 Sat Oct 25 20:00:00 2014 UTC
2418 (1 row)
2420 SELECT '2014-10-26 00:59:59 Europe/Moscow'::timestamptz;
2421 timestamptz
2422 ------------------------------
2423 Sat Oct 25 20:59:59 2014 UTC
2424 (1 row)
2426 SELECT '2014-10-26 01:00:00 Europe/Moscow'::timestamptz;
2427 timestamptz
2428 ------------------------------
2429 Sat Oct 25 22:00:00 2014 UTC
2430 (1 row)
2432 SELECT '2014-10-26 01:00:01 Europe/Moscow'::timestamptz;
2433 timestamptz
2434 ------------------------------
2435 Sat Oct 25 22:00:01 2014 UTC
2436 (1 row)
2438 SELECT '2014-10-26 02:00:00 Europe/Moscow'::timestamptz;
2439 timestamptz
2440 ------------------------------
2441 Sat Oct 25 23:00:00 2014 UTC
2442 (1 row)
2444 SELECT '2014-10-26 00:00:00 MSK'::timestamptz;
2445 timestamptz
2446 ------------------------------
2447 Sat Oct 25 20:00:00 2014 UTC
2448 (1 row)
2450 SELECT '2014-10-26 00:59:59 MSK'::timestamptz;
2451 timestamptz
2452 ------------------------------
2453 Sat Oct 25 20:59:59 2014 UTC
2454 (1 row)
2456 SELECT '2014-10-26 01:00:00 MSK'::timestamptz;
2457 timestamptz
2458 ------------------------------
2459 Sat Oct 25 22:00:00 2014 UTC
2460 (1 row)
2462 SELECT '2014-10-26 01:00:01 MSK'::timestamptz;
2463 timestamptz
2464 ------------------------------
2465 Sat Oct 25 22:00:01 2014 UTC
2466 (1 row)
2468 SELECT '2014-10-26 02:00:00 MSK'::timestamptz;
2469 timestamptz
2470 ------------------------------
2471 Sat Oct 25 23:00:00 2014 UTC
2472 (1 row)
2474 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2475 timezone
2476 ------------------------------
2477 Sat Mar 26 21:00:00 2011 UTC
2478 (1 row)
2480 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2481 timezone
2482 ------------------------------
2483 Sat Mar 26 22:00:00 2011 UTC
2484 (1 row)
2486 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2487 timezone
2488 ------------------------------
2489 Sat Mar 26 22:59:59 2011 UTC
2490 (1 row)
2492 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2493 timezone
2494 ------------------------------
2495 Sat Mar 26 23:00:00 2011 UTC
2496 (1 row)
2498 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2499 timezone
2500 ------------------------------
2501 Sat Mar 26 23:00:01 2011 UTC
2502 (1 row)
2504 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2505 timezone
2506 ------------------------------
2507 Sat Mar 26 23:59:59 2011 UTC
2508 (1 row)
2510 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2511 timezone
2512 ------------------------------
2513 Sat Mar 26 23:00:00 2011 UTC
2514 (1 row)
2516 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2517 timezone
2518 ------------------------------
2519 Sat Mar 26 23:00:01 2011 UTC
2520 (1 row)
2522 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2523 timezone
2524 ------------------------------
2525 Sun Mar 27 00:00:00 2011 UTC
2526 (1 row)
2528 SELECT '2011-03-27 00:00:00'::timestamp AT TIME ZONE 'MSK';
2529 timezone
2530 ------------------------------
2531 Sat Mar 26 21:00:00 2011 UTC
2532 (1 row)
2534 SELECT '2011-03-27 01:00:00'::timestamp AT TIME ZONE 'MSK';
2535 timezone
2536 ------------------------------
2537 Sat Mar 26 22:00:00 2011 UTC
2538 (1 row)
2540 SELECT '2011-03-27 01:59:59'::timestamp AT TIME ZONE 'MSK';
2541 timezone
2542 ------------------------------
2543 Sat Mar 26 22:59:59 2011 UTC
2544 (1 row)
2546 SELECT '2011-03-27 02:00:00'::timestamp AT TIME ZONE 'MSK';
2547 timezone
2548 ------------------------------
2549 Sat Mar 26 22:00:00 2011 UTC
2550 (1 row)
2552 SELECT '2011-03-27 02:00:01'::timestamp AT TIME ZONE 'MSK';
2553 timezone
2554 ------------------------------
2555 Sat Mar 26 22:00:01 2011 UTC
2556 (1 row)
2558 SELECT '2011-03-27 02:59:59'::timestamp AT TIME ZONE 'MSK';
2559 timezone
2560 ------------------------------
2561 Sat Mar 26 22:59:59 2011 UTC
2562 (1 row)
2564 SELECT '2011-03-27 03:00:00'::timestamp AT TIME ZONE 'MSK';
2565 timezone
2566 ------------------------------
2567 Sat Mar 26 23:00:00 2011 UTC
2568 (1 row)
2570 SELECT '2011-03-27 03:00:01'::timestamp AT TIME ZONE 'MSK';
2571 timezone
2572 ------------------------------
2573 Sat Mar 26 23:00:01 2011 UTC
2574 (1 row)
2576 SELECT '2011-03-27 04:00:00'::timestamp AT TIME ZONE 'MSK';
2577 timezone
2578 ------------------------------
2579 Sun Mar 27 00:00:00 2011 UTC
2580 (1 row)
2582 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2583 timezone
2584 ------------------------------
2585 Sat Oct 25 20:00:00 2014 UTC
2586 (1 row)
2588 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'Europe/Moscow';
2589 timezone
2590 ------------------------------
2591 Sat Oct 25 20:59:59 2014 UTC
2592 (1 row)
2594 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2595 timezone
2596 ------------------------------
2597 Sat Oct 25 22:00:00 2014 UTC
2598 (1 row)
2600 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'Europe/Moscow';
2601 timezone
2602 ------------------------------
2603 Sat Oct 25 22:00:01 2014 UTC
2604 (1 row)
2606 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'Europe/Moscow';
2607 timezone
2608 ------------------------------
2609 Sat Oct 25 23:00:00 2014 UTC
2610 (1 row)
2612 SELECT '2014-10-26 00:00:00'::timestamp AT TIME ZONE 'MSK';
2613 timezone
2614 ------------------------------
2615 Sat Oct 25 20:00:00 2014 UTC
2616 (1 row)
2618 SELECT '2014-10-26 00:59:59'::timestamp AT TIME ZONE 'MSK';
2619 timezone
2620 ------------------------------
2621 Sat Oct 25 20:59:59 2014 UTC
2622 (1 row)
2624 SELECT '2014-10-26 01:00:00'::timestamp AT TIME ZONE 'MSK';
2625 timezone
2626 ------------------------------
2627 Sat Oct 25 22:00:00 2014 UTC
2628 (1 row)
2630 SELECT '2014-10-26 01:00:01'::timestamp AT TIME ZONE 'MSK';
2631 timezone
2632 ------------------------------
2633 Sat Oct 25 22:00:01 2014 UTC
2634 (1 row)
2636 SELECT '2014-10-26 02:00:00'::timestamp AT TIME ZONE 'MSK';
2637 timezone
2638 ------------------------------
2639 Sat Oct 25 23:00:00 2014 UTC
2640 (1 row)
2642 SELECT make_timestamptz(2014, 10, 26, 0, 0, 0, 'MSK');
2643 make_timestamptz
2644 ------------------------------
2645 Sat Oct 25 20:00:00 2014 UTC
2646 (1 row)
2648 SELECT make_timestamptz(2014, 10, 26, 1, 0, 0, 'MSK');
2649 make_timestamptz
2650 ------------------------------
2651 Sat Oct 25 22:00:00 2014 UTC
2652 (1 row)
2654 SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
2655 to_timestamp
2656 ------------------------------
2657 Thu Jan 01 00:00:00 1970 UTC
2658 (1 row)
2660 SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
2661 to_timestamp
2662 ------------------------------
2663 Sat Jan 01 00:00:00 2000 UTC
2664 (1 row)
2666 SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
2667 to_timestamp
2668 -------------------------------------
2669 Fri Jan 01 12:34:56.789012 2010 UTC
2670 (1 row)
2672 -- edge cases
2673 SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
2674 to_timestamp
2675 ---------------------------------
2676 Mon Nov 24 00:00:00 4714 UTC BC
2677 (1 row)
2679 -- upper limit varies between integer and float timestamps, so hard to test
2680 -- nonfinite values
2681 SELECT to_timestamp(' Infinity'::float);
2682 to_timestamp
2683 --------------
2684 infinity
2685 (1 row)
2687 SELECT to_timestamp('-Infinity'::float);
2688 to_timestamp
2689 --------------
2690 -infinity
2691 (1 row)
2693 SELECT to_timestamp('NaN'::float);
2694 ERROR: timestamp cannot be NaN
2695 SET TimeZone to 'Europe/Moscow';
2696 SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
2697 timestamptz
2698 ------------------------------
2699 Sun Mar 27 00:00:00 2011 MSK
2700 (1 row)
2702 SELECT '2011-03-26 22:00:00 UTC'::timestamptz;
2703 timestamptz
2704 ------------------------------
2705 Sun Mar 27 01:00:00 2011 MSK
2706 (1 row)
2708 SELECT '2011-03-26 22:59:59 UTC'::timestamptz;
2709 timestamptz
2710 ------------------------------
2711 Sun Mar 27 01:59:59 2011 MSK
2712 (1 row)
2714 SELECT '2011-03-26 23:00:00 UTC'::timestamptz;
2715 timestamptz
2716 ------------------------------
2717 Sun Mar 27 03:00:00 2011 MSK
2718 (1 row)
2720 SELECT '2011-03-26 23:00:01 UTC'::timestamptz;
2721 timestamptz
2722 ------------------------------
2723 Sun Mar 27 03:00:01 2011 MSK
2724 (1 row)
2726 SELECT '2011-03-26 23:59:59 UTC'::timestamptz;
2727 timestamptz
2728 ------------------------------
2729 Sun Mar 27 03:59:59 2011 MSK
2730 (1 row)
2732 SELECT '2011-03-27 00:00:00 UTC'::timestamptz;
2733 timestamptz
2734 ------------------------------
2735 Sun Mar 27 04:00:00 2011 MSK
2736 (1 row)
2738 SELECT '2014-10-25 21:00:00 UTC'::timestamptz;
2739 timestamptz
2740 ------------------------------
2741 Sun Oct 26 01:00:00 2014 MSK
2742 (1 row)
2744 SELECT '2014-10-25 21:59:59 UTC'::timestamptz;
2745 timestamptz
2746 ------------------------------
2747 Sun Oct 26 01:59:59 2014 MSK
2748 (1 row)
2750 SELECT '2014-10-25 22:00:00 UTC'::timestamptz;
2751 timestamptz
2752 ------------------------------
2753 Sun Oct 26 01:00:00 2014 MSK
2754 (1 row)
2756 SELECT '2014-10-25 22:00:01 UTC'::timestamptz;
2757 timestamptz
2758 ------------------------------
2759 Sun Oct 26 01:00:01 2014 MSK
2760 (1 row)
2762 SELECT '2014-10-25 23:00:00 UTC'::timestamptz;
2763 timestamptz
2764 ------------------------------
2765 Sun Oct 26 02:00:00 2014 MSK
2766 (1 row)
2768 RESET TimeZone;
2769 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2770 timezone
2771 --------------------------
2772 Sun Mar 27 00:00:00 2011
2773 (1 row)
2775 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2776 timezone
2777 --------------------------
2778 Sun Mar 27 01:00:00 2011
2779 (1 row)
2781 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2782 timezone
2783 --------------------------
2784 Sun Mar 27 01:59:59 2011
2785 (1 row)
2787 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2788 timezone
2789 --------------------------
2790 Sun Mar 27 03:00:00 2011
2791 (1 row)
2793 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2794 timezone
2795 --------------------------
2796 Sun Mar 27 03:00:01 2011
2797 (1 row)
2799 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2800 timezone
2801 --------------------------
2802 Sun Mar 27 03:59:59 2011
2803 (1 row)
2805 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2806 timezone
2807 --------------------------
2808 Sun Mar 27 04:00:00 2011
2809 (1 row)
2811 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2812 timezone
2813 --------------------------
2814 Sun Oct 26 01:00:00 2014
2815 (1 row)
2817 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2818 timezone
2819 --------------------------
2820 Sun Oct 26 01:59:59 2014
2821 (1 row)
2823 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2824 timezone
2825 --------------------------
2826 Sun Oct 26 01:00:00 2014
2827 (1 row)
2829 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2830 timezone
2831 --------------------------
2832 Sun Oct 26 01:00:01 2014
2833 (1 row)
2835 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'Europe/Moscow';
2836 timezone
2837 --------------------------
2838 Sun Oct 26 02:00:00 2014
2839 (1 row)
2841 SELECT '2011-03-26 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2842 timezone
2843 --------------------------
2844 Sun Mar 27 00:00:00 2011
2845 (1 row)
2847 SELECT '2011-03-26 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2848 timezone
2849 --------------------------
2850 Sun Mar 27 01:00:00 2011
2851 (1 row)
2853 SELECT '2011-03-26 22:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
2854 timezone
2855 --------------------------
2856 Sun Mar 27 01:59:59 2011
2857 (1 row)
2859 SELECT '2011-03-26 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2860 timezone
2861 --------------------------
2862 Sun Mar 27 03:00:00 2011
2863 (1 row)
2865 SELECT '2011-03-26 23:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
2866 timezone
2867 --------------------------
2868 Sun Mar 27 03:00:01 2011
2869 (1 row)
2871 SELECT '2011-03-26 23:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
2872 timezone
2873 --------------------------
2874 Sun Mar 27 03:59:59 2011
2875 (1 row)
2877 SELECT '2011-03-27 00:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2878 timezone
2879 --------------------------
2880 Sun Mar 27 04:00:00 2011
2881 (1 row)
2883 SELECT '2014-10-25 21:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2884 timezone
2885 --------------------------
2886 Sun Oct 26 01:00:00 2014
2887 (1 row)
2889 SELECT '2014-10-25 21:59:59 UTC'::timestamptz AT TIME ZONE 'MSK';
2890 timezone
2891 --------------------------
2892 Sun Oct 26 01:59:59 2014
2893 (1 row)
2895 SELECT '2014-10-25 22:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2896 timezone
2897 --------------------------
2898 Sun Oct 26 01:00:00 2014
2899 (1 row)
2901 SELECT '2014-10-25 22:00:01 UTC'::timestamptz AT TIME ZONE 'MSK';
2902 timezone
2903 --------------------------
2904 Sun Oct 26 01:00:01 2014
2905 (1 row)
2907 SELECT '2014-10-25 23:00:00 UTC'::timestamptz AT TIME ZONE 'MSK';
2908 timezone
2909 --------------------------
2910 Sun Oct 26 02:00:00 2014
2911 (1 row)
2913 --
2914 -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
2915 --
2916 create temp table tmptz (f1 timestamptz primary key);
2917 insert into tmptz values ('2017-01-18 00:00+00');
2918 explain (costs off)
2919 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
2920 QUERY PLAN
2921 -----------------------------------------------------------------------------------------------------
2922 Seq Scan on tmptz
2923 Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
2924 (2 rows)
2926 select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
2927 f1
2928 ------------------------------
2929 Tue Jan 17 16:00:00 2017 PST
2930 (1 row)