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