| blob | e4b1246f4534977fefa1ff3e26ba14aebeaab44c |
1 --
2 -- INTERVAL
3 --
4 SET DATESTYLE = 'ISO';
5 SET IntervalStyle to postgres;
6 -- check acceptance of "time zone style"
7 SELECT INTERVAL '01:00' AS "One hour";
8 One hour
9 ----------
10 01:00:00
11 (1 row)
13 SELECT INTERVAL '+02:00' AS "Two hours";
14 Two hours
15 -----------
16 02:00:00
17 (1 row)
19 SELECT INTERVAL '-08:00' AS "Eight hours";
20 Eight hours
21 -------------
22 -08:00:00
23 (1 row)
25 SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
26 22 hours ago...
27 -------------------
28 -1 days +02:03:00
29 (1 row)
31 SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
32 22 hours ago...
33 -------------------
34 -1 days +02:03:00
35 (1 row)
37 SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
38 Ten days twelve hours
39 -----------------------
40 10 days 12:00:00
41 (1 row)
43 SELECT INTERVAL '1.5 months' AS "One month 15 days";
44 One month 15 days
45 -------------------
46 1 mon 15 days
47 (1 row)
49 SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
50 9 years...
51 ----------------------------------
52 9 years 1 mon -12 days +13:14:00
53 (1 row)
55 CREATE TABLE INTERVAL_TBL (f1 interval);
56 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
57 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
58 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 10 day');
59 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 34 year');
60 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 3 months');
61 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 14 seconds ago');
62 INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
63 INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
64 INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
65 INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
66 -- badly formatted interval
67 INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
68 ERROR: invalid input syntax for type interval: "badly formatted interval"
69 LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted inter...
70 ^
71 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
72 ERROR: invalid input syntax for type interval: "@ 30 eons ago"
73 LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
74 ^
75 -- test interval operators
76 SELECT * FROM INTERVAL_TBL;
77 f1
78 -----------------
79 00:01:00
80 05:00:00
81 10 days
82 34 years
83 3 mons
84 -00:00:14
85 1 day 02:03:04
86 6 years
87 5 mons
88 5 mons 12:00:00
89 (10 rows)
91 SELECT * FROM INTERVAL_TBL
92 WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
93 f1
94 -----------------
95 00:01:00
96 05:00:00
97 34 years
98 3 mons
99 -00:00:14
100 1 day 02:03:04
101 6 years
102 5 mons
103 5 mons 12:00:00
104 (9 rows)
106 SELECT * FROM INTERVAL_TBL
107 WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
108 f1
109 -----------
110 00:01:00
111 05:00:00
112 -00:00:14
113 (3 rows)
115 SELECT * FROM INTERVAL_TBL
116 WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
117 f1
118 -----------
119 00:01:00
120 05:00:00
121 -00:00:14
122 (3 rows)
124 SELECT * FROM INTERVAL_TBL
125 WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
126 f1
127 ----------
128 34 years
129 (1 row)
131 SELECT * FROM INTERVAL_TBL
132 WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
133 f1
134 -----------------
135 34 years
136 3 mons
137 6 years
138 5 mons
139 5 mons 12:00:00
140 (5 rows)
142 SELECT * FROM INTERVAL_TBL
143 WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
144 f1
145 -----------------
146 00:01:00
147 05:00:00
148 10 days
149 34 years
150 3 mons
151 1 day 02:03:04
152 6 years
153 5 mons
154 5 mons 12:00:00
155 (9 rows)
157 SELECT r1.*, r2.*
158 FROM INTERVAL_TBL r1, INTERVAL_TBL r2
159 WHERE r1.f1 > r2.f1
160 ORDER BY r1.f1, r2.f1;
161 f1 | f1
162 -----------------+-----------------
163 00:01:00 | -00:00:14
164 05:00:00 | -00:00:14
165 05:00:00 | 00:01:00
166 1 day 02:03:04 | -00:00:14
167 1 day 02:03:04 | 00:01:00
168 1 day 02:03:04 | 05:00:00
169 10 days | -00:00:14
170 10 days | 00:01:00
171 10 days | 05:00:00
172 10 days | 1 day 02:03:04
173 3 mons | -00:00:14
174 3 mons | 00:01:00
175 3 mons | 05:00:00
176 3 mons | 1 day 02:03:04
177 3 mons | 10 days
178 5 mons | -00:00:14
179 5 mons | 00:01:00
180 5 mons | 05:00:00
181 5 mons | 1 day 02:03:04
182 5 mons | 10 days
183 5 mons | 3 mons
184 5 mons 12:00:00 | -00:00:14
185 5 mons 12:00:00 | 00:01:00
186 5 mons 12:00:00 | 05:00:00
187 5 mons 12:00:00 | 1 day 02:03:04
188 5 mons 12:00:00 | 10 days
189 5 mons 12:00:00 | 3 mons
190 5 mons 12:00:00 | 5 mons
191 6 years | -00:00:14
192 6 years | 00:01:00
193 6 years | 05:00:00
194 6 years | 1 day 02:03:04
195 6 years | 10 days
196 6 years | 3 mons
197 6 years | 5 mons
198 6 years | 5 mons 12:00:00
199 34 years | -00:00:14
200 34 years | 00:01:00
201 34 years | 05:00:00
202 34 years | 1 day 02:03:04
203 34 years | 10 days
204 34 years | 3 mons
205 34 years | 5 mons
206 34 years | 5 mons 12:00:00
207 34 years | 6 years
208 (45 rows)
210 -- Test intervals that are large enough to overflow 64 bits in comparisons
211 CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
212 INSERT INTO INTERVAL_TBL_OF (f1) VALUES
213 ('2147483647 days 2147483647 months'),
214 ('2147483647 days -2147483648 months'),
215 ('1 year'),
216 ('-2147483648 days 2147483647 months'),
217 ('-2147483648 days -2147483648 months');
218 -- these should fail as out-of-range
219 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
220 ERROR: interval field value out of range: "2147483648 days"
221 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
222 ^
223 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days');
224 ERROR: interval field value out of range: "-2147483649 days"
225 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days')...
226 ^
227 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years');
228 ERROR: interval out of range
229 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years')...
230 ^
231 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years');
232 ERROR: interval out of range
233 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'...
234 ^
235 -- Test edge-case overflow detection in interval multiplication
236 select extract(epoch from '256 microseconds'::interval * (2^55)::float8);
237 ERROR: interval out of range
238 SELECT r1.*, r2.*
239 FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2
240 WHERE r1.f1 > r2.f1
241 ORDER BY r1.f1, r2.f1;
242 f1 | f1
243 -------------------------------------------+-------------------------------------------
244 -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days
245 1 year | -178956970 years -8 mons -2147483648 days
246 1 year | -178956970 years -8 mons +2147483647 days
247 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons -2147483648 days
248 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons +2147483647 days
249 178956970 years 7 mons -2147483648 days | 1 year
250 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons -2147483648 days
251 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons +2147483647 days
252 178956970 years 7 mons 2147483647 days | 1 year
253 178956970 years 7 mons 2147483647 days | 178956970 years 7 mons -2147483648 days
254 (10 rows)
256 CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1);
257 SET enable_seqscan TO false;
258 EXPLAIN (COSTS OFF)
259 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
260 QUERY PLAN
261 --------------------------------------------------------------------
262 Index Only Scan using interval_tbl_of_f1_idx on interval_tbl_of r1
263 (1 row)
265 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
266 f1
267 -------------------------------------------
268 -178956970 years -8 mons -2147483648 days
269 -178956970 years -8 mons +2147483647 days
270 1 year
271 178956970 years 7 mons -2147483648 days
272 178956970 years 7 mons 2147483647 days
273 (5 rows)
275 RESET enable_seqscan;
276 DROP TABLE INTERVAL_TBL_OF;
277 -- Test multiplication and division with intervals.
278 -- Floating point arithmetic rounding errors can lead to unexpected results,
279 -- though the code attempts to do the right thing and round up to days and
280 -- minutes to avoid results such as '3 days 24:00 hours' or '14:20:60'.
281 -- Note that it is expected for some day components to be greater than 29 and
282 -- some time components be greater than 23:59:59 due to how intervals are
283 -- stored internally.
284 CREATE TABLE INTERVAL_MULDIV_TBL (span interval);
285 COPY INTERVAL_MULDIV_TBL FROM STDIN;
286 SELECT span * 0.3 AS product
287 FROM INTERVAL_MULDIV_TBL;
288 product
289 ------------------------------------
290 1 year 12 days 122:24:00
291 -1 years -12 days +93:36:00
292 -3 days -14:24:00
293 2 mons 13 days 01:22:28.8
294 -10 mons +120 days 37:28:21.6567
295 1 mon 6 days
296 4 mons 6 days
297 24 years 11 mons 320 days 16:48:00
298 (8 rows)
300 SELECT span * 8.2 AS product
301 FROM INTERVAL_MULDIV_TBL;
302 product
303 ---------------------------------------------
304 28 years 104 days 2961:36:00
305 -28 years -104 days +2942:24:00
306 -98 days -09:36:00
307 6 years 1 mon -197 days +93:34:27.2
308 -24 years -7 mons +3946 days 640:15:11.9498
309 2 years 8 mons 24 days
310 9 years 6 mons 24 days
311 682 years 7 mons 8215 days 19:12:00
312 (8 rows)
314 SELECT span / 10 AS quotient
315 FROM INTERVAL_MULDIV_TBL;
316 quotient
317 ----------------------------------
318 4 mons 4 days 40:48:00
319 -4 mons -4 days +31:12:00
320 -1 days -04:48:00
321 25 days -15:32:30.4
322 -3 mons +30 days 12:29:27.2189
323 12 days
324 1 mon 12 days
325 8 years 3 mons 126 days 21:36:00
326 (8 rows)
328 SELECT span / 100 AS quotient
329 FROM INTERVAL_MULDIV_TBL;
330 quotient
331 -------------------------
332 12 days 13:40:48
333 -12 days -06:28:48
334 -02:52:48
335 2 days 10:26:44.96
336 -6 days +01:14:56.72189
337 1 day 04:48:00
338 4 days 04:48:00
339 9 mons 39 days 16:33:36
340 (8 rows)
342 DROP TABLE INTERVAL_MULDIV_TBL;
343 SET DATESTYLE = 'postgres';
344 SET IntervalStyle to postgres_verbose;
345 SELECT * FROM INTERVAL_TBL;
346 f1
347 -------------------------------
348 @ 1 min
349 @ 5 hours
350 @ 10 days
351 @ 34 years
352 @ 3 mons
353 @ 14 secs ago
354 @ 1 day 2 hours 3 mins 4 secs
355 @ 6 years
356 @ 5 mons
357 @ 5 mons 12 hours
358 (10 rows)
360 -- test avg(interval), which is somewhat fragile since people have been
361 -- known to change the allowed input syntax for type interval without
362 -- updating pg_aggregate.agginitval
363 select avg(f1) from interval_tbl;
364 avg
365 -------------------------------------------------
366 @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
367 (1 row)
369 -- test long interval input
370 select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
371 interval
372 --------------------------------------------
373 @ 4541 years 4 mons 4 days 17 mins 31 secs
374 (1 row)
376 -- test long interval output
377 -- Note: the actual maximum length of the interval output is longer,
378 -- but we need the test to work for both integer and floating-point
379 -- timestamps.
380 select '100000000y 10mon -1000000000d -100000h -10min -10.000001s ago'::interval;
381 interval
382 ---------------------------------------------------------------------------------------
383 @ 100000000 years 10 mons -1000000000 days -100000 hours -10 mins -10.000001 secs ago
384 (1 row)
386 -- test justify_hours() and justify_days()
387 SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as "6 mons 5 days 4 hours 3 mins 2 seconds";
388 6 mons 5 days 4 hours 3 mins 2 seconds
389 ----------------------------------------
390 @ 6 mons 5 days 4 hours 3 mins 2 secs
391 (1 row)
393 SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds";
394 7 mons 6 days 5 hours 4 mins 3 seconds
395 ----------------------------------------
396 @ 7 mons 6 days 5 hours 4 mins 3 secs
397 (1 row)
399 -- test justify_interval()
400 SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
401 1 month -1 hour
402 --------------------
403 @ 29 days 23 hours
404 (1 row)
406 -- test fractional second input, and detection of duplicate units
407 SET DATESTYLE = 'ISO';
408 SET IntervalStyle TO postgres;
409 SELECT '1 millisecond'::interval, '1 microsecond'::interval,
410 '500 seconds 99 milliseconds 51 microseconds'::interval;
411 interval | interval | interval
412 --------------+-----------------+-----------------
413 00:00:00.001 | 00:00:00.000001 | 00:08:20.099051
414 (1 row)
416 SELECT '3 days 5 milliseconds'::interval;
417 interval
418 ---------------------
419 3 days 00:00:00.005
420 (1 row)
422 SELECT '1 second 2 seconds'::interval; -- error
423 ERROR: invalid input syntax for type interval: "1 second 2 seconds"
424 LINE 1: SELECT '1 second 2 seconds'::interval;
425 ^
426 SELECT '10 milliseconds 20 milliseconds'::interval; -- error
427 ERROR: invalid input syntax for type interval: "10 milliseconds 20 milliseconds"
428 LINE 1: SELECT '10 milliseconds 20 milliseconds'::interval;
429 ^
430 SELECT '5.5 seconds 3 milliseconds'::interval; -- error
431 ERROR: invalid input syntax for type interval: "5.5 seconds 3 milliseconds"
432 LINE 1: SELECT '5.5 seconds 3 milliseconds'::interval;
433 ^
434 SELECT '1:20:05 5 microseconds'::interval; -- error
435 ERROR: invalid input syntax for type interval: "1:20:05 5 microseconds"
436 LINE 1: SELECT '1:20:05 5 microseconds'::interval;
437 ^
438 SELECT '1 day 1 day'::interval; -- error
439 ERROR: invalid input syntax for type interval: "1 day 1 day"
440 LINE 1: SELECT '1 day 1 day'::interval;
441 ^
442 SELECT interval '1-2'; -- SQL year-month literal
443 interval
444 ---------------
445 1 year 2 mons
446 (1 row)
448 SELECT interval '999' second; -- oversize leading field is ok
449 interval
450 ----------
451 00:16:39
452 (1 row)
454 SELECT interval '999' minute;
455 interval
456 ----------
457 16:39:00
458 (1 row)
460 SELECT interval '999' hour;
461 interval
462 -----------
463 999:00:00
464 (1 row)
466 SELECT interval '999' day;
467 interval
468 ----------
469 999 days
470 (1 row)
472 SELECT interval '999' month;
473 interval
474 -----------------
475 83 years 3 mons
476 (1 row)
478 -- test SQL-spec syntaxes for restricted field sets
479 SELECT interval '1' year;
480 interval
481 ----------
482 1 year
483 (1 row)
485 SELECT interval '2' month;
486 interval
487 ----------
488 2 mons
489 (1 row)
491 SELECT interval '3' day;
492 interval
493 ----------
494 3 days
495 (1 row)
497 SELECT interval '4' hour;
498 interval
499 ----------
500 04:00:00
501 (1 row)
503 SELECT interval '5' minute;
504 interval
505 ----------
506 00:05:00
507 (1 row)
509 SELECT interval '6' second;
510 interval
511 ----------
512 00:00:06
513 (1 row)
515 SELECT interval '1' year to month;
516 interval
517 ----------
518 1 mon
519 (1 row)
521 SELECT interval '1-2' year to month;
522 interval
523 ---------------
524 1 year 2 mons
525 (1 row)
527 SELECT interval '1 2' day to hour;
528 interval
529 ----------------
530 1 day 02:00:00
531 (1 row)
533 SELECT interval '1 2:03' day to hour;
534 interval
535 ----------------
536 1 day 02:00:00
537 (1 row)
539 SELECT interval '1 2:03:04' day to hour;
540 interval
541 ----------------
542 1 day 02:00:00
543 (1 row)
545 SELECT interval '1 2' day to minute;
546 ERROR: invalid input syntax for type interval: "1 2"
547 LINE 1: SELECT interval '1 2' day to minute;
548 ^
549 SELECT interval '1 2:03' day to minute;
550 interval
551 ----------------
552 1 day 02:03:00
553 (1 row)
555 SELECT interval '1 2:03:04' day to minute;
556 interval
557 ----------------
558 1 day 02:03:00
559 (1 row)
561 SELECT interval '1 2' day to second;
562 ERROR: invalid input syntax for type interval: "1 2"
563 LINE 1: SELECT interval '1 2' day to second;
564 ^
565 SELECT interval '1 2:03' day to second;
566 interval
567 ----------------
568 1 day 02:03:00
569 (1 row)
571 SELECT interval '1 2:03:04' day to second;
572 interval
573 ----------------
574 1 day 02:03:04
575 (1 row)
577 SELECT interval '1 2' hour to minute;
578 ERROR: invalid input syntax for type interval: "1 2"
579 LINE 1: SELECT interval '1 2' hour to minute;
580 ^
581 SELECT interval '1 2:03' hour to minute;
582 interval
583 ----------------
584 1 day 02:03:00
585 (1 row)
587 SELECT interval '1 2:03:04' hour to minute;
588 interval
589 ----------------
590 1 day 02:03:00
591 (1 row)
593 SELECT interval '1 2' hour to second;
594 ERROR: invalid input syntax for type interval: "1 2"
595 LINE 1: SELECT interval '1 2' hour to second;
596 ^
597 SELECT interval '1 2:03' hour to second;
598 interval
599 ----------------
600 1 day 02:03:00
601 (1 row)
603 SELECT interval '1 2:03:04' hour to second;
604 interval
605 ----------------
606 1 day 02:03:04
607 (1 row)
609 SELECT interval '1 2' minute to second;
610 ERROR: invalid input syntax for type interval: "1 2"
611 LINE 1: SELECT interval '1 2' minute to second;
612 ^
613 SELECT interval '1 2:03' minute to second;
614 interval
615 ----------------
616 1 day 00:02:03
617 (1 row)
619 SELECT interval '1 2:03:04' minute to second;
620 interval
621 ----------------
622 1 day 02:03:04
623 (1 row)
625 SELECT interval '1 +2:03' minute to second;
626 interval
627 ----------------
628 1 day 00:02:03
629 (1 row)
631 SELECT interval '1 +2:03:04' minute to second;
632 interval
633 ----------------
634 1 day 02:03:04
635 (1 row)
637 SELECT interval '1 -2:03' minute to second;
638 interval
639 -----------------
640 1 day -00:02:03
641 (1 row)
643 SELECT interval '1 -2:03:04' minute to second;
644 interval
645 -----------------
646 1 day -02:03:04
647 (1 row)
649 SELECT interval '123 11' day to hour; -- ok
650 interval
651 -------------------
652 123 days 11:00:00
653 (1 row)
655 SELECT interval '123 11' day; -- not ok
656 ERROR: invalid input syntax for type interval: "123 11"
657 LINE 1: SELECT interval '123 11' day;
658 ^
659 SELECT interval '123 11'; -- not ok, too ambiguous
660 ERROR: invalid input syntax for type interval: "123 11"
661 LINE 1: SELECT interval '123 11';
662 ^
663 SELECT interval '123 2:03 -2:04'; -- not ok, redundant hh:mm fields
664 ERROR: invalid input syntax for type interval: "123 2:03 -2:04"
665 LINE 1: SELECT interval '123 2:03 -2:04';
666 ^
667 -- test syntaxes for restricted precision
668 SELECT interval(0) '1 day 01:23:45.6789';
669 interval
670 ----------------
671 1 day 01:23:46
672 (1 row)
674 SELECT interval(2) '1 day 01:23:45.6789';
675 interval
676 -------------------
677 1 day 01:23:45.68
678 (1 row)
680 SELECT interval '12:34.5678' minute to second(2); -- per SQL spec
681 interval
682 -------------
683 00:12:34.57
684 (1 row)
686 SELECT interval '1.234' second;
687 interval
688 --------------
689 00:00:01.234
690 (1 row)
692 SELECT interval '1.234' second(2);
693 interval
694 -------------
695 00:00:01.23
696 (1 row)
698 SELECT interval '1 2.345' day to second(2);
699 ERROR: invalid input syntax for type interval: "1 2.345"
700 LINE 1: SELECT interval '1 2.345' day to second(2);
701 ^
702 SELECT interval '1 2:03' day to second(2);
703 interval
704 ----------------
705 1 day 02:03:00
706 (1 row)
708 SELECT interval '1 2:03.4567' day to second(2);
709 interval
710 -------------------
711 1 day 00:02:03.46
712 (1 row)
714 SELECT interval '1 2:03:04.5678' day to second(2);
715 interval
716 -------------------
717 1 day 02:03:04.57
718 (1 row)
720 SELECT interval '1 2.345' hour to second(2);
721 ERROR: invalid input syntax for type interval: "1 2.345"
722 LINE 1: SELECT interval '1 2.345' hour to second(2);
723 ^
724 SELECT interval '1 2:03.45678' hour to second(2);
725 interval
726 -------------------
727 1 day 00:02:03.46
728 (1 row)
730 SELECT interval '1 2:03:04.5678' hour to second(2);
731 interval
732 -------------------
733 1 day 02:03:04.57
734 (1 row)
736 SELECT interval '1 2.3456' minute to second(2);
737 ERROR: invalid input syntax for type interval: "1 2.3456"
738 LINE 1: SELECT interval '1 2.3456' minute to second(2);
739 ^
740 SELECT interval '1 2:03.5678' minute to second(2);
741 interval
742 -------------------
743 1 day 00:02:03.57
744 (1 row)
746 SELECT interval '1 2:03:04.5678' minute to second(2);
747 interval
748 -------------------
749 1 day 02:03:04.57
750 (1 row)
752 -- test casting to restricted precision (bug #14479)
753 SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
754 (f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
755 FROM interval_tbl;
756 f1 | minutes | years
757 -----------------+-----------------+----------
758 00:01:00 | 00:01:00 | 00:00:00
759 05:00:00 | 05:00:00 | 00:00:00
760 10 days | 10 days | 00:00:00
761 34 years | 34 years | 34 years
762 3 mons | 3 mons | 00:00:00
763 -00:00:14 | 00:00:00 | 00:00:00
764 1 day 02:03:04 | 1 day 02:03:00 | 00:00:00
765 6 years | 6 years | 6 years
766 5 mons | 5 mons | 00:00:00
767 5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
768 (10 rows)
770 -- test inputting and outputting SQL standard interval literals
771 SET IntervalStyle TO sql_standard;
772 SELECT interval '0' AS "zero",
773 interval '1-2' year to month AS "year-month",
774 interval '1 2:03:04' day to second AS "day-time",
775 - interval '1-2' AS "negative year-month",
776 - interval '1 2:03:04' AS "negative day-time";
777 zero | year-month | day-time | negative year-month | negative day-time
778 ------+------------+-----------+---------------------+-------------------
779 0 | 1-2 | 1 2:03:04 | -1-2 | -1 2:03:04
780 (1 row)
782 -- test input of some not-quite-standard interval values in the sql style
783 SET IntervalStyle TO postgres;
784 SELECT interval '+1 -1:00:00',
785 interval '-1 +1:00:00',
786 interval '+1-2 -3 +4:05:06.789',
787 interval '-1-2 +3 -4:05:06.789';
788 interval | interval | interval | interval
789 -----------------+-------------------+-------------------------------------+----------------------------------------
790 1 day -01:00:00 | -1 days +01:00:00 | 1 year 2 mons -3 days +04:05:06.789 | -1 years -2 mons +3 days -04:05:06.789
791 (1 row)
793 -- test output of couple non-standard interval values in the sql style
794 SET IntervalStyle TO sql_standard;
795 SELECT interval '1 day -1 hours',
796 interval '-1 days +1 hours',
797 interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
798 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
799 interval | interval | interval | ?column?
800 ------------------+------------------+----------------------+----------------------
801 +0-0 +1 -1:00:00 | +0-0 -1 +1:00:00 | +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
802 (1 row)
804 -- test outputting iso8601 intervals
805 SET IntervalStyle to iso_8601;
806 select interval '0' AS "zero",
807 interval '1-2' AS "a year 2 months",
808 interval '1 2:03:04' AS "a bit over a day",
809 interval '2:03:04.45679' AS "a bit over 2 hours",
810 (interval '1-2' + interval '3 4:05:06.7') AS "all fields",
811 (interval '1-2' - interval '3 4:05:06.7') AS "mixed sign",
812 (- interval '1-2' + interval '3 4:05:06.7') AS "negative";
813 zero | a year 2 months | a bit over a day | a bit over 2 hours | all fields | mixed sign | negative
814 ------+-----------------+------------------+--------------------+------------------+----------------------+--------------------
815 PT0S | P1Y2M | P1DT2H3M4S | PT2H3M4.45679S | P1Y2M3DT4H5M6.7S | P1Y2M-3DT-4H-5M-6.7S | P-1Y-2M3DT4H5M6.7S
816 (1 row)
818 -- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
819 SET IntervalStyle to sql_standard;
820 select interval 'P0Y' AS "zero",
821 interval 'P1Y2M' AS "a year 2 months",
822 interval 'P1W' AS "a week",
823 interval 'P1DT2H3M4S' AS "a bit over a day",
824 interval 'P1Y2M3DT4H5M6.7S' AS "all fields",
825 interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
826 interval 'PT-0.1S' AS "fractional second";
827 zero | a year 2 months | a week | a bit over a day | all fields | negative | fractional second
828 ------+-----------------+-----------+------------------+--------------------+--------------------+-------------------
829 0 | 1-2 | 7 0:00:00 | 1 2:03:04 | +1-2 +3 +4:05:06.7 | -1-2 -3 -4:05:06.7 | -0:00:00.1
830 (1 row)
832 -- test inputting ISO 8601 4.4.2.2 "Alternative Format"
833 SET IntervalStyle to postgres;
834 select interval 'P00021015T103020' AS "ISO8601 Basic Format",
835 interval 'P0002-10-15T10:30:20' AS "ISO8601 Extended Format";
836 ISO8601 Basic Format | ISO8601 Extended Format
837 ----------------------------------+----------------------------------
838 2 years 10 mons 15 days 10:30:20 | 2 years 10 mons 15 days 10:30:20
839 (1 row)
841 -- Make sure optional ISO8601 alternative format fields are optional.
842 select interval 'P0002' AS "year only",
843 interval 'P0002-10' AS "year month",
844 interval 'P0002-10-15' AS "year month day",
845 interval 'P0002T1S' AS "year only plus time",
846 interval 'P0002-10T1S' AS "year month plus time",
847 interval 'P0002-10-15T1S' AS "year month day plus time",
848 interval 'PT10' AS "hour only",
849 interval 'PT10:30' AS "hour minute";
850 year only | year month | year month day | year only plus time | year month plus time | year month day plus time | hour only | hour minute
851 -----------+-----------------+-------------------------+---------------------+--------------------------+----------------------------------+-----------+-------------
852 2 years | 2 years 10 mons | 2 years 10 mons 15 days | 2 years 00:00:01 | 2 years 10 mons 00:00:01 | 2 years 10 mons 15 days 00:00:01 | 10:00:00 | 10:30:00
853 (1 row)
855 -- test a couple rounding cases that changed since 8.3 w/ HAVE_INT64_TIMESTAMP.
856 SET IntervalStyle to postgres_verbose;
857 select interval '-10 mons -3 days +03:55:06.70';
858 interval
859 --------------------------------------------------
860 @ 10 mons 3 days -3 hours -55 mins -6.7 secs ago
861 (1 row)
863 select interval '1 year 2 mons 3 days 04:05:06.699999';
864 interval
865 -----------------------------------------------------
866 @ 1 year 2 mons 3 days 4 hours 5 mins 6.699999 secs
867 (1 row)
869 select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds';
870 interval | interval | interval
871 ------------+------------+------------
872 @ 0.7 secs | @ 0.7 secs | @ 0.7 secs
873 (1 row)
875 -- check that '30 days' equals '1 month' according to the hash function
876 select '30 days'::interval = '1 month'::interval as t;
877 t
878 ---
879 t
880 (1 row)
882 select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t;
883 t
884 ---
885 t
886 (1 row)
888 -- numeric constructor
889 select make_interval(years := 2);
890 make_interval
891 ---------------
892 @ 2 years
893 (1 row)
895 select make_interval(years := 1, months := 6);
896 make_interval
897 -----------------
898 @ 1 year 6 mons
899 (1 row)
901 select make_interval(years := 1, months := -1, weeks := 5, days := -7, hours := 25, mins := -180);
902 make_interval
903 ----------------------------
904 @ 11 mons 28 days 22 hours
905 (1 row)
907 select make_interval() = make_interval(years := 0, months := 0, weeks := 0, days := 0, mins := 0, secs := 0.0);
908 ?column?
909 ----------
910 t
911 (1 row)
913 select make_interval(hours := -2, mins := -10, secs := -25.3);
914 make_interval
915 ---------------------------------
916 @ 2 hours 10 mins 25.3 secs ago
917 (1 row)
919 select make_interval(years := 'inf'::float::int);
920 ERROR: integer out of range
921 select make_interval(months := 'NaN'::float::int);
922 ERROR: integer out of range
923 select make_interval(secs := 'inf');
924 ERROR: interval out of range
925 select make_interval(secs := 'NaN');
926 ERROR: interval out of range
927 select make_interval(secs := 7e12);
928 make_interval
929 ------------------------------------
930 @ 1944444444 hours 26 mins 40 secs
931 (1 row)
933 --
934 -- test EXTRACT
935 --
936 SELECT f1,
937 EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
938 EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
939 EXTRACT(SECOND FROM f1) AS SECOND,
940 EXTRACT(MINUTE FROM f1) AS MINUTE,
941 EXTRACT(HOUR FROM f1) AS HOUR,
942 EXTRACT(DAY FROM f1) AS DAY,
943 EXTRACT(MONTH FROM f1) AS MONTH,
944 EXTRACT(QUARTER FROM f1) AS QUARTER,
945 EXTRACT(YEAR FROM f1) AS YEAR,
946 EXTRACT(DECADE FROM f1) AS DECADE,
947 EXTRACT(CENTURY FROM f1) AS CENTURY,
948 EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
949 EXTRACT(EPOCH FROM f1) AS EPOCH
950 FROM INTERVAL_TBL;
951 f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
952 -------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
953 @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
954 @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
955 @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
956 @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072224000.000000
957 @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
958 @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
959 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
960 @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189216000.000000
961 @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
962 @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
963 (10 rows)
965 SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
966 ERROR: interval units "fortnight" not recognized
967 SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
968 ERROR: interval units "timezone" not supported
969 SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
970 extract
971 ---------
972 10
973 (1 row)
975 SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
976 extract
977 ---------
978 9
979 (1 row)
981 SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
982 extract
983 ---------
984 -9
985 (1 row)
987 SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
988 extract
989 ---------
990 -10
991 (1 row)
993 SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
994 extract
995 ---------
996 1
997 (1 row)
999 SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
1000 extract
1001 ---------
1002 0
1003 (1 row)
1005 SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
1006 extract
1007 ---------
1008 0
1009 (1 row)
1011 SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
1012 extract
1013 ---------
1014 -1
1015 (1 row)
1017 -- date_part implementation is mostly the same as extract, so only
1018 -- test a few cases for additional coverage.
1019 SELECT f1,
1020 date_part('microsecond', f1) AS microsecond,
1021 date_part('millisecond', f1) AS millisecond,
1022 date_part('second', f1) AS second,
1023 date_part('epoch', f1) AS epoch
1024 FROM INTERVAL_TBL;
1025 f1 | microsecond | millisecond | second | epoch
1026 -------------------------------+-------------+-------------+--------+------------
1027 @ 1 min | 0 | 0 | 0 | 60
1028 @ 5 hours | 0 | 0 | 0 | 18000
1029 @ 10 days | 0 | 0 | 0 | 864000
1030 @ 34 years | 0 | 0 | 0 | 1072958400
1031 @ 3 mons | 0 | 0 | 0 | 7776000
1032 @ 14 secs ago | -14000000 | -14000 | -14 | -14
1033 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 93784
1034 @ 6 years | 0 | 0 | 0 | 189345600
1035 @ 5 mons | 0 | 0 | 0 | 12960000
1036 @ 5 mons 12 hours | 0 | 0 | 0 | 13003200
1037 (10 rows)
1039 -- internal overflow test case
1040 SELECT extract(epoch from interval '1000000000 days');
1041 extract
1042 -----------------------
1043 86400000000000.000000
1044 (1 row)