| blob | c64bcb7c127fc3b0e3d23f9283345ed5126db578 |
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 -- disallow intervals with months or years
740 SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
741 ERROR: timestamps cannot be binned into intervals containing months or years
742 SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
743 ERROR: timestamps cannot be binned into intervals containing months or years
744 -- disallow zero intervals
745 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
746 ERROR: stride must be greater than zero
747 -- disallow negative intervals
748 SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
749 ERROR: stride must be greater than zero
750 -- Test casting within a BETWEEN qualifier
751 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
752 FROM TIMESTAMP_TBL
753 WHERE d1 BETWEEN timestamp without time zone '1902-01-01'
754 AND timestamp without time zone '2038-01-01';
755 diff
756 ----------------------------------------
757 @ 9863 days ago
758 @ 39 days 17 hours 32 mins 1 sec
759 @ 39 days 17 hours 32 mins 1 sec
760 @ 39 days 17 hours 32 mins 2 secs
761 @ 39 days 17 hours 32 mins 1.4 secs
762 @ 39 days 17 hours 32 mins 1.5 secs
763 @ 39 days 17 hours 32 mins 1.6 secs
764 @ 0
765 @ 3 hours 4 mins 5 secs
766 @ 39 days 17 hours 32 mins 1 sec
767 @ 39 days 17 hours 32 mins 1 sec
768 @ 39 days 17 hours 32 mins 1 sec
769 @ 39 days 17 hours 32 mins 1 sec
770 @ 159 days 17 hours 32 mins 1 sec
771 @ 1724 days 18 hours 19 mins 20 secs
772 @ 1168 days 8 hours 14 mins 1 sec
773 @ 1168 days 13 hours 14 mins 2 secs
774 @ 1168 days 12 hours 14 mins 3 secs
775 @ 1168 days 3 hours 14 mins 4 secs
776 @ 1168 days 2 hours 14 mins 5 secs
777 @ 39 days 17 hours 32 mins 1 sec
778 @ 39 days 17 hours 32 mins 1 sec
779 @ 39 days 17 hours 32 mins
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 @ 39 days 17 hours 32 mins 1 sec
785 @ 39 days 17 hours 32 mins 1 sec
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 @ 159 days 18 hours 32 mins 1 sec
790 @ 39 days 17 hours 32 mins 1 sec
791 @ 40 days 17 hours 32 mins 1 sec
792 @ 41 days 17 hours 32 mins 1 sec
793 @ 42 days 17 hours 32 mins 1 sec
794 @ 43 days 17 hours 32 mins 1 sec
795 @ 44 days 17 hours 32 mins 1 sec
796 @ 45 days 17 hours 32 mins 1 sec
797 @ 45 days 17 hours 32 mins 1 sec
798 @ 308 days 6 hours 27 mins 59 secs ago
799 @ 307 days 6 hours 27 mins 59 secs ago
800 @ 306 days 6 hours 27 mins 59 secs ago
801 @ 2 days 6 hours 27 mins 59 secs ago
802 @ 1 day 6 hours 27 mins 59 secs ago
803 @ 6 hours 27 mins 59 secs ago
804 @ 57 days 17 hours 32 mins 1 sec
805 @ 58 days 17 hours 32 mins 1 sec
806 @ 362 days 17 hours 32 mins 1 sec
807 @ 363 days 17 hours 32 mins 1 sec
808 @ 1093 days 17 hours 32 mins 1 sec
809 @ 1094 days 17 hours 32 mins 1 sec
810 @ 1459 days 17 hours 32 mins 1 sec
811 @ 1460 days 17 hours 32 mins 1 sec
812 (55 rows)
814 -- DATE_PART (timestamp_part)
815 SELECT d1 as "timestamp",
816 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
817 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
818 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
819 FROM TIMESTAMP_TBL;
820 timestamp | year | month | day | hour | minute | second
821 -----------------------------+-----------+-------+-----+------+--------+--------
822 -infinity | -Infinity | | | | |
823 infinity | Infinity | | | | |
824 Thu Jan 01 00:00:00 1970 | 1970 | 1 | 1 | 0 | 0 | 0
825 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
826 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
827 Mon Feb 10 17:32:02 1997 | 1997 | 2 | 10 | 17 | 32 | 2
828 Mon Feb 10 17:32:01.4 1997 | 1997 | 2 | 10 | 17 | 32 | 1.4
829 Mon Feb 10 17:32:01.5 1997 | 1997 | 2 | 10 | 17 | 32 | 1.5
830 Mon Feb 10 17:32:01.6 1997 | 1997 | 2 | 10 | 17 | 32 | 1.6
831 Thu Jan 02 00:00:00 1997 | 1997 | 1 | 2 | 0 | 0 | 0
832 Thu Jan 02 03:04:05 1997 | 1997 | 1 | 2 | 3 | 4 | 5
833 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
834 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
835 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
836 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
837 Tue Jun 10 17:32:01 1997 | 1997 | 6 | 10 | 17 | 32 | 1
838 Sat Sep 22 18:19:20 2001 | 2001 | 9 | 22 | 18 | 19 | 20
839 Wed Mar 15 08:14:01 2000 | 2000 | 3 | 15 | 8 | 14 | 1
840 Wed Mar 15 13:14:02 2000 | 2000 | 3 | 15 | 13 | 14 | 2
841 Wed Mar 15 12:14:03 2000 | 2000 | 3 | 15 | 12 | 14 | 3
842 Wed Mar 15 03:14:04 2000 | 2000 | 3 | 15 | 3 | 14 | 4
843 Wed Mar 15 02:14:05 2000 | 2000 | 3 | 15 | 2 | 14 | 5
844 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
845 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
846 Mon Feb 10 17:32:00 1997 | 1997 | 2 | 10 | 17 | 32 | 0
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 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
852 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
853 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
854 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
855 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
856 Tue Jun 10 18:32:01 1997 | 1997 | 6 | 10 | 18 | 32 | 1
857 Mon Feb 10 17:32:01 1997 | 1997 | 2 | 10 | 17 | 32 | 1
858 Tue Feb 11 17:32:01 1997 | 1997 | 2 | 11 | 17 | 32 | 1
859 Wed Feb 12 17:32:01 1997 | 1997 | 2 | 12 | 17 | 32 | 1
860 Thu Feb 13 17:32:01 1997 | 1997 | 2 | 13 | 17 | 32 | 1
861 Fri Feb 14 17:32:01 1997 | 1997 | 2 | 14 | 17 | 32 | 1
862 Sat Feb 15 17:32:01 1997 | 1997 | 2 | 15 | 17 | 32 | 1
863 Sun Feb 16 17:32:01 1997 | 1997 | 2 | 16 | 17 | 32 | 1
864 Tue Feb 16 17:32:01 0097 BC | -97 | 2 | 16 | 17 | 32 | 1
865 Sat Feb 16 17:32:01 0097 | 97 | 2 | 16 | 17 | 32 | 1
866 Thu Feb 16 17:32:01 0597 | 597 | 2 | 16 | 17 | 32 | 1
867 Tue Feb 16 17:32:01 1097 | 1097 | 2 | 16 | 17 | 32 | 1
868 Sat Feb 16 17:32:01 1697 | 1697 | 2 | 16 | 17 | 32 | 1
869 Thu Feb 16 17:32:01 1797 | 1797 | 2 | 16 | 17 | 32 | 1
870 Tue Feb 16 17:32:01 1897 | 1897 | 2 | 16 | 17 | 32 | 1
871 Sun Feb 16 17:32:01 1997 | 1997 | 2 | 16 | 17 | 32 | 1
872 Sat Feb 16 17:32:01 2097 | 2097 | 2 | 16 | 17 | 32 | 1
873 Wed Feb 28 17:32:01 1996 | 1996 | 2 | 28 | 17 | 32 | 1
874 Thu Feb 29 17:32:01 1996 | 1996 | 2 | 29 | 17 | 32 | 1
875 Fri Mar 01 17:32:01 1996 | 1996 | 3 | 1 | 17 | 32 | 1
876 Mon Dec 30 17:32:01 1996 | 1996 | 12 | 30 | 17 | 32 | 1
877 Tue Dec 31 17:32:01 1996 | 1996 | 12 | 31 | 17 | 32 | 1
878 Wed Jan 01 17:32:01 1997 | 1997 | 1 | 1 | 17 | 32 | 1
879 Fri Feb 28 17:32:01 1997 | 1997 | 2 | 28 | 17 | 32 | 1
880 Sat Mar 01 17:32:01 1997 | 1997 | 3 | 1 | 17 | 32 | 1
881 Tue Dec 30 17:32:01 1997 | 1997 | 12 | 30 | 17 | 32 | 1
882 Wed Dec 31 17:32:01 1997 | 1997 | 12 | 31 | 17 | 32 | 1
883 Fri Dec 31 17:32:01 1999 | 1999 | 12 | 31 | 17 | 32 | 1
884 Sat Jan 01 17:32:01 2000 | 2000 | 1 | 1 | 17 | 32 | 1
885 Sun Dec 31 17:32:01 2000 | 2000 | 12 | 31 | 17 | 32 | 1
886 Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 17 | 32 | 1
887 (65 rows)
889 SELECT d1 as "timestamp",
890 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
891 date_part( 'usec', d1) AS usec
892 FROM TIMESTAMP_TBL;
893 timestamp | quarter | msec | usec
894 -----------------------------+---------+-------+----------
895 -infinity | | |
896 infinity | | |
897 Thu Jan 01 00:00:00 1970 | 1 | 0 | 0
898 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
899 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
900 Mon Feb 10 17:32:02 1997 | 1 | 2000 | 2000000
901 Mon Feb 10 17:32:01.4 1997 | 1 | 1400 | 1400000
902 Mon Feb 10 17:32:01.5 1997 | 1 | 1500 | 1500000
903 Mon Feb 10 17:32:01.6 1997 | 1 | 1600 | 1600000
904 Thu Jan 02 00:00:00 1997 | 1 | 0 | 0
905 Thu Jan 02 03:04:05 1997 | 1 | 5000 | 5000000
906 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
907 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
908 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
909 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
910 Tue Jun 10 17:32:01 1997 | 2 | 1000 | 1000000
911 Sat Sep 22 18:19:20 2001 | 3 | 20000 | 20000000
912 Wed Mar 15 08:14:01 2000 | 1 | 1000 | 1000000
913 Wed Mar 15 13:14:02 2000 | 1 | 2000 | 2000000
914 Wed Mar 15 12:14:03 2000 | 1 | 3000 | 3000000
915 Wed Mar 15 03:14:04 2000 | 1 | 4000 | 4000000
916 Wed Mar 15 02:14:05 2000 | 1 | 5000 | 5000000
917 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
918 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
919 Mon Feb 10 17:32:00 1997 | 1 | 0 | 0
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 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
925 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
926 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
927 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
928 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
929 Tue Jun 10 18:32:01 1997 | 2 | 1000 | 1000000
930 Mon Feb 10 17:32:01 1997 | 1 | 1000 | 1000000
931 Tue Feb 11 17:32:01 1997 | 1 | 1000 | 1000000
932 Wed Feb 12 17:32:01 1997 | 1 | 1000 | 1000000
933 Thu Feb 13 17:32:01 1997 | 1 | 1000 | 1000000
934 Fri Feb 14 17:32:01 1997 | 1 | 1000 | 1000000
935 Sat Feb 15 17:32:01 1997 | 1 | 1000 | 1000000
936 Sun Feb 16 17:32:01 1997 | 1 | 1000 | 1000000
937 Tue Feb 16 17:32:01 0097 BC | 1 | 1000 | 1000000
938 Sat Feb 16 17:32:01 0097 | 1 | 1000 | 1000000
939 Thu Feb 16 17:32:01 0597 | 1 | 1000 | 1000000
940 Tue Feb 16 17:32:01 1097 | 1 | 1000 | 1000000
941 Sat Feb 16 17:32:01 1697 | 1 | 1000 | 1000000
942 Thu Feb 16 17:32:01 1797 | 1 | 1000 | 1000000
943 Tue Feb 16 17:32:01 1897 | 1 | 1000 | 1000000
944 Sun Feb 16 17:32:01 1997 | 1 | 1000 | 1000000
945 Sat Feb 16 17:32:01 2097 | 1 | 1000 | 1000000
946 Wed Feb 28 17:32:01 1996 | 1 | 1000 | 1000000
947 Thu Feb 29 17:32:01 1996 | 1 | 1000 | 1000000
948 Fri Mar 01 17:32:01 1996 | 1 | 1000 | 1000000
949 Mon Dec 30 17:32:01 1996 | 4 | 1000 | 1000000
950 Tue Dec 31 17:32:01 1996 | 4 | 1000 | 1000000
951 Wed Jan 01 17:32:01 1997 | 1 | 1000 | 1000000
952 Fri Feb 28 17:32:01 1997 | 1 | 1000 | 1000000
953 Sat Mar 01 17:32:01 1997 | 1 | 1000 | 1000000
954 Tue Dec 30 17:32:01 1997 | 4 | 1000 | 1000000
955 Wed Dec 31 17:32:01 1997 | 4 | 1000 | 1000000
956 Fri Dec 31 17:32:01 1999 | 4 | 1000 | 1000000
957 Sat Jan 01 17:32:01 2000 | 1 | 1000 | 1000000
958 Sun Dec 31 17:32:01 2000 | 4 | 1000 | 1000000
959 Mon Jan 01 17:32:01 2001 | 1 | 1000 | 1000000
960 (65 rows)
962 SELECT d1 as "timestamp",
963 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
964 date_part( 'isodow', d1) AS isodow, date_part( 'dow', d1) AS dow,
965 date_part( 'doy', d1) AS doy
966 FROM TIMESTAMP_TBL;
967 timestamp | isoyear | week | isodow | dow | doy
968 -----------------------------+-----------+------+--------+-----+-----
969 -infinity | -Infinity | | | |
970 infinity | Infinity | | | |
971 Thu Jan 01 00:00:00 1970 | 1970 | 1 | 4 | 4 | 1
972 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
973 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
974 Mon Feb 10 17:32:02 1997 | 1997 | 7 | 1 | 1 | 41
975 Mon Feb 10 17:32:01.4 1997 | 1997 | 7 | 1 | 1 | 41
976 Mon Feb 10 17:32:01.5 1997 | 1997 | 7 | 1 | 1 | 41
977 Mon Feb 10 17:32:01.6 1997 | 1997 | 7 | 1 | 1 | 41
978 Thu Jan 02 00:00:00 1997 | 1997 | 1 | 4 | 4 | 2
979 Thu Jan 02 03:04:05 1997 | 1997 | 1 | 4 | 4 | 2
980 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
981 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
982 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
983 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
984 Tue Jun 10 17:32:01 1997 | 1997 | 24 | 2 | 2 | 161
985 Sat Sep 22 18:19:20 2001 | 2001 | 38 | 6 | 6 | 265
986 Wed Mar 15 08:14:01 2000 | 2000 | 11 | 3 | 3 | 75
987 Wed Mar 15 13:14:02 2000 | 2000 | 11 | 3 | 3 | 75
988 Wed Mar 15 12:14:03 2000 | 2000 | 11 | 3 | 3 | 75
989 Wed Mar 15 03:14:04 2000 | 2000 | 11 | 3 | 3 | 75
990 Wed Mar 15 02:14:05 2000 | 2000 | 11 | 3 | 3 | 75
991 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
992 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
993 Mon Feb 10 17:32:00 1997 | 1997 | 7 | 1 | 1 | 41
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 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
999 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1000 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1001 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1002 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1003 Tue Jun 10 18:32:01 1997 | 1997 | 24 | 2 | 2 | 161
1004 Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1 | 1 | 41
1005 Tue Feb 11 17:32:01 1997 | 1997 | 7 | 2 | 2 | 42
1006 Wed Feb 12 17:32:01 1997 | 1997 | 7 | 3 | 3 | 43
1007 Thu Feb 13 17:32:01 1997 | 1997 | 7 | 4 | 4 | 44
1008 Fri Feb 14 17:32:01 1997 | 1997 | 7 | 5 | 5 | 45
1009 Sat Feb 15 17:32:01 1997 | 1997 | 7 | 6 | 6 | 46
1010 Sun Feb 16 17:32:01 1997 | 1997 | 7 | 7 | 0 | 47
1011 Tue Feb 16 17:32:01 0097 BC | -97 | 7 | 2 | 2 | 47
1012 Sat Feb 16 17:32:01 0097 | 97 | 7 | 6 | 6 | 47
1013 Thu Feb 16 17:32:01 0597 | 597 | 7 | 4 | 4 | 47
1014 Tue Feb 16 17:32:01 1097 | 1097 | 7 | 2 | 2 | 47
1015 Sat Feb 16 17:32:01 1697 | 1697 | 7 | 6 | 6 | 47
1016 Thu Feb 16 17:32:01 1797 | 1797 | 7 | 4 | 4 | 47
1017 Tue Feb 16 17:32:01 1897 | 1897 | 7 | 2 | 2 | 47
1018 Sun Feb 16 17:32:01 1997 | 1997 | 7 | 7 | 0 | 47
1019 Sat Feb 16 17:32:01 2097 | 2097 | 7 | 6 | 6 | 47
1020 Wed Feb 28 17:32:01 1996 | 1996 | 9 | 3 | 3 | 59
1021 Thu Feb 29 17:32:01 1996 | 1996 | 9 | 4 | 4 | 60
1022 Fri Mar 01 17:32:01 1996 | 1996 | 9 | 5 | 5 | 61
1023 Mon Dec 30 17:32:01 1996 | 1997 | 1 | 1 | 1 | 365
1024 Tue Dec 31 17:32:01 1996 | 1997 | 1 | 2 | 2 | 366
1025 Wed Jan 01 17:32:01 1997 | 1997 | 1 | 3 | 3 | 1
1026 Fri Feb 28 17:32:01 1997 | 1997 | 9 | 5 | 5 | 59
1027 Sat Mar 01 17:32:01 1997 | 1997 | 9 | 6 | 6 | 60
1028 Tue Dec 30 17:32:01 1997 | 1998 | 1 | 2 | 2 | 364
1029 Wed Dec 31 17:32:01 1997 | 1998 | 1 | 3 | 3 | 365
1030 Fri Dec 31 17:32:01 1999 | 1999 | 52 | 5 | 5 | 365
1031 Sat Jan 01 17:32:01 2000 | 1999 | 52 | 6 | 6 | 1
1032 Sun Dec 31 17:32:01 2000 | 2000 | 52 | 7 | 0 | 366
1033 Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 1 | 1
1034 (65 rows)
1036 SELECT d1 as "timestamp",
1037 date_part( 'decade', d1) AS decade,
1038 date_part( 'century', d1) AS century,
1039 date_part( 'millennium', d1) AS millennium,
1040 round(date_part( 'julian', d1)) AS julian,
1041 date_part( 'epoch', d1) AS epoch
1042 FROM TIMESTAMP_TBL;
1043 timestamp | decade | century | millennium | julian | epoch
1044 -----------------------------+-----------+-----------+------------+-----------+--------------
1045 -infinity | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1046 infinity | Infinity | Infinity | Infinity | Infinity | Infinity
1047 Thu Jan 01 00:00:00 1970 | 197 | 20 | 2 | 2440588 | 0
1048 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1049 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1050 Mon Feb 10 17:32:02 1997 | 199 | 20 | 2 | 2450491 | 855595922
1051 Mon Feb 10 17:32:01.4 1997 | 199 | 20 | 2 | 2450491 | 855595921.4
1052 Mon Feb 10 17:32:01.5 1997 | 199 | 20 | 2 | 2450491 | 855595921.5
1053 Mon Feb 10 17:32:01.6 1997 | 199 | 20 | 2 | 2450491 | 855595921.6
1054 Thu Jan 02 00:00:00 1997 | 199 | 20 | 2 | 2450451 | 852163200
1055 Thu Jan 02 03:04:05 1997 | 199 | 20 | 2 | 2450451 | 852174245
1056 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1057 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1058 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1059 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1060 Tue Jun 10 17:32:01 1997 | 199 | 20 | 2 | 2450611 | 865963921
1061 Sat Sep 22 18:19:20 2001 | 200 | 21 | 3 | 2452176 | 1001182760
1062 Wed Mar 15 08:14:01 2000 | 200 | 20 | 2 | 2451619 | 953108041
1063 Wed Mar 15 13:14:02 2000 | 200 | 20 | 2 | 2451620 | 953126042
1064 Wed Mar 15 12:14:03 2000 | 200 | 20 | 2 | 2451620 | 953122443
1065 Wed Mar 15 03:14:04 2000 | 200 | 20 | 2 | 2451619 | 953090044
1066 Wed Mar 15 02:14:05 2000 | 200 | 20 | 2 | 2451619 | 953086445
1067 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1068 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1069 Mon Feb 10 17:32:00 1997 | 199 | 20 | 2 | 2450491 | 855595920
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 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1075 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1076 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1077 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1078 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1079 Tue Jun 10 18:32:01 1997 | 199 | 20 | 2 | 2450611 | 865967521
1080 Mon Feb 10 17:32:01 1997 | 199 | 20 | 2 | 2450491 | 855595921
1081 Tue Feb 11 17:32:01 1997 | 199 | 20 | 2 | 2450492 | 855682321
1082 Wed Feb 12 17:32:01 1997 | 199 | 20 | 2 | 2450493 | 855768721
1083 Thu Feb 13 17:32:01 1997 | 199 | 20 | 2 | 2450494 | 855855121
1084 Fri Feb 14 17:32:01 1997 | 199 | 20 | 2 | 2450495 | 855941521
1085 Sat Feb 15 17:32:01 1997 | 199 | 20 | 2 | 2450496 | 856027921
1086 Sun Feb 16 17:32:01 1997 | 199 | 20 | 2 | 2450497 | 856114321
1087 Tue Feb 16 17:32:01 0097 BC | -10 | -1 | -1 | 1686043 | -65192711279
1088 Sat Feb 16 17:32:01 0097 | 9 | 1 | 1 | 1756537 | -59102029679
1089 Thu Feb 16 17:32:01 0597 | 59 | 6 | 1 | 1939158 | -43323575279
1090 Tue Feb 16 17:32:01 1097 | 109 | 11 | 2 | 2121779 | -27545120879
1091 Sat Feb 16 17:32:01 1697 | 169 | 17 | 2 | 2340925 | -8610906479
1092 Thu Feb 16 17:32:01 1797 | 179 | 18 | 2 | 2377449 | -5455232879
1093 Tue Feb 16 17:32:01 1897 | 189 | 19 | 2 | 2413973 | -2299559279
1094 Sun Feb 16 17:32:01 1997 | 199 | 20 | 2 | 2450497 | 856114321
1095 Sat Feb 16 17:32:01 2097 | 209 | 21 | 3 | 2487022 | 4011874321
1096 Wed Feb 28 17:32:01 1996 | 199 | 20 | 2 | 2450143 | 825528721
1097 Thu Feb 29 17:32:01 1996 | 199 | 20 | 2 | 2450144 | 825615121
1098 Fri Mar 01 17:32:01 1996 | 199 | 20 | 2 | 2450145 | 825701521
1099 Mon Dec 30 17:32:01 1996 | 199 | 20 | 2 | 2450449 | 851967121
1100 Tue Dec 31 17:32:01 1996 | 199 | 20 | 2 | 2450450 | 852053521
1101 Wed Jan 01 17:32:01 1997 | 199 | 20 | 2 | 2450451 | 852139921
1102 Fri Feb 28 17:32:01 1997 | 199 | 20 | 2 | 2450509 | 857151121
1103 Sat Mar 01 17:32:01 1997 | 199 | 20 | 2 | 2450510 | 857237521
1104 Tue Dec 30 17:32:01 1997 | 199 | 20 | 2 | 2450814 | 883503121
1105 Wed Dec 31 17:32:01 1997 | 199 | 20 | 2 | 2450815 | 883589521
1106 Fri Dec 31 17:32:01 1999 | 199 | 20 | 2 | 2451545 | 946661521
1107 Sat Jan 01 17:32:01 2000 | 200 | 20 | 2 | 2451546 | 946747921
1108 Sun Dec 31 17:32:01 2000 | 200 | 20 | 2 | 2451911 | 978283921
1109 Mon Jan 01 17:32:01 2001 | 200 | 21 | 3 | 2451912 | 978370321
1110 (65 rows)
1112 -- extract implementation is mostly the same as date_part, so only
1113 -- test a few cases for additional coverage.
1114 SELECT d1 as "timestamp",
1115 extract(microseconds from d1) AS microseconds,
1116 extract(milliseconds from d1) AS milliseconds,
1117 extract(seconds from d1) AS seconds,
1118 round(extract(julian from d1)) AS julian,
1119 extract(epoch from d1) AS epoch
1120 FROM TIMESTAMP_TBL;
1121 timestamp | microseconds | milliseconds | seconds | julian | epoch
1122 -----------------------------+--------------+--------------+-----------+-----------+---------------------
1123 -infinity | | | | -Infinity | -Infinity
1124 infinity | | | | Infinity | Infinity
1125 Thu Jan 01 00:00:00 1970 | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
1126 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1127 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1128 Mon Feb 10 17:32:02 1997 | 2000000 | 2000.000 | 2.000000 | 2450491 | 855595922.000000
1129 Mon Feb 10 17:32:01.4 1997 | 1400000 | 1400.000 | 1.400000 | 2450491 | 855595921.400000
1130 Mon Feb 10 17:32:01.5 1997 | 1500000 | 1500.000 | 1.500000 | 2450491 | 855595921.500000
1131 Mon Feb 10 17:32:01.6 1997 | 1600000 | 1600.000 | 1.600000 | 2450491 | 855595921.600000
1132 Thu Jan 02 00:00:00 1997 | 0 | 0.000 | 0.000000 | 2450451 | 852163200.000000
1133 Thu Jan 02 03:04:05 1997 | 5000000 | 5000.000 | 5.000000 | 2450451 | 852174245.000000
1134 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1135 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1136 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1137 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1138 Tue Jun 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865963921.000000
1139 Sat Sep 22 18:19:20 2001 | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001182760.000000
1140 Wed Mar 15 08:14:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451619 | 953108041.000000
1141 Wed Mar 15 13:14:02 2000 | 2000000 | 2000.000 | 2.000000 | 2451620 | 953126042.000000
1142 Wed Mar 15 12:14:03 2000 | 3000000 | 3000.000 | 3.000000 | 2451620 | 953122443.000000
1143 Wed Mar 15 03:14:04 2000 | 4000000 | 4000.000 | 4.000000 | 2451619 | 953090044.000000
1144 Wed Mar 15 02:14:05 2000 | 5000000 | 5000.000 | 5.000000 | 2451619 | 953086445.000000
1145 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1146 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1147 Mon Feb 10 17:32:00 1997 | 0 | 0.000 | 0.000000 | 2450491 | 855595920.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 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1153 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1154 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1155 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1156 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1157 Tue Jun 10 18:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865967521.000000
1158 Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
1159 Tue Feb 11 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450492 | 855682321.000000
1160 Wed Feb 12 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450493 | 855768721.000000
1161 Thu Feb 13 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450494 | 855855121.000000
1162 Fri Feb 14 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450495 | 855941521.000000
1163 Sat Feb 15 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450496 | 856027921.000000
1164 Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
1165 Tue Feb 16 17:32:01 0097 BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192711279.000000
1166 Sat Feb 16 17:32:01 0097 | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102029679.000000
1167 Thu Feb 16 17:32:01 0597 | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323575279.000000
1168 Tue Feb 16 17:32:01 1097 | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545120879.000000
1169 Sat Feb 16 17:32:01 1697 | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610906479.000000
1170 Thu Feb 16 17:32:01 1797 | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455232879.000000
1171 Tue Feb 16 17:32:01 1897 | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299559279.000000
1172 Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
1173 Sat Feb 16 17:32:01 2097 | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011874321.000000
1174 Wed Feb 28 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450143 | 825528721.000000
1175 Thu Feb 29 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450144 | 825615121.000000
1176 Fri Mar 01 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450145 | 825701521.000000
1177 Mon Dec 30 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450449 | 851967121.000000
1178 Tue Dec 31 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450450 | 852053521.000000
1179 Wed Jan 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450451 | 852139921.000000
1180 Fri Feb 28 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450509 | 857151121.000000
1181 Sat Mar 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450510 | 857237521.000000
1182 Tue Dec 30 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450814 | 883503121.000000
1183 Wed Dec 31 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450815 | 883589521.000000
1184 Fri Dec 31 17:32:01 1999 | 1000000 | 1000.000 | 1.000000 | 2451545 | 946661521.000000
1185 Sat Jan 01 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451546 | 946747921.000000
1186 Sun Dec 31 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451911 | 978283921.000000
1187 Mon Jan 01 17:32:01 2001 | 1000000 | 1000.000 | 1.000000 | 2451912 | 978370321.000000
1188 (65 rows)
1190 -- value near upper bound uses special case in code
1191 SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp);
1192 date_part
1193 ---------------
1194 9224097091200
1195 (1 row)
1197 SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp);
1198 extract
1199 ----------------------
1200 9224097091200.000000
1201 (1 row)
1203 -- another internal overflow test case
1204 SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp);
1205 extract
1206 --------------------
1207 95617584000.000000
1208 (1 row)
1210 -- test edge-case overflow in timestamp subtraction
1211 SELECT timestamp '294276-12-31 23:59:59' - timestamp '1999-12-23 19:59:04.224193' AS ok;
1212 ok
1213 -----------------------------------------
1214 @ 106751991 days 4 hours 54.775807 secs
1215 (1 row)
1217 SELECT timestamp '294276-12-31 23:59:59' - timestamp '1999-12-23 19:59:04.224192' AS overflows;
1218 ERROR: interval out of range
1219 -- TO_CHAR()
1220 SELECT to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
1221 FROM TIMESTAMP_TBL;
1222 to_char
1223 ------------------------------------------------------------------------------------------
1226 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1227 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1228 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1229 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1230 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1231 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1232 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1233 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1234 THURSDAY Thursday thursday THU Thu thu JANUARY January january I JAN Jan jan
1235 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1236 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1237 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1238 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1239 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1240 SATURDAY Saturday saturday SAT Sat sat SEPTEMBER September september IX SEP Sep sep
1241 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1242 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1243 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1244 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1245 WEDNESDAY Wednesday wednesday WED Wed wed MARCH March march III MAR Mar mar
1246 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1247 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1248 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
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 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1254 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1255 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1256 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1257 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1258 TUESDAY Tuesday tuesday TUE Tue tue JUNE June june VI JUN Jun jun
1259 MONDAY Monday monday MON Mon mon FEBRUARY February february II FEB Feb feb
1260 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1261 WEDNESDAY Wednesday wednesday WED Wed wed FEBRUARY February february II FEB Feb feb
1262 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1263 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1264 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1265 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1266 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1267 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1268 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1269 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1270 SATURDAY Saturday saturday SAT Sat sat FEBRUARY February february II FEB Feb feb
1271 THURSDAY Thursday thursday THU Thu thu FEBRUARY February february II FEB Feb feb
1272 TUESDAY Tuesday tuesday TUE Tue tue FEBRUARY February february II FEB Feb feb
1273 SUNDAY Sunday sunday SUN Sun sun FEBRUARY February february II FEB Feb feb
1274 SATURDAY Saturday saturday SAT Sat sat 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 MARCH March march III MAR Mar mar
1278 MONDAY Monday monday MON Mon mon DECEMBER December december XII DEC Dec dec
1279 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1280 WEDNESDAY Wednesday wednesday WED Wed wed JANUARY January january I JAN Jan jan
1281 FRIDAY Friday friday FRI Fri fri FEBRUARY February february II FEB Feb feb
1282 SATURDAY Saturday saturday SAT Sat sat MARCH March march III MAR Mar mar
1283 TUESDAY Tuesday tuesday TUE Tue tue DECEMBER December december XII DEC Dec dec
1284 WEDNESDAY Wednesday wednesday WED Wed wed DECEMBER December december XII DEC Dec dec
1285 FRIDAY Friday friday FRI Fri fri DECEMBER December december XII DEC Dec dec
1286 SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
1287 SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
1288 MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
1289 (65 rows)
1291 SELECT to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
1292 FROM TIMESTAMP_TBL;
1293 to_char
1294 --------------------------------------------------------------
1297 THURSDAY Thursday thursday JANUARY January january I
1298 MONDAY Monday monday FEBRUARY February february II
1299 MONDAY Monday monday FEBRUARY February february II
1300 MONDAY Monday monday FEBRUARY February february II
1301 MONDAY Monday monday FEBRUARY February february II
1302 MONDAY Monday monday FEBRUARY February february II
1303 MONDAY Monday monday FEBRUARY February february II
1304 THURSDAY Thursday thursday JANUARY January january I
1305 THURSDAY Thursday thursday JANUARY January january I
1306 MONDAY Monday monday FEBRUARY February february II
1307 MONDAY Monday monday FEBRUARY February february II
1308 MONDAY Monday monday FEBRUARY February february II
1309 MONDAY Monday monday FEBRUARY February february II
1310 TUESDAY Tuesday tuesday JUNE June june VI
1311 SATURDAY Saturday saturday SEPTEMBER September september IX
1312 WEDNESDAY Wednesday wednesday MARCH March march III
1313 WEDNESDAY Wednesday wednesday MARCH March march III
1314 WEDNESDAY Wednesday wednesday MARCH March march III
1315 WEDNESDAY Wednesday wednesday MARCH March march III
1316 WEDNESDAY Wednesday wednesday MARCH March march III
1317 MONDAY Monday monday FEBRUARY February february II
1318 MONDAY Monday monday FEBRUARY February february II
1319 MONDAY Monday monday FEBRUARY February february II
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 MONDAY Monday monday FEBRUARY February february II
1325 MONDAY Monday monday FEBRUARY February february II
1326 MONDAY Monday monday FEBRUARY February february II
1327 MONDAY Monday monday FEBRUARY February february II
1328 MONDAY Monday monday FEBRUARY February february II
1329 TUESDAY Tuesday tuesday JUNE June june VI
1330 MONDAY Monday monday FEBRUARY February february II
1331 TUESDAY Tuesday tuesday FEBRUARY February february II
1332 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1333 THURSDAY Thursday thursday FEBRUARY February february II
1334 FRIDAY Friday friday FEBRUARY February february II
1335 SATURDAY Saturday saturday FEBRUARY February february II
1336 SUNDAY Sunday sunday FEBRUARY February february II
1337 TUESDAY Tuesday tuesday FEBRUARY February february II
1338 SATURDAY Saturday saturday FEBRUARY February february II
1339 THURSDAY Thursday thursday FEBRUARY February february II
1340 TUESDAY Tuesday tuesday FEBRUARY February february II
1341 SATURDAY Saturday saturday FEBRUARY February february II
1342 THURSDAY Thursday thursday FEBRUARY February february II
1343 TUESDAY Tuesday tuesday FEBRUARY February february II
1344 SUNDAY Sunday sunday FEBRUARY February february II
1345 SATURDAY Saturday saturday FEBRUARY February february II
1346 WEDNESDAY Wednesday wednesday FEBRUARY February february II
1347 THURSDAY Thursday thursday FEBRUARY February february II
1348 FRIDAY Friday friday MARCH March march III
1349 MONDAY Monday monday DECEMBER December december XII
1350 TUESDAY Tuesday tuesday DECEMBER December december XII
1351 WEDNESDAY Wednesday wednesday JANUARY January january I
1352 FRIDAY Friday friday FEBRUARY February february II
1353 SATURDAY Saturday saturday MARCH March march III
1354 TUESDAY Tuesday tuesday DECEMBER December december XII
1355 WEDNESDAY Wednesday wednesday DECEMBER December december XII
1356 FRIDAY Friday friday DECEMBER December december XII
1357 SATURDAY Saturday saturday JANUARY January january I
1358 SUNDAY Sunday sunday DECEMBER December december XII
1359 MONDAY Monday monday JANUARY January january I
1360 (65 rows)
1362 SELECT to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
1363 FROM TIMESTAMP_TBL;
1364 to_char
1365 --------------------------------------------------
1368 1,970 1970 970 70 0 20 1 01 01 001 01 5 2440588
1369 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1370 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1371 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1372 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1373 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1374 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1375 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1376 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
1377 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1378 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1379 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1380 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1381 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1382 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
1383 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1384 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1385 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1386 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1387 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
1388 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1389 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1390 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
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 1 02 06 041 10 2 2450490
1396 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1397 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1398 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1399 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1400 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
1401 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
1402 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
1403 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
1404 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
1405 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
1406 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
1407 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1408 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
1409 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
1410 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
1411 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
1412 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
1413 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
1414 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
1415 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
1416 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
1417 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
1418 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
1419 1,996 1996 996 96 6 20 1 03 09 061 01 6 2450144
1420 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1421 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1422 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
1423 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
1424 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
1425 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1426 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1427 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1428 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
1429 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
1430 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
1431 (65 rows)
1433 SELECT to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
1434 FROM TIMESTAMP_TBL;
1435 to_char
1436 -------------------------------------------------
1439 1,970 1970 970 70 0 20 1 1 1 1 1 5 2440588
1440 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1441 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1442 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1443 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1444 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1445 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1446 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1447 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
1448 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1449 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1450 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1451 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1452 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1453 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
1454 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1455 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1456 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1457 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1458 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
1459 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1460 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1461 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
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 1 2 6 41 10 2 2450490
1467 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1468 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1469 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1470 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1471 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
1472 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
1473 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
1474 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
1475 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
1476 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
1477 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
1478 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1479 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
1480 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
1481 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
1482 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
1483 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
1484 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
1485 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
1486 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
1487 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
1488 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
1489 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
1490 1,996 1996 996 96 6 20 1 3 9 61 1 6 2450144
1491 1,996 1996 996 96 6 20 4 12 53 365 30 2 2450448
1492 1,996 1996 996 96 6 20 4 12 53 366 31 3 2450449
1493 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
1494 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
1495 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
1496 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
1497 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
1498 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
1499 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
1500 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
1501 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
1502 (65 rows)
1504 SELECT to_char(d1, 'HH HH12 HH24 MI SS SSSS')
1505 FROM TIMESTAMP_TBL;
1506 to_char
1507 ----------------------
1510 12 12 00 00 00 0
1511 05 05 17 32 01 63121
1512 05 05 17 32 01 63121
1513 05 05 17 32 02 63122
1514 05 05 17 32 01 63121
1515 05 05 17 32 01 63121
1516 05 05 17 32 01 63121
1517 12 12 00 00 00 0
1518 03 03 03 04 05 11045
1519 05 05 17 32 01 63121
1520 05 05 17 32 01 63121
1521 05 05 17 32 01 63121
1522 05 05 17 32 01 63121
1523 05 05 17 32 01 63121
1524 06 06 18 19 20 65960
1525 08 08 08 14 01 29641
1526 01 01 13 14 02 47642
1527 12 12 12 14 03 44043
1528 03 03 03 14 04 11644
1529 02 02 02 14 05 8045
1530 05 05 17 32 01 63121
1531 05 05 17 32 01 63121
1532 05 05 17 32 00 63120
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 05 05 17 32 01 63121
1539 05 05 17 32 01 63121
1540 05 05 17 32 01 63121
1541 05 05 17 32 01 63121
1542 06 06 18 32 01 66721
1543 05 05 17 32 01 63121
1544 05 05 17 32 01 63121
1545 05 05 17 32 01 63121
1546 05 05 17 32 01 63121
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 05 05 17 32 01 63121
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 (65 rows)
1575 SELECT to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
1576 FROM TIMESTAMP_TBL;
1577 to_char
1578 -------------------------------------------------
1581 HH:MI:SS is 12:00:00 "text between quote marks"
1582 HH:MI:SS is 05:32:01 "text between quote marks"
1583 HH:MI:SS is 05:32:01 "text between quote marks"
1584 HH:MI:SS is 05:32:02 "text between quote marks"
1585 HH:MI:SS is 05:32:01 "text between quote marks"
1586 HH:MI:SS is 05:32:01 "text between quote marks"
1587 HH:MI:SS is 05:32:01 "text between quote marks"
1588 HH:MI:SS is 12:00:00 "text between quote marks"
1589 HH:MI:SS is 03:04:05 "text between quote marks"
1590 HH:MI:SS is 05:32:01 "text between quote marks"
1591 HH:MI:SS is 05:32:01 "text between quote marks"
1592 HH:MI:SS is 05:32:01 "text between quote marks"
1593 HH:MI:SS is 05:32:01 "text between quote marks"
1594 HH:MI:SS is 05:32:01 "text between quote marks"
1595 HH:MI:SS is 06:19:20 "text between quote marks"
1596 HH:MI:SS is 08:14:01 "text between quote marks"
1597 HH:MI:SS is 01:14:02 "text between quote marks"
1598 HH:MI:SS is 12:14:03 "text between quote marks"
1599 HH:MI:SS is 03:14:04 "text between quote marks"
1600 HH:MI:SS is 02:14:05 "text between quote marks"
1601 HH:MI:SS is 05:32:01 "text between quote marks"
1602 HH:MI:SS is 05:32:01 "text between quote marks"
1603 HH:MI:SS is 05:32:00 "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 05:32:01 "text between quote marks"
1610 HH:MI:SS is 05:32:01 "text between quote marks"
1611 HH:MI:SS is 05:32:01 "text between quote marks"
1612 HH:MI:SS is 05:32:01 "text between quote marks"
1613 HH:MI:SS is 06:32:01 "text between quote marks"
1614 HH:MI:SS is 05:32:01 "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:01 "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 05: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 (65 rows)
1646 SELECT to_char(d1, 'HH24--text--MI--text--SS')
1647 FROM TIMESTAMP_TBL;
1648 to_char
1649 ------------------------
1652 00--text--00--text--00
1653 17--text--32--text--01
1654 17--text--32--text--01
1655 17--text--32--text--02
1656 17--text--32--text--01
1657 17--text--32--text--01
1658 17--text--32--text--01
1659 00--text--00--text--00
1660 03--text--04--text--05
1661 17--text--32--text--01
1662 17--text--32--text--01
1663 17--text--32--text--01
1664 17--text--32--text--01
1665 17--text--32--text--01
1666 18--text--19--text--20
1667 08--text--14--text--01
1668 13--text--14--text--02
1669 12--text--14--text--03
1670 03--text--14--text--04
1671 02--text--14--text--05
1672 17--text--32--text--01
1673 17--text--32--text--01
1674 17--text--32--text--00
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 17--text--32--text--01
1681 17--text--32--text--01
1682 17--text--32--text--01
1683 17--text--32--text--01
1684 18--text--32--text--01
1685 17--text--32--text--01
1686 17--text--32--text--01
1687 17--text--32--text--01
1688 17--text--32--text--01
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 17--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 (65 rows)
1717 SELECT to_char(d1, 'YYYYTH YYYYth Jth')
1718 FROM TIMESTAMP_TBL;
1719 to_char
1720 -------------------------
1723 1970TH 1970th 2440588th
1724 1997TH 1997th 2450490th
1725 1997TH 1997th 2450490th
1726 1997TH 1997th 2450490th
1727 1997TH 1997th 2450490th
1728 1997TH 1997th 2450490th
1729 1997TH 1997th 2450490th
1730 1997TH 1997th 2450451st
1731 1997TH 1997th 2450451st
1732 1997TH 1997th 2450490th
1733 1997TH 1997th 2450490th
1734 1997TH 1997th 2450490th
1735 1997TH 1997th 2450490th
1736 1997TH 1997th 2450610th
1737 2001ST 2001st 2452175th
1738 2000TH 2000th 2451619th
1739 2000TH 2000th 2451619th
1740 2000TH 2000th 2451619th
1741 2000TH 2000th 2451619th
1742 2000TH 2000th 2451619th
1743 1997TH 1997th 2450490th
1744 1997TH 1997th 2450490th
1745 1997TH 1997th 2450490th
1746 1997TH 1997th 2450490th
1747 1997TH 1997th 2450490th
1748 1997TH 1997th 2450490th
1749 1997TH 1997th 2450490th
1750 1997TH 1997th 2450490th
1751 1997TH 1997th 2450490th
1752 1997TH 1997th 2450490th
1753 1997TH 1997th 2450490th
1754 1997TH 1997th 2450490th
1755 1997TH 1997th 2450610th
1756 1997TH 1997th 2450490th
1757 1997TH 1997th 2450491st
1758 1997TH 1997th 2450492nd
1759 1997TH 1997th 2450493rd
1760 1997TH 1997th 2450494th
1761 1997TH 1997th 2450495th
1762 1997TH 1997th 2450496th
1763 0097TH 0097th 1686042nd
1764 0097TH 0097th 1756536th
1765 0597TH 0597th 1939157th
1766 1097TH 1097th 2121778th
1767 1697TH 1697th 2340924th
1768 1797TH 1797th 2377448th
1769 1897TH 1897th 2413972nd
1770 1997TH 1997th 2450496th
1771 2097TH 2097th 2487021st
1772 1996TH 1996th 2450142nd
1773 1996TH 1996th 2450143rd
1774 1996TH 1996th 2450144th
1775 1996TH 1996th 2450448th
1776 1996TH 1996th 2450449th
1777 1997TH 1997th 2450450th
1778 1997TH 1997th 2450508th
1779 1997TH 1997th 2450509th
1780 1997TH 1997th 2450813th
1781 1997TH 1997th 2450814th
1782 1999TH 1999th 2451544th
1783 2000TH 2000th 2451545th
1784 2000TH 2000th 2451910th
1785 2001ST 2001st 2451911th
1786 (65 rows)
1788 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')
1789 FROM TIMESTAMP_TBL;
1790 to_char
1791 ---------------------------------------------------------------------
1794 1970 A.D. 1970 a.d. 1970 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1795 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1796 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1797 1997 A.D. 1997 a.d. 1997 ad 05:32:02 P.M. 05:32:02 p.m. 05:32:02 pm
1798 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1799 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1800 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1801 1997 A.D. 1997 a.d. 1997 ad 12:00:00 A.M. 12:00:00 a.m. 12:00:00 am
1802 1997 A.D. 1997 a.d. 1997 ad 03:04:05 A.M. 03:04:05 a.m. 03:04:05 am
1803 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1804 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1805 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1806 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1807 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1808 2001 A.D. 2001 a.d. 2001 ad 06:19:20 P.M. 06:19:20 p.m. 06:19:20 pm
1809 2000 A.D. 2000 a.d. 2000 ad 08:14:01 A.M. 08:14:01 a.m. 08:14:01 am
1810 2000 A.D. 2000 a.d. 2000 ad 01:14:02 P.M. 01:14:02 p.m. 01:14:02 pm
1811 2000 A.D. 2000 a.d. 2000 ad 12:14:03 P.M. 12:14:03 p.m. 12:14:03 pm
1812 2000 A.D. 2000 a.d. 2000 ad 03:14:04 A.M. 03:14:04 a.m. 03:14:04 am
1813 2000 A.D. 2000 a.d. 2000 ad 02:14:05 A.M. 02:14:05 a.m. 02:14:05 am
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 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1816 1997 A.D. 1997 a.d. 1997 ad 05:32:00 P.M. 05:32:00 p.m. 05:32:00 pm
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 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1823 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1824 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1825 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1826 1997 A.D. 1997 a.d. 1997 ad 06:32:01 P.M. 06:32:01 p.m. 06:32:01 pm
1827 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
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:01 P.M. 05:32:01 p.m. 05:32:01 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 0097 B.C. 0097 b.c. 0097 bc 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1835 0097 A.D. 0097 a.d. 0097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1836 0597 A.D. 0597 a.d. 0597 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1837 1097 A.D. 1097 a.d. 1097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1838 1697 A.D. 1697 a.d. 1697 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1839 1797 A.D. 1797 a.d. 1797 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1840 1897 A.D. 1897 a.d. 1897 ad 05:32:01 P.M. 05:32:01 p.m. 05: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 2097 A.D. 2097 a.d. 2097 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1843 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1844 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1845 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1846 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1847 1996 A.D. 1996 a.d. 1996 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1848 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1849 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1850 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1851 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1852 1997 A.D. 1997 a.d. 1997 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1853 1999 A.D. 1999 a.d. 1999 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1854 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1855 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1856 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
1857 (65 rows)
1859 SELECT to_char(d1, 'IYYY IYY IY I IW IDDD ID')
1860 FROM TIMESTAMP_TBL;
1861 to_char
1862 ------------------------
1865 1970 970 70 0 01 004 4
1866 1997 997 97 7 07 043 1
1867 1997 997 97 7 07 043 1
1868 1997 997 97 7 07 043 1
1869 1997 997 97 7 07 043 1
1870 1997 997 97 7 07 043 1
1871 1997 997 97 7 07 043 1
1872 1997 997 97 7 01 004 4
1873 1997 997 97 7 01 004 4
1874 1997 997 97 7 07 043 1
1875 1997 997 97 7 07 043 1
1876 1997 997 97 7 07 043 1
1877 1997 997 97 7 07 043 1
1878 1997 997 97 7 24 163 2
1879 2001 001 01 1 38 265 6
1880 2000 000 00 0 11 073 3
1881 2000 000 00 0 11 073 3
1882 2000 000 00 0 11 073 3
1883 2000 000 00 0 11 073 3
1884 2000 000 00 0 11 073 3
1885 1997 997 97 7 07 043 1
1886 1997 997 97 7 07 043 1
1887 1997 997 97 7 07 043 1
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 07 043 1
1893 1997 997 97 7 07 043 1
1894 1997 997 97 7 07 043 1
1895 1997 997 97 7 07 043 1
1896 1997 997 97 7 07 043 1
1897 1997 997 97 7 24 163 2
1898 1997 997 97 7 07 043 1
1899 1997 997 97 7 07 044 2
1900 1997 997 97 7 07 045 3
1901 1997 997 97 7 07 046 4
1902 1997 997 97 7 07 047 5
1903 1997 997 97 7 07 048 6
1904 1997 997 97 7 07 049 7
1905 0097 097 97 7 07 044 2
1906 0097 097 97 7 07 048 6
1907 0597 597 97 7 07 046 4
1908 1097 097 97 7 07 044 2
1909 1697 697 97 7 07 048 6
1910 1797 797 97 7 07 046 4
1911 1897 897 97 7 07 044 2
1912 1997 997 97 7 07 049 7
1913 2097 097 97 7 07 048 6
1914 1996 996 96 6 09 059 3
1915 1996 996 96 6 09 060 4
1916 1996 996 96 6 09 061 5
1917 1997 997 97 7 01 001 1
1918 1997 997 97 7 01 002 2
1919 1997 997 97 7 01 003 3
1920 1997 997 97 7 09 061 5
1921 1997 997 97 7 09 062 6
1922 1998 998 98 8 01 002 2
1923 1998 998 98 8 01 003 3
1924 1999 999 99 9 52 362 5
1925 1999 999 99 9 52 363 6
1926 2000 000 00 0 52 364 7
1927 2001 001 01 1 01 001 1
1928 (65 rows)
1930 SELECT to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
1931 FROM TIMESTAMP_TBL;
1932 to_char
1933 ------------------------
1936 1970 970 70 0 1 4 4
1937 1997 997 97 7 7 43 1
1938 1997 997 97 7 7 43 1
1939 1997 997 97 7 7 43 1
1940 1997 997 97 7 7 43 1
1941 1997 997 97 7 7 43 1
1942 1997 997 97 7 7 43 1
1943 1997 997 97 7 1 4 4
1944 1997 997 97 7 1 4 4
1945 1997 997 97 7 7 43 1
1946 1997 997 97 7 7 43 1
1947 1997 997 97 7 7 43 1
1948 1997 997 97 7 7 43 1
1949 1997 997 97 7 24 163 2
1950 2001 1 1 1 38 265 6
1951 2000 0 0 0 11 73 3
1952 2000 0 0 0 11 73 3
1953 2000 0 0 0 11 73 3
1954 2000 0 0 0 11 73 3
1955 2000 0 0 0 11 73 3
1956 1997 997 97 7 7 43 1
1957 1997 997 97 7 7 43 1
1958 1997 997 97 7 7 43 1
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 7 43 1
1964 1997 997 97 7 7 43 1
1965 1997 997 97 7 7 43 1
1966 1997 997 97 7 7 43 1
1967 1997 997 97 7 7 43 1
1968 1997 997 97 7 24 163 2
1969 1997 997 97 7 7 43 1
1970 1997 997 97 7 7 44 2
1971 1997 997 97 7 7 45 3
1972 1997 997 97 7 7 46 4
1973 1997 997 97 7 7 47 5
1974 1997 997 97 7 7 48 6
1975 1997 997 97 7 7 49 7
1976 97 97 97 7 7 44 2
1977 97 97 97 7 7 48 6
1978 597 597 97 7 7 46 4
1979 1097 97 97 7 7 44 2
1980 1697 697 97 7 7 48 6
1981 1797 797 97 7 7 46 4
1982 1897 897 97 7 7 44 2
1983 1997 997 97 7 7 49 7
1984 2097 97 97 7 7 48 6
1985 1996 996 96 6 9 59 3
1986 1996 996 96 6 9 60 4
1987 1996 996 96 6 9 61 5
1988 1997 997 97 7 1 1 1
1989 1997 997 97 7 1 2 2
1990 1997 997 97 7 1 3 3
1991 1997 997 97 7 9 61 5
1992 1997 997 97 7 9 62 6
1993 1998 998 98 8 1 2 2
1994 1998 998 98 8 1 3 3
1995 1999 999 99 9 52 362 5
1996 1999 999 99 9 52 363 6
1997 2000 0 0 0 52 364 7
1998 2001 1 1 1 1 1 1
1999 (65 rows)
2001 SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
2002 FROM (VALUES
2003 ('2018-11-02 12:34:56'::timestamp),
2004 ('2018-11-02 12:34:56.78'),
2005 ('2018-11-02 12:34:56.78901'),
2006 ('2018-11-02 12:34:56.78901234')
2007 ) d(d);
2008 to_char
2009 --------------------------------------------------------------------
2010 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000
2011 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000
2012 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010
2013 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012
2014 (4 rows)
2016 -- Roman months, with upper and lower case.
2017 SELECT i,
2018 to_char(i * interval '1mon', 'rm'),
2019 to_char(i * interval '1mon', 'RM')
2020 FROM generate_series(-13, 13) i;
2021 i | to_char | to_char
2022 -----+---------+---------
2023 -13 | xii | XII
2024 -12 | i | I
2025 -11 | ii | II
2026 -10 | iii | III
2027 -9 | iv | IV
2028 -8 | v | V
2029 -7 | vi | VI
2030 -6 | vii | VII
2031 -5 | viii | VIII
2032 -4 | ix | IX
2033 -3 | x | X
2034 -2 | xi | XI
2035 -1 | xii | XII
2036 0 | |
2037 1 | i | I
2038 2 | ii | II
2039 3 | iii | III
2040 4 | iv | IV
2041 5 | v | V
2042 6 | vi | VI
2043 7 | vii | VII
2044 8 | viii | VIII
2045 9 | ix | IX
2046 10 | x | X
2047 11 | xi | XI
2048 12 | xii | XII
2049 13 | i | I
2050 (27 rows)
2052 -- timestamp numeric fields constructor
2053 SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
2054 make_timestamp
2055 ------------------------------
2056 Sun Dec 28 06:30:45.887 2014
2057 (1 row)
2059 SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
2060 make_timestamp
2061 -----------------------------
2062 Fri Mar 15 12:30:15 0044 BC
2063 (1 row)
2065 -- should fail
2066 select make_timestamp(0, 7, 15, 12, 30, 15);
2067 ERROR: date field value out of range: 0-07-15
2068 -- generate_series for timestamp
2069 select * from generate_series('2020-01-01 00:00'::timestamp,
2070 '2020-01-02 03:00'::timestamp,
2071 '1 hour'::interval);
2072 generate_series
2073 --------------------------
2074 Wed Jan 01 00:00:00 2020
2075 Wed Jan 01 01:00:00 2020
2076 Wed Jan 01 02:00:00 2020
2077 Wed Jan 01 03:00:00 2020
2078 Wed Jan 01 04:00:00 2020
2079 Wed Jan 01 05:00:00 2020
2080 Wed Jan 01 06:00:00 2020
2081 Wed Jan 01 07:00:00 2020
2082 Wed Jan 01 08:00:00 2020
2083 Wed Jan 01 09:00:00 2020
2084 Wed Jan 01 10:00:00 2020
2085 Wed Jan 01 11:00:00 2020
2086 Wed Jan 01 12:00:00 2020
2087 Wed Jan 01 13:00:00 2020
2088 Wed Jan 01 14:00:00 2020
2089 Wed Jan 01 15:00:00 2020
2090 Wed Jan 01 16:00:00 2020
2091 Wed Jan 01 17:00:00 2020
2092 Wed Jan 01 18:00:00 2020
2093 Wed Jan 01 19:00:00 2020
2094 Wed Jan 01 20:00:00 2020
2095 Wed Jan 01 21:00:00 2020
2096 Wed Jan 01 22:00:00 2020
2097 Wed Jan 01 23:00:00 2020
2098 Thu Jan 02 00:00:00 2020
2099 Thu Jan 02 01:00:00 2020
2100 Thu Jan 02 02:00:00 2020
2101 Thu Jan 02 03:00:00 2020
2102 (28 rows)
2104 -- the LIMIT should allow this to terminate in a reasonable amount of time
2105 -- (but that unfortunately doesn't work yet for SELECT * FROM ...)
2106 select generate_series('2022-01-01 00:00'::timestamp,
2107 'infinity'::timestamp,
2108 '1 month'::interval) limit 10;
2109 generate_series
2110 --------------------------
2111 Sat Jan 01 00:00:00 2022
2112 Tue Feb 01 00:00:00 2022
2113 Tue Mar 01 00:00:00 2022
2114 Fri Apr 01 00:00:00 2022
2115 Sun May 01 00:00:00 2022
2116 Wed Jun 01 00:00:00 2022
2117 Fri Jul 01 00:00:00 2022
2118 Mon Aug 01 00:00:00 2022
2119 Thu Sep 01 00:00:00 2022
2120 Sat Oct 01 00:00:00 2022
2121 (10 rows)
2123 -- errors
2124 select * from generate_series('2020-01-01 00:00'::timestamp,
2125 '2020-01-02 03:00'::timestamp,
2126 '0 hour'::interval);
2127 ERROR: step size cannot equal zero