| blob | c0ca8e041b1631d152ff450aa75a35fb3d1c95b5 |
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 non-error-throwing API
76 SELECT pg_input_is_valid('1.5 weeks', 'interval');
77 pg_input_is_valid
78 -------------------
79 t
80 (1 row)
82 SELECT pg_input_is_valid('garbage', 'interval');
83 pg_input_is_valid
84 -------------------
85 f
86 (1 row)
88 SELECT pg_input_is_valid('@ 30 eons ago', 'interval');
89 pg_input_is_valid
90 -------------------
91 f
92 (1 row)
94 SELECT * FROM pg_input_error_info('garbage', 'interval');
95 message | detail | hint | sql_error_code
96 ---------------------------------------------------+--------+------+----------------
97 invalid input syntax for type interval: "garbage" | | | 22007
98 (1 row)
100 SELECT * FROM pg_input_error_info('@ 30 eons ago', 'interval');
101 message | detail | hint | sql_error_code
102 ---------------------------------------------------------+--------+------+----------------
103 invalid input syntax for type interval: "@ 30 eons ago" | | | 22007
104 (1 row)
106 -- test interval operators
107 SELECT * FROM INTERVAL_TBL;
108 f1
109 -----------------
110 00:01:00
111 05:00:00
112 10 days
113 34 years
114 3 mons
115 -00:00:14
116 1 day 02:03:04
117 6 years
118 5 mons
119 5 mons 12:00:00
120 (10 rows)
122 SELECT * FROM INTERVAL_TBL
123 WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
124 f1
125 -----------------
126 00:01:00
127 05:00:00
128 34 years
129 3 mons
130 -00:00:14
131 1 day 02:03:04
132 6 years
133 5 mons
134 5 mons 12:00:00
135 (9 rows)
137 SELECT * FROM INTERVAL_TBL
138 WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
139 f1
140 -----------
141 00:01:00
142 05:00:00
143 -00:00:14
144 (3 rows)
146 SELECT * FROM INTERVAL_TBL
147 WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
148 f1
149 -----------
150 00:01:00
151 05:00:00
152 -00:00:14
153 (3 rows)
155 SELECT * FROM INTERVAL_TBL
156 WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
157 f1
158 ----------
159 34 years
160 (1 row)
162 SELECT * FROM INTERVAL_TBL
163 WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
164 f1
165 -----------------
166 34 years
167 3 mons
168 6 years
169 5 mons
170 5 mons 12:00:00
171 (5 rows)
173 SELECT * FROM INTERVAL_TBL
174 WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
175 f1
176 -----------------
177 00:01:00
178 05:00:00
179 10 days
180 34 years
181 3 mons
182 1 day 02:03:04
183 6 years
184 5 mons
185 5 mons 12:00:00
186 (9 rows)
188 SELECT r1.*, r2.*
189 FROM INTERVAL_TBL r1, INTERVAL_TBL r2
190 WHERE r1.f1 > r2.f1
191 ORDER BY r1.f1, r2.f1;
192 f1 | f1
193 -----------------+-----------------
194 00:01:00 | -00:00:14
195 05:00:00 | -00:00:14
196 05:00:00 | 00:01:00
197 1 day 02:03:04 | -00:00:14
198 1 day 02:03:04 | 00:01:00
199 1 day 02:03:04 | 05:00:00
200 10 days | -00:00:14
201 10 days | 00:01:00
202 10 days | 05:00:00
203 10 days | 1 day 02:03:04
204 3 mons | -00:00:14
205 3 mons | 00:01:00
206 3 mons | 05:00:00
207 3 mons | 1 day 02:03:04
208 3 mons | 10 days
209 5 mons | -00:00:14
210 5 mons | 00:01:00
211 5 mons | 05:00:00
212 5 mons | 1 day 02:03:04
213 5 mons | 10 days
214 5 mons | 3 mons
215 5 mons 12:00:00 | -00:00:14
216 5 mons 12:00:00 | 00:01:00
217 5 mons 12:00:00 | 05:00:00
218 5 mons 12:00:00 | 1 day 02:03:04
219 5 mons 12:00:00 | 10 days
220 5 mons 12:00:00 | 3 mons
221 5 mons 12:00:00 | 5 mons
222 6 years | -00:00:14
223 6 years | 00:01:00
224 6 years | 05:00:00
225 6 years | 1 day 02:03:04
226 6 years | 10 days
227 6 years | 3 mons
228 6 years | 5 mons
229 6 years | 5 mons 12:00:00
230 34 years | -00:00:14
231 34 years | 00:01:00
232 34 years | 05:00:00
233 34 years | 1 day 02:03:04
234 34 years | 10 days
235 34 years | 3 mons
236 34 years | 5 mons
237 34 years | 5 mons 12:00:00
238 34 years | 6 years
239 (45 rows)
241 -- Test intervals that are large enough to overflow 64 bits in comparisons
242 CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
243 INSERT INTO INTERVAL_TBL_OF (f1) VALUES
244 ('2147483647 days 2147483647 months'),
245 ('2147483647 days -2147483648 months'),
246 ('1 year'),
247 ('-2147483648 days 2147483647 months'),
248 ('-2147483648 days -2147483648 months');
249 -- these should fail as out-of-range
250 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
251 ERROR: interval field value out of range: "2147483648 days"
252 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
253 ^
254 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days');
255 ERROR: interval field value out of range: "-2147483649 days"
256 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days')...
257 ^
258 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years');
259 ERROR: interval out of range
260 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years')...
261 ^
262 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years');
263 ERROR: interval out of range
264 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'...
265 ^
266 -- Test edge-case overflow detection in interval multiplication
267 select extract(epoch from '256 microseconds'::interval * (2^55)::float8);
268 ERROR: interval out of range
269 SELECT r1.*, r2.*
270 FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2
271 WHERE r1.f1 > r2.f1
272 ORDER BY r1.f1, r2.f1;
273 f1 | f1
274 -------------------------------------------+-------------------------------------------
275 -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days
276 1 year | -178956970 years -8 mons -2147483648 days
277 1 year | -178956970 years -8 mons +2147483647 days
278 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons -2147483648 days
279 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons +2147483647 days
280 178956970 years 7 mons -2147483648 days | 1 year
281 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons -2147483648 days
282 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons +2147483647 days
283 178956970 years 7 mons 2147483647 days | 1 year
284 178956970 years 7 mons 2147483647 days | 178956970 years 7 mons -2147483648 days
285 (10 rows)
287 CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1);
288 SET enable_seqscan TO false;
289 EXPLAIN (COSTS OFF)
290 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
291 QUERY PLAN
292 --------------------------------------------------------------------
293 Index Only Scan using interval_tbl_of_f1_idx on interval_tbl_of r1
294 (1 row)
296 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
297 f1
298 -------------------------------------------
299 -178956970 years -8 mons -2147483648 days
300 -178956970 years -8 mons +2147483647 days
301 1 year
302 178956970 years 7 mons -2147483648 days
303 178956970 years 7 mons 2147483647 days
304 (5 rows)
306 RESET enable_seqscan;
307 DROP TABLE INTERVAL_TBL_OF;
308 -- Test multiplication and division with intervals.
309 -- Floating point arithmetic rounding errors can lead to unexpected results,
310 -- though the code attempts to do the right thing and round up to days and
311 -- minutes to avoid results such as '3 days 24:00 hours' or '14:20:60'.
312 -- Note that it is expected for some day components to be greater than 29 and
313 -- some time components be greater than 23:59:59 due to how intervals are
314 -- stored internally.
315 CREATE TABLE INTERVAL_MULDIV_TBL (span interval);
316 COPY INTERVAL_MULDIV_TBL FROM STDIN;
317 SELECT span * 0.3 AS product
318 FROM INTERVAL_MULDIV_TBL;
319 product
320 ------------------------------------
321 1 year 12 days 122:24:00
322 -1 years -12 days +93:36:00
323 -3 days -14:24:00
324 2 mons 13 days 01:22:28.8
325 -10 mons +120 days 37:28:21.6567
326 1 mon 6 days
327 4 mons 6 days
328 24 years 11 mons 320 days 16:48:00
329 (8 rows)
331 SELECT span * 8.2 AS product
332 FROM INTERVAL_MULDIV_TBL;
333 product
334 ---------------------------------------------
335 28 years 104 days 2961:36:00
336 -28 years -104 days +2942:24:00
337 -98 days -09:36:00
338 6 years 1 mon -197 days +93:34:27.2
339 -24 years -7 mons +3946 days 640:15:11.9498
340 2 years 8 mons 24 days
341 9 years 6 mons 24 days
342 682 years 7 mons 8215 days 19:12:00
343 (8 rows)
345 SELECT span / 10 AS quotient
346 FROM INTERVAL_MULDIV_TBL;
347 quotient
348 ----------------------------------
349 4 mons 4 days 40:48:00
350 -4 mons -4 days +31:12:00
351 -1 days -04:48:00
352 25 days -15:32:30.4
353 -3 mons +30 days 12:29:27.2189
354 12 days
355 1 mon 12 days
356 8 years 3 mons 126 days 21:36:00
357 (8 rows)
359 SELECT span / 100 AS quotient
360 FROM INTERVAL_MULDIV_TBL;
361 quotient
362 -------------------------
363 12 days 13:40:48
364 -12 days -06:28:48
365 -02:52:48
366 2 days 10:26:44.96
367 -6 days +01:14:56.72189
368 1 day 04:48:00
369 4 days 04:48:00
370 9 mons 39 days 16:33:36
371 (8 rows)
373 DROP TABLE INTERVAL_MULDIV_TBL;
374 SET DATESTYLE = 'postgres';
375 SET IntervalStyle to postgres_verbose;
376 SELECT * FROM INTERVAL_TBL;
377 f1
378 -------------------------------
379 @ 1 min
380 @ 5 hours
381 @ 10 days
382 @ 34 years
383 @ 3 mons
384 @ 14 secs ago
385 @ 1 day 2 hours 3 mins 4 secs
386 @ 6 years
387 @ 5 mons
388 @ 5 mons 12 hours
389 (10 rows)
391 -- test avg(interval), which is somewhat fragile since people have been
392 -- known to change the allowed input syntax for type interval without
393 -- updating pg_aggregate.agginitval
394 select avg(f1) from interval_tbl;
395 avg
396 -------------------------------------------------
397 @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
398 (1 row)
400 -- test long interval input
401 select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
402 interval
403 --------------------------------------------
404 @ 4541 years 4 mons 4 days 17 mins 31 secs
405 (1 row)
407 -- test long interval output
408 -- Note: the actual maximum length of the interval output is longer,
409 -- but we need the test to work for both integer and floating-point
410 -- timestamps.
411 select '100000000y 10mon -1000000000d -100000h -10min -10.000001s ago'::interval;
412 interval
413 ---------------------------------------------------------------------------------------
414 @ 100000000 years 10 mons -1000000000 days -100000 hours -10 mins -10.000001 secs ago
415 (1 row)
417 -- test justify_hours() and justify_days()
418 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";
419 6 mons 5 days 4 hours 3 mins 2 seconds
420 ----------------------------------------
421 @ 6 mons 5 days 4 hours 3 mins 2 secs
422 (1 row)
424 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";
425 7 mons 6 days 5 hours 4 mins 3 seconds
426 ----------------------------------------
427 @ 7 mons 6 days 5 hours 4 mins 3 secs
428 (1 row)
430 SELECT justify_hours(interval '2147483647 days 24 hrs');
431 ERROR: interval out of range
432 SELECT justify_days(interval '2147483647 months 30 days');
433 ERROR: interval out of range
434 -- test justify_interval()
435 SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
436 1 month -1 hour
437 --------------------
438 @ 29 days 23 hours
439 (1 row)
441 SELECT justify_interval(interval '2147483647 days 24 hrs');
442 justify_interval
443 -------------------------------
444 @ 5965232 years 4 mons 8 days
445 (1 row)
447 SELECT justify_interval(interval '-2147483648 days -24 hrs');
448 justify_interval
449 -----------------------------------
450 @ 5965232 years 4 mons 9 days ago
451 (1 row)
453 SELECT justify_interval(interval '2147483647 months 30 days');
454 ERROR: interval out of range
455 SELECT justify_interval(interval '-2147483648 months -30 days');
456 ERROR: interval out of range
457 SELECT justify_interval(interval '2147483647 months 30 days -24 hrs');
458 justify_interval
459 ----------------------------------
460 @ 178956970 years 7 mons 29 days
461 (1 row)
463 SELECT justify_interval(interval '-2147483648 months -30 days 24 hrs');
464 justify_interval
465 --------------------------------------
466 @ 178956970 years 8 mons 29 days ago
467 (1 row)
469 SELECT justify_interval(interval '2147483647 months -30 days 1440 hrs');
470 ERROR: interval out of range
471 SELECT justify_interval(interval '-2147483648 months 30 days -1440 hrs');
472 ERROR: interval out of range
473 -- test fractional second input, and detection of duplicate units
474 SET DATESTYLE = 'ISO';
475 SET IntervalStyle TO postgres;
476 SELECT '1 millisecond'::interval, '1 microsecond'::interval,
477 '500 seconds 99 milliseconds 51 microseconds'::interval;
478 interval | interval | interval
479 --------------+-----------------+-----------------
480 00:00:00.001 | 00:00:00.000001 | 00:08:20.099051
481 (1 row)
483 SELECT '3 days 5 milliseconds'::interval;
484 interval
485 ---------------------
486 3 days 00:00:00.005
487 (1 row)
489 SELECT '1 second 2 seconds'::interval; -- error
490 ERROR: invalid input syntax for type interval: "1 second 2 seconds"
491 LINE 1: SELECT '1 second 2 seconds'::interval;
492 ^
493 SELECT '10 milliseconds 20 milliseconds'::interval; -- error
494 ERROR: invalid input syntax for type interval: "10 milliseconds 20 milliseconds"
495 LINE 1: SELECT '10 milliseconds 20 milliseconds'::interval;
496 ^
497 SELECT '5.5 seconds 3 milliseconds'::interval; -- error
498 ERROR: invalid input syntax for type interval: "5.5 seconds 3 milliseconds"
499 LINE 1: SELECT '5.5 seconds 3 milliseconds'::interval;
500 ^
501 SELECT '1:20:05 5 microseconds'::interval; -- error
502 ERROR: invalid input syntax for type interval: "1:20:05 5 microseconds"
503 LINE 1: SELECT '1:20:05 5 microseconds'::interval;
504 ^
505 SELECT '1 day 1 day'::interval; -- error
506 ERROR: invalid input syntax for type interval: "1 day 1 day"
507 LINE 1: SELECT '1 day 1 day'::interval;
508 ^
509 SELECT interval '1-2'; -- SQL year-month literal
510 interval
511 ---------------
512 1 year 2 mons
513 (1 row)
515 SELECT interval '999' second; -- oversize leading field is ok
516 interval
517 ----------
518 00:16:39
519 (1 row)
521 SELECT interval '999' minute;
522 interval
523 ----------
524 16:39:00
525 (1 row)
527 SELECT interval '999' hour;
528 interval
529 -----------
530 999:00:00
531 (1 row)
533 SELECT interval '999' day;
534 interval
535 ----------
536 999 days
537 (1 row)
539 SELECT interval '999' month;
540 interval
541 -----------------
542 83 years 3 mons
543 (1 row)
545 -- test SQL-spec syntaxes for restricted field sets
546 SELECT interval '1' year;
547 interval
548 ----------
549 1 year
550 (1 row)
552 SELECT interval '2' month;
553 interval
554 ----------
555 2 mons
556 (1 row)
558 SELECT interval '3' day;
559 interval
560 ----------
561 3 days
562 (1 row)
564 SELECT interval '4' hour;
565 interval
566 ----------
567 04:00:00
568 (1 row)
570 SELECT interval '5' minute;
571 interval
572 ----------
573 00:05:00
574 (1 row)
576 SELECT interval '6' second;
577 interval
578 ----------
579 00:00:06
580 (1 row)
582 SELECT interval '1' year to month;
583 interval
584 ----------
585 1 mon
586 (1 row)
588 SELECT interval '1-2' year to month;
589 interval
590 ---------------
591 1 year 2 mons
592 (1 row)
594 SELECT interval '1 2' day to hour;
595 interval
596 ----------------
597 1 day 02:00:00
598 (1 row)
600 SELECT interval '1 2:03' day to hour;
601 interval
602 ----------------
603 1 day 02:00:00
604 (1 row)
606 SELECT interval '1 2:03:04' day to hour;
607 interval
608 ----------------
609 1 day 02:00:00
610 (1 row)
612 SELECT interval '1 2' day to minute;
613 ERROR: invalid input syntax for type interval: "1 2"
614 LINE 1: SELECT interval '1 2' day to minute;
615 ^
616 SELECT interval '1 2:03' day to minute;
617 interval
618 ----------------
619 1 day 02:03:00
620 (1 row)
622 SELECT interval '1 2:03:04' day to minute;
623 interval
624 ----------------
625 1 day 02:03:00
626 (1 row)
628 SELECT interval '1 2' day to second;
629 ERROR: invalid input syntax for type interval: "1 2"
630 LINE 1: SELECT interval '1 2' day to second;
631 ^
632 SELECT interval '1 2:03' day to second;
633 interval
634 ----------------
635 1 day 02:03:00
636 (1 row)
638 SELECT interval '1 2:03:04' day to second;
639 interval
640 ----------------
641 1 day 02:03:04
642 (1 row)
644 SELECT interval '1 2' hour to minute;
645 ERROR: invalid input syntax for type interval: "1 2"
646 LINE 1: SELECT interval '1 2' hour to minute;
647 ^
648 SELECT interval '1 2:03' hour to minute;
649 interval
650 ----------------
651 1 day 02:03:00
652 (1 row)
654 SELECT interval '1 2:03:04' hour to minute;
655 interval
656 ----------------
657 1 day 02:03:00
658 (1 row)
660 SELECT interval '1 2' hour to second;
661 ERROR: invalid input syntax for type interval: "1 2"
662 LINE 1: SELECT interval '1 2' hour to second;
663 ^
664 SELECT interval '1 2:03' hour to second;
665 interval
666 ----------------
667 1 day 02:03:00
668 (1 row)
670 SELECT interval '1 2:03:04' hour to second;
671 interval
672 ----------------
673 1 day 02:03:04
674 (1 row)
676 SELECT interval '1 2' minute to second;
677 ERROR: invalid input syntax for type interval: "1 2"
678 LINE 1: SELECT interval '1 2' minute to second;
679 ^
680 SELECT interval '1 2:03' minute to second;
681 interval
682 ----------------
683 1 day 00:02:03
684 (1 row)
686 SELECT interval '1 2:03:04' minute to second;
687 interval
688 ----------------
689 1 day 02:03:04
690 (1 row)
692 SELECT interval '1 +2:03' minute to second;
693 interval
694 ----------------
695 1 day 00:02:03
696 (1 row)
698 SELECT interval '1 +2:03:04' minute to second;
699 interval
700 ----------------
701 1 day 02:03:04
702 (1 row)
704 SELECT interval '1 -2:03' minute to second;
705 interval
706 -----------------
707 1 day -00:02:03
708 (1 row)
710 SELECT interval '1 -2:03:04' minute to second;
711 interval
712 -----------------
713 1 day -02:03:04
714 (1 row)
716 SELECT interval '123 11' day to hour; -- ok
717 interval
718 -------------------
719 123 days 11:00:00
720 (1 row)
722 SELECT interval '123 11' day; -- not ok
723 ERROR: invalid input syntax for type interval: "123 11"
724 LINE 1: SELECT interval '123 11' day;
725 ^
726 SELECT interval '123 11'; -- not ok, too ambiguous
727 ERROR: invalid input syntax for type interval: "123 11"
728 LINE 1: SELECT interval '123 11';
729 ^
730 SELECT interval '123 2:03 -2:04'; -- not ok, redundant hh:mm fields
731 ERROR: invalid input syntax for type interval: "123 2:03 -2:04"
732 LINE 1: SELECT interval '123 2:03 -2:04';
733 ^
734 -- test syntaxes for restricted precision
735 SELECT interval(0) '1 day 01:23:45.6789';
736 interval
737 ----------------
738 1 day 01:23:46
739 (1 row)
741 SELECT interval(2) '1 day 01:23:45.6789';
742 interval
743 -------------------
744 1 day 01:23:45.68
745 (1 row)
747 SELECT interval '12:34.5678' minute to second(2); -- per SQL spec
748 interval
749 -------------
750 00:12:34.57
751 (1 row)
753 SELECT interval '1.234' second;
754 interval
755 --------------
756 00:00:01.234
757 (1 row)
759 SELECT interval '1.234' second(2);
760 interval
761 -------------
762 00:00:01.23
763 (1 row)
765 SELECT interval '1 2.345' day to second(2);
766 ERROR: invalid input syntax for type interval: "1 2.345"
767 LINE 1: SELECT interval '1 2.345' day to second(2);
768 ^
769 SELECT interval '1 2:03' day to second(2);
770 interval
771 ----------------
772 1 day 02:03:00
773 (1 row)
775 SELECT interval '1 2:03.4567' day to second(2);
776 interval
777 -------------------
778 1 day 00:02:03.46
779 (1 row)
781 SELECT interval '1 2:03:04.5678' day to second(2);
782 interval
783 -------------------
784 1 day 02:03:04.57
785 (1 row)
787 SELECT interval '1 2.345' hour to second(2);
788 ERROR: invalid input syntax for type interval: "1 2.345"
789 LINE 1: SELECT interval '1 2.345' hour to second(2);
790 ^
791 SELECT interval '1 2:03.45678' hour to second(2);
792 interval
793 -------------------
794 1 day 00:02:03.46
795 (1 row)
797 SELECT interval '1 2:03:04.5678' hour to second(2);
798 interval
799 -------------------
800 1 day 02:03:04.57
801 (1 row)
803 SELECT interval '1 2.3456' minute to second(2);
804 ERROR: invalid input syntax for type interval: "1 2.3456"
805 LINE 1: SELECT interval '1 2.3456' minute to second(2);
806 ^
807 SELECT interval '1 2:03.5678' minute to second(2);
808 interval
809 -------------------
810 1 day 00:02:03.57
811 (1 row)
813 SELECT interval '1 2:03:04.5678' minute to second(2);
814 interval
815 -------------------
816 1 day 02:03:04.57
817 (1 row)
819 -- test casting to restricted precision (bug #14479)
820 SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
821 (f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
822 FROM interval_tbl;
823 f1 | minutes | years
824 -----------------+-----------------+----------
825 00:01:00 | 00:01:00 | 00:00:00
826 05:00:00 | 05:00:00 | 00:00:00
827 10 days | 10 days | 00:00:00
828 34 years | 34 years | 34 years
829 3 mons | 3 mons | 00:00:00
830 -00:00:14 | 00:00:00 | 00:00:00
831 1 day 02:03:04 | 1 day 02:03:00 | 00:00:00
832 6 years | 6 years | 6 years
833 5 mons | 5 mons | 00:00:00
834 5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
835 (10 rows)
837 -- test inputting and outputting SQL standard interval literals
838 SET IntervalStyle TO sql_standard;
839 SELECT interval '0' AS "zero",
840 interval '1-2' year to month AS "year-month",
841 interval '1 2:03:04' day to second AS "day-time",
842 - interval '1-2' AS "negative year-month",
843 - interval '1 2:03:04' AS "negative day-time";
844 zero | year-month | day-time | negative year-month | negative day-time
845 ------+------------+-----------+---------------------+-------------------
846 0 | 1-2 | 1 2:03:04 | -1-2 | -1 2:03:04
847 (1 row)
849 -- test input of some not-quite-standard interval values in the sql style
850 SET IntervalStyle TO postgres;
851 SELECT interval '+1 -1:00:00',
852 interval '-1 +1:00:00',
853 interval '+1-2 -3 +4:05:06.789',
854 interval '-1-2 +3 -4:05:06.789';
855 interval | interval | interval | interval
856 -----------------+-------------------+-------------------------------------+----------------------------------------
857 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
858 (1 row)
860 -- cases that trigger sign-matching rules in the sql style
861 SELECT interval '-23 hours 45 min 12.34 sec',
862 interval '-1 day 23 hours 45 min 12.34 sec',
863 interval '-1 year 2 months 1 day 23 hours 45 min 12.34 sec',
864 interval '-1 year 2 months 1 day 23 hours 45 min +12.34 sec';
865 interval | interval | interval | interval
866 --------------+----------------------+-----------------------------+-----------------------------
867 -22:14:47.66 | -1 days +23:45:12.34 | -10 mons +1 day 23:45:12.34 | -10 mons +1 day 23:45:12.34
868 (1 row)
870 -- test output of couple non-standard interval values in the sql style
871 SET IntervalStyle TO sql_standard;
872 SELECT interval '1 day -1 hours',
873 interval '-1 days +1 hours',
874 interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
875 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
876 interval | interval | interval | ?column?
877 ------------------+------------------+----------------------+----------------------
878 +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
879 (1 row)
881 -- cases that trigger sign-matching rules in the sql style
882 SELECT interval '-23 hours 45 min 12.34 sec',
883 interval '-1 day 23 hours 45 min 12.34 sec',
884 interval '-1 year 2 months 1 day 23 hours 45 min 12.34 sec',
885 interval '-1 year 2 months 1 day 23 hours 45 min +12.34 sec';
886 interval | interval | interval | interval
887 --------------+----------------+----------------------+-----------------------
888 -23:45:12.34 | -1 23:45:12.34 | -1-2 -1 -23:45:12.34 | -0-10 +1 +23:45:12.34
889 (1 row)
891 -- edge case for sign-matching rules
892 SELECT interval ''; -- error
893 ERROR: invalid input syntax for type interval: ""
894 LINE 1: SELECT interval '';
895 ^
896 -- test outputting iso8601 intervals
897 SET IntervalStyle to iso_8601;
898 select interval '0' AS "zero",
899 interval '1-2' AS "a year 2 months",
900 interval '1 2:03:04' AS "a bit over a day",
901 interval '2:03:04.45679' AS "a bit over 2 hours",
902 (interval '1-2' + interval '3 4:05:06.7') AS "all fields",
903 (interval '1-2' - interval '3 4:05:06.7') AS "mixed sign",
904 (- interval '1-2' + interval '3 4:05:06.7') AS "negative";
905 zero | a year 2 months | a bit over a day | a bit over 2 hours | all fields | mixed sign | negative
906 ------+-----------------+------------------+--------------------+------------------+----------------------+--------------------
907 PT0S | P1Y2M | P1DT2H3M4S | PT2H3M4.45679S | P1Y2M3DT4H5M6.7S | P1Y2M-3DT-4H-5M-6.7S | P-1Y-2M3DT4H5M6.7S
908 (1 row)
910 -- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
911 SET IntervalStyle to sql_standard;
912 select interval 'P0Y' AS "zero",
913 interval 'P1Y2M' AS "a year 2 months",
914 interval 'P1W' AS "a week",
915 interval 'P1DT2H3M4S' AS "a bit over a day",
916 interval 'P1Y2M3DT4H5M6.7S' AS "all fields",
917 interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
918 interval 'PT-0.1S' AS "fractional second";
919 zero | a year 2 months | a week | a bit over a day | all fields | negative | fractional second
920 ------+-----------------+-----------+------------------+--------------------+--------------------+-------------------
921 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
922 (1 row)
924 -- test inputting ISO 8601 4.4.2.2 "Alternative Format"
925 SET IntervalStyle to postgres;
926 select interval 'P00021015T103020' AS "ISO8601 Basic Format",
927 interval 'P0002-10-15T10:30:20' AS "ISO8601 Extended Format";
928 ISO8601 Basic Format | ISO8601 Extended Format
929 ----------------------------------+----------------------------------
930 2 years 10 mons 15 days 10:30:20 | 2 years 10 mons 15 days 10:30:20
931 (1 row)
933 -- Make sure optional ISO8601 alternative format fields are optional.
934 select interval 'P0002' AS "year only",
935 interval 'P0002-10' AS "year month",
936 interval 'P0002-10-15' AS "year month day",
937 interval 'P0002T1S' AS "year only plus time",
938 interval 'P0002-10T1S' AS "year month plus time",
939 interval 'P0002-10-15T1S' AS "year month day plus time",
940 interval 'PT10' AS "hour only",
941 interval 'PT10:30' AS "hour minute";
942 year only | year month | year month day | year only plus time | year month plus time | year month day plus time | hour only | hour minute
943 -----------+-----------------+-------------------------+---------------------+--------------------------+----------------------------------+-----------+-------------
944 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
945 (1 row)
947 -- Check handling of fractional fields in ISO8601 format.
948 select interval 'P1Y0M3DT4H5M6S';
949 interval
950 ------------------------
951 1 year 3 days 04:05:06
952 (1 row)
954 select interval 'P1.0Y0M3DT4H5M6S';
955 interval
956 ------------------------
957 1 year 3 days 04:05:06
958 (1 row)
960 select interval 'P1.1Y0M3DT4H5M6S';
961 interval
962 ------------------------------
963 1 year 1 mon 3 days 04:05:06
964 (1 row)
966 select interval 'P1.Y0M3DT4H5M6S';
967 interval
968 ------------------------
969 1 year 3 days 04:05:06
970 (1 row)
972 select interval 'P.1Y0M3DT4H5M6S';
973 interval
974 -----------------------
975 1 mon 3 days 04:05:06
976 (1 row)
978 select interval 'P10.5e4Y'; -- not per spec, but we've historically taken it
979 interval
980 --------------
981 105000 years
982 (1 row)
984 select interval 'P.Y0M3DT4H5M6S'; -- error
985 ERROR: invalid input syntax for type interval: "P.Y0M3DT4H5M6S"
986 LINE 1: select interval 'P.Y0M3DT4H5M6S';
987 ^
988 -- test a couple rounding cases that changed since 8.3 w/ HAVE_INT64_TIMESTAMP.
989 SET IntervalStyle to postgres_verbose;
990 select interval '-10 mons -3 days +03:55:06.70';
991 interval
992 --------------------------------------------------
993 @ 10 mons 3 days -3 hours -55 mins -6.7 secs ago
994 (1 row)
996 select interval '1 year 2 mons 3 days 04:05:06.699999';
997 interval
998 -----------------------------------------------------
999 @ 1 year 2 mons 3 days 4 hours 5 mins 6.699999 secs
1000 (1 row)
1002 select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds';
1003 interval | interval | interval
1004 ------------+------------+------------
1005 @ 0.7 secs | @ 0.7 secs | @ 0.7 secs
1006 (1 row)
1008 -- test time fields using entire 64 bit microseconds range
1009 select interval '2562047788.01521550194 hours';
1010 interval
1011 -----------------------------------
1012 @ 2562047788 hours 54.775807 secs
1013 (1 row)
1015 select interval '-2562047788.01521550222 hours';
1016 interval
1017 ---------------------------------------
1018 @ 2562047788 hours 54.775808 secs ago
1019 (1 row)
1021 select interval '153722867280.912930117 minutes';
1022 interval
1023 -----------------------------------
1024 @ 2562047788 hours 54.775807 secs
1025 (1 row)
1027 select interval '-153722867280.912930133 minutes';
1028 interval
1029 ---------------------------------------
1030 @ 2562047788 hours 54.775808 secs ago
1031 (1 row)
1033 select interval '9223372036854.775807 seconds';
1034 interval
1035 -----------------------------------
1036 @ 2562047788 hours 54.775807 secs
1037 (1 row)
1039 select interval '-9223372036854.775808 seconds';
1040 interval
1041 ---------------------------------------
1042 @ 2562047788 hours 54.775808 secs ago
1043 (1 row)
1045 select interval '9223372036854775.807 milliseconds';
1046 interval
1047 -----------------------------------
1048 @ 2562047788 hours 54.775807 secs
1049 (1 row)
1051 select interval '-9223372036854775.808 milliseconds';
1052 interval
1053 ---------------------------------------
1054 @ 2562047788 hours 54.775808 secs ago
1055 (1 row)
1057 select interval '9223372036854775807 microseconds';
1058 interval
1059 -----------------------------------
1060 @ 2562047788 hours 54.775807 secs
1061 (1 row)
1063 select interval '-9223372036854775808 microseconds';
1064 interval
1065 ---------------------------------------
1066 @ 2562047788 hours 54.775808 secs ago
1067 (1 row)
1069 select interval 'PT2562047788H54.775807S';
1070 interval
1071 -----------------------------------
1072 @ 2562047788 hours 54.775807 secs
1073 (1 row)
1075 select interval 'PT-2562047788H-54.775808S';
1076 interval
1077 ---------------------------------------
1078 @ 2562047788 hours 54.775808 secs ago
1079 (1 row)
1081 select interval 'PT2562047788:00:54.775807';
1082 interval
1083 -----------------------------------
1084 @ 2562047788 hours 54.775807 secs
1085 (1 row)
1087 select interval 'PT2562047788.0152155019444';
1088 interval
1089 -----------------------------------
1090 @ 2562047788 hours 54.775429 secs
1091 (1 row)
1093 select interval 'PT-2562047788.0152155022222';
1094 interval
1095 ---------------------------------------
1096 @ 2562047788 hours 54.775429 secs ago
1097 (1 row)
1099 -- overflow each date/time field
1100 select interval '2147483648 years';
1101 ERROR: interval field value out of range: "2147483648 years"
1102 LINE 1: select interval '2147483648 years';
1103 ^
1104 select interval '-2147483649 years';
1105 ERROR: interval field value out of range: "-2147483649 years"
1106 LINE 1: select interval '-2147483649 years';
1107 ^
1108 select interval '2147483648 months';
1109 ERROR: interval field value out of range: "2147483648 months"
1110 LINE 1: select interval '2147483648 months';
1111 ^
1112 select interval '-2147483649 months';
1113 ERROR: interval field value out of range: "-2147483649 months"
1114 LINE 1: select interval '-2147483649 months';
1115 ^
1116 select interval '2147483648 days';
1117 ERROR: interval field value out of range: "2147483648 days"
1118 LINE 1: select interval '2147483648 days';
1119 ^
1120 select interval '-2147483649 days';
1121 ERROR: interval field value out of range: "-2147483649 days"
1122 LINE 1: select interval '-2147483649 days';
1123 ^
1124 select interval '2562047789 hours';
1125 ERROR: interval field value out of range: "2562047789 hours"
1126 LINE 1: select interval '2562047789 hours';
1127 ^
1128 select interval '-2562047789 hours';
1129 ERROR: interval field value out of range: "-2562047789 hours"
1130 LINE 1: select interval '-2562047789 hours';
1131 ^
1132 select interval '153722867281 minutes';
1133 ERROR: interval field value out of range: "153722867281 minutes"
1134 LINE 1: select interval '153722867281 minutes';
1135 ^
1136 select interval '-153722867281 minutes';
1137 ERROR: interval field value out of range: "-153722867281 minutes"
1138 LINE 1: select interval '-153722867281 minutes';
1139 ^
1140 select interval '9223372036855 seconds';
1141 ERROR: interval field value out of range: "9223372036855 seconds"
1142 LINE 1: select interval '9223372036855 seconds';
1143 ^
1144 select interval '-9223372036855 seconds';
1145 ERROR: interval field value out of range: "-9223372036855 seconds"
1146 LINE 1: select interval '-9223372036855 seconds';
1147 ^
1148 select interval '9223372036854777 millisecond';
1149 ERROR: interval field value out of range: "9223372036854777 millisecond"
1150 LINE 1: select interval '9223372036854777 millisecond';
1151 ^
1152 select interval '-9223372036854777 millisecond';
1153 ERROR: interval field value out of range: "-9223372036854777 millisecond"
1154 LINE 1: select interval '-9223372036854777 millisecond';
1155 ^
1156 select interval '9223372036854775808 microsecond';
1157 ERROR: interval field value out of range: "9223372036854775808 microsecond"
1158 LINE 1: select interval '9223372036854775808 microsecond';
1159 ^
1160 select interval '-9223372036854775809 microsecond';
1161 ERROR: interval field value out of range: "-9223372036854775809 microsecond"
1162 LINE 1: select interval '-9223372036854775809 microsecond';
1163 ^
1164 select interval 'P2147483648';
1165 ERROR: interval field value out of range: "P2147483648"
1166 LINE 1: select interval 'P2147483648';
1167 ^
1168 select interval 'P-2147483649';
1169 ERROR: interval field value out of range: "P-2147483649"
1170 LINE 1: select interval 'P-2147483649';
1171 ^
1172 select interval 'P1-2147483647-2147483647';
1173 ERROR: interval out of range
1174 LINE 1: select interval 'P1-2147483647-2147483647';
1175 ^
1176 select interval 'PT2562047789';
1177 ERROR: interval field value out of range: "PT2562047789"
1178 LINE 1: select interval 'PT2562047789';
1179 ^
1180 select interval 'PT-2562047789';
1181 ERROR: interval field value out of range: "PT-2562047789"
1182 LINE 1: select interval 'PT-2562047789';
1183 ^
1184 -- overflow with date/time unit aliases
1185 select interval '2147483647 weeks';
1186 ERROR: interval field value out of range: "2147483647 weeks"
1187 LINE 1: select interval '2147483647 weeks';
1188 ^
1189 select interval '-2147483648 weeks';
1190 ERROR: interval field value out of range: "-2147483648 weeks"
1191 LINE 1: select interval '-2147483648 weeks';
1192 ^
1193 select interval '2147483647 decades';
1194 ERROR: interval field value out of range: "2147483647 decades"
1195 LINE 1: select interval '2147483647 decades';
1196 ^
1197 select interval '-2147483648 decades';
1198 ERROR: interval field value out of range: "-2147483648 decades"
1199 LINE 1: select interval '-2147483648 decades';
1200 ^
1201 select interval '2147483647 centuries';
1202 ERROR: interval field value out of range: "2147483647 centuries"
1203 LINE 1: select interval '2147483647 centuries';
1204 ^
1205 select interval '-2147483648 centuries';
1206 ERROR: interval field value out of range: "-2147483648 centuries"
1207 LINE 1: select interval '-2147483648 centuries';
1208 ^
1209 select interval '2147483647 millennium';
1210 ERROR: interval field value out of range: "2147483647 millennium"
1211 LINE 1: select interval '2147483647 millennium';
1212 ^
1213 select interval '-2147483648 millennium';
1214 ERROR: interval field value out of range: "-2147483648 millennium"
1215 LINE 1: select interval '-2147483648 millennium';
1216 ^
1217 select interval '1 week 2147483647 days';
1218 ERROR: interval field value out of range: "1 week 2147483647 days"
1219 LINE 1: select interval '1 week 2147483647 days';
1220 ^
1221 select interval '-1 week -2147483648 days';
1222 ERROR: interval field value out of range: "-1 week -2147483648 days"
1223 LINE 1: select interval '-1 week -2147483648 days';
1224 ^
1225 select interval '2147483647 days 1 week';
1226 ERROR: interval field value out of range: "2147483647 days 1 week"
1227 LINE 1: select interval '2147483647 days 1 week';
1228 ^
1229 select interval '-2147483648 days -1 week';
1230 ERROR: interval field value out of range: "-2147483648 days -1 week"
1231 LINE 1: select interval '-2147483648 days -1 week';
1232 ^
1233 select interval 'P1W2147483647D';
1234 ERROR: interval field value out of range: "P1W2147483647D"
1235 LINE 1: select interval 'P1W2147483647D';
1236 ^
1237 select interval 'P-1W-2147483648D';
1238 ERROR: interval field value out of range: "P-1W-2147483648D"
1239 LINE 1: select interval 'P-1W-2147483648D';
1240 ^
1241 select interval 'P2147483647D1W';
1242 ERROR: interval field value out of range: "P2147483647D1W"
1243 LINE 1: select interval 'P2147483647D1W';
1244 ^
1245 select interval 'P-2147483648D-1W';
1246 ERROR: interval field value out of range: "P-2147483648D-1W"
1247 LINE 1: select interval 'P-2147483648D-1W';
1248 ^
1249 select interval '1 decade 2147483647 years';
1250 ERROR: interval field value out of range: "1 decade 2147483647 years"
1251 LINE 1: select interval '1 decade 2147483647 years';
1252 ^
1253 select interval '1 century 2147483647 years';
1254 ERROR: interval field value out of range: "1 century 2147483647 years"
1255 LINE 1: select interval '1 century 2147483647 years';
1256 ^
1257 select interval '1 millennium 2147483647 years';
1258 ERROR: interval field value out of range: "1 millennium 2147483647 years"
1259 LINE 1: select interval '1 millennium 2147483647 years';
1260 ^
1261 select interval '-1 decade -2147483648 years';
1262 ERROR: interval field value out of range: "-1 decade -2147483648 years"
1263 LINE 1: select interval '-1 decade -2147483648 years';
1264 ^
1265 select interval '-1 century -2147483648 years';
1266 ERROR: interval field value out of range: "-1 century -2147483648 years"
1267 LINE 1: select interval '-1 century -2147483648 years';
1268 ^
1269 select interval '-1 millennium -2147483648 years';
1270 ERROR: interval field value out of range: "-1 millennium -2147483648 years"
1271 LINE 1: select interval '-1 millennium -2147483648 years';
1272 ^
1273 select interval '2147483647 years 1 decade';
1274 ERROR: interval field value out of range: "2147483647 years 1 decade"
1275 LINE 1: select interval '2147483647 years 1 decade';
1276 ^
1277 select interval '2147483647 years 1 century';
1278 ERROR: interval field value out of range: "2147483647 years 1 century"
1279 LINE 1: select interval '2147483647 years 1 century';
1280 ^
1281 select interval '2147483647 years 1 millennium';
1282 ERROR: interval field value out of range: "2147483647 years 1 millennium"
1283 LINE 1: select interval '2147483647 years 1 millennium';
1284 ^
1285 select interval '-2147483648 years -1 decade';
1286 ERROR: interval field value out of range: "-2147483648 years -1 decade"
1287 LINE 1: select interval '-2147483648 years -1 decade';
1288 ^
1289 select interval '-2147483648 years -1 century';
1290 ERROR: interval field value out of range: "-2147483648 years -1 century"
1291 LINE 1: select interval '-2147483648 years -1 century';
1292 ^
1293 select interval '-2147483648 years -1 millennium';
1294 ERROR: interval field value out of range: "-2147483648 years -1 millennium"
1295 LINE 1: select interval '-2147483648 years -1 millennium';
1296 ^
1297 -- overflowing with fractional fields - postgres format
1298 select interval '0.1 millennium 2147483647 months';
1299 ERROR: interval field value out of range: "0.1 millennium 2147483647 months"
1300 LINE 1: select interval '0.1 millennium 2147483647 months';
1301 ^
1302 select interval '0.1 centuries 2147483647 months';
1303 ERROR: interval field value out of range: "0.1 centuries 2147483647 months"
1304 LINE 1: select interval '0.1 centuries 2147483647 months';
1305 ^
1306 select interval '0.1 decades 2147483647 months';
1307 ERROR: interval field value out of range: "0.1 decades 2147483647 months"
1308 LINE 1: select interval '0.1 decades 2147483647 months';
1309 ^
1310 select interval '0.1 yrs 2147483647 months';
1311 ERROR: interval field value out of range: "0.1 yrs 2147483647 months"
1312 LINE 1: select interval '0.1 yrs 2147483647 months';
1313 ^
1314 select interval '-0.1 millennium -2147483648 months';
1315 ERROR: interval field value out of range: "-0.1 millennium -2147483648 months"
1316 LINE 1: select interval '-0.1 millennium -2147483648 months';
1317 ^
1318 select interval '-0.1 centuries -2147483648 months';
1319 ERROR: interval field value out of range: "-0.1 centuries -2147483648 months"
1320 LINE 1: select interval '-0.1 centuries -2147483648 months';
1321 ^
1322 select interval '-0.1 decades -2147483648 months';
1323 ERROR: interval field value out of range: "-0.1 decades -2147483648 months"
1324 LINE 1: select interval '-0.1 decades -2147483648 months';
1325 ^
1326 select interval '-0.1 yrs -2147483648 months';
1327 ERROR: interval field value out of range: "-0.1 yrs -2147483648 months"
1328 LINE 1: select interval '-0.1 yrs -2147483648 months';
1329 ^
1330 select interval '2147483647 months 0.1 millennium';
1331 ERROR: interval field value out of range: "2147483647 months 0.1 millennium"
1332 LINE 1: select interval '2147483647 months 0.1 millennium';
1333 ^
1334 select interval '2147483647 months 0.1 centuries';
1335 ERROR: interval field value out of range: "2147483647 months 0.1 centuries"
1336 LINE 1: select interval '2147483647 months 0.1 centuries';
1337 ^
1338 select interval '2147483647 months 0.1 decades';
1339 ERROR: interval field value out of range: "2147483647 months 0.1 decades"
1340 LINE 1: select interval '2147483647 months 0.1 decades';
1341 ^
1342 select interval '2147483647 months 0.1 yrs';
1343 ERROR: interval field value out of range: "2147483647 months 0.1 yrs"
1344 LINE 1: select interval '2147483647 months 0.1 yrs';
1345 ^
1346 select interval '-2147483648 months -0.1 millennium';
1347 ERROR: interval field value out of range: "-2147483648 months -0.1 millennium"
1348 LINE 1: select interval '-2147483648 months -0.1 millennium';
1349 ^
1350 select interval '-2147483648 months -0.1 centuries';
1351 ERROR: interval field value out of range: "-2147483648 months -0.1 centuries"
1352 LINE 1: select interval '-2147483648 months -0.1 centuries';
1353 ^
1354 select interval '-2147483648 months -0.1 decades';
1355 ERROR: interval field value out of range: "-2147483648 months -0.1 decades"
1356 LINE 1: select interval '-2147483648 months -0.1 decades';
1357 ^
1358 select interval '-2147483648 months -0.1 yrs';
1359 ERROR: interval field value out of range: "-2147483648 months -0.1 yrs"
1360 LINE 1: select interval '-2147483648 months -0.1 yrs';
1361 ^
1362 select interval '0.1 months 2147483647 days';
1363 ERROR: interval field value out of range: "0.1 months 2147483647 days"
1364 LINE 1: select interval '0.1 months 2147483647 days';
1365 ^
1366 select interval '-0.1 months -2147483648 days';
1367 ERROR: interval field value out of range: "-0.1 months -2147483648 days"
1368 LINE 1: select interval '-0.1 months -2147483648 days';
1369 ^
1370 select interval '2147483647 days 0.1 months';
1371 ERROR: interval field value out of range: "2147483647 days 0.1 months"
1372 LINE 1: select interval '2147483647 days 0.1 months';
1373 ^
1374 select interval '-2147483648 days -0.1 months';
1375 ERROR: interval field value out of range: "-2147483648 days -0.1 months"
1376 LINE 1: select interval '-2147483648 days -0.1 months';
1377 ^
1378 select interval '0.5 weeks 2147483647 days';
1379 ERROR: interval field value out of range: "0.5 weeks 2147483647 days"
1380 LINE 1: select interval '0.5 weeks 2147483647 days';
1381 ^
1382 select interval '-0.5 weeks -2147483648 days';
1383 ERROR: interval field value out of range: "-0.5 weeks -2147483648 days"
1384 LINE 1: select interval '-0.5 weeks -2147483648 days';
1385 ^
1386 select interval '2147483647 days 0.5 weeks';
1387 ERROR: interval field value out of range: "2147483647 days 0.5 weeks"
1388 LINE 1: select interval '2147483647 days 0.5 weeks';
1389 ^
1390 select interval '-2147483648 days -0.5 weeks';
1391 ERROR: interval field value out of range: "-2147483648 days -0.5 weeks"
1392 LINE 1: select interval '-2147483648 days -0.5 weeks';
1393 ^
1394 select interval '0.01 months 9223372036854775807 microseconds';
1395 ERROR: interval field value out of range: "0.01 months 9223372036854775807 microseconds"
1396 LINE 1: select interval '0.01 months 9223372036854775807 microsecond...
1397 ^
1398 select interval '-0.01 months -9223372036854775808 microseconds';
1399 ERROR: interval field value out of range: "-0.01 months -9223372036854775808 microseconds"
1400 LINE 1: select interval '-0.01 months -9223372036854775808 microseco...
1401 ^
1402 select interval '9223372036854775807 microseconds 0.01 months';
1403 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.01 months"
1404 LINE 1: select interval '9223372036854775807 microseconds 0.01 month...
1405 ^
1406 select interval '-9223372036854775808 microseconds -0.01 months';
1407 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.01 months"
1408 LINE 1: select interval '-9223372036854775808 microseconds -0.01 mon...
1409 ^
1410 select interval '0.1 weeks 9223372036854775807 microseconds';
1411 ERROR: interval field value out of range: "0.1 weeks 9223372036854775807 microseconds"
1412 LINE 1: select interval '0.1 weeks 9223372036854775807 microseconds'...
1413 ^
1414 select interval '-0.1 weeks -9223372036854775808 microseconds';
1415 ERROR: interval field value out of range: "-0.1 weeks -9223372036854775808 microseconds"
1416 LINE 1: select interval '-0.1 weeks -9223372036854775808 microsecond...
1417 ^
1418 select interval '9223372036854775807 microseconds 0.1 weeks';
1419 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 weeks"
1420 LINE 1: select interval '9223372036854775807 microseconds 0.1 weeks'...
1421 ^
1422 select interval '-9223372036854775808 microseconds -0.1 weeks';
1423 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 weeks"
1424 LINE 1: select interval '-9223372036854775808 microseconds -0.1 week...
1425 ^
1426 select interval '0.1 days 9223372036854775807 microseconds';
1427 ERROR: interval field value out of range: "0.1 days 9223372036854775807 microseconds"
1428 LINE 1: select interval '0.1 days 9223372036854775807 microseconds';
1429 ^
1430 select interval '-0.1 days -9223372036854775808 microseconds';
1431 ERROR: interval field value out of range: "-0.1 days -9223372036854775808 microseconds"
1432 LINE 1: select interval '-0.1 days -9223372036854775808 microseconds...
1433 ^
1434 select interval '9223372036854775807 microseconds 0.1 days';
1435 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 days"
1436 LINE 1: select interval '9223372036854775807 microseconds 0.1 days';
1437 ^
1438 select interval '-9223372036854775808 microseconds -0.1 days';
1439 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 days"
1440 LINE 1: select interval '-9223372036854775808 microseconds -0.1 days...
1441 ^
1442 -- overflowing with fractional fields - ISO8601 format
1443 select interval 'P0.1Y2147483647M';
1444 ERROR: interval field value out of range: "P0.1Y2147483647M"
1445 LINE 1: select interval 'P0.1Y2147483647M';
1446 ^
1447 select interval 'P-0.1Y-2147483648M';
1448 ERROR: interval field value out of range: "P-0.1Y-2147483648M"
1449 LINE 1: select interval 'P-0.1Y-2147483648M';
1450 ^
1451 select interval 'P2147483647M0.1Y';
1452 ERROR: interval field value out of range: "P2147483647M0.1Y"
1453 LINE 1: select interval 'P2147483647M0.1Y';
1454 ^
1455 select interval 'P-2147483648M-0.1Y';
1456 ERROR: interval field value out of range: "P-2147483648M-0.1Y"
1457 LINE 1: select interval 'P-2147483648M-0.1Y';
1458 ^
1459 select interval 'P0.1M2147483647D';
1460 ERROR: interval field value out of range: "P0.1M2147483647D"
1461 LINE 1: select interval 'P0.1M2147483647D';
1462 ^
1463 select interval 'P-0.1M-2147483648D';
1464 ERROR: interval field value out of range: "P-0.1M-2147483648D"
1465 LINE 1: select interval 'P-0.1M-2147483648D';
1466 ^
1467 select interval 'P2147483647D0.1M';
1468 ERROR: interval field value out of range: "P2147483647D0.1M"
1469 LINE 1: select interval 'P2147483647D0.1M';
1470 ^
1471 select interval 'P-2147483648D-0.1M';
1472 ERROR: interval field value out of range: "P-2147483648D-0.1M"
1473 LINE 1: select interval 'P-2147483648D-0.1M';
1474 ^
1475 select interval 'P0.5W2147483647D';
1476 ERROR: interval field value out of range: "P0.5W2147483647D"
1477 LINE 1: select interval 'P0.5W2147483647D';
1478 ^
1479 select interval 'P-0.5W-2147483648D';
1480 ERROR: interval field value out of range: "P-0.5W-2147483648D"
1481 LINE 1: select interval 'P-0.5W-2147483648D';
1482 ^
1483 select interval 'P2147483647D0.5W';
1484 ERROR: interval field value out of range: "P2147483647D0.5W"
1485 LINE 1: select interval 'P2147483647D0.5W';
1486 ^
1487 select interval 'P-2147483648D-0.5W';
1488 ERROR: interval field value out of range: "P-2147483648D-0.5W"
1489 LINE 1: select interval 'P-2147483648D-0.5W';
1490 ^
1491 select interval 'P0.01MT2562047788H54.775807S';
1492 ERROR: interval field value out of range: "P0.01MT2562047788H54.775807S"
1493 LINE 1: select interval 'P0.01MT2562047788H54.775807S';
1494 ^
1495 select interval 'P-0.01MT-2562047788H-54.775808S';
1496 ERROR: interval field value out of range: "P-0.01MT-2562047788H-54.775808S"
1497 LINE 1: select interval 'P-0.01MT-2562047788H-54.775808S';
1498 ^
1499 select interval 'P0.1DT2562047788H54.775807S';
1500 ERROR: interval field value out of range: "P0.1DT2562047788H54.775807S"
1501 LINE 1: select interval 'P0.1DT2562047788H54.775807S';
1502 ^
1503 select interval 'P-0.1DT-2562047788H-54.775808S';
1504 ERROR: interval field value out of range: "P-0.1DT-2562047788H-54.775808S"
1505 LINE 1: select interval 'P-0.1DT-2562047788H-54.775808S';
1506 ^
1507 select interval 'PT2562047788.1H54.775807S';
1508 ERROR: interval field value out of range: "PT2562047788.1H54.775807S"
1509 LINE 1: select interval 'PT2562047788.1H54.775807S';
1510 ^
1511 select interval 'PT-2562047788.1H-54.775808S';
1512 ERROR: interval field value out of range: "PT-2562047788.1H-54.775808S"
1513 LINE 1: select interval 'PT-2562047788.1H-54.775808S';
1514 ^
1515 select interval 'PT2562047788H0.1M54.775807S';
1516 ERROR: interval field value out of range: "PT2562047788H0.1M54.775807S"
1517 LINE 1: select interval 'PT2562047788H0.1M54.775807S';
1518 ^
1519 select interval 'PT-2562047788H-0.1M-54.775808S';
1520 ERROR: interval field value out of range: "PT-2562047788H-0.1M-54.775808S"
1521 LINE 1: select interval 'PT-2562047788H-0.1M-54.775808S';
1522 ^
1523 -- overflowing with fractional fields - ISO8601 alternative format
1524 select interval 'P0.1-2147483647-00';
1525 ERROR: interval field value out of range: "P0.1-2147483647-00"
1526 LINE 1: select interval 'P0.1-2147483647-00';
1527 ^
1528 select interval 'P00-0.1-2147483647';
1529 ERROR: interval field value out of range: "P00-0.1-2147483647"
1530 LINE 1: select interval 'P00-0.1-2147483647';
1531 ^
1532 select interval 'P00-0.01-00T2562047788:00:54.775807';
1533 ERROR: interval field value out of range: "P00-0.01-00T2562047788:00:54.775807"
1534 LINE 1: select interval 'P00-0.01-00T2562047788:00:54.775807';
1535 ^
1536 select interval 'P00-00-0.1T2562047788:00:54.775807';
1537 ERROR: interval field value out of range: "P00-00-0.1T2562047788:00:54.775807"
1538 LINE 1: select interval 'P00-00-0.1T2562047788:00:54.775807';
1539 ^
1540 select interval 'PT2562047788.1:00:54.775807';
1541 ERROR: interval field value out of range: "PT2562047788.1:00:54.775807"
1542 LINE 1: select interval 'PT2562047788.1:00:54.775807';
1543 ^
1544 select interval 'PT2562047788:01.:54.775807';
1545 ERROR: interval field value out of range: "PT2562047788:01.:54.775807"
1546 LINE 1: select interval 'PT2562047788:01.:54.775807';
1547 ^
1548 -- overflowing with fractional fields - SQL standard format
1549 select interval '0.1 2562047788:0:54.775807';
1550 ERROR: interval field value out of range: "0.1 2562047788:0:54.775807"
1551 LINE 1: select interval '0.1 2562047788:0:54.775807';
1552 ^
1553 select interval '0.1 2562047788:0:54.775808 ago';
1554 ERROR: interval field value out of range: "0.1 2562047788:0:54.775808 ago"
1555 LINE 1: select interval '0.1 2562047788:0:54.775808 ago';
1556 ^
1557 select interval '2562047788.1:0:54.775807';
1558 ERROR: interval field value out of range: "2562047788.1:0:54.775807"
1559 LINE 1: select interval '2562047788.1:0:54.775807';
1560 ^
1561 select interval '2562047788.1:0:54.775808 ago';
1562 ERROR: interval field value out of range: "2562047788.1:0:54.775808 ago"
1563 LINE 1: select interval '2562047788.1:0:54.775808 ago';
1564 ^
1565 select interval '2562047788:0.1:54.775807';
1566 ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775807"
1567 LINE 1: select interval '2562047788:0.1:54.775807';
1568 ^
1569 select interval '2562047788:0.1:54.775808 ago';
1570 ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775808 ago"
1571 LINE 1: select interval '2562047788:0.1:54.775808 ago';
1572 ^
1573 -- overflowing using AGO with INT_MIN
1574 select interval '-2147483648 months ago';
1575 ERROR: interval field value out of range: "-2147483648 months ago"
1576 LINE 1: select interval '-2147483648 months ago';
1577 ^
1578 select interval '-2147483648 days ago';
1579 ERROR: interval field value out of range: "-2147483648 days ago"
1580 LINE 1: select interval '-2147483648 days ago';
1581 ^
1582 select interval '-9223372036854775808 microseconds ago';
1583 ERROR: interval field value out of range: "-9223372036854775808 microseconds ago"
1584 LINE 1: select interval '-9223372036854775808 microseconds ago';
1585 ^
1586 select interval '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago';
1587 ERROR: interval field value out of range: "-2147483648 months -2147483648 days -9223372036854775808 microseconds ago"
1588 LINE 1: select interval '-2147483648 months -2147483648 days -922337...
1589 ^
1590 -- test that INT_MIN number is formatted properly
1591 SET IntervalStyle to postgres;
1592 select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
1593 interval
1594 --------------------------------------------------------------------
1595 -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808
1596 (1 row)
1598 SET IntervalStyle to sql_standard;
1599 select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
1600 interval
1601 ---------------------------------------------------
1602 -178956970-8 -2147483648 -2562047788:00:54.775808
1603 (1 row)
1605 SET IntervalStyle to iso_8601;
1606 select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
1607 interval
1608 -----------------------------------------------------
1609 P-178956970Y-8M-2147483648DT-2562047788H-54.775808S
1610 (1 row)
1612 SET IntervalStyle to postgres_verbose;
1613 select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
1614 interval
1615 ------------------------------------------------------------------------------
1616 @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago
1617 (1 row)
1619 -- check that '30 days' equals '1 month' according to the hash function
1620 select '30 days'::interval = '1 month'::interval as t;
1621 t
1622 ---
1623 t
1624 (1 row)
1626 select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t;
1627 t
1628 ---
1629 t
1630 (1 row)
1632 -- numeric constructor
1633 select make_interval(years := 2);
1634 make_interval
1635 ---------------
1636 @ 2 years
1637 (1 row)
1639 select make_interval(years := 1, months := 6);
1640 make_interval
1641 -----------------
1642 @ 1 year 6 mons
1643 (1 row)
1645 select make_interval(years := 1, months := -1, weeks := 5, days := -7, hours := 25, mins := -180);
1646 make_interval
1647 ----------------------------
1648 @ 11 mons 28 days 22 hours
1649 (1 row)
1651 select make_interval() = make_interval(years := 0, months := 0, weeks := 0, days := 0, mins := 0, secs := 0.0);
1652 ?column?
1653 ----------
1654 t
1655 (1 row)
1657 select make_interval(hours := -2, mins := -10, secs := -25.3);
1658 make_interval
1659 ---------------------------------
1660 @ 2 hours 10 mins 25.3 secs ago
1661 (1 row)
1663 select make_interval(years := 'inf'::float::int);
1664 ERROR: integer out of range
1665 select make_interval(months := 'NaN'::float::int);
1666 ERROR: integer out of range
1667 select make_interval(secs := 'inf');
1668 ERROR: interval out of range
1669 select make_interval(secs := 'NaN');
1670 ERROR: interval out of range
1671 select make_interval(secs := 7e12);
1672 make_interval
1673 ------------------------------------
1674 @ 1944444444 hours 26 mins 40 secs
1675 (1 row)
1677 --
1678 -- test EXTRACT
1679 --
1680 SELECT f1,
1681 EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
1682 EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
1683 EXTRACT(SECOND FROM f1) AS SECOND,
1684 EXTRACT(MINUTE FROM f1) AS MINUTE,
1685 EXTRACT(HOUR FROM f1) AS HOUR,
1686 EXTRACT(DAY FROM f1) AS DAY,
1687 EXTRACT(MONTH FROM f1) AS MONTH,
1688 EXTRACT(QUARTER FROM f1) AS QUARTER,
1689 EXTRACT(YEAR FROM f1) AS YEAR,
1690 EXTRACT(DECADE FROM f1) AS DECADE,
1691 EXTRACT(CENTURY FROM f1) AS CENTURY,
1692 EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
1693 EXTRACT(EPOCH FROM f1) AS EPOCH
1694 FROM INTERVAL_TBL;
1695 f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
1696 -------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
1697 @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
1698 @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
1699 @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
1700 @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
1701 @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
1702 @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
1703 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
1704 @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
1705 @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
1706 @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
1707 (10 rows)
1709 SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
1710 ERROR: unit "fortnight" not recognized for type interval
1711 SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
1712 ERROR: unit "timezone" not supported for type interval
1713 SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
1714 extract
1715 ---------
1716 10
1717 (1 row)
1719 SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
1720 extract
1721 ---------
1722 9
1723 (1 row)
1725 SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
1726 extract
1727 ---------
1728 -9
1729 (1 row)
1731 SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
1732 extract
1733 ---------
1734 -10
1735 (1 row)
1737 SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
1738 extract
1739 ---------
1740 1
1741 (1 row)
1743 SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
1744 extract
1745 ---------
1746 0
1747 (1 row)
1749 SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
1750 extract
1751 ---------
1752 0
1753 (1 row)
1755 SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
1756 extract
1757 ---------
1758 -1
1759 (1 row)
1761 -- date_part implementation is mostly the same as extract, so only
1762 -- test a few cases for additional coverage.
1763 SELECT f1,
1764 date_part('microsecond', f1) AS microsecond,
1765 date_part('millisecond', f1) AS millisecond,
1766 date_part('second', f1) AS second,
1767 date_part('epoch', f1) AS epoch
1768 FROM INTERVAL_TBL;
1769 f1 | microsecond | millisecond | second | epoch
1770 -------------------------------+-------------+-------------+--------+------------
1771 @ 1 min | 0 | 0 | 0 | 60
1772 @ 5 hours | 0 | 0 | 0 | 18000
1773 @ 10 days | 0 | 0 | 0 | 864000
1774 @ 34 years | 0 | 0 | 0 | 1072958400
1775 @ 3 mons | 0 | 0 | 0 | 7776000
1776 @ 14 secs ago | -14000000 | -14000 | -14 | -14
1777 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 93784
1778 @ 6 years | 0 | 0 | 0 | 189345600
1779 @ 5 mons | 0 | 0 | 0 | 12960000
1780 @ 5 mons 12 hours | 0 | 0 | 0 | 13003200
1781 (10 rows)
1783 -- internal overflow test case
1784 SELECT extract(epoch from interval '1000000000 days');
1785 extract
1786 -----------------------
1787 86400000000000.000000
1788 (1 row)
1790 -- "ago" can only appear once at the end of an interval.
1791 SELECT INTERVAL '42 days 2 seconds ago ago';
1792 ERROR: invalid input syntax for type interval: "42 days 2 seconds ago ago"
1793 LINE 1: SELECT INTERVAL '42 days 2 seconds ago ago';
1794 ^
1795 SELECT INTERVAL '2 minutes ago 5 days';
1796 ERROR: invalid input syntax for type interval: "2 minutes ago 5 days"
1797 LINE 1: SELECT INTERVAL '2 minutes ago 5 days';
1798 ^
1799 -- consecutive and dangling units are not allowed.
1800 SELECT INTERVAL 'hour 5 months';
1801 ERROR: invalid input syntax for type interval: "hour 5 months"
1802 LINE 1: SELECT INTERVAL 'hour 5 months';
1803 ^
1804 SELECT INTERVAL '1 year months days 5 hours';
1805 ERROR: invalid input syntax for type interval: "1 year months days 5 hours"
1806 LINE 1: SELECT INTERVAL '1 year months days 5 hours';
1807 ^