| blob | e287260051cecc4b5796ba27feb9dfaf70310482 |
1 --
2 -- TIMESTAMP
3 --
4 CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without 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 TIMESTAMP_TBL VALUES ('today');
12 INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
13 INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
14 -- time zone should be ignored by this data type
15 INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
16 INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
17 SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today';
18 one
19 -----
20 1
21 (1 row)
23 SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow';
24 three
25 -------
26 3
27 (1 row)
29 SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday';
30 one
31 -----
32 1
33 (1 row)
35 COMMIT;
36 DELETE FROM TIMESTAMP_TBL;
37 -- Verify that 'now' *does* change over a reasonable interval such as 100 msec,
38 -- and that it doesn't change over the same interval within a transaction block
39 INSERT INTO TIMESTAMP_TBL VALUES ('now');
40 SELECT pg_sleep(0.1);
41 pg_sleep
42 ----------
44 (1 row)
46 BEGIN;
47 INSERT INTO TIMESTAMP_TBL VALUES ('now');
48 SELECT pg_sleep(0.1);
49 pg_sleep
50 ----------
52 (1 row)
54 INSERT INTO TIMESTAMP_TBL VALUES ('now');
55 SELECT pg_sleep(0.1);
56 pg_sleep
57 ----------
59 (1 row)
61 SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now';
62 two
63 -----
64 2
65 (1 row)
67 SELECT count(d1) AS three, count(DISTINCT d1) AS two FROM TIMESTAMP_TBL;
68 three | two
69 -------+-----
70 3 | 2
71 (1 row)
73 COMMIT;
74 TRUNCATE TIMESTAMP_TBL;
75 -- Special values
76 INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
77 INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
78 INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
79 SELECT timestamp 'infinity' = timestamp '+infinity' AS t;
80 t
81 ---
82 t
83 (1 row)
85 -- Postgres v6.0 standard output format
86 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
87 -- Variations on Postgres v6.1 standard output format
88 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
89 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
90 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
91 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
92 INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
93 -- ISO 8601 format
94 INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02');
95 INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02 03:04:05');
96 INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-08');
97 INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-0800');
98 INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00');
99 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800');
100 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00');
101 INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20');
102 -- POSIX format (note that the timezone abbrev is just decoration here)
103 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8');
104 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1');
105 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2');
106 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8');
107 INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00');
108 -- Variations for acceptable input formats
109 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
110 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
111 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 5:32PM 1997');
112 INSERT INTO TIMESTAMP_TBL VALUES ('1997/02/10 17:32:01-0800');
113 INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST');
114 INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST');
115 INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
116 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
117 set datestyle to ymd;
118 INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
119 INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
120 reset datestyle;
121 INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC');
122 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York');
123 -- this fails (even though TZ is a no-op, we still look it up)
124 INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist');
125 ERROR: time zone "america/does_not_exist" not recognized
126 LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/D...
127 ^
128 -- Test non-error-throwing API
129 SELECT pg_input_is_valid('now', 'timestamp');
130 pg_input_is_valid
131 -------------------
132 t
133 (1 row)
135 SELECT pg_input_is_valid('garbage', 'timestamp');
136 pg_input_is_valid
137 -------------------
138 f
139 (1 row)
141 SELECT pg_input_is_valid('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamp');
142 pg_input_is_valid
143 -------------------
144 f
145 (1 row)
147 SELECT * FROM pg_input_error_info('garbage', 'timestamp');
148 message | detail | hint | sql_error_code
149 ----------------------------------------------------+--------+------+----------------
150 invalid input syntax for type timestamp: "garbage" | | | 22007
151 (1 row)
153 SELECT * FROM pg_input_error_info('2001-01-01 00:00 Nehwon/Lankhmar', 'timestamp');
154 message | detail | hint | sql_error_code
155 --------------------------------------------+--------+------+----------------
156 time zone "nehwon/lankhmar" not recognized | | | 22023
157 (1 row)
159 -- Check date conversion and date arithmetic
160 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
161 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
162 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 11 17:32:01 1997');
163 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 12 17:32:01 1997');
164 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 13 17:32:01 1997');
165 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 14 17:32:01 1997');
166 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 15 17:32:01 1997');
167 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
168 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097 BC');
169 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097');
170 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0597');
171 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1097');
172 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1697');
173 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1797');
174 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1897');
175 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
176 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 2097');
177 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1996');
178 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1996');
179 INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1996');
180 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1996');
181 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1996');
182 INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 1997');
183 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1997');
184 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997');
185 ERROR: date/time field value out of range: "Feb 29 17:32:01 1997"
186 LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997');
187 ^
188 INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1997');
189 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1997');
190 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1997');
191 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999');
192 INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000');
193 INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000');
194 INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001');
195 -- Currently unsupported syntax and ranges
196 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
197 ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097"
198 LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
199 ^
200 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC');
201 ERROR: timestamp out of range: "Feb 16 17:32:01 5097 BC"
202 LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC')...
203 ^
204 SELECT d1 FROM TIMESTAMP_TBL;
205 d1
206 -----------------------------
207 -infinity
208 infinity
209 Thu Jan 01 00:00:00 1970
210 Mon Feb 10 17:32:01 1997
211 Mon Feb 10 17:32:01 1997
212 Mon Feb 10 17:32:02 1997
213 Mon Feb 10 17:32:01.4 1997
214 Mon Feb 10 17:32:01.5 1997
215 Mon Feb 10 17:32:01.6 1997
216 Thu Jan 02 00:00:00 1997
217 Thu Jan 02 03:04:05 1997
218 Mon Feb 10 17:32:01 1997
219 Mon Feb 10 17:32:01 1997
220 Mon Feb 10 17:32:01 1997
221 Mon Feb 10 17:32:01 1997
222 Tue Jun 10 17:32:01 1997
223 Sat Sep 22 18:19:20 2001
224 Wed Mar 15 08:14:01 2000
225 Wed Mar 15 13:14:02 2000
226 Wed Mar 15 12:14:03 2000
227 Wed Mar 15 03:14:04 2000
228 Wed Mar 15 02:14:05 2000
229 Mon Feb 10 17:32:01 1997
230 Mon Feb 10 17:32:01 1997
231 Mon Feb 10 17:32:00 1997
232 Mon Feb 10 17:32:01 1997
233 Mon Feb 10 17:32:01 1997
234 Mon Feb 10 17:32:01 1997
235 Mon Feb 10 17:32:01 1997
236 Mon Feb 10 17:32:01 1997
237 Mon Feb 10 17:32:01 1997
238 Mon Feb 10 17:32:01 1997
239 Mon Feb 10 17:32:01 1997
240 Mon Feb 10 17:32:01 1997
241 Tue Jun 10 18:32:01 1997
242 Mon Feb 10 17:32:01 1997
243 Tue Feb 11 17:32:01 1997
244 Wed Feb 12 17:32:01 1997
245 Thu Feb 13 17:32:01 1997
246 Fri Feb 14 17:32:01 1997
247 Sat Feb 15 17:32:01 1997
248 Sun Feb 16 17:32:01 1997
249 Tue Feb 16 17:32:01 0097 BC
250 Sat Feb 16 17:32:01 0097
251 Thu Feb 16 17:32:01 0597
252 Tue Feb 16 17:32:01 1097
253 Sat Feb 16 17:32:01 1697
254 Thu Feb 16 17:32:01 1797
255 Tue Feb 16 17:32:01 1897
256 Sun Feb 16 17:32:01 1997
257 Sat Feb 16 17:32:01 2097
258 Wed Feb 28 17:32:01 1996
259 Thu Feb 29 17:32:01 1996
260 Fri Mar 01 17:32:01 1996
261 Mon Dec 30 17:32:01 1996
262 Tue Dec 31 17:32:01 1996
263 Wed Jan 01 17:32:01 1997
264 Fri Feb 28 17:32:01 1997
265 Sat Mar 01 17:32:01 1997
266 Tue Dec 30 17:32:01 1997
267 Wed Dec 31 17:32:01 1997
268 Fri Dec 31 17:32:01 1999
269 Sat Jan 01 17:32:01 2000
270 Sun Dec 31 17:32:01 2000
271 Mon Jan 01 17:32:01 2001
272 (65 rows)
274 -- Check behavior at the boundaries of the timestamp range
275 SELECT '4714-11-24 00:00:00 BC'::timestamp;
276 timestamp
277 -----------------------------
278 Mon Nov 24 00:00:00 4714 BC
279 (1 row)
281 SELECT '4714-11-23 23:59:59 BC'::timestamp; -- out of range
282 ERROR: timestamp out of range: "4714-11-23 23:59:59 BC"
283 LINE 1: SELECT '4714-11-23 23:59:59 BC'::timestamp;
284 ^
285 SELECT '294276-12-31 23:59:59'::timestamp;
286 timestamp
287 ----------------------------
288 Sun Dec 31 23:59:59 294276
289 (1 row)
291 SELECT '294277-01-01 00:00:00'::timestamp; -- out of range
292 ERROR: timestamp out of range: "294277-01-01 00:00:00"
293 LINE 1: SELECT '294277-01-01 00:00:00'::timestamp;
294 ^
295 -- Demonstrate functions and operators
296 SELECT d1 FROM TIMESTAMP_TBL
297 WHERE d1 > timestamp without time zone '1997-01-02';
298 d1
299 ----------------------------
300 infinity
301 Mon Feb 10 17:32:01 1997
302 Mon Feb 10 17:32:01 1997
303 Mon Feb 10 17:32:02 1997
304 Mon Feb 10 17:32:01.4 1997
305 Mon Feb 10 17:32:01.5 1997
306 Mon Feb 10 17:32:01.6 1997
307 Thu Jan 02 03:04:05 1997
308 Mon Feb 10 17:32:01 1997
309 Mon Feb 10 17:32:01 1997
310 Mon Feb 10 17:32:01 1997
311 Mon Feb 10 17:32:01 1997
312 Tue Jun 10 17:32:01 1997
313 Sat Sep 22 18:19:20 2001
314 Wed Mar 15 08:14:01 2000
315 Wed Mar 15 13:14:02 2000
316 Wed Mar 15 12:14:03 2000
317 Wed Mar 15 03:14:04 2000
318 Wed Mar 15 02:14:05 2000
319 Mon Feb 10 17:32:01 1997
320 Mon Feb 10 17:32:01 1997
321 Mon Feb 10 17:32:00 1997
322 Mon Feb 10 17:32:01 1997
323 Mon Feb 10 17:32:01 1997
324 Mon Feb 10 17:32:01 1997
325 Mon Feb 10 17:32:01 1997
326 Mon Feb 10 17:32:01 1997
327 Mon Feb 10 17:32:01 1997
328 Mon Feb 10 17:32:01 1997
329 Mon Feb 10 17:32:01 1997
330 Mon Feb 10 17:32:01 1997
331 Tue Jun 10 18:32:01 1997
332 Mon Feb 10 17:32:01 1997
333 Tue Feb 11 17:32:01 1997
334 Wed Feb 12 17:32:01 1997
335 Thu Feb 13 17:32:01 1997
336 Fri Feb 14 17:32:01 1997
337 Sat Feb 15 17:32:01 1997
338 Sun Feb 16 17:32:01 1997
339 Sun Feb 16 17:32:01 1997
340 Sat Feb 16 17:32:01 2097
341 Fri Feb 28 17:32:01 1997
342 Sat Mar 01 17:32:01 1997
343 Tue Dec 30 17:32:01 1997
344 Wed Dec 31 17:32:01 1997
345 Fri Dec 31 17:32:01 1999
346 Sat Jan 01 17:32:01 2000
347 Sun Dec 31 17:32:01 2000
348 Mon Jan 01 17:32:01 2001
349 (49 rows)
351 SELECT d1 FROM TIMESTAMP_TBL
352 WHERE d1 < timestamp without time zone '1997-01-02';
353 d1
354 -----------------------------
355 -infinity
356 Thu Jan 01 00:00:00 1970
357 Tue Feb 16 17:32:01 0097 BC
358 Sat Feb 16 17:32:01 0097
359 Thu Feb 16 17:32:01 0597
360 Tue Feb 16 17:32:01 1097
361 Sat Feb 16 17:32:01 1697
362 Thu Feb 16 17:32:01 1797
363 Tue Feb 16 17:32:01 1897
364 Wed Feb 28 17:32:01 1996
365 Thu Feb 29 17:32:01 1996
366 Fri Mar 01 17:32:01 1996
367 Mon Dec 30 17:32:01 1996
368 Tue Dec 31 17:32:01 1996
369 Wed Jan 01 17:32:01 1997
370 (15 rows)
372 SELECT d1 FROM TIMESTAMP_TBL
373 WHERE d1 = timestamp without time zone '1997-01-02';
374 d1
375 --------------------------
376 Thu Jan 02 00:00:00 1997
377 (1 row)
379 SELECT d1 FROM TIMESTAMP_TBL
380 WHERE d1 != timestamp without time zone '1997-01-02';
381 d1
382 -----------------------------
383 -infinity
384 infinity
385 Thu Jan 01 00:00:00 1970
386 Mon Feb 10 17:32:01 1997
387 Mon Feb 10 17:32:01 1997
388 Mon Feb 10 17:32:02 1997
389 Mon Feb 10 17:32:01.4 1997
390 Mon Feb 10 17:32:01.5 1997
391 Mon Feb 10 17:32:01.6 1997
392 Thu Jan 02 03:04:05 1997
393 Mon Feb 10 17:32:01 1997
394 Mon Feb 10 17:32:01 1997
395 Mon Feb 10 17:32:01 1997
396 Mon Feb 10 17:32:01 1997
397 Tue Jun 10 17:32:01 1997
398 Sat Sep 22 18:19:20 2001
399 Wed Mar 15 08:14:01 2000
400 Wed Mar 15 13:14:02 2000
401 Wed Mar 15 12:14:03 2000
402 Wed Mar 15 03:14:04 2000
403 Wed Mar 15 02:14:05 2000
404 Mon Feb 10 17:32:01 1997
405 Mon Feb 10 17:32:01 1997
406 Mon Feb 10 17:32:00 1997
407 Mon Feb 10 17:32:01 1997
408 Mon Feb 10 17:32:01 1997
409 Mon Feb 10 17:32:01 1997
410 Mon Feb 10 17:32:01 1997
411 Mon Feb 10 17:32:01 1997
412 Mon Feb 10 17:32:01 1997
413 Mon Feb 10 17:32:01 1997
414 Mon Feb 10 17:32:01 1997
415 Mon Feb 10 17:32:01 1997
416 Tue Jun 10 18:32:01 1997
417 Mon Feb 10 17:32:01 1997
418 Tue Feb 11 17:32:01 1997
419 Wed Feb 12 17:32:01 1997
420 Thu Feb 13 17:32:01 1997
421 Fri Feb 14 17:32:01 1997
422 Sat Feb 15 17:32:01 1997
423 Sun Feb 16 17:32:01 1997
424 Tue Feb 16 17:32:01 0097 BC
425 Sat Feb 16 17:32:01 0097
426 Thu Feb 16 17:32:01 0597
427 Tue Feb 16 17:32:01 1097
428 Sat Feb 16 17:32:01 1697
429 Thu Feb 16 17:32:01 1797
430 Tue Feb 16 17:32:01 1897
431 Sun Feb 16 17:32:01 1997
432 Sat Feb 16 17:32:01 2097
433 Wed Feb 28 17:32:01 1996
434 Thu Feb 29 17:32:01 1996
435 Fri Mar 01 17:32:01 1996
436 Mon Dec 30 17:32:01 1996
437 Tue Dec 31 17:32:01 1996
438 Wed Jan 01 17:32:01 1997
439 Fri Feb 28 17:32:01 1997
440 Sat Mar 01 17:32:01 1997
441 Tue Dec 30 17:32:01 1997
442 Wed Dec 31 17:32:01 1997
443 Fri Dec 31 17:32:01 1999
444 Sat Jan 01 17:32:01 2000
445 Sun Dec 31 17:32:01 2000
446 Mon Jan 01 17:32:01 2001
447 (64 rows)
449 SELECT d1 FROM TIMESTAMP_TBL
450 WHERE d1 <= timestamp without time zone '1997-01-02';
451 d1
452 -----------------------------
453 -infinity
454 Thu Jan 01 00:00:00 1970
455 Thu Jan 02 00:00:00 1997
456 Tue Feb 16 17:32:01 0097 BC
457 Sat Feb 16 17:32:01 0097
458 Thu Feb 16 17:32:01 0597
459 Tue Feb 16 17:32:01 1097
460 Sat Feb 16 17:32:01 1697
461 Thu Feb 16 17:32:01 1797
462 Tue Feb 16 17:32:01 1897
463 Wed Feb 28 17:32:01 1996
464 Thu Feb 29 17:32:01 1996
465 Fri Mar 01 17:32:01 1996
466 Mon Dec 30 17:32:01 1996
467 Tue Dec 31 17:32:01 1996
468 Wed Jan 01 17:32:01 1997
469 (16 rows)
471 SELECT d1 FROM TIMESTAMP_TBL
472 WHERE d1 >= timestamp without time zone '1997-01-02';
473 d1
474 ----------------------------
475 infinity
476 Mon Feb 10 17:32:01 1997
477 Mon Feb 10 17:32:01 1997
478 Mon Feb 10 17:32:02 1997
479 Mon Feb 10 17:32:01.4 1997
480 Mon Feb 10 17:32:01.5 1997
481 Mon Feb 10 17:32:01.6 1997
482 Thu Jan 02 00:00:00 1997
483 Thu Jan 02 03:04:05 1997
484 Mon Feb 10 17:32:01 1997
485 Mon Feb 10 17:32:01 1997
486 Mon Feb 10 17:32:01 1997
487 Mon Feb 10 17:32:01 1997
488 Tue Jun 10 17:32:01 1997
489 Sat Sep 22 18:19:20 2001
490 Wed Mar 15 08:14:01 2000
491 Wed Mar 15 13:14:02 2000
492 Wed Mar 15 12:14:03 2000
493 Wed Mar 15 03:14:04 2000
494 Wed Mar 15 02:14:05 2000
495 Mon Feb 10 17:32:01 1997
496 Mon Feb 10 17:32:01 1997
497 Mon Feb 10 17:32:00 1997
498 Mon Feb 10 17:32:01 1997
499 Mon Feb 10 17:32:01 1997
500 Mon Feb 10 17:32:01 1997
501 Mon Feb 10 17:32:01 1997
502 Mon Feb 10 17:32:01 1997
503 Mon Feb 10 17:32:01 1997
504 Mon Feb 10 17:32:01 1997
505 Mon Feb 10 17:32:01 1997
506 Mon Feb 10 17:32:01 1997
507 Tue Jun 10 18:32:01 1997
508 Mon Feb 10 17:32:01 1997
509 Tue Feb 11 17:32:01 1997
510 Wed Feb 12 17:32:01 1997
511 Thu Feb 13 17:32:01 1997
512 Fri Feb 14 17:32:01 1997
513 Sat Feb 15 17:32:01 1997
514 Sun Feb 16 17:32:01 1997
515 Sun Feb 16 17:32:01 1997
516 Sat Feb 16 17:32:01 2097
517 Fri Feb 28 17:32:01 1997
518 Sat Mar 01 17:32:01 1997
519 Tue Dec 30 17:32:01 1997
520 Wed Dec 31 17:32:01 1997
521 Fri Dec 31 17:32:01 1999
522 Sat Jan 01 17:32:01 2000
523 Sun Dec 31 17:32:01 2000
524 Mon Jan 01 17:32:01 2001
525 (50 rows)
527 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
528 FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
529 diff
530 ----------------------------------------
531 @ 9863 days ago
532 @ 39 days 17 hours 32 mins 1 sec
533 @ 39 days 17 hours 32 mins 1 sec
534 @ 39 days 17 hours 32 mins 2 secs
535 @ 39 days 17 hours 32 mins 1.4 secs
536 @ 39 days 17 hours 32 mins 1.5 secs
537 @ 39 days 17 hours 32 mins 1.6 secs
538 @ 0
539 @ 3 hours 4 mins 5 secs
540 @ 39 days 17 hours 32 mins 1 sec
541 @ 39 days 17 hours 32 mins 1 sec
542 @ 39 days 17 hours 32 mins 1 sec
543 @ 39 days 17 hours 32 mins 1 sec
544 @ 159 days 17 hours 32 mins 1 sec
545 @ 1724 days 18 hours 19 mins 20 secs
546 @ 1168 days 8 hours 14 mins 1 sec
547 @ 1168 days 13 hours 14 mins 2 secs
548 @ 1168 days 12 hours 14 mins 3 secs
549 @ 1168 days 3 hours 14 mins 4 secs
550 @ 1168 days 2 hours 14 mins 5 secs
551 @ 39 days 17 hours 32 mins 1 sec
552 @ 39 days 17 hours 32 mins 1 sec
553 @ 39 days 17 hours 32 mins
554 @ 39 days 17 hours 32 mins 1 sec
555 @ 39 days 17 hours 32 mins 1 sec
556 @ 39 days 17 hours 32 mins 1 sec
557 @ 39 days 17 hours 32 mins 1 sec
558 @ 39 days 17 hours 32 mins 1 sec
559 @ 39 days 17 hours 32 mins 1 sec
560 @ 39 days 17 hours 32 mins 1 sec
561 @ 39 days 17 hours 32 mins 1 sec
562 @ 39 days 17 hours 32 mins 1 sec
563 @ 159 days 18 hours 32 mins 1 sec
564 @ 39 days 17 hours 32 mins 1 sec
565 @ 40 days 17 hours 32 mins 1 sec
566 @ 41 days 17 hours 32 mins 1 sec
567 @ 42 days 17 hours 32 mins 1 sec
568 @ 43 days 17 hours 32 mins 1 sec
569 @ 44 days 17 hours 32 mins 1 sec
570 @ 45 days 17 hours 32 mins 1 sec
571 @ 45 days 17 hours 32 mins 1 sec
572 @ 308 days 6 hours 27 mins 59 secs ago
573 @ 307 days 6 hours 27 mins 59 secs ago
574 @ 306 days 6 hours 27 mins 59 secs ago
575 @ 2 days 6 hours 27 mins 59 secs ago
576 @ 1 day 6 hours 27 mins 59 secs ago
577 @ 6 hours 27 mins 59 secs ago
578 @ 57 days 17 hours 32 mins 1 sec
579 @ 58 days 17 hours 32 mins 1 sec
580 @ 362 days 17 hours 32 mins 1 sec
581 @ 363 days 17 hours 32 mins 1 sec
582 @ 1093 days 17 hours 32 mins 1 sec
583 @ 1094 days 17 hours 32 mins 1 sec
584 @ 1459 days 17 hours 32 mins 1 sec
585 @ 1460 days 17 hours 32 mins 1 sec
586 (55 rows)
588 SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
589 week_trunc
590 --------------------------
591 Mon Feb 23 00:00:00 2004
592 (1 row)
594 -- verify date_bin behaves the same as date_trunc for relevant intervals
595 -- case 1: AD dates, origin < input
596 SELECT
597 str,
598 interval,
599 date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2001-01-01') AS equal
600 FROM (
601 VALUES
602 ('week', '7 d'),
603 ('day', '1 d'),
604 ('hour', '1 h'),
605 ('minute', '1 m'),
606 ('second', '1 s'),
607 ('millisecond', '1 ms'),
608 ('microsecond', '1 us')
609 ) intervals (str, interval),
610 (VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
611 str | interval | equal
612 -------------+----------+-------
613 week | 7 d | t
614 day | 1 d | t
615 hour | 1 h | t
616 minute | 1 m | t
617 second | 1 s | t
618 millisecond | 1 ms | t
619 microsecond | 1 us | t
620 (7 rows)
622 -- case 2: BC dates, origin < input
623 SELECT
624 str,
625 interval,
626 date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
627 FROM (
628 VALUES
629 ('week', '7 d'),
630 ('day', '1 d'),
631 ('hour', '1 h'),
632 ('minute', '1 m'),
633 ('second', '1 s'),
634 ('millisecond', '1 ms'),
635 ('microsecond', '1 us')
636 ) intervals (str, interval),
637 (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
638 str | interval | equal
639 -------------+----------+-------
640 week | 7 d | t
641 day | 1 d | t
642 hour | 1 h | t
643 minute | 1 m | t
644 second | 1 s | t
645 millisecond | 1 ms | t
646 microsecond | 1 us | t
647 (7 rows)
649 -- case 3: AD dates, origin > input
650 SELECT
651 str,
652 interval,
653 date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
654 FROM (
655 VALUES
656 ('week', '7 d'),
657 ('day', '1 d'),
658 ('hour', '1 h'),
659 ('minute', '1 m'),
660 ('second', '1 s'),
661 ('millisecond', '1 ms'),
662 ('microsecond', '1 us')
663 ) intervals (str, interval),
664 (VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
665 str | interval | equal
666 -------------+----------+-------
667 week | 7 d | t
668 day | 1 d | t
669 hour | 1 h | t
670 minute | 1 m | t
671 second | 1 s | t
672 millisecond | 1 ms | t
673 microsecond | 1 us | t
674 (7 rows)
676 -- case 4: BC dates, origin > input
677 SELECT
678 str,
679 interval,
680 date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
681 FROM (
682 VALUES
683 ('week', '7 d'),
684 ('day', '1 d'),
685 ('hour', '1 h'),
686 ('minute', '1 m'),
687 ('second', '1 s'),
688 ('millisecond', '1 ms'),
689 ('microsecond', '1 us')
690 ) intervals (str, interval),
691 (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
692 str | interval | equal
693 -------------+----------+-------
694 week | 7 d | t
695 day | 1 d | t
696 hour | 1 h | t
697 minute | 1 m | t
698 second | 1 s | t
699 millisecond | 1 ms | t
700 microsecond | 1 us | t
701 (7 rows)
703 -- bin timestamps into arbitrary intervals
704 SELECT
705 interval,
706 ts,
707 origin,
708 date_bin(interval::interval, ts, origin)
709 FROM (
710 VALUES
711 ('15 days'),
712 ('2 hours'),
713 ('1 hour 30 minutes'),
714 ('15 minutes'),
715 ('10 seconds'),
716 ('100 milliseconds'),
717 ('250 microseconds')
718 ) intervals (interval),
719 (VALUES (timestamp '2020-02-11 15:44:17.71393')) ts (ts),
720 (VALUES (timestamp '2001-01-01')) origin (origin);
721 interval | ts | origin | date_bin
722 -------------------+--------------------------------+--------------------------+--------------------------------
723 15 days | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Thu Feb 06 00:00:00 2020
724 2 hours | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 14:00:00 2020
725 1 hour 30 minutes | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:00:00 2020
726 15 minutes | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:30:00 2020
727 10 seconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:10 2020
728 100 milliseconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:17.7 2020
729 250 microseconds | Tue Feb 11 15:44:17.71393 2020 | Mon Jan 01 00:00:00 2001 | Tue Feb 11 15:44:17.71375 2020
730 (7 rows)
732 -- shift bins using the origin parameter:
733 SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
734 date_bin
735 --------------------------
736 Sat Feb 01 00:57:30 2020
737 (1 row)
739 -- test roundoff edge case when source < origin
740 SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
741 date_bin
742 --------------------------
743 Thu Feb 01 15:00:00 2024
744 (1 row)
746 -- disallow intervals with months or years
747 SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
748 ERROR: timestamps cannot be binned into intervals containing months or years
749 SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
750 ERROR: timestamps cannot be binned into intervals containing months or years
751 -- disallow zero intervals
752 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
753 ERROR: stride must be greater than zero
754 -- disallow negative intervals
755 SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
756 ERROR: stride must be greater than zero
757 -- test overflow cases
758 select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC');
759 ERROR: interval out of range
760 select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp);
761 ERROR: interval out of range
762 select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp);
763 ERROR: timestamp out of range
764 -- Test casting within a BETWEEN qualifier
765 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
766 FROM TIMESTAMP_TBL
767 WHERE d1 BETWEEN timestamp without time zone '1902-01-01'
768 AND timestamp without time zone '2038-01-01';
769 diff
770 ----------------------------------------
771 @ 9863 days ago
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 2 secs
775 @ 39 days 17 hours 32 mins 1.4 secs
776 @ 39 days 17 hours 32 mins 1.5 secs
777 @ 39 days 17 hours 32 mins 1.6 secs
778 @ 0
779 @ 3 hours 4 mins 5 secs
780 @ 39 days 17 hours 32 mins 1 sec
781 @ 39 days 17 hours 32 mins 1 sec
782 @ 39 days 17 hours 32 mins 1 sec
783 @ 39 days 17 hours 32 mins 1 sec
784 @ 159 days 17 hours 32 mins 1 sec
785 @ 1724 days 18 hours 19 mins 20 secs
786 @ 1168 days 8 hours 14 mins 1 sec
787 @ 1168 days 13 hours 14 mins 2 secs
788 @ 1168 days 12 hours 14 mins 3 secs
789 @ 1168 days 3 hours 14 mins 4 secs
790 @ 1168 days 2 hours 14 mins 5 secs
791 @ 39 days 17 hours 32 mins 1 sec
792 @ 39 days 17 hours 32 mins 1 sec
793 @ 39 days 17 hours 32 mins
794 @ 39 days 17 hours 32 mins 1 sec
795 @ 39 days 17 hours 32 mins 1 sec
796 @ 39 days 17 hours 32 mins 1 sec
797 @ 39 days 17 hours 32 mins 1 sec
798 @ 39 days 17 hours 32 mins 1 sec
799 @ 39 days 17 hours 32 mins 1 sec
800 @ 39 days 17 hours 32 mins 1 sec
801 @ 39 days 17 hours 32 mins 1 sec
802 @ 39 days 17 hours 32 mins 1 sec
803 @ 159 days 18 hours 32 mins 1 sec
804 @ 39 days 17 hours 32 mins 1 sec
805 @ 40 days 17 hours 32 mins 1 sec
806 @ 41 days 17 hours 32 mins 1 sec
807 @ 42 days 17 hours 32 mins 1 sec
808 @ 43 days 17 hours 32 mins 1 sec
809 @ 44 days 17 hours 32 mins 1 sec
810 @ 45 days 17 hours 32 mins 1 sec
811 @ 45 days 17 hours 32 mins 1 sec
812 @ 308 days 6 hours 27 mins 59 secs ago
813 @ 307 days 6 hours 27 mins 59 secs ago
814 @ 306 days 6 hours 27 mins 59 secs ago
815 @ 2 days 6 hours 27 mins 59 secs ago
816 @ 1 day 6 hours 27 mins 59 secs ago
817 @ 6 hours 27 mins 59 secs ago
818 @ 57 days 17 hours 32 mins 1 sec
819 @ 58 days 17 hours 32 mins 1 sec
820 @ 362 days 17 hours 32 mins 1 sec
821 @ 363 days 17 hours 32 mins 1 sec
822 @ 1093 days 17 hours 32 mins 1 sec
823 @ 1094 days 17 hours 32 mins 1 sec
824 @ 1459 days 17 hours 32 mins 1 sec
825 @ 1460 days 17 hours 32 mins 1 sec
826 (55 rows)
828 -- DATE_PART (timestamp_part)
829 SELECT d1 as "timestamp",
830 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
831 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
832 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
833 FROM TIMESTAMP_TBL;
834 timestamp | year | month | day | hour | minute | second
835 -----------------------------+-----------+-------+-----+------+--------+--------
836 -infinity | -Infinity | | | | |
837 infinity | Infinity | | | | |
838 Thu Jan 01 00:00:00 1970 | 1970 | 1 | 1 | 0 | 0 | 0
839 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
840 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
841 Mon Feb 10 17:32:02 1997 | 1997 | 2 | 10 | 17 | 32 | 2
842 Mon Feb 10 17:32:01.4 1997 | 1997 | 2 | 10 | 17 | 32 | 1.4
843 Mon Feb 10 17:32:01.5 1997 | 1997 | 2 | 10 | 17 | 32 | 1.5
844 Mon Feb 10 17:32:01.6 1997 | 1997 | 2 | 10 | 17 | 32 | 1.6
845 Thu Jan 02 00:00:00 1997 | 1997 | 1 | 2 | 0 | 0 | 0
846 Thu Jan 02 03:04:05 1997 | 1997 | 1 | 2 | 3 | 4 | 5
847 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
848 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
849 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
850 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
851 Tue Jun 10 17:32:01 1997 | 1997 | 6 | 10 | 17 | 32 | 1
852 Sat Sep 22 18:19:20 2001 | 2001 | 9 | 22 | 18 | 19 | 20
853 Wed Mar 15 08:14:01 2000 | 2000 | 3 | 15 | 8 | 14 | 1
854 Wed Mar 15 13:14:02 2000 | 2000 | 3 | 15 | 13 | 14 | 2
855 Wed Mar 15 12:14:03 2000 | 2000 | 3 | 15 | 12 | 14 | 3
856 Wed Mar 15 03:14:04 2000 | 2000 | 3 | 15 | 3 | 14 | 4
857 Wed Mar 15 02:14:05 2000 | 2000 | 3 | 15 | 2 | 14 | 5
858 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
859 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
860 Mon Feb 10 17:32:00 1997 | 1997 | 2 | 10 | 17 | 32 | 0
861 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
862 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
863 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
864 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
865 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
866 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
867 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
868 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
869 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
870 Tue Jun 10 18:32:01 1997 | 1997 | 6 | 10 | 18 | 32 | 1
871 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
872 Tue Feb 11 17:32:01 1997 | 1997 | 2 | 11 | 17 | 32 | 1
873 Wed Feb 12 17:32:01 1997 | 1997 | 2 | 12 | 17 | 32 | 1
874 Thu Feb 13 17:32:01 1997 | 1997 | 2 | 13 | 17 | 32 | 1
875 Fri Feb 14 17:32:01 1997 | 1997 | 2 | 14 | 17 | 32 | 1
876 Sat Feb 15 17:32:01 1997 | 1997 | 2 | 15 | 17 | 32 | 1
877 Sun Feb 16 17:32:01 1997 | 1997 | 2 | 16 | 17 | 32 | 1
878 Tue Feb 16 17:32:01 0097 BC | -97 | 2 | 16 | 17 | 32 | 1
879 Sat Feb 16 17:32:01 0097 | 97 | 2 | 16 | 17 | 32 | 1
880 Thu Feb 16 17:32:01 0597 | 597 | 2 | 16 | 17 | 32 | 1
881 Tue Feb 16 17:32:01 1097 | 1097 | 2 | 16 | 17 | 32 | 1
882 Sat Feb 16 17:32:01 1697 | 1697 | 2 | 16 | 17 | 32 | 1
883 Thu Feb 16 17:32:01 1797 | 1797 | 2 | 16 | 17 | 32 | 1
884 Tue Feb 16 17:32:01 1897 | 1897 | 2 | 16 | 17 | 32 | 1
885 Sun Feb 16 17:32:01 1997 | 1997 | 2 | 16 | 17 | 32 | 1
886 Sat Feb 16 17:32:01 2097 | 2097 | 2 | 16 | 17 | 32 | 1
887 Wed Feb 28 17:32:01 1996 | 1996 | 2 | 28 | 17 | 32 | 1
888 Thu Feb 29 17:32:01 1996 | 1996 | 2 | 29 | 17 | 32 | 1
889 Fri Mar 01 17:32:01 1996 | 1996 | 3 | 1 | 17 | 32 | 1
890 Mon Dec 30 17:32:01 1996 | 1996 | 12 | 30 | 17 | 32 | 1
891 Tue Dec 31 17:32:01 1996 | 1996 | 12 | 31 | 17 | 32 | 1
892 Wed Jan 01 17:32:01 1997 | 1997 | 1 | 1 | 17 | 32 | 1
893 Fri Feb 28 17:32:01 1997 | 1997 | 2 | 28 | 17 | 32 | 1
894 Sat Mar 01 17:32:01 1997 | 1997 | 3 | 1 | 17 | 32 | 1
895 Tue Dec 30 17:32:01 1997 | 1997 | 12 | 30 | 17 | 32 | 1
896 Wed Dec 31 17:32:01 1997 | 1997 | 12 | 31 | 17 | 32 | 1
897 Fri Dec 31 17:32:01 1999 | 1999 | 12 | 31 | 17 | 32 | 1
898 Sat Jan 01 17:32:01 2000 | 2000 | 1 | 1 | 17 | 32 | 1
899 Sun Dec 31 17:32:01 2000 | 2000 | 12 | 31 | 17 | 32 | 1
900 Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 17 | 32 | 1
901 (65 rows)
903 SELECT d1 as "timestamp",
904 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
905 date_part( 'usec', d1) AS usec
906 FROM TIMESTAMP_TBL;
907 timestamp | quarter | msec | usec
908 -----------------------------+---------+-------+----------
909 -infinity | | |
910 infinity | | |
911 Thu Jan 01 00:00:00 1970 | 1 | 0 | 0
912 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
913 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
914 Mon Feb 10 17:32:02 1997 | 1 | 2000 | 2000000
915 Mon Feb 10 17:32:01.4 1997 | 1 | 1400 | 1400000
916 Mon Feb 10 17:32:01.5 1997 | 1 | 1500 | 1500000
917 Mon Feb 10 17:32:01.6 1997 | 1 | 1600 | 1600000
918 Thu Jan 02 00:00:00 1997 | 1 | 0 | 0
919 Thu Jan 02 03:04:05 1997 | 1 | 5000 | 5000000
920 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
921 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
922 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
923 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
924 Tue Jun 10 17:32:01 1997 | 2 | 1000 | 1000000
925 Sat Sep 22 18:19:20 2001 | 3 | 20000 | 20000000
926 Wed Mar 15 08:14:01 2000 | 1 | 1000 | 1000000
927 Wed Mar 15 13:14:02 2000 | 1 | 2000 | 2000000
928 Wed Mar 15 12:14:03 2000 | 1 | 3000 | 3000000
929 Wed Mar 15 03:14:04 2000 | 1 | 4000 | 4000000
930 Wed Mar 15 02:14:05 2000 | 1 | 5000 | 5000000
931 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
932 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
933 Mon Feb 10 17:32:00 1997 | 1 | 0 | 0
934 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
935 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
936 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
937 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
938 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
939 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
940 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
941 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
942 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
943 Tue Jun 10 18:32:01 1997 | 2 | 1000 | 1000000
944 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
945 Tue Feb 11 17:32:01 1997 | 1 | 1000 | 1000000
946 Wed Feb 12 17:32:01 1997 | 1 | 1000 | 1000000
947 Thu Feb 13 17:32:01 1997 | 1 | 1000 | 1000000
948 Fri Feb 14 17:32:01 1997 | 1 | 1000 | 1000000
949 Sat Feb 15 17:32:01 1997 | 1 | 1000 | 1000000
950 Sun Feb 16 17:32:01 1997 | 1 | 1000 | 1000000
951 Tue Feb 16 17:32:01 0097 BC | 1 | 1000 | 1000000
952 Sat Feb 16 17:32:01 0097 | 1 | 1000 | 1000000
953 Thu Feb 16 17:32:01 0597 | 1 | 1000 | 1000000
954 Tue Feb 16 17:32:01 1097 | 1 | 1000 | 1000000
955 Sat Feb 16 17:32:01 1697 | 1 | 1000 | 1000000
956 Thu Feb 16 17:32:01 1797 | 1 | 1000 | 1000000
957 Tue Feb 16 17:32:01 1897 | 1 | 1000 | 1000000
958 Sun Feb 16 17:32:01 1997 | 1 | 1000 | 1000000
959 Sat Feb 16 17:32:01 2097 | 1 | 1000 | 1000000
960 Wed Feb 28 17:32:01 1996 | 1 | 1000 | 1000000
961 Thu Feb 29 17:32:01 1996 | 1 | 1000 | 1000000
962 Fri Mar 01 17:32:01 1996 | 1 | 1000 | 1000000
963 Mon Dec 30 17:32:01 1996 | 4 | 1000 | 1000000
964 Tue Dec 31 17:32:01 1996 | 4 | 1000 | 1000000
965 Wed Jan 01 17:32:01 1997 | 1 | 1000 | 1000000
966 Fri Feb 28 17:32:01 1997 | 1 | 1000 | 1000000
967 Sat Mar 01 17:32:01 1997 | 1 | 1000 | 1000000
968 Tue Dec 30 17:32:01 1997 | 4 | 1000 | 1000000
969 Wed Dec 31 17:32:01 1997 | 4 | 1000 | 1000000
970 Fri Dec 31 17:32:01 1999 | 4 | 1000 | 1000000
971 Sat Jan 01 17:32:01 2000 | 1 | 1000 | 1000000
972 Sun Dec 31 17:32:01 2000 | 4 | 1000 | 1000000
973 Mon Jan 01 17:32:01 2001 | 1 | 1000 | 1000000
974 (65 rows)
976 SELECT d1 as "timestamp",
977 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
978 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
979 date_part( 'doy', d1) AS doy
980 FROM TIMESTAMP_TBL;
981 timestamp | isoyear | week | isodow | dow | doy
982 -----------------------------+-----------+------+--------+-----+-----
983 -infinity | -Infinity | | | |
984 infinity | Infinity | | | |
985 Thu Jan 01 00:00:00 1970 | 1970 | 1 | 4 | 4 | 1
986 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
987 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
988 Mon Feb 10 17:32:02 1997 | 1997 | 7 | 1 | 1 | 41
989 Mon Feb 10 17:32:01.4 1997 | 1997 | 7 | 1 | 1 | 41
990 Mon Feb 10 17:32:01.5 1997 | 1997 | 7 | 1 | 1 | 41
991 Mon Feb 10 17:32:01.6 1997 | 1997 | 7 | 1 | 1 | 41
992 Thu Jan 02 00:00:00 1997 | 1997 | 1 | 4 | 4 | 2
993 Thu Jan 02 03:04:05 1997 | 1997 | 1 | 4 | 4 | 2
994 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
995 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
996 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
997 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
998 Tue Jun 10 17:32:01 1997 | 1997 | 24 | 2 | 2 | 161
999 Sat Sep 22 18:19:20 2001 | 2001 | 38 | 6 | 6 | 265
1000 Wed Mar 15 08:14:01 2000 | 2000 | 11 | 3 | 3 | 75
1001 Wed Mar 15 13:14:02 2000 | 2000 | 11 | 3 | 3 | 75
1002 Wed Mar 15 12:14:03 2000 | 2000 | 11 | 3 | 3 | 75
1003 Wed Mar 15 03:14:04 2000 | 2000 | 11 | 3 | 3 | 75
1004 Wed Mar 15 02:14:05 2000 | 2000 | 11 | 3 | 3 | 75
1005 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1006 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1007 Mon Feb 10 17:32:00 1997 | 1997 | 7 | 1 | 1 | 41
1008 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1009 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1010 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1011 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1012 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1013 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1014 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1015 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1016 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1017 Tue Jun 10 18:32:01 1997 | 1997 | 24 | 2 | 2 | 161
1018 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1019 Tue Feb 11 17:32:01 1997 | 1997 | 7 | 2 | 2 | 42
1020 Wed Feb 12 17:32:01 1997 | 1997 | 7 | 3 | 3 | 43
1021 Thu Feb 13 17:32:01 1997 | 1997 | 7 | 4 | 4 | 44
1022 Fri Feb 14 17:32:01 1997 | 1997 | 7 | 5 | 5 | 45
1023 Sat Feb 15 17:32:01 1997 | 1997 | 7 | 6 | 6 | 46
1024 Sun Feb 16 17:32:01 1997 | 1997 | 7 | 7 | 0 | 47
1025 Tue Feb 16 17:32:01 0097 BC | -97 | 7 | 2 | 2 | 47
1026 Sat Feb 16 17:32:01 0097 | 97 | 7 | 6 | 6 | 47
1027 Thu Feb 16 17:32:01 0597 | 597 | 7 | 4 | 4 | 47
1028 Tue Feb 16 17:32:01 1097 | 1097 | 7 | 2 | 2 | 47
1029 Sat Feb 16 17:32:01 1697 | 1697 | 7 | 6 | 6 | 47
1030 Thu Feb 16 17:32:01 1797 | 1797 | 7 | 4 | 4 | 47
1031 Tue Feb 16 17:32:01 1897 | 1897 | 7 | 2 | 2 | 47
1032 Sun Feb 16 17:32:01 1997 | 1997 | 7 | 7 | 0 | 47
1033 Sat Feb 16 17:32:01 2097 | 2097 | 7 | 6 | 6 | 47
1034 Wed Feb 28 17:32:01 1996 | 1996 | 9 | 3 | 3 | 59
1035 Thu Feb 29 17:32:01 1996 | 1996 | 9 | 4 | 4 | 60
1036 Fri Mar 01 17:32:01 1996 | 1996 | 9 | 5 | 5 | 61
1037 Mon Dec 30 17:32:01 1996 | 1997 | 1 | 1 | 1 | 365
1038 Tue Dec 31 17:32:01 1996 | 1997 | 1 | 2 | 2 | 366
1039 Wed Jan 01 17:32:01 1997 | 1997 | 1 | 3 | 3 | 1
1040 Fri Feb 28 17:32:01 1997 | 1997 | 9 | 5 | 5 | 59
1041 Sat Mar 01 17:32:01 1997 | 1997 | 9 | 6 | 6 | 60
1042 Tue Dec 30 17:32:01 1997 | 1998 | 1 | 2 | 2 | 364
1043 Wed Dec 31 17:32:01 1997 | 1998 | 1 | 3 | 3 | 365
1044 Fri Dec 31 17:32:01 1999 | 1999 | 52 | 5 | 5 | 365
1045 Sat Jan 01 17:32:01 2000 | 1999 | 52 | 6 | 6 | 1
1046 Sun Dec 31 17:32:01 2000 | 2000 | 52 | 7 | 0 | 366
1047 Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 1 | 1
1048 (65 rows)
1050 SELECT d1 as "timestamp",
1051 date_part( 'decade', d1) AS decade,
1052 date_part( 'century', d1) AS century,
1053 date_part( 'millennium', d1) AS millennium,
1054 round(date_part( 'julian', d1)) AS julian,
1055 date_part( 'epoch', d1) AS epoch
1056 FROM TIMESTAMP_TBL;
1057 timestamp | decade | century | millennium | julian | epoch
1058 -----------------------------+-----------+-----------+------------+-----------+--------------
1059 -infinity | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1060 infinity | Infinity | Infinity | Infinity | Infinity | Infinity
1061 Thu Jan 01 00:00:00 1970 | 197 | 20 | 2 | 2440588 | 0
1062 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1063 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1064 Mon Feb 10 17:32:02 1997 | 199 | 20 | 2 | 2450491 | 855595922
1065 Mon Feb 10 17:32:01.4 1997 | 199 | 20 | 2 | 2450491 | 855595921.4
1066 Mon Feb 10 17:32:01.5 1997 | 199 | 20 | 2 | 2450491 | 855595921.5
1067 Mon Feb 10 17:32:01.6 1997 | 199 | 20 | 2 | 2450491 | 855595921.6
1068 Thu Jan 02 00:00:00 1997 | 199 | 20 | 2 | 2450451 | 852163200
1069 Thu Jan 02 03:04:05 1997 | 199 | 20 | 2 | 2450451 | 852174245
1070 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1071 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1072 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1073 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1074 Tue Jun 10 17:32:01 1997 | 199 | 20 | 2 | 2450611 | 865963921
1075 Sat Sep 22 18:19:20 2001 | 200 | 21 | 3 | 2452176 | 1001182760
1076 Wed Mar 15 08:14:01 2000 | 200 | 20 | 2 | 2451619 | 953108041
1077 Wed Mar 15 13:14:02 2000 | 200 | 20 | 2 | 2451620 | 953126042
1078 Wed Mar 15 12:14:03 2000 | 200 | 20 | 2 | 2451620 | 953122443
1079 Wed Mar 15 03:14:04 2000 | 200 | 20 | 2 | 2451619 | 953090044
1080 Wed Mar 15 02:14:05 2000 | 200 | 20 | 2 | 2451619 | 953086445
1081 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1082 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1083 Mon Feb 10 17:32:00 1997 | 199 | 20 | 2 | 2450491 | 855595920
1084 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1085 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1086 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1087 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1088 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1089 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1090 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1091 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1092 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1093 Tue Jun 10 18:32:01 1997 | 199 | 20 | 2 | 2450611 | 865967521
1094 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1095 Tue Feb 11 17:32:01 1997 | 199 | 20 | 2 | 2450492 | 855682321
1096 Wed Feb 12 17:32:01 1997 | 199 | 20 | 2 | 2450493 | 855768721
1097 Thu Feb 13 17:32:01 1997 | 199 | 20 | 2 | 2450494 | 855855121
1098 Fri Feb 14 17:32:01 1997 | 199 | 20 | 2 | 2450495 | 855941521
1099 Sat Feb 15 17:32:01 1997 | 199 | 20 | 2 | 2450496 | 856027921
1100 Sun Feb 16 17:32:01 1997 | 199 | 20 | 2 | 2450497 | 856114321
1101 Tue Feb 16 17:32:01 0097 BC | -10 | -1 | -1 | 1686043 | -65192711279
1102 Sat Feb 16 17:32:01 0097 | 9 | 1 | 1 | 1756537 | -59102029679
1103 Thu Feb 16 17:32:01 0597 | 59 | 6 | 1 | 1939158 | -43323575279
1104 Tue Feb 16 17:32:01 1097 | 109 | 11 | 2 | 2121779 | -27545120879
1105 Sat Feb 16 17:32:01 1697 | 169 | 17 | 2 | 2340925 | -8610906479
1106 Thu Feb 16 17:32:01 1797 | 179 | 18 | 2 | 2377449 | -5455232879
1107 Tue Feb 16 17:32:01 1897 | 189 | 19 | 2 | 2413973 | -2299559279
1108 Sun Feb 16 17:32:01 1997 | 199 | 20 | 2 | 2450497 | 856114321
1109 Sat Feb 16 17:32:01 2097 | 209 | 21 | 3 | 2487022 | 4011874321
1110 Wed Feb 28 17:32:01 1996 | 199 | 20 | 2 | 2450143 | 825528721
1111 Thu Feb 29 17:32:01 1996 | 199 | 20 | 2 | 2450144 | 825615121
1112 Fri Mar 01 17:32:01 1996 | 199 | 20 | 2 | 2450145 | 825701521
1113 Mon Dec 30 17:32:01 1996 | 199 | 20 | 2 | 2450449 | 851967121
1114 Tue Dec 31 17:32:01 1996 | 199 | 20 | 2 | 2450450 | 852053521
1115 Wed Jan 01 17:32:01 1997 | 199 | 20 | 2 | 2450451 | 852139921
1116 Fri Feb 28 17:32:01 1997 | 199 | 20 | 2 | 2450509 | 857151121
1117 Sat Mar 01 17:32:01 1997 | 199 | 20 | 2 | 2450510 | 857237521
1118 Tue Dec 30 17:32:01 1997 | 199 | 20 | 2 | 2450814 | 883503121
1119 Wed Dec 31 17:32:01 1997 | 199 | 20 | 2 | 2450815 | 883589521
1120 Fri Dec 31 17:32:01 1999 | 199 | 20 | 2 | 2451545 | 946661521
1121 Sat Jan 01 17:32:01 2000 | 200 | 20 | 2 | 2451546 | 946747921
1122 Sun Dec 31 17:32:01 2000 | 200 | 20 | 2 | 2451911 | 978283921
1123 Mon Jan 01 17:32:01 2001 | 200 | 21 | 3 | 2451912 | 978370321
1124 (65 rows)
1126 -- extract implementation is mostly the same as date_part, so only
1127 -- test a few cases for additional coverage.
1128 SELECT d1 as "timestamp",
1129 extract(microseconds from d1) AS microseconds,
1130 extract(milliseconds from d1) AS milliseconds,
1131 extract(seconds from d1) AS seconds,
1132 round(extract(julian from d1)) AS julian,
1133 extract(epoch from d1) AS epoch
1134 FROM TIMESTAMP_TBL;
1135 timestamp | microseconds | milliseconds | seconds | julian | epoch
1136 -----------------------------+--------------+--------------+-----------+-----------+---------------------
1137 -infinity | | | | -Infinity | -Infinity
1138 infinity | | | | Infinity | Infinity
1139 Thu Jan 01 00:00:00 1970 | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
1140 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1141 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1142 Mon Feb 10 17:32:02 1997 | 2000000 | 2000.000 | 2.000000 | 2450491 | 855595922.000000
1143 Mon Feb 10 17:32:01.4 1997 | 1400000 | 1400.000 | 1.400000 | 2450491 | 855595921.400000
1144 Mon Feb 10 17:32:01.5 1997 | 1500000 | 1500.000 | 1.500000 | 2450491 | 855595921.500000
1145 Mon Feb 10 17:32:01.6 1997 | 1600000 | 1600.000 | 1.600000 | 2450491 | 855595921.600000
1146 Thu Jan 02 00:00:00 1997 | 0 | 0.000 | 0.000000 | 2450451 | 852163200.000000
1147 Thu Jan 02 03:04:05 1997 | 5000000 | 5000.000 | 5.000000 | 2450451 | 852174245.000000
1148 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1149 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1150 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1151 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1152 Tue Jun 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865963921.000000
1153 Sat Sep 22 18:19:20 2001 | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001182760.000000
1154 Wed Mar 15 08:14:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451619 | 953108041.000000
1155 Wed Mar 15 13:14:02 2000 | 2000000 | 2000.000 | 2.000000 | 2451620 | 953126042.000000
1156 Wed Mar 15 12:14:03 2000 | 3000000 | 3000.000 | 3.000000 | 2451620 | 953122443.000000
1157 Wed Mar 15 03:14:04 2000 | 4000000 | 4000.000 | 4.000000 | 2451619 | 953090044.000000
1158 Wed Mar 15 02:14:05 2000 | 5000000 | 5000.000 | 5.000000 | 2451619 | 953086445.000000
1159 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1160 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1161 Mon Feb 10 17:32:00 1997 | 0 | 0.000 | 0.000000 | 2450491 | 855595920.000000
1162 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1163 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1164 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1165 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1166 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1167 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1168 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1169 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1170 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1171 Tue Jun 10 18:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865967521.000000
1172 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1173 Tue Feb 11 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450492 | 855682321.000000
1174 Wed Feb 12 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450493 | 855768721.000000
1175 Thu Feb 13 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450494 | 855855121.000000
1176 Fri Feb 14 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450495 | 855941521.000000
1177 Sat Feb 15 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450496 | 856027921.000000
1178 Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
1179 Tue Feb 16 17:32:01 0097 BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192711279.000000
1180 Sat Feb 16 17:32:01 0097 | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102029679.000000
1181 Thu Feb 16 17:32:01 0597 | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323575279.000000
1182 Tue Feb 16 17:32:01 1097 | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545120879.000000
1183 Sat Feb 16 17:32:01 1697 | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610906479.000000
1184 Thu Feb 16 17:32:01 1797 | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455232879.000000
1185 Tue Feb 16 17:32:01 1897 | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299559279.000000
1186 Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
1187 Sat Feb 16 17:32:01 2097 | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011874321.000000
1188 Wed Feb 28 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450143 | 825528721.000000
1189 Thu Feb 29 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450144 | 825615121.000000
1190 Fri Mar 01 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450145 | 825701521.000000
1191 Mon Dec 30 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450449 | 851967121.000000
1192 Tue Dec 31 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450450 | 852053521.000000
1193 Wed Jan 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450451 | 852139921.000000
1194 Fri Feb 28 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450509 | 857151121.000000
1195 Sat Mar 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450510 | 857237521.000000
1196 Tue Dec 30 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450814 | 883503121.000000
1197 Wed Dec 31 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450815 | 883589521.000000
1198 Fri Dec 31 17:32:01 1999 | 1000000 | 1000.000 | 1.000000 | 2451545 | 946661521.000000
1199 Sat Jan 01 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451546 | 946747921.000000
1200 Sun Dec 31 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451911 | 978283921.000000
1201 Mon Jan 01 17:32:01 2001 | 1000000 | 1000.000 | 1.000000 | 2451912 | 978370321.000000
1202 (65 rows)
1204 -- value near upper bound uses special case in code
1205 SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp);
1206 date_part
1207 ---------------
1208 9224097091200
1209 (1 row)
1211 SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp);
1212 extract
1213 ----------------------
1214 9224097091200.000000
1215 (1 row)
1217 -- another internal overflow test case
1218 SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp);
1219 extract
1220 --------------------
1221 95617584000.000000
1222 (1 row)
1224 -- test edge-case overflow in timestamp subtraction
1225 SELECT timestamp '294276-12-31 23:59:59' - timestamp '1999-12-23 19:59:04.224193' AS ok;
1226 ok
1227 -----------------------------------------
1228 @ 106751991 days 4 hours 54.775807 secs
1229 (1 row)
1231 SELECT timestamp '294276-12-31 23:59:59' - timestamp '1999-12-23 19:59:04.224192' AS overflows;
1232 ERROR: interval out of range
1233 -- TO_CHAR()
1234 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
1235 FROM TIMESTAMP_TBL;
1236 to_char
1237 ------------------------------------------------------------------------------------------
1240 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1241 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1242 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1243 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1244 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1245 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1246 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1247 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1248 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1249 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1250 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1251 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1252 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1253 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1254 SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep
1255 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1256 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1257 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1258 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1259 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1260 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1261 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1262 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1263 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1264 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1265 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1266 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1267 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1268 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1269 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1270 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1271 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1272 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1273 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1274 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1275 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1276 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1277 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1278 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1279 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1280 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1281 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1282 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1283 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1284 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1285 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1286 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1287 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1288 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1289 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1290 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1291 FRIDAY Friday friday FRI Fri fri MARCH March march III MAR Mar mar
1292 MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec
1293 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1294 WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan
1295 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1296 SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar
1297 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1298 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1299 FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec
1300 SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
1301 SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
1302 MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
1303 (65 rows)
1305 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
1306 FROM TIMESTAMP_TBL;
1307 to_char
1308 --------------------------------------------------------------
1311 THURSDAY Thursday thursday JANUARY January january I
1312 MONDAY Monday monday FEBRUARY February february II
1313 MONDAY Monday monday FEBRUARY February february II
1314 MONDAY Monday monday FEBRUARY February february II
1315 MONDAY Monday monday FEBRUARY February february II
1316 MONDAY Monday monday FEBRUARY February february II
1317 MONDAY Monday monday FEBRUARY February february II
1318 THURSDAY Thursday thursday JANUARY January january I
1319 THURSDAY Thursday thursday JANUARY January january I
1320 MONDAY Monday monday FEBRUARY February february II
1321 MONDAY Monday monday FEBRUARY February february II
1322 MONDAY Monday monday FEBRUARY February february II
1323 MONDAY Monday monday FEBRUARY February february II
1324 TUESDAY Tuesday tuesday JUNE June june VI
1325 SATURDAY Saturday saturday SEPTEMBER September september IX
1326 WEDNESDAY Wednesday wednesday MARCH March march III
1327 WEDNESDAY Wednesday wednesday MARCH March march III
1328 WEDNESDAY Wednesday wednesday MARCH March march III
1329 WEDNESDAY Wednesday wednesday MARCH March march III
1330 WEDNESDAY Wednesday wednesday MARCH March march III
1331 MONDAY Monday monday FEBRUARY February february II
1332 MONDAY Monday monday FEBRUARY February february II
1333 MONDAY Monday monday FEBRUARY February february II
1334 MONDAY Monday monday FEBRUARY February february II
1335 MONDAY Monday monday FEBRUARY February february II
1336 MONDAY Monday monday FEBRUARY February february II
1337 MONDAY Monday monday FEBRUARY February february II
1338 MONDAY Monday monday FEBRUARY February february II
1339 MONDAY Monday monday FEBRUARY February february II
1340 MONDAY Monday monday FEBRUARY February february II
1341 MONDAY Monday monday FEBRUARY February february II
1342 MONDAY Monday monday FEBRUARY February february II
1343 TUESDAY Tuesday tuesday JUNE June june VI
1344 MONDAY Monday monday FEBRUARY February february II
1345 TUESDAY Tuesday tuesday FEBRUARY February february II
1346 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1347 THURSDAY Thursday thursday FEBRUARY February february II
1348 FRIDAY Friday friday FEBRUARY February february II
1349 SATURDAY Saturday saturday FEBRUARY February february II
1350 SUNDAY Sunday sunday FEBRUARY February february II
1351 TUESDAY Tuesday tuesday FEBRUARY February february II
1352 SATURDAY Saturday saturday FEBRUARY February february II
1353 THURSDAY Thursday thursday FEBRUARY February february II
1354 TUESDAY Tuesday tuesday FEBRUARY February february II
1355 SATURDAY Saturday saturday FEBRUARY February february II
1356 THURSDAY Thursday thursday FEBRUARY February february II
1357 TUESDAY Tuesday tuesday FEBRUARY February february II
1358 SUNDAY Sunday sunday FEBRUARY February february II
1359 SATURDAY Saturday saturday FEBRUARY February february II
1360 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1361 THURSDAY Thursday thursday FEBRUARY February february II
1362 FRIDAY Friday friday MARCH March march III
1363 MONDAY Monday monday DECEMBER December december XII
1364 TUESDAY Tuesday tuesday DECEMBER December december XII
1365 WEDNESDAY Wednesday wednesday JANUARY January january I
1366 FRIDAY Friday friday FEBRUARY February february II
1367 SATURDAY Saturday saturday MARCH March march III
1368 TUESDAY Tuesday tuesday DECEMBER December december XII
1369 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1370 FRIDAY Friday friday DECEMBER December december XII
1371 SATURDAY Saturday saturday JANUARY January january I
1372 SUNDAY Sunday sunday DECEMBER December december XII
1373 MONDAY Monday monday JANUARY January january I
1374 (65 rows)
1376 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
1377 FROM TIMESTAMP_TBL;
1378 to_char
1379 --------------------------------------------------
1382 1,970 1970 970 70 0 20 1 01 01 001 01 5 2440588
1383 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1384 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1385 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1386 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1387 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1388 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1389 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1390 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1391 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1392 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1393 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1394 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1395 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1396 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
1397 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1398 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1399 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1400 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1401 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1402 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1403 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1404 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1405 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1406 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1407 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1408 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1409 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1410 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1411 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1412 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1413 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1414 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1415 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1416 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
1417 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1418 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1419 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1420 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1421 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1422 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
1423 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
1424 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1425 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1426 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1427 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1428 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1429 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1430 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
1431 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1432 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1433 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1434 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1435 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1436 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1437 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1438 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1439 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1440 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1441 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1442 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
1443 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
1444 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
1445 (65 rows)
1447 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
1448 FROM TIMESTAMP_TBL;
1449 to_char
1450 -------------------------------------------------
1453 1,970 1970 970 70 0 20 1 1 1 1 1 5 2440588
1454 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1455 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1456 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1457 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1458 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1459 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1460 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1461 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1462 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1463 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1464 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1465 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1466 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1467 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
1468 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1469 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1470 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1471 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1472 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1473 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1474 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1475 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1476 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1477 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1478 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1479 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1480 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1481 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1482 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1483 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1484 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1485 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1486 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1487 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
1488 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1489 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1490 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1491 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1492 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1493 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
1494 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
1495 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1496 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1497 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1498 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1499 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1500 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1501 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
1502 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1503 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1504 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1505 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1506 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1507 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1508 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1509 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1510 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1511 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1512 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1513 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
1514 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
1515 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
1516 (65 rows)
1518 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
1519 FROM TIMESTAMP_TBL;
1520 to_char
1521 ----------------------
1524 12 12 00 00 00 0
1525 05 05 17 32 01 63121
1526 05 05 17 32 01 63121
1527 05 05 17 32 02 63122
1528 05 05 17 32 01 63121
1529 05 05 17 32 01 63121
1530 05 05 17 32 01 63121
1531 12 12 00 00 00 0
1532 03 03 03 04 05 11045
1533 05 05 17 32 01 63121
1534 05 05 17 32 01 63121
1535 05 05 17 32 01 63121
1536 05 05 17 32 01 63121
1537 05 05 17 32 01 63121
1538 06 06 18 19 20 65960
1539 08 08 08 14 01 29641
1540 01 01 13 14 02 47642
1541 12 12 12 14 03 44043
1542 03 03 03 14 04 11644
1543 02 02 02 14 05 8045
1544 05 05 17 32 01 63121
1545 05 05 17 32 01 63121
1546 05 05 17 32 00 63120
1547 05 05 17 32 01 63121
1548 05 05 17 32 01 63121
1549 05 05 17 32 01 63121
1550 05 05 17 32 01 63121
1551 05 05 17 32 01 63121
1552 05 05 17 32 01 63121
1553 05 05 17 32 01 63121
1554 05 05 17 32 01 63121
1555 05 05 17 32 01 63121
1556 06 06 18 32 01 66721
1557 05 05 17 32 01 63121
1558 05 05 17 32 01 63121
1559 05 05 17 32 01 63121
1560 05 05 17 32 01 63121
1561 05 05 17 32 01 63121
1562 05 05 17 32 01 63121
1563 05 05 17 32 01 63121
1564 05 05 17 32 01 63121
1565 05 05 17 32 01 63121
1566 05 05 17 32 01 63121
1567 05 05 17 32 01 63121
1568 05 05 17 32 01 63121
1569 05 05 17 32 01 63121
1570 05 05 17 32 01 63121
1571 05 05 17 32 01 63121
1572 05 05 17 32 01 63121
1573 05 05 17 32 01 63121
1574 05 05 17 32 01 63121
1575 05 05 17 32 01 63121
1576 05 05 17 32 01 63121
1577 05 05 17 32 01 63121
1578 05 05 17 32 01 63121
1579 05 05 17 32 01 63121
1580 05 05 17 32 01 63121
1581 05 05 17 32 01 63121
1582 05 05 17 32 01 63121
1583 05 05 17 32 01 63121
1584 05 05 17 32 01 63121
1585 05 05 17 32 01 63121
1586 05 05 17 32 01 63121
1587 (65 rows)
1589 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
1590 FROM TIMESTAMP_TBL;
1591 to_char
1592 -------------------------------------------------
1595 HH:MI:SS is 12:00:00 "text between quote marks"
1596 HH:MI:SS is 05:32:01 "text between quote marks"
1597 HH:MI:SS is 05:32:01 "text between quote marks"
1598 HH:MI:SS is 05:32:02 "text between quote marks"
1599 HH:MI:SS is 05:32:01 "text between quote marks"
1600 HH:MI:SS is 05:32:01 "text between quote marks"
1601 HH:MI:SS is 05:32:01 "text between quote marks"
1602 HH:MI:SS is 12:00:00 "text between quote marks"
1603 HH:MI:SS is 03:04:05 "text between quote marks"
1604 HH:MI:SS is 05:32:01 "text between quote marks"
1605 HH:MI:SS is 05:32:01 "text between quote marks"
1606 HH:MI:SS is 05:32:01 "text between quote marks"
1607 HH:MI:SS is 05:32:01 "text between quote marks"
1608 HH:MI:SS is 05:32:01 "text between quote marks"
1609 HH:MI:SS is 06:19:20 "text between quote marks"
1610 HH:MI:SS is 08:14:01 "text between quote marks"
1611 HH:MI:SS is 01:14:02 "text between quote marks"
1612 HH:MI:SS is 12:14:03 "text between quote marks"
1613 HH:MI:SS is 03:14:04 "text between quote marks"
1614 HH:MI:SS is 02:14:05 "text between quote marks"
1615 HH:MI:SS is 05:32:01 "text between quote marks"
1616 HH:MI:SS is 05:32:01 "text between quote marks"
1617 HH:MI:SS is 05:32:00 "text between quote marks"
1618 HH:MI:SS is 05:32:01 "text between quote marks"
1619 HH:MI:SS is 05:32:01 "text between quote marks"
1620 HH:MI:SS is 05:32:01 "text between quote marks"
1621 HH:MI:SS is 05:32:01 "text between quote marks"
1622 HH:MI:SS is 05:32:01 "text between quote marks"
1623 HH:MI:SS is 05:32:01 "text between quote marks"
1624 HH:MI:SS is 05:32:01 "text between quote marks"
1625 HH:MI:SS is 05:32:01 "text between quote marks"
1626 HH:MI:SS is 05:32:01 "text between quote marks"
1627 HH:MI:SS is 06:32:01 "text between quote marks"
1628 HH:MI:SS is 05:32:01 "text between quote marks"
1629 HH:MI:SS is 05:32:01 "text between quote marks"
1630 HH:MI:SS is 05:32:01 "text between quote marks"
1631 HH:MI:SS is 05:32:01 "text between quote marks"
1632 HH:MI:SS is 05:32:01 "text between quote marks"
1633 HH:MI:SS is 05:32:01 "text between quote marks"
1634 HH:MI:SS is 05:32:01 "text between quote marks"
1635 HH:MI:SS is 05:32:01 "text between quote marks"
1636 HH:MI:SS is 05:32:01 "text between quote marks"
1637 HH:MI:SS is 05:32:01 "text between quote marks"
1638 HH:MI:SS is 05:32:01 "text between quote marks"
1639 HH:MI:SS is 05:32:01 "text between quote marks"
1640 HH:MI:SS is 05:32:01 "text between quote marks"
1641 HH:MI:SS is 05:32:01 "text between quote marks"
1642 HH:MI:SS is 05:32:01 "text between quote marks"
1643 HH:MI:SS is 05:32:01 "text between quote marks"
1644 HH:MI:SS is 05:32:01 "text between quote marks"
1645 HH:MI:SS is 05:32:01 "text between quote marks"
1646 HH:MI:SS is 05:32:01 "text between quote marks"
1647 HH:MI:SS is 05:32:01 "text between quote marks"
1648 HH:MI:SS is 05:32:01 "text between quote marks"
1649 HH:MI:SS is 05:32:01 "text between quote marks"
1650 HH:MI:SS is 05:32:01 "text between quote marks"
1651 HH:MI:SS is 05:32:01 "text between quote marks"
1652 HH:MI:SS is 05:32:01 "text between quote marks"
1653 HH:MI:SS is 05:32:01 "text between quote marks"
1654 HH:MI:SS is 05:32:01 "text between quote marks"
1655 HH:MI:SS is 05:32:01 "text between quote marks"
1656 HH:MI:SS is 05:32:01 "text between quote marks"
1657 HH:MI:SS is 05:32:01 "text between quote marks"
1658 (65 rows)
1660 SELECT to_char(d1, 'HH24--text--MI--text--SS')
1661 FROM TIMESTAMP_TBL;
1662 to_char
1663 ------------------------
1666 00--text--00--text--00
1667 17--text--32--text--01
1668 17--text--32--text--01
1669 17--text--32--text--02
1670 17--text--32--text--01
1671 17--text--32--text--01
1672 17--text--32--text--01
1673 00--text--00--text--00
1674 03--text--04--text--05
1675 17--text--32--text--01
1676 17--text--32--text--01
1677 17--text--32--text--01
1678 17--text--32--text--01
1679 17--text--32--text--01
1680 18--text--19--text--20
1681 08--text--14--text--01
1682 13--text--14--text--02
1683 12--text--14--text--03
1684 03--text--14--text--04
1685 02--text--14--text--05
1686 17--text--32--text--01
1687 17--text--32--text--01
1688 17--text--32--text--00
1689 17--text--32--text--01
1690 17--text--32--text--01
1691 17--text--32--text--01
1692 17--text--32--text--01
1693 17--text--32--text--01
1694 17--text--32--text--01
1695 17--text--32--text--01
1696 17--text--32--text--01
1697 17--text--32--text--01
1698 18--text--32--text--01
1699 17--text--32--text--01
1700 17--text--32--text--01
1701 17--text--32--text--01
1702 17--text--32--text--01
1703 17--text--32--text--01
1704 17--text--32--text--01
1705 17--text--32--text--01
1706 17--text--32--text--01
1707 17--text--32--text--01
1708 17--text--32--text--01
1709 17--text--32--text--01
1710 17--text--32--text--01
1711 17--text--32--text--01
1712 17--text--32--text--01
1713 17--text--32--text--01
1714 17--text--32--text--01
1715 17--text--32--text--01
1716 17--text--32--text--01
1717 17--text--32--text--01
1718 17--text--32--text--01
1719 17--text--32--text--01
1720 17--text--32--text--01
1721 17--text--32--text--01
1722 17--text--32--text--01
1723 17--text--32--text--01
1724 17--text--32--text--01
1725 17--text--32--text--01
1726 17--text--32--text--01
1727 17--text--32--text--01
1728 17--text--32--text--01
1729 (65 rows)
1731 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
1732 FROM TIMESTAMP_TBL;
1733 to_char
1734 -------------------------
1737 1970TH 1970th 2440588th
1738 1997TH 1997th 2450490th
1739 1997TH 1997th 2450490th
1740 1997TH 1997th 2450490th
1741 1997TH 1997th 2450490th
1742 1997TH 1997th 2450490th
1743 1997TH 1997th 2450490th
1744 1997TH 1997th 2450451st
1745 1997TH 1997th 2450451st
1746 1997TH 1997th 2450490th
1747 1997TH 1997th 2450490th
1748 1997TH 1997th 2450490th
1749 1997TH 1997th 2450490th
1750 1997TH 1997th 2450610th
1751 2001ST 2001st 2452175th
1752 2000TH 2000th 2451619th
1753 2000TH 2000th 2451619th
1754 2000TH 2000th 2451619th
1755 2000TH 2000th 2451619th
1756 2000TH 2000th 2451619th
1757 1997TH 1997th 2450490th
1758 1997TH 1997th 2450490th
1759 1997TH 1997th 2450490th
1760 1997TH 1997th 2450490th
1761 1997TH 1997th 2450490th
1762 1997TH 1997th 2450490th
1763 1997TH 1997th 2450490th
1764 1997TH 1997th 2450490th
1765 1997TH 1997th 2450490th
1766 1997TH 1997th 2450490th
1767 1997TH 1997th 2450490th
1768 1997TH 1997th 2450490th
1769 1997TH 1997th 2450610th
1770 1997TH 1997th 2450490th
1771 1997TH 1997th 2450491st
1772 1997TH 1997th 2450492nd
1773 1997TH 1997th 2450493rd
1774 1997TH 1997th 2450494th
1775 1997TH 1997th 2450495th
1776 1997TH 1997th 2450496th
1777 0097TH 0097th 1686042nd
1778 0097TH 0097th 1756536th
1779 0597TH 0597th 1939157th
1780 1097TH 1097th 2121778th
1781 1697TH 1697th 2340924th
1782 1797TH 1797th 2377448th
1783 1897TH 1897th 2413972nd
1784 1997TH 1997th 2450496th
1785 2097TH 2097th 2487021st
1786 1996TH 1996th 2450142nd
1787 1996TH 1996th 2450143rd
1788 1996TH 1996th 2450144th
1789 1996TH 1996th 2450448th
1790 1996TH 1996th 2450449th
1791 1997TH 1997th 2450450th
1792 1997TH 1997th 2450508th
1793 1997TH 1997th 2450509th
1794 1997TH 1997th 2450813th
1795 1997TH 1997th 2450814th
1796 1999TH 1999th 2451544th
1797 2000TH 2000th 2451545th
1798 2000TH 2000th 2451910th
1799 2001ST 2001st 2451911th
1800 (65 rows)
1802 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')
1803 FROM TIMESTAMP_TBL;
1804 to_char
1805 ---------------------------------------------------------------------
1808 1970 A.D. 1970 a.d. 1970 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1809 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1810 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1811 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm
1812 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1813 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1814 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1815 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1816 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1817 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1818 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1819 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1820 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1821 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1822 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
1823 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
1824 2000 A.D. 2000 a.d. 2000 ad 01:14:02 P.M. 01:14:02 p.m. 01:14:02 pm
1825 2000 A.D. 2000 a.d. 2000 ad 12:14:03 P.M. 12:14:03 p.m. 12:14:03 pm
1826 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
1827 2000 A.D. 2000 a.d. 2000 ad 02:14:05 A.M. 02:14:05 a.m. 02:14:05 am
1828 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1829 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1830 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
1831 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1832 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1833 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1834 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1835 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1836 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1837 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1838 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1839 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1840 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
1841 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1842 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1843 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1844 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1845 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1846 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1847 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1848 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1849 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1850 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1851 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1852 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1853 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1854 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1855 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1856 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1857 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1858 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1859 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1860 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1861 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1862 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1863 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1864 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1865 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1866 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1867 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1868 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1869 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1870 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1871 (65 rows)
1873 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
1874 FROM TIMESTAMP_TBL;
1875 to_char
1876 ------------------------
1879 1970 970 70 0 01 004 4
1880 1997 997 97 7 07 043 1
1881 1997 997 97 7 07 043 1
1882 1997 997 97 7 07 043 1
1883 1997 997 97 7 07 043 1
1884 1997 997 97 7 07 043 1
1885 1997 997 97 7 07 043 1
1886 1997 997 97 7 01 004 4
1887 1997 997 97 7 01 004 4
1888 1997 997 97 7 07 043 1
1889 1997 997 97 7 07 043 1
1890 1997 997 97 7 07 043 1
1891 1997 997 97 7 07 043 1
1892 1997 997 97 7 24 163 2
1893 2001 001 01 1 38 265 6
1894 2000 000 00 0 11 073 3
1895 2000 000 00 0 11 073 3
1896 2000 000 00 0 11 073 3
1897 2000 000 00 0 11 073 3
1898 2000 000 00 0 11 073 3
1899 1997 997 97 7 07 043 1
1900 1997 997 97 7 07 043 1
1901 1997 997 97 7 07 043 1
1902 1997 997 97 7 07 043 1
1903 1997 997 97 7 07 043 1
1904 1997 997 97 7 07 043 1
1905 1997 997 97 7 07 043 1
1906 1997 997 97 7 07 043 1
1907 1997 997 97 7 07 043 1
1908 1997 997 97 7 07 043 1
1909 1997 997 97 7 07 043 1
1910 1997 997 97 7 07 043 1
1911 1997 997 97 7 24 163 2
1912 1997 997 97 7 07 043 1
1913 1997 997 97 7 07 044 2
1914 1997 997 97 7 07 045 3
1915 1997 997 97 7 07 046 4
1916 1997 997 97 7 07 047 5
1917 1997 997 97 7 07 048 6
1918 1997 997 97 7 07 049 7
1919 0097 097 97 7 07 044 2
1920 0097 097 97 7 07 048 6
1921 0597 597 97 7 07 046 4
1922 1097 097 97 7 07 044 2
1923 1697 697 97 7 07 048 6
1924 1797 797 97 7 07 046 4
1925 1897 897 97 7 07 044 2
1926 1997 997 97 7 07 049 7
1927 2097 097 97 7 07 048 6
1928 1996 996 96 6 09 059 3
1929 1996 996 96 6 09 060 4
1930 1996 996 96 6 09 061 5
1931 1997 997 97 7 01 001 1
1932 1997 997 97 7 01 002 2
1933 1997 997 97 7 01 003 3
1934 1997 997 97 7 09 061 5
1935 1997 997 97 7 09 062 6
1936 1998 998 98 8 01 002 2
1937 1998 998 98 8 01 003 3
1938 1999 999 99 9 52 362 5
1939 1999 999 99 9 52 363 6
1940 2000 000 00 0 52 364 7
1941 2001 001 01 1 01 001 1
1942 (65 rows)
1944 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
1945 FROM TIMESTAMP_TBL;
1946 to_char
1947 ------------------------
1950 1970 970 70 0 1 4 4
1951 1997 997 97 7 7 43 1
1952 1997 997 97 7 7 43 1
1953 1997 997 97 7 7 43 1
1954 1997 997 97 7 7 43 1
1955 1997 997 97 7 7 43 1
1956 1997 997 97 7 7 43 1
1957 1997 997 97 7 1 4 4
1958 1997 997 97 7 1 4 4
1959 1997 997 97 7 7 43 1
1960 1997 997 97 7 7 43 1
1961 1997 997 97 7 7 43 1
1962 1997 997 97 7 7 43 1
1963 1997 997 97 7 24 163 2
1964 2001 1 1 1 38 265 6
1965 2000 0 0 0 11 73 3
1966 2000 0 0 0 11 73 3
1967 2000 0 0 0 11 73 3
1968 2000 0 0 0 11 73 3
1969 2000 0 0 0 11 73 3
1970 1997 997 97 7 7 43 1
1971 1997 997 97 7 7 43 1
1972 1997 997 97 7 7 43 1
1973 1997 997 97 7 7 43 1
1974 1997 997 97 7 7 43 1
1975 1997 997 97 7 7 43 1
1976 1997 997 97 7 7 43 1
1977 1997 997 97 7 7 43 1
1978 1997 997 97 7 7 43 1
1979 1997 997 97 7 7 43 1
1980 1997 997 97 7 7 43 1
1981 1997 997 97 7 7 43 1
1982 1997 997 97 7 24 163 2
1983 1997 997 97 7 7 43 1
1984 1997 997 97 7 7 44 2
1985 1997 997 97 7 7 45 3
1986 1997 997 97 7 7 46 4
1987 1997 997 97 7 7 47 5
1988 1997 997 97 7 7 48 6
1989 1997 997 97 7 7 49 7
1990 97 97 97 7 7 44 2
1991 97 97 97 7 7 48 6
1992 597 597 97 7 7 46 4
1993 1097 97 97 7 7 44 2
1994 1697 697 97 7 7 48 6
1995 1797 797 97 7 7 46 4
1996 1897 897 97 7 7 44 2
1997 1997 997 97 7 7 49 7
1998 2097 97 97 7 7 48 6
1999 1996 996 96 6 9 59 3
2000 1996 996 96 6 9 60 4
2001 1996 996 96 6 9 61 5
2002 1997 997 97 7 1 1 1
2003 1997 997 97 7 1 2 2
2004 1997 997 97 7 1 3 3
2005 1997 997 97 7 9 61 5
2006 1997 997 97 7 9 62 6
2007 1998 998 98 8 1 2 2
2008 1998 998 98 8 1 3 3
2009 1999 999 99 9 52 362 5
2010 1999 999 99 9 52 363 6
2011 2000 0 0 0 52 364 7
2012 2001 1 1 1 1 1 1
2013 (65 rows)
2015 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
2016 FROM (VALUES
2017 ('2018-11-02 12:34:56'::timestamp),
2018 ('2018-11-02 12:34:56.78'),
2019 ('2018-11-02 12:34:56.78901'),
2020 ('2018-11-02 12:34:56.78901234')
2021 ) d(d);
2022 to_char
2023 --------------------------------------------------------------------
2024 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000
2025 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000
2026 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010
2027 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012
2028 (4 rows)
2030 -- Roman months, with upper and lower case.
2031 SELECT i,
2032 to_char(i * interval '1mon', 'rm'),
2033 to_char(i * interval '1mon', 'RM')
2034 FROM generate_series(-13, 13) i;
2035 i | to_char | to_char
2036 -----+---------+---------
2037 -13 | xii | XII
2038 -12 | i | I
2039 -11 | ii | II
2040 -10 | iii | III
2041 -9 | iv | IV
2042 -8 | v | V
2043 -7 | vi | VI
2044 -6 | vii | VII
2045 -5 | viii | VIII
2046 -4 | ix | IX
2047 -3 | x | X
2048 -2 | xi | XI
2049 -1 | xii | XII
2050 0 | |
2051 1 | i | I
2052 2 | ii | II
2053 3 | iii | III
2054 4 | iv | IV
2055 5 | v | V
2056 6 | vi | VI
2057 7 | vii | VII
2058 8 | viii | VIII
2059 9 | ix | IX
2060 10 | x | X
2061 11 | xi | XI
2062 12 | xii | XII
2063 13 | i | I
2064 (27 rows)
2066 -- timestamp numeric fields constructor
2067 SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
2068 make_timestamp
2069 ------------------------------
2070 Sun Dec 28 06:30:45.887 2014
2071 (1 row)
2073 SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
2074 make_timestamp
2075 -----------------------------
2076 Fri Mar 15 12:30:15 0044 BC
2077 (1 row)
2079 -- should fail
2080 select make_timestamp(0, 7, 15, 12, 30, 15);
2081 ERROR: date field value out of range: 0-07-15
2082 -- generate_series for timestamp
2083 select * from generate_series('2020-01-01 00:00'::timestamp,
2084 '2020-01-02 03:00'::timestamp,
2085 '1 hour'::interval);
2086 generate_series
2087 --------------------------
2088 Wed Jan 01 00:00:00 2020
2089 Wed Jan 01 01:00:00 2020
2090 Wed Jan 01 02:00:00 2020
2091 Wed Jan 01 03:00:00 2020
2092 Wed Jan 01 04:00:00 2020
2093 Wed Jan 01 05:00:00 2020
2094 Wed Jan 01 06:00:00 2020
2095 Wed Jan 01 07:00:00 2020
2096 Wed Jan 01 08:00:00 2020
2097 Wed Jan 01 09:00:00 2020
2098 Wed Jan 01 10:00:00 2020
2099 Wed Jan 01 11:00:00 2020
2100 Wed Jan 01 12:00:00 2020
2101 Wed Jan 01 13:00:00 2020
2102 Wed Jan 01 14:00:00 2020
2103 Wed Jan 01 15:00:00 2020
2104 Wed Jan 01 16:00:00 2020
2105 Wed Jan 01 17:00:00 2020
2106 Wed Jan 01 18:00:00 2020
2107 Wed Jan 01 19:00:00 2020
2108 Wed Jan 01 20:00:00 2020
2109 Wed Jan 01 21:00:00 2020
2110 Wed Jan 01 22:00:00 2020
2111 Wed Jan 01 23:00:00 2020
2112 Thu Jan 02 00:00:00 2020
2113 Thu Jan 02 01:00:00 2020
2114 Thu Jan 02 02:00:00 2020
2115 Thu Jan 02 03:00:00 2020
2116 (28 rows)
2118 -- the LIMIT should allow this to terminate in a reasonable amount of time
2119 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
2120 select generate_series('2022-01-01 00:00'::timestamp,
2121 'infinity'::timestamp,
2122 '1 month'::interval) limit 10;
2123 generate_series
2124 --------------------------
2125 Sat Jan 01 00:00:00 2022
2126 Tue Feb 01 00:00:00 2022
2127 Tue Mar 01 00:00:00 2022
2128 Fri Apr 01 00:00:00 2022
2129 Sun May 01 00:00:00 2022
2130 Wed Jun 01 00:00:00 2022
2131 Fri Jul 01 00:00:00 2022
2132 Mon Aug 01 00:00:00 2022
2133 Thu Sep 01 00:00:00 2022
2134 Sat Oct 01 00:00:00 2022
2135 (10 rows)
2137 -- errors
2138 select * from generate_series('2020-01-01 00:00'::timestamp,
2139 '2020-01-02 03:00'::timestamp,
2140 '0 hour'::interval);
2141 ERROR: step size cannot equal zero
2142 select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity');
2143 ERROR: step size cannot be infinite
2144 select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity');
2145 ERROR: step size cannot be infinite
2146 -- test arithmetic with infinite timestamps
2147 select timestamp 'infinity' - timestamp 'infinity';
2148 ERROR: interval out of range
2149 select timestamp 'infinity' - timestamp '-infinity';
2150 ?column?
2151 ----------
2152 infinity
2153 (1 row)
2155 select timestamp '-infinity' - timestamp 'infinity';
2156 ?column?
2157 -----------
2158 -infinity
2159 (1 row)
2161 select timestamp '-infinity' - timestamp '-infinity';
2162 ERROR: interval out of range
2163 select timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
2164 ?column?
2165 ----------
2166 infinity
2167 (1 row)
2169 select timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
2170 ?column?
2171 -----------
2172 -infinity
2173 (1 row)
2175 -- test age() with infinite timestamps
2176 select age(timestamp 'infinity');
2177 age
2178 -----------
2179 -infinity
2180 (1 row)
2182 select age(timestamp '-infinity');
2183 age
2184 ----------
2185 infinity
2186 (1 row)
2188 select age(timestamp 'infinity', timestamp 'infinity');
2189 ERROR: interval out of range
2190 select age(timestamp 'infinity', timestamp '-infinity');
2191 age
2192 ----------
2193 infinity
2194 (1 row)
2196 select age(timestamp '-infinity', timestamp 'infinity');
2197 age
2198 -----------
2199 -infinity
2200 (1 row)
2202 select age(timestamp '-infinity', timestamp '-infinity');
2203 ERROR: interval out of range
2204 -- test timestamp near POSTGRES_EPOCH_JDATE
2205 select timestamp '1999-12-31 24:00:00';
2206 timestamp
2207 --------------------------
2208 Sat Jan 01 00:00:00 2000
2209 (1 row)
2211 select make_timestamp(1999, 12, 31, 24, 0, 0);
2212 make_timestamp
2213 --------------------------
2214 Sat Jan 01 00:00:00 2000
2215 (1 row)