| blob | e5d919d0cf025e4d8963f8730b51bd3c6512f8f1 |
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 SELECT INTERVAL 'infinity' AS "eternity";
56 eternity
57 ----------
58 infinity
59 (1 row)
61 SELECT INTERVAL '-infinity' AS "beginning of time";
62 beginning of time
63 -------------------
64 -infinity
65 (1 row)
67 CREATE TABLE INTERVAL_TBL (f1 interval);
68 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
69 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
70 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 10 day');
71 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 34 year');
72 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 3 months');
73 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 14 seconds ago');
74 INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
75 INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
76 INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
77 INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
78 INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
79 INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
80 -- badly formatted interval
81 INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
82 ERROR: invalid input syntax for type interval: "badly formatted interval"
83 LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted inter...
84 ^
85 INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
86 ERROR: invalid input syntax for type interval: "@ 30 eons ago"
87 LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
88 ^
89 -- Test non-error-throwing API
90 SELECT pg_input_is_valid('1.5 weeks', 'interval');
91 pg_input_is_valid
92 -------------------
93 t
94 (1 row)
96 SELECT pg_input_is_valid('garbage', 'interval');
97 pg_input_is_valid
98 -------------------
99 f
100 (1 row)
102 SELECT pg_input_is_valid('@ 30 eons ago', 'interval');
103 pg_input_is_valid
104 -------------------
105 f
106 (1 row)
108 SELECT * FROM pg_input_error_info('garbage', 'interval');
109 message | detail | hint | sql_error_code
110 ---------------------------------------------------+--------+------+----------------
111 invalid input syntax for type interval: "garbage" | | | 22007
112 (1 row)
114 SELECT * FROM pg_input_error_info('@ 30 eons ago', 'interval');
115 message | detail | hint | sql_error_code
116 ---------------------------------------------------------+--------+------+----------------
117 invalid input syntax for type interval: "@ 30 eons ago" | | | 22007
118 (1 row)
120 -- test interval operators
121 SELECT * FROM INTERVAL_TBL;
122 f1
123 -----------------
124 00:01:00
125 05:00:00
126 10 days
127 34 years
128 3 mons
129 -00:00:14
130 1 day 02:03:04
131 6 years
132 5 mons
133 5 mons 12:00:00
134 infinity
135 -infinity
136 (12 rows)
138 SELECT * FROM INTERVAL_TBL
139 WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
140 f1
141 -----------------
142 00:01:00
143 05:00:00
144 34 years
145 3 mons
146 -00:00:14
147 1 day 02:03:04
148 6 years
149 5 mons
150 5 mons 12:00:00
151 infinity
152 -infinity
153 (11 rows)
155 SELECT * FROM INTERVAL_TBL
156 WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
157 f1
158 -----------
159 00:01:00
160 05:00:00
161 -00:00:14
162 -infinity
163 (4 rows)
165 SELECT * FROM INTERVAL_TBL
166 WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
167 f1
168 -----------
169 00:01:00
170 05:00:00
171 -00:00:14
172 -infinity
173 (4 rows)
175 SELECT * FROM INTERVAL_TBL
176 WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
177 f1
178 ----------
179 34 years
180 (1 row)
182 SELECT * FROM INTERVAL_TBL
183 WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
184 f1
185 -----------------
186 34 years
187 3 mons
188 6 years
189 5 mons
190 5 mons 12:00:00
191 infinity
192 (6 rows)
194 SELECT * FROM INTERVAL_TBL
195 WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
196 f1
197 -----------------
198 00:01:00
199 05:00:00
200 10 days
201 34 years
202 3 mons
203 1 day 02:03:04
204 6 years
205 5 mons
206 5 mons 12:00:00
207 infinity
208 (10 rows)
210 SELECT r1.*, r2.*
211 FROM INTERVAL_TBL r1, INTERVAL_TBL r2
212 WHERE r1.f1 > r2.f1
213 ORDER BY r1.f1, r2.f1;
214 f1 | f1
215 -----------------+-----------------
216 -00:00:14 | -infinity
217 00:01:00 | -infinity
218 00:01:00 | -00:00:14
219 05:00:00 | -infinity
220 05:00:00 | -00:00:14
221 05:00:00 | 00:01:00
222 1 day 02:03:04 | -infinity
223 1 day 02:03:04 | -00:00:14
224 1 day 02:03:04 | 00:01:00
225 1 day 02:03:04 | 05:00:00
226 10 days | -infinity
227 10 days | -00:00:14
228 10 days | 00:01:00
229 10 days | 05:00:00
230 10 days | 1 day 02:03:04
231 3 mons | -infinity
232 3 mons | -00:00:14
233 3 mons | 00:01:00
234 3 mons | 05:00:00
235 3 mons | 1 day 02:03:04
236 3 mons | 10 days
237 5 mons | -infinity
238 5 mons | -00:00:14
239 5 mons | 00:01:00
240 5 mons | 05:00:00
241 5 mons | 1 day 02:03:04
242 5 mons | 10 days
243 5 mons | 3 mons
244 5 mons 12:00:00 | -infinity
245 5 mons 12:00:00 | -00:00:14
246 5 mons 12:00:00 | 00:01:00
247 5 mons 12:00:00 | 05:00:00
248 5 mons 12:00:00 | 1 day 02:03:04
249 5 mons 12:00:00 | 10 days
250 5 mons 12:00:00 | 3 mons
251 5 mons 12:00:00 | 5 mons
252 6 years | -infinity
253 6 years | -00:00:14
254 6 years | 00:01:00
255 6 years | 05:00:00
256 6 years | 1 day 02:03:04
257 6 years | 10 days
258 6 years | 3 mons
259 6 years | 5 mons
260 6 years | 5 mons 12:00:00
261 34 years | -infinity
262 34 years | -00:00:14
263 34 years | 00:01:00
264 34 years | 05:00:00
265 34 years | 1 day 02:03:04
266 34 years | 10 days
267 34 years | 3 mons
268 34 years | 5 mons
269 34 years | 5 mons 12:00:00
270 34 years | 6 years
271 infinity | -infinity
272 infinity | -00:00:14
273 infinity | 00:01:00
274 infinity | 05:00:00
275 infinity | 1 day 02:03:04
276 infinity | 10 days
277 infinity | 3 mons
278 infinity | 5 mons
279 infinity | 5 mons 12:00:00
280 infinity | 6 years
281 infinity | 34 years
282 (66 rows)
284 -- test unary minus
285 SELECT f1, -f1 FROM INTERVAL_TBL;
286 f1 | ?column?
287 -----------------+-------------------
288 00:01:00 | -00:01:00
289 05:00:00 | -05:00:00
290 10 days | -10 days
291 34 years | -34 years
292 3 mons | -3 mons
293 -00:00:14 | 00:00:14
294 1 day 02:03:04 | -1 days -02:03:04
295 6 years | -6 years
296 5 mons | -5 mons
297 5 mons 12:00:00 | -5 mons -12:00:00
298 infinity | -infinity
299 -infinity | infinity
300 (12 rows)
302 SELECT -('-2147483648 months'::interval); -- should fail
303 ERROR: interval out of range
304 SELECT -('-2147483647 months'::interval); -- ok
305 ?column?
306 ------------------------
307 178956970 years 7 mons
308 (1 row)
310 SELECT -('-2147483648 days'::interval); -- should fail
311 ERROR: interval out of range
312 SELECT -('-2147483647 days'::interval); -- ok
313 ?column?
314 -----------------
315 2147483647 days
316 (1 row)
318 SELECT -('-9223372036854775808 us'::interval); -- should fail
319 ERROR: interval out of range
320 SELECT -('-9223372036854775807 us'::interval); -- ok
321 ?column?
322 -------------------------
323 2562047788:00:54.775807
324 (1 row)
326 SELECT -('-2147483647 months -2147483647 days -9223372036854775807 us'::interval); -- should fail
327 ERROR: interval out of range
328 -- Test intervals that are large enough to overflow 64 bits in comparisons
329 CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
330 INSERT INTO INTERVAL_TBL_OF (f1) VALUES
331 ('2147483647 days 2147483647 months'),
332 ('2147483647 days -2147483648 months'),
333 ('1 year'),
334 ('-2147483648 days 2147483647 months'),
335 ('-2147483648 days -2147483648 months');
336 -- these should fail as out-of-range
337 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
338 ERROR: interval field value out of range: "2147483648 days"
339 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
340 ^
341 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days');
342 ERROR: interval field value out of range: "-2147483649 days"
343 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days')...
344 ^
345 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years');
346 ERROR: interval out of range
347 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years')...
348 ^
349 INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years');
350 ERROR: interval out of range
351 LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'...
352 ^
353 -- Test edge-case overflow detection in interval multiplication
354 select extract(epoch from '256 microseconds'::interval * (2^55)::float8);
355 ERROR: interval out of range
356 SELECT r1.*, r2.*
357 FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2
358 WHERE r1.f1 > r2.f1
359 ORDER BY r1.f1, r2.f1;
360 f1 | f1
361 -------------------------------------------+-------------------------------------------
362 -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days
363 1 year | -178956970 years -8 mons -2147483648 days
364 1 year | -178956970 years -8 mons +2147483647 days
365 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons -2147483648 days
366 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons +2147483647 days
367 178956970 years 7 mons -2147483648 days | 1 year
368 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons -2147483648 days
369 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons +2147483647 days
370 178956970 years 7 mons 2147483647 days | 1 year
371 178956970 years 7 mons 2147483647 days | 178956970 years 7 mons -2147483648 days
372 (10 rows)
374 CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1);
375 SET enable_seqscan TO false;
376 EXPLAIN (COSTS OFF)
377 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
378 QUERY PLAN
379 --------------------------------------------------------------------
380 Index Only Scan using interval_tbl_of_f1_idx on interval_tbl_of r1
381 (1 row)
383 SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
384 f1
385 -------------------------------------------
386 -178956970 years -8 mons -2147483648 days
387 -178956970 years -8 mons +2147483647 days
388 1 year
389 178956970 years 7 mons -2147483648 days
390 178956970 years 7 mons 2147483647 days
391 (5 rows)
393 RESET enable_seqscan;
394 -- subtracting about-to-overflow values should result in 0
395 SELECT f1 - f1 FROM INTERVAL_TBL_OF;
396 ?column?
397 ----------
398 00:00:00
399 00:00:00
400 00:00:00
401 00:00:00
402 00:00:00
403 (5 rows)
405 DROP TABLE INTERVAL_TBL_OF;
406 -- Test multiplication and division with intervals.
407 -- Floating point arithmetic rounding errors can lead to unexpected results,
408 -- though the code attempts to do the right thing and round up to days and
409 -- minutes to avoid results such as '3 days 24:00 hours' or '14:20:60'.
410 -- Note that it is expected for some day components to be greater than 29 and
411 -- some time components be greater than 23:59:59 due to how intervals are
412 -- stored internally.
413 CREATE TABLE INTERVAL_MULDIV_TBL (span interval);
414 COPY INTERVAL_MULDIV_TBL FROM STDIN;
415 SELECT span * 0.3 AS product
416 FROM INTERVAL_MULDIV_TBL;
417 product
418 ------------------------------------
419 1 year 12 days 122:24:00
420 -1 years -12 days +93:36:00
421 -3 days -14:24:00
422 2 mons 13 days 01:22:28.8
423 -10 mons +120 days 37:28:21.6567
424 1 mon 6 days
425 4 mons 6 days
426 24 years 11 mons 320 days 16:48:00
427 (8 rows)
429 SELECT span * 8.2 AS product
430 FROM INTERVAL_MULDIV_TBL;
431 product
432 ---------------------------------------------
433 28 years 104 days 2961:36:00
434 -28 years -104 days +2942:24:00
435 -98 days -09:36:00
436 6 years 1 mon -197 days +93:34:27.2
437 -24 years -7 mons +3946 days 640:15:11.9498
438 2 years 8 mons 24 days
439 9 years 6 mons 24 days
440 682 years 7 mons 8215 days 19:12:00
441 (8 rows)
443 SELECT span / 10 AS quotient
444 FROM INTERVAL_MULDIV_TBL;
445 quotient
446 ----------------------------------
447 4 mons 4 days 40:48:00
448 -4 mons -4 days +31:12:00
449 -1 days -04:48:00
450 25 days -15:32:30.4
451 -3 mons +30 days 12:29:27.2189
452 12 days
453 1 mon 12 days
454 8 years 3 mons 126 days 21:36:00
455 (8 rows)
457 SELECT span / 100 AS quotient
458 FROM INTERVAL_MULDIV_TBL;
459 quotient
460 -------------------------
461 12 days 13:40:48
462 -12 days -06:28:48
463 -02:52:48
464 2 days 10:26:44.96
465 -6 days +01:14:56.72189
466 1 day 04:48:00
467 4 days 04:48:00
468 9 mons 39 days 16:33:36
469 (8 rows)
471 DROP TABLE INTERVAL_MULDIV_TBL;
472 SET DATESTYLE = 'postgres';
473 SET IntervalStyle to postgres_verbose;
474 SELECT * FROM INTERVAL_TBL;
475 f1
476 -------------------------------
477 @ 1 min
478 @ 5 hours
479 @ 10 days
480 @ 34 years
481 @ 3 mons
482 @ 14 secs ago
483 @ 1 day 2 hours 3 mins 4 secs
484 @ 6 years
485 @ 5 mons
486 @ 5 mons 12 hours
487 infinity
488 -infinity
489 (12 rows)
491 -- multiplication and division overflow test cases
492 SELECT '3000000 months'::interval * 1000;
493 ERROR: interval out of range
494 SELECT '3000000 months'::interval / 0.001;
495 ERROR: interval out of range
496 SELECT '3000000 days'::interval * 1000;
497 ERROR: interval out of range
498 SELECT '3000000 days'::interval / 0.001;
499 ERROR: interval out of range
500 SELECT '1 month 2146410 days'::interval * 1000.5002;
501 ERROR: interval out of range
502 SELECT '4611686018427387904 usec'::interval / 0.1;
503 ERROR: interval out of range
504 -- test avg(interval), which is somewhat fragile since people have been
505 -- known to change the allowed input syntax for type interval without
506 -- updating pg_aggregate.agginitval
507 select avg(f1) from interval_tbl where isfinite(f1);
508 avg
509 -------------------------------------------------
510 @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
511 (1 row)
513 -- test long interval input
514 select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
515 interval
516 --------------------------------------------
517 @ 4541 years 4 mons 4 days 17 mins 31 secs
518 (1 row)
520 -- test long interval output
521 -- Note: the actual maximum length of the interval output is longer,
522 -- but we need the test to work for both integer and floating-point
523 -- timestamps.
524 select '100000000y 10mon -1000000000d -100000h -10min -10.000001s ago'::interval;
525 interval
526 ---------------------------------------------------------------------------------------
527 @ 100000000 years 10 mons -1000000000 days -100000 hours -10 mins -10.000001 secs ago
528 (1 row)
530 -- test justify_hours() and justify_days()
531 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";
532 6 mons 5 days 4 hours 3 mins 2 seconds
533 ----------------------------------------
534 @ 6 mons 5 days 4 hours 3 mins 2 secs
535 (1 row)
537 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";
538 7 mons 6 days 5 hours 4 mins 3 seconds
539 ----------------------------------------
540 @ 7 mons 6 days 5 hours 4 mins 3 secs
541 (1 row)
543 SELECT justify_hours(interval '2147483647 days 24 hrs');
544 ERROR: interval out of range
545 SELECT justify_days(interval '2147483647 months 30 days');
546 ERROR: interval out of range
547 -- test justify_interval()
548 SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
549 1 month -1 hour
550 --------------------
551 @ 29 days 23 hours
552 (1 row)
554 SELECT justify_interval(interval '2147483647 days 24 hrs');
555 justify_interval
556 -------------------------------
557 @ 5965232 years 4 mons 8 days
558 (1 row)
560 SELECT justify_interval(interval '-2147483648 days -24 hrs');
561 justify_interval
562 -----------------------------------
563 @ 5965232 years 4 mons 9 days ago
564 (1 row)
566 SELECT justify_interval(interval '2147483647 months 30 days');
567 ERROR: interval out of range
568 SELECT justify_interval(interval '-2147483648 months -30 days');
569 ERROR: interval out of range
570 SELECT justify_interval(interval '2147483647 months 30 days -24 hrs');
571 justify_interval
572 ----------------------------------
573 @ 178956970 years 7 mons 29 days
574 (1 row)
576 SELECT justify_interval(interval '-2147483648 months -30 days 24 hrs');
577 justify_interval
578 --------------------------------------
579 @ 178956970 years 8 mons 29 days ago
580 (1 row)
582 SELECT justify_interval(interval '2147483647 months -30 days 1440 hrs');
583 ERROR: interval out of range
584 SELECT justify_interval(interval '-2147483648 months 30 days -1440 hrs');
585 ERROR: interval out of range
586 -- test fractional second input, and detection of duplicate units
587 SET DATESTYLE = 'ISO';
588 SET IntervalStyle TO postgres;
589 SELECT '1 millisecond'::interval, '1 microsecond'::interval,
590 '500 seconds 99 milliseconds 51 microseconds'::interval;
591 interval | interval | interval
592 --------------+-----------------+-----------------
593 00:00:00.001 | 00:00:00.000001 | 00:08:20.099051
594 (1 row)
596 SELECT '3 days 5 milliseconds'::interval;
597 interval
598 ---------------------
599 3 days 00:00:00.005
600 (1 row)
602 SELECT '1 second 2 seconds'::interval; -- error
603 ERROR: invalid input syntax for type interval: "1 second 2 seconds"
604 LINE 1: SELECT '1 second 2 seconds'::interval;
605 ^
606 SELECT '10 milliseconds 20 milliseconds'::interval; -- error
607 ERROR: invalid input syntax for type interval: "10 milliseconds 20 milliseconds"
608 LINE 1: SELECT '10 milliseconds 20 milliseconds'::interval;
609 ^
610 SELECT '5.5 seconds 3 milliseconds'::interval; -- error
611 ERROR: invalid input syntax for type interval: "5.5 seconds 3 milliseconds"
612 LINE 1: SELECT '5.5 seconds 3 milliseconds'::interval;
613 ^
614 SELECT '1:20:05 5 microseconds'::interval; -- error
615 ERROR: invalid input syntax for type interval: "1:20:05 5 microseconds"
616 LINE 1: SELECT '1:20:05 5 microseconds'::interval;
617 ^
618 SELECT '1 day 1 day'::interval; -- error
619 ERROR: invalid input syntax for type interval: "1 day 1 day"
620 LINE 1: SELECT '1 day 1 day'::interval;
621 ^
622 SELECT interval '1-2'; -- SQL year-month literal
623 interval
624 ---------------
625 1 year 2 mons
626 (1 row)
628 SELECT interval '999' second; -- oversize leading field is ok
629 interval
630 ----------
631 00:16:39
632 (1 row)
634 SELECT interval '999' minute;
635 interval
636 ----------
637 16:39:00
638 (1 row)
640 SELECT interval '999' hour;
641 interval
642 -----------
643 999:00:00
644 (1 row)
646 SELECT interval '999' day;
647 interval
648 ----------
649 999 days
650 (1 row)
652 SELECT interval '999' month;
653 interval
654 -----------------
655 83 years 3 mons
656 (1 row)
658 -- test SQL-spec syntaxes for restricted field sets
659 SELECT interval '1' year;
660 interval
661 ----------
662 1 year
663 (1 row)
665 SELECT interval '2' month;
666 interval
667 ----------
668 2 mons
669 (1 row)
671 SELECT interval '3' day;
672 interval
673 ----------
674 3 days
675 (1 row)
677 SELECT interval '4' hour;
678 interval
679 ----------
680 04:00:00
681 (1 row)
683 SELECT interval '5' minute;
684 interval
685 ----------
686 00:05:00
687 (1 row)
689 SELECT interval '6' second;
690 interval
691 ----------
692 00:00:06
693 (1 row)
695 SELECT interval '1' year to month;
696 interval
697 ----------
698 1 mon
699 (1 row)
701 SELECT interval '1-2' year to month;
702 interval
703 ---------------
704 1 year 2 mons
705 (1 row)
707 SELECT interval '1 2' day to hour;
708 interval
709 ----------------
710 1 day 02:00:00
711 (1 row)
713 SELECT interval '1 2:03' day to hour;
714 interval
715 ----------------
716 1 day 02:00:00
717 (1 row)
719 SELECT interval '1 2:03:04' day to hour;
720 interval
721 ----------------
722 1 day 02:00:00
723 (1 row)
725 SELECT interval '1 2' day to minute;
726 ERROR: invalid input syntax for type interval: "1 2"
727 LINE 1: SELECT interval '1 2' day to minute;
728 ^
729 SELECT interval '1 2:03' day to minute;
730 interval
731 ----------------
732 1 day 02:03:00
733 (1 row)
735 SELECT interval '1 2:03:04' day to minute;
736 interval
737 ----------------
738 1 day 02:03:00
739 (1 row)
741 SELECT interval '1 2' day to second;
742 ERROR: invalid input syntax for type interval: "1 2"
743 LINE 1: SELECT interval '1 2' day to second;
744 ^
745 SELECT interval '1 2:03' day to second;
746 interval
747 ----------------
748 1 day 02:03:00
749 (1 row)
751 SELECT interval '1 2:03:04' day to second;
752 interval
753 ----------------
754 1 day 02:03:04
755 (1 row)
757 SELECT interval '1 2' hour to minute;
758 ERROR: invalid input syntax for type interval: "1 2"
759 LINE 1: SELECT interval '1 2' hour to minute;
760 ^
761 SELECT interval '1 2:03' hour to minute;
762 interval
763 ----------------
764 1 day 02:03:00
765 (1 row)
767 SELECT interval '1 2:03:04' hour to minute;
768 interval
769 ----------------
770 1 day 02:03:00
771 (1 row)
773 SELECT interval '1 2' hour to second;
774 ERROR: invalid input syntax for type interval: "1 2"
775 LINE 1: SELECT interval '1 2' hour to second;
776 ^
777 SELECT interval '1 2:03' hour to second;
778 interval
779 ----------------
780 1 day 02:03:00
781 (1 row)
783 SELECT interval '1 2:03:04' hour to second;
784 interval
785 ----------------
786 1 day 02:03:04
787 (1 row)
789 SELECT interval '1 2' minute to second;
790 ERROR: invalid input syntax for type interval: "1 2"
791 LINE 1: SELECT interval '1 2' minute to second;
792 ^
793 SELECT interval '1 2:03' minute to second;
794 interval
795 ----------------
796 1 day 00:02:03
797 (1 row)
799 SELECT interval '1 2:03:04' minute to second;
800 interval
801 ----------------
802 1 day 02:03:04
803 (1 row)
805 SELECT interval '1 +2:03' minute to second;
806 interval
807 ----------------
808 1 day 00:02:03
809 (1 row)
811 SELECT interval '1 +2:03:04' minute to second;
812 interval
813 ----------------
814 1 day 02:03:04
815 (1 row)
817 SELECT interval '1 -2:03' minute to second;
818 interval
819 -----------------
820 1 day -00:02:03
821 (1 row)
823 SELECT interval '1 -2:03:04' minute to second;
824 interval
825 -----------------
826 1 day -02:03:04
827 (1 row)
829 SELECT interval '123 11' day to hour; -- ok
830 interval
831 -------------------
832 123 days 11:00:00
833 (1 row)
835 SELECT interval '123 11' day; -- not ok
836 ERROR: invalid input syntax for type interval: "123 11"
837 LINE 1: SELECT interval '123 11' day;
838 ^
839 SELECT interval '123 11'; -- not ok, too ambiguous
840 ERROR: invalid input syntax for type interval: "123 11"
841 LINE 1: SELECT interval '123 11';
842 ^
843 SELECT interval '123 2:03 -2:04'; -- not ok, redundant hh:mm fields
844 ERROR: invalid input syntax for type interval: "123 2:03 -2:04"
845 LINE 1: SELECT interval '123 2:03 -2:04';
846 ^
847 -- test syntaxes for restricted precision
848 SELECT interval(0) '1 day 01:23:45.6789';
849 interval
850 ----------------
851 1 day 01:23:46
852 (1 row)
854 SELECT interval(2) '1 day 01:23:45.6789';
855 interval
856 -------------------
857 1 day 01:23:45.68
858 (1 row)
860 SELECT interval '12:34.5678' minute to second(2); -- per SQL spec
861 interval
862 -------------
863 00:12:34.57
864 (1 row)
866 SELECT interval '1.234' second;
867 interval
868 --------------
869 00:00:01.234
870 (1 row)
872 SELECT interval '1.234' second(2);
873 interval
874 -------------
875 00:00:01.23
876 (1 row)
878 SELECT interval '1 2.345' day to second(2);
879 ERROR: invalid input syntax for type interval: "1 2.345"
880 LINE 1: SELECT interval '1 2.345' day to second(2);
881 ^
882 SELECT interval '1 2:03' day to second(2);
883 interval
884 ----------------
885 1 day 02:03:00
886 (1 row)
888 SELECT interval '1 2:03.4567' day to second(2);
889 interval
890 -------------------
891 1 day 00:02:03.46
892 (1 row)
894 SELECT interval '1 2:03:04.5678' day to second(2);
895 interval
896 -------------------
897 1 day 02:03:04.57
898 (1 row)
900 SELECT interval '1 2.345' hour to second(2);
901 ERROR: invalid input syntax for type interval: "1 2.345"
902 LINE 1: SELECT interval '1 2.345' hour to second(2);
903 ^
904 SELECT interval '1 2:03.45678' hour to second(2);
905 interval
906 -------------------
907 1 day 00:02:03.46
908 (1 row)
910 SELECT interval '1 2:03:04.5678' hour to second(2);
911 interval
912 -------------------
913 1 day 02:03:04.57
914 (1 row)
916 SELECT interval '1 2.3456' minute to second(2);
917 ERROR: invalid input syntax for type interval: "1 2.3456"
918 LINE 1: SELECT interval '1 2.3456' minute to second(2);
919 ^
920 SELECT interval '1 2:03.5678' minute to second(2);
921 interval
922 -------------------
923 1 day 00:02:03.57
924 (1 row)
926 SELECT interval '1 2:03:04.5678' minute to second(2);
927 interval
928 -------------------
929 1 day 02:03:04.57
930 (1 row)
932 SELECT interval '2562047788:00:54.775807' second(2); -- out of range
933 ERROR: interval out of range
934 LINE 1: SELECT interval '2562047788:00:54.775807' second(2);
935 ^
936 SELECT interval '-2562047788:00:54.775807' second(2); -- out of range
937 ERROR: interval out of range
938 LINE 1: SELECT interval '-2562047788:00:54.775807' second(2);
939 ^
940 -- test casting to restricted precision (bug #14479)
941 SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
942 (f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
943 FROM interval_tbl;
944 f1 | minutes | years
945 -----------------+-----------------+-----------
946 00:01:00 | 00:01:00 | 00:00:00
947 05:00:00 | 05:00:00 | 00:00:00
948 10 days | 10 days | 00:00:00
949 34 years | 34 years | 34 years
950 3 mons | 3 mons | 00:00:00
951 -00:00:14 | 00:00:00 | 00:00:00
952 1 day 02:03:04 | 1 day 02:03:00 | 00:00:00
953 6 years | 6 years | 6 years
954 5 mons | 5 mons | 00:00:00
955 5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
956 infinity | infinity | infinity
957 -infinity | -infinity | -infinity
958 (12 rows)
960 -- test inputting and outputting SQL standard interval literals
961 SET IntervalStyle TO sql_standard;
962 SELECT interval '0' AS "zero",
963 interval '1-2' year to month AS "year-month",
964 interval '1 2:03:04' day to second AS "day-time",
965 - interval '1-2' AS "negative year-month",
966 - interval '1 2:03:04' AS "negative day-time";
967 zero | year-month | day-time | negative year-month | negative day-time
968 ------+------------+-----------+---------------------+-------------------
969 0 | 1-2 | 1 2:03:04 | -1-2 | -1 2:03:04
970 (1 row)
972 -- test input of some not-quite-standard interval values in the sql style
973 SET IntervalStyle TO postgres;
974 SELECT interval '+1 -1:00:00',
975 interval '-1 +1:00:00',
976 interval '+1-2 -3 +4:05:06.789',
977 interval '-1-2 +3 -4:05:06.789';
978 interval | interval | interval | interval
979 -----------------+-------------------+-------------------------------------+----------------------------------------
980 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
981 (1 row)
983 -- cases that trigger sign-matching rules in the sql style
984 SELECT interval '-23 hours 45 min 12.34 sec',
985 interval '-1 day 23 hours 45 min 12.34 sec',
986 interval '-1 year 2 months 1 day 23 hours 45 min 12.34 sec',
987 interval '-1 year 2 months 1 day 23 hours 45 min +12.34 sec';
988 interval | interval | interval | interval
989 --------------+----------------------+-----------------------------+-----------------------------
990 -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
991 (1 row)
993 -- test output of couple non-standard interval values in the sql style
994 SET IntervalStyle TO sql_standard;
995 SELECT interval '1 day -1 hours',
996 interval '-1 days +1 hours',
997 interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
998 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
999 interval | interval | interval | ?column?
1000 ------------------+------------------+----------------------+----------------------
1001 +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
1002 (1 row)
1004 -- cases that trigger sign-matching rules in the sql style
1005 SELECT interval '-23 hours 45 min 12.34 sec',
1006 interval '-1 day 23 hours 45 min 12.34 sec',
1007 interval '-1 year 2 months 1 day 23 hours 45 min 12.34 sec',
1008 interval '-1 year 2 months 1 day 23 hours 45 min +12.34 sec';
1009 interval | interval | interval | interval
1010 --------------+----------------+----------------------+-----------------------
1011 -23:45:12.34 | -1 23:45:12.34 | -1-2 -1 -23:45:12.34 | -0-10 +1 +23:45:12.34
1012 (1 row)
1014 -- edge case for sign-matching rules
1015 SELECT interval ''; -- error
1016 ERROR: invalid input syntax for type interval: ""
1017 LINE 1: SELECT interval '';
1018 ^
1019 -- test outputting iso8601 intervals
1020 SET IntervalStyle to iso_8601;
1021 select interval '0' AS "zero",
1022 interval '1-2' AS "a year 2 months",
1023 interval '1 2:03:04' AS "a bit over a day",
1024 interval '2:03:04.45679' AS "a bit over 2 hours",
1025 (interval '1-2' + interval '3 4:05:06.7') AS "all fields",
1026 (interval '1-2' - interval '3 4:05:06.7') AS "mixed sign",
1027 (- interval '1-2' + interval '3 4:05:06.7') AS "negative";
1028 zero | a year 2 months | a bit over a day | a bit over 2 hours | all fields | mixed sign | negative
1029 ------+-----------------+------------------+--------------------+------------------+----------------------+--------------------
1030 PT0S | P1Y2M | P1DT2H3M4S | PT2H3M4.45679S | P1Y2M3DT4H5M6.7S | P1Y2M-3DT-4H-5M-6.7S | P-1Y-2M3DT4H5M6.7S
1031 (1 row)
1033 -- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
1034 SET IntervalStyle to sql_standard;
1035 select interval 'P0Y' AS "zero",
1036 interval 'P1Y2M' AS "a year 2 months",
1037 interval 'P1W' AS "a week",
1038 interval 'P1DT2H3M4S' AS "a bit over a day",
1039 interval 'P1Y2M3DT4H5M6.7S' AS "all fields",
1040 interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
1041 interval 'PT-0.1S' AS "fractional second";
1042 zero | a year 2 months | a week | a bit over a day | all fields | negative | fractional second
1043 ------+-----------------+-----------+------------------+--------------------+--------------------+-------------------
1044 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
1045 (1 row)
1047 -- test inputting ISO 8601 4.4.2.2 "Alternative Format"
1048 SET IntervalStyle to postgres;
1049 select interval 'P00021015T103020' AS "ISO8601 Basic Format",
1050 interval 'P0002-10-15T10:30:20' AS "ISO8601 Extended Format";
1051 ISO8601 Basic Format | ISO8601 Extended Format
1052 ----------------------------------+----------------------------------
1053 2 years 10 mons 15 days 10:30:20 | 2 years 10 mons 15 days 10:30:20
1054 (1 row)
1056 -- Make sure optional ISO8601 alternative format fields are optional.
1057 select interval 'P0002' AS "year only",
1058 interval 'P0002-10' AS "year month",
1059 interval 'P0002-10-15' AS "year month day",
1060 interval 'P0002T1S' AS "year only plus time",
1061 interval 'P0002-10T1S' AS "year month plus time",
1062 interval 'P0002-10-15T1S' AS "year month day plus time",
1063 interval 'PT10' AS "hour only",
1064 interval 'PT10:30' AS "hour minute";
1065 year only | year month | year month day | year only plus time | year month plus time | year month day plus time | hour only | hour minute
1066 -----------+-----------------+-------------------------+---------------------+--------------------------+----------------------------------+-----------+-------------
1067 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
1068 (1 row)
1070 -- Check handling of fractional fields in ISO8601 format.
1071 select interval 'P1Y0M3DT4H5M6S';
1072 interval
1073 ------------------------
1074 1 year 3 days 04:05:06
1075 (1 row)
1077 select interval 'P1.0Y0M3DT4H5M6S';
1078 interval
1079 ------------------------
1080 1 year 3 days 04:05:06
1081 (1 row)
1083 select interval 'P1.1Y0M3DT4H5M6S';
1084 interval
1085 ------------------------------
1086 1 year 1 mon 3 days 04:05:06
1087 (1 row)
1089 select interval 'P1.Y0M3DT4H5M6S';
1090 interval
1091 ------------------------
1092 1 year 3 days 04:05:06
1093 (1 row)
1095 select interval 'P.1Y0M3DT4H5M6S';
1096 interval
1097 -----------------------
1098 1 mon 3 days 04:05:06
1099 (1 row)
1101 select interval 'P10.5e4Y'; -- not per spec, but we've historically taken it
1102 interval
1103 --------------
1104 105000 years
1105 (1 row)
1107 select interval 'P.Y0M3DT4H5M6S'; -- error
1108 ERROR: invalid input syntax for type interval: "P.Y0M3DT4H5M6S"
1109 LINE 1: select interval 'P.Y0M3DT4H5M6S';
1110 ^
1111 -- test a couple rounding cases that changed since 8.3 w/ HAVE_INT64_TIMESTAMP.
1112 SET IntervalStyle to postgres_verbose;
1113 select interval '-10 mons -3 days +03:55:06.70';
1114 interval
1115 --------------------------------------------------
1116 @ 10 mons 3 days -3 hours -55 mins -6.7 secs ago
1117 (1 row)
1119 select interval '1 year 2 mons 3 days 04:05:06.699999';
1120 interval
1121 -----------------------------------------------------
1122 @ 1 year 2 mons 3 days 4 hours 5 mins 6.699999 secs
1123 (1 row)
1125 select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds';
1126 interval | interval | interval
1127 ------------+------------+------------
1128 @ 0.7 secs | @ 0.7 secs | @ 0.7 secs
1129 (1 row)
1131 -- test time fields using entire 64 bit microseconds range
1132 select interval '2562047788.01521550194 hours';
1133 interval
1134 -----------------------------------
1135 @ 2562047788 hours 54.775807 secs
1136 (1 row)
1138 select interval '-2562047788.01521550222 hours';
1139 interval
1140 ---------------------------------------
1141 @ 2562047788 hours 54.775808 secs ago
1142 (1 row)
1144 select interval '153722867280.912930117 minutes';
1145 interval
1146 -----------------------------------
1147 @ 2562047788 hours 54.775807 secs
1148 (1 row)
1150 select interval '-153722867280.912930133 minutes';
1151 interval
1152 ---------------------------------------
1153 @ 2562047788 hours 54.775808 secs ago
1154 (1 row)
1156 select interval '9223372036854.775807 seconds';
1157 interval
1158 -----------------------------------
1159 @ 2562047788 hours 54.775807 secs
1160 (1 row)
1162 select interval '-9223372036854.775808 seconds';
1163 interval
1164 ---------------------------------------
1165 @ 2562047788 hours 54.775808 secs ago
1166 (1 row)
1168 select interval '9223372036854775.807 milliseconds';
1169 interval
1170 -----------------------------------
1171 @ 2562047788 hours 54.775807 secs
1172 (1 row)
1174 select interval '-9223372036854775.808 milliseconds';
1175 interval
1176 ---------------------------------------
1177 @ 2562047788 hours 54.775808 secs ago
1178 (1 row)
1180 select interval '9223372036854775807 microseconds';
1181 interval
1182 -----------------------------------
1183 @ 2562047788 hours 54.775807 secs
1184 (1 row)
1186 select interval '-9223372036854775808 microseconds';
1187 interval
1188 ---------------------------------------
1189 @ 2562047788 hours 54.775808 secs ago
1190 (1 row)
1192 select interval 'PT2562047788H54.775807S';
1193 interval
1194 -----------------------------------
1195 @ 2562047788 hours 54.775807 secs
1196 (1 row)
1198 select interval 'PT-2562047788H-54.775808S';
1199 interval
1200 ---------------------------------------
1201 @ 2562047788 hours 54.775808 secs ago
1202 (1 row)
1204 select interval 'PT2562047788:00:54.775807';
1205 interval
1206 -----------------------------------
1207 @ 2562047788 hours 54.775807 secs
1208 (1 row)
1210 select interval 'PT2562047788.0152155019444';
1211 interval
1212 -----------------------------------
1213 @ 2562047788 hours 54.775429 secs
1214 (1 row)
1216 select interval 'PT-2562047788.0152155022222';
1217 interval
1218 ---------------------------------------
1219 @ 2562047788 hours 54.775429 secs ago
1220 (1 row)
1222 -- overflow each date/time field
1223 select interval '2147483648 years';
1224 ERROR: interval field value out of range: "2147483648 years"
1225 LINE 1: select interval '2147483648 years';
1226 ^
1227 select interval '-2147483649 years';
1228 ERROR: interval field value out of range: "-2147483649 years"
1229 LINE 1: select interval '-2147483649 years';
1230 ^
1231 select interval '2147483648 months';
1232 ERROR: interval field value out of range: "2147483648 months"
1233 LINE 1: select interval '2147483648 months';
1234 ^
1235 select interval '-2147483649 months';
1236 ERROR: interval field value out of range: "-2147483649 months"
1237 LINE 1: select interval '-2147483649 months';
1238 ^
1239 select interval '2147483648 days';
1240 ERROR: interval field value out of range: "2147483648 days"
1241 LINE 1: select interval '2147483648 days';
1242 ^
1243 select interval '-2147483649 days';
1244 ERROR: interval field value out of range: "-2147483649 days"
1245 LINE 1: select interval '-2147483649 days';
1246 ^
1247 select interval '2562047789 hours';
1248 ERROR: interval field value out of range: "2562047789 hours"
1249 LINE 1: select interval '2562047789 hours';
1250 ^
1251 select interval '-2562047789 hours';
1252 ERROR: interval field value out of range: "-2562047789 hours"
1253 LINE 1: select interval '-2562047789 hours';
1254 ^
1255 select interval '153722867281 minutes';
1256 ERROR: interval field value out of range: "153722867281 minutes"
1257 LINE 1: select interval '153722867281 minutes';
1258 ^
1259 select interval '-153722867281 minutes';
1260 ERROR: interval field value out of range: "-153722867281 minutes"
1261 LINE 1: select interval '-153722867281 minutes';
1262 ^
1263 select interval '9223372036855 seconds';
1264 ERROR: interval field value out of range: "9223372036855 seconds"
1265 LINE 1: select interval '9223372036855 seconds';
1266 ^
1267 select interval '-9223372036855 seconds';
1268 ERROR: interval field value out of range: "-9223372036855 seconds"
1269 LINE 1: select interval '-9223372036855 seconds';
1270 ^
1271 select interval '9223372036854777 millisecond';
1272 ERROR: interval field value out of range: "9223372036854777 millisecond"
1273 LINE 1: select interval '9223372036854777 millisecond';
1274 ^
1275 select interval '-9223372036854777 millisecond';
1276 ERROR: interval field value out of range: "-9223372036854777 millisecond"
1277 LINE 1: select interval '-9223372036854777 millisecond';
1278 ^
1279 select interval '9223372036854775808 microsecond';
1280 ERROR: interval field value out of range: "9223372036854775808 microsecond"
1281 LINE 1: select interval '9223372036854775808 microsecond';
1282 ^
1283 select interval '-9223372036854775809 microsecond';
1284 ERROR: interval field value out of range: "-9223372036854775809 microsecond"
1285 LINE 1: select interval '-9223372036854775809 microsecond';
1286 ^
1287 select interval 'P2147483648';
1288 ERROR: interval field value out of range: "P2147483648"
1289 LINE 1: select interval 'P2147483648';
1290 ^
1291 select interval 'P-2147483649';
1292 ERROR: interval field value out of range: "P-2147483649"
1293 LINE 1: select interval 'P-2147483649';
1294 ^
1295 select interval 'P1-2147483647-2147483647';
1296 ERROR: interval out of range
1297 LINE 1: select interval 'P1-2147483647-2147483647';
1298 ^
1299 select interval 'PT2562047789';
1300 ERROR: interval field value out of range: "PT2562047789"
1301 LINE 1: select interval 'PT2562047789';
1302 ^
1303 select interval 'PT-2562047789';
1304 ERROR: interval field value out of range: "PT-2562047789"
1305 LINE 1: select interval 'PT-2562047789';
1306 ^
1307 -- overflow with date/time unit aliases
1308 select interval '2147483647 weeks';
1309 ERROR: interval field value out of range: "2147483647 weeks"
1310 LINE 1: select interval '2147483647 weeks';
1311 ^
1312 select interval '-2147483648 weeks';
1313 ERROR: interval field value out of range: "-2147483648 weeks"
1314 LINE 1: select interval '-2147483648 weeks';
1315 ^
1316 select interval '2147483647 decades';
1317 ERROR: interval field value out of range: "2147483647 decades"
1318 LINE 1: select interval '2147483647 decades';
1319 ^
1320 select interval '-2147483648 decades';
1321 ERROR: interval field value out of range: "-2147483648 decades"
1322 LINE 1: select interval '-2147483648 decades';
1323 ^
1324 select interval '2147483647 centuries';
1325 ERROR: interval field value out of range: "2147483647 centuries"
1326 LINE 1: select interval '2147483647 centuries';
1327 ^
1328 select interval '-2147483648 centuries';
1329 ERROR: interval field value out of range: "-2147483648 centuries"
1330 LINE 1: select interval '-2147483648 centuries';
1331 ^
1332 select interval '2147483647 millennium';
1333 ERROR: interval field value out of range: "2147483647 millennium"
1334 LINE 1: select interval '2147483647 millennium';
1335 ^
1336 select interval '-2147483648 millennium';
1337 ERROR: interval field value out of range: "-2147483648 millennium"
1338 LINE 1: select interval '-2147483648 millennium';
1339 ^
1340 select interval '1 week 2147483647 days';
1341 ERROR: interval field value out of range: "1 week 2147483647 days"
1342 LINE 1: select interval '1 week 2147483647 days';
1343 ^
1344 select interval '-1 week -2147483648 days';
1345 ERROR: interval field value out of range: "-1 week -2147483648 days"
1346 LINE 1: select interval '-1 week -2147483648 days';
1347 ^
1348 select interval '2147483647 days 1 week';
1349 ERROR: interval field value out of range: "2147483647 days 1 week"
1350 LINE 1: select interval '2147483647 days 1 week';
1351 ^
1352 select interval '-2147483648 days -1 week';
1353 ERROR: interval field value out of range: "-2147483648 days -1 week"
1354 LINE 1: select interval '-2147483648 days -1 week';
1355 ^
1356 select interval 'P1W2147483647D';
1357 ERROR: interval field value out of range: "P1W2147483647D"
1358 LINE 1: select interval 'P1W2147483647D';
1359 ^
1360 select interval 'P-1W-2147483648D';
1361 ERROR: interval field value out of range: "P-1W-2147483648D"
1362 LINE 1: select interval 'P-1W-2147483648D';
1363 ^
1364 select interval 'P2147483647D1W';
1365 ERROR: interval field value out of range: "P2147483647D1W"
1366 LINE 1: select interval 'P2147483647D1W';
1367 ^
1368 select interval 'P-2147483648D-1W';
1369 ERROR: interval field value out of range: "P-2147483648D-1W"
1370 LINE 1: select interval 'P-2147483648D-1W';
1371 ^
1372 select interval '1 decade 2147483647 years';
1373 ERROR: interval field value out of range: "1 decade 2147483647 years"
1374 LINE 1: select interval '1 decade 2147483647 years';
1375 ^
1376 select interval '1 century 2147483647 years';
1377 ERROR: interval field value out of range: "1 century 2147483647 years"
1378 LINE 1: select interval '1 century 2147483647 years';
1379 ^
1380 select interval '1 millennium 2147483647 years';
1381 ERROR: interval field value out of range: "1 millennium 2147483647 years"
1382 LINE 1: select interval '1 millennium 2147483647 years';
1383 ^
1384 select interval '-1 decade -2147483648 years';
1385 ERROR: interval field value out of range: "-1 decade -2147483648 years"
1386 LINE 1: select interval '-1 decade -2147483648 years';
1387 ^
1388 select interval '-1 century -2147483648 years';
1389 ERROR: interval field value out of range: "-1 century -2147483648 years"
1390 LINE 1: select interval '-1 century -2147483648 years';
1391 ^
1392 select interval '-1 millennium -2147483648 years';
1393 ERROR: interval field value out of range: "-1 millennium -2147483648 years"
1394 LINE 1: select interval '-1 millennium -2147483648 years';
1395 ^
1396 select interval '2147483647 years 1 decade';
1397 ERROR: interval field value out of range: "2147483647 years 1 decade"
1398 LINE 1: select interval '2147483647 years 1 decade';
1399 ^
1400 select interval '2147483647 years 1 century';
1401 ERROR: interval field value out of range: "2147483647 years 1 century"
1402 LINE 1: select interval '2147483647 years 1 century';
1403 ^
1404 select interval '2147483647 years 1 millennium';
1405 ERROR: interval field value out of range: "2147483647 years 1 millennium"
1406 LINE 1: select interval '2147483647 years 1 millennium';
1407 ^
1408 select interval '-2147483648 years -1 decade';
1409 ERROR: interval field value out of range: "-2147483648 years -1 decade"
1410 LINE 1: select interval '-2147483648 years -1 decade';
1411 ^
1412 select interval '-2147483648 years -1 century';
1413 ERROR: interval field value out of range: "-2147483648 years -1 century"
1414 LINE 1: select interval '-2147483648 years -1 century';
1415 ^
1416 select interval '-2147483648 years -1 millennium';
1417 ERROR: interval field value out of range: "-2147483648 years -1 millennium"
1418 LINE 1: select interval '-2147483648 years -1 millennium';
1419 ^
1420 -- overflowing with fractional fields - postgres format
1421 select interval '0.1 millennium 2147483647 months';
1422 ERROR: interval field value out of range: "0.1 millennium 2147483647 months"
1423 LINE 1: select interval '0.1 millennium 2147483647 months';
1424 ^
1425 select interval '0.1 centuries 2147483647 months';
1426 ERROR: interval field value out of range: "0.1 centuries 2147483647 months"
1427 LINE 1: select interval '0.1 centuries 2147483647 months';
1428 ^
1429 select interval '0.1 decades 2147483647 months';
1430 ERROR: interval field value out of range: "0.1 decades 2147483647 months"
1431 LINE 1: select interval '0.1 decades 2147483647 months';
1432 ^
1433 select interval '0.1 yrs 2147483647 months';
1434 ERROR: interval field value out of range: "0.1 yrs 2147483647 months"
1435 LINE 1: select interval '0.1 yrs 2147483647 months';
1436 ^
1437 select interval '-0.1 millennium -2147483648 months';
1438 ERROR: interval field value out of range: "-0.1 millennium -2147483648 months"
1439 LINE 1: select interval '-0.1 millennium -2147483648 months';
1440 ^
1441 select interval '-0.1 centuries -2147483648 months';
1442 ERROR: interval field value out of range: "-0.1 centuries -2147483648 months"
1443 LINE 1: select interval '-0.1 centuries -2147483648 months';
1444 ^
1445 select interval '-0.1 decades -2147483648 months';
1446 ERROR: interval field value out of range: "-0.1 decades -2147483648 months"
1447 LINE 1: select interval '-0.1 decades -2147483648 months';
1448 ^
1449 select interval '-0.1 yrs -2147483648 months';
1450 ERROR: interval field value out of range: "-0.1 yrs -2147483648 months"
1451 LINE 1: select interval '-0.1 yrs -2147483648 months';
1452 ^
1453 select interval '2147483647 months 0.1 millennium';
1454 ERROR: interval field value out of range: "2147483647 months 0.1 millennium"
1455 LINE 1: select interval '2147483647 months 0.1 millennium';
1456 ^
1457 select interval '2147483647 months 0.1 centuries';
1458 ERROR: interval field value out of range: "2147483647 months 0.1 centuries"
1459 LINE 1: select interval '2147483647 months 0.1 centuries';
1460 ^
1461 select interval '2147483647 months 0.1 decades';
1462 ERROR: interval field value out of range: "2147483647 months 0.1 decades"
1463 LINE 1: select interval '2147483647 months 0.1 decades';
1464 ^
1465 select interval '2147483647 months 0.1 yrs';
1466 ERROR: interval field value out of range: "2147483647 months 0.1 yrs"
1467 LINE 1: select interval '2147483647 months 0.1 yrs';
1468 ^
1469 select interval '-2147483648 months -0.1 millennium';
1470 ERROR: interval field value out of range: "-2147483648 months -0.1 millennium"
1471 LINE 1: select interval '-2147483648 months -0.1 millennium';
1472 ^
1473 select interval '-2147483648 months -0.1 centuries';
1474 ERROR: interval field value out of range: "-2147483648 months -0.1 centuries"
1475 LINE 1: select interval '-2147483648 months -0.1 centuries';
1476 ^
1477 select interval '-2147483648 months -0.1 decades';
1478 ERROR: interval field value out of range: "-2147483648 months -0.1 decades"
1479 LINE 1: select interval '-2147483648 months -0.1 decades';
1480 ^
1481 select interval '-2147483648 months -0.1 yrs';
1482 ERROR: interval field value out of range: "-2147483648 months -0.1 yrs"
1483 LINE 1: select interval '-2147483648 months -0.1 yrs';
1484 ^
1485 select interval '0.1 months 2147483647 days';
1486 ERROR: interval field value out of range: "0.1 months 2147483647 days"
1487 LINE 1: select interval '0.1 months 2147483647 days';
1488 ^
1489 select interval '-0.1 months -2147483648 days';
1490 ERROR: interval field value out of range: "-0.1 months -2147483648 days"
1491 LINE 1: select interval '-0.1 months -2147483648 days';
1492 ^
1493 select interval '2147483647 days 0.1 months';
1494 ERROR: interval field value out of range: "2147483647 days 0.1 months"
1495 LINE 1: select interval '2147483647 days 0.1 months';
1496 ^
1497 select interval '-2147483648 days -0.1 months';
1498 ERROR: interval field value out of range: "-2147483648 days -0.1 months"
1499 LINE 1: select interval '-2147483648 days -0.1 months';
1500 ^
1501 select interval '0.5 weeks 2147483647 days';
1502 ERROR: interval field value out of range: "0.5 weeks 2147483647 days"
1503 LINE 1: select interval '0.5 weeks 2147483647 days';
1504 ^
1505 select interval '-0.5 weeks -2147483648 days';
1506 ERROR: interval field value out of range: "-0.5 weeks -2147483648 days"
1507 LINE 1: select interval '-0.5 weeks -2147483648 days';
1508 ^
1509 select interval '2147483647 days 0.5 weeks';
1510 ERROR: interval field value out of range: "2147483647 days 0.5 weeks"
1511 LINE 1: select interval '2147483647 days 0.5 weeks';
1512 ^
1513 select interval '-2147483648 days -0.5 weeks';
1514 ERROR: interval field value out of range: "-2147483648 days -0.5 weeks"
1515 LINE 1: select interval '-2147483648 days -0.5 weeks';
1516 ^
1517 select interval '0.01 months 9223372036854775807 microseconds';
1518 ERROR: interval field value out of range: "0.01 months 9223372036854775807 microseconds"
1519 LINE 1: select interval '0.01 months 9223372036854775807 microsecond...
1520 ^
1521 select interval '-0.01 months -9223372036854775808 microseconds';
1522 ERROR: interval field value out of range: "-0.01 months -9223372036854775808 microseconds"
1523 LINE 1: select interval '-0.01 months -9223372036854775808 microseco...
1524 ^
1525 select interval '9223372036854775807 microseconds 0.01 months';
1526 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.01 months"
1527 LINE 1: select interval '9223372036854775807 microseconds 0.01 month...
1528 ^
1529 select interval '-9223372036854775808 microseconds -0.01 months';
1530 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.01 months"
1531 LINE 1: select interval '-9223372036854775808 microseconds -0.01 mon...
1532 ^
1533 select interval '0.1 weeks 9223372036854775807 microseconds';
1534 ERROR: interval field value out of range: "0.1 weeks 9223372036854775807 microseconds"
1535 LINE 1: select interval '0.1 weeks 9223372036854775807 microseconds'...
1536 ^
1537 select interval '-0.1 weeks -9223372036854775808 microseconds';
1538 ERROR: interval field value out of range: "-0.1 weeks -9223372036854775808 microseconds"
1539 LINE 1: select interval '-0.1 weeks -9223372036854775808 microsecond...
1540 ^
1541 select interval '9223372036854775807 microseconds 0.1 weeks';
1542 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 weeks"
1543 LINE 1: select interval '9223372036854775807 microseconds 0.1 weeks'...
1544 ^
1545 select interval '-9223372036854775808 microseconds -0.1 weeks';
1546 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 weeks"
1547 LINE 1: select interval '-9223372036854775808 microseconds -0.1 week...
1548 ^
1549 select interval '0.1 days 9223372036854775807 microseconds';
1550 ERROR: interval field value out of range: "0.1 days 9223372036854775807 microseconds"
1551 LINE 1: select interval '0.1 days 9223372036854775807 microseconds';
1552 ^
1553 select interval '-0.1 days -9223372036854775808 microseconds';
1554 ERROR: interval field value out of range: "-0.1 days -9223372036854775808 microseconds"
1555 LINE 1: select interval '-0.1 days -9223372036854775808 microseconds...
1556 ^
1557 select interval '9223372036854775807 microseconds 0.1 days';
1558 ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 days"
1559 LINE 1: select interval '9223372036854775807 microseconds 0.1 days';
1560 ^
1561 select interval '-9223372036854775808 microseconds -0.1 days';
1562 ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 days"
1563 LINE 1: select interval '-9223372036854775808 microseconds -0.1 days...
1564 ^
1565 -- overflowing with fractional fields - ISO8601 format
1566 select interval 'P0.1Y2147483647M';
1567 ERROR: interval field value out of range: "P0.1Y2147483647M"
1568 LINE 1: select interval 'P0.1Y2147483647M';
1569 ^
1570 select interval 'P-0.1Y-2147483648M';
1571 ERROR: interval field value out of range: "P-0.1Y-2147483648M"
1572 LINE 1: select interval 'P-0.1Y-2147483648M';
1573 ^
1574 select interval 'P2147483647M0.1Y';
1575 ERROR: interval field value out of range: "P2147483647M0.1Y"
1576 LINE 1: select interval 'P2147483647M0.1Y';
1577 ^
1578 select interval 'P-2147483648M-0.1Y';
1579 ERROR: interval field value out of range: "P-2147483648M-0.1Y"
1580 LINE 1: select interval 'P-2147483648M-0.1Y';
1581 ^
1582 select interval 'P0.1M2147483647D';
1583 ERROR: interval field value out of range: "P0.1M2147483647D"
1584 LINE 1: select interval 'P0.1M2147483647D';
1585 ^
1586 select interval 'P-0.1M-2147483648D';
1587 ERROR: interval field value out of range: "P-0.1M-2147483648D"
1588 LINE 1: select interval 'P-0.1M-2147483648D';
1589 ^
1590 select interval 'P2147483647D0.1M';
1591 ERROR: interval field value out of range: "P2147483647D0.1M"
1592 LINE 1: select interval 'P2147483647D0.1M';
1593 ^
1594 select interval 'P-2147483648D-0.1M';
1595 ERROR: interval field value out of range: "P-2147483648D-0.1M"
1596 LINE 1: select interval 'P-2147483648D-0.1M';
1597 ^
1598 select interval 'P0.5W2147483647D';
1599 ERROR: interval field value out of range: "P0.5W2147483647D"
1600 LINE 1: select interval 'P0.5W2147483647D';
1601 ^
1602 select interval 'P-0.5W-2147483648D';
1603 ERROR: interval field value out of range: "P-0.5W-2147483648D"
1604 LINE 1: select interval 'P-0.5W-2147483648D';
1605 ^
1606 select interval 'P2147483647D0.5W';
1607 ERROR: interval field value out of range: "P2147483647D0.5W"
1608 LINE 1: select interval 'P2147483647D0.5W';
1609 ^
1610 select interval 'P-2147483648D-0.5W';
1611 ERROR: interval field value out of range: "P-2147483648D-0.5W"
1612 LINE 1: select interval 'P-2147483648D-0.5W';
1613 ^
1614 select interval 'P0.01MT2562047788H54.775807S';
1615 ERROR: interval field value out of range: "P0.01MT2562047788H54.775807S"
1616 LINE 1: select interval 'P0.01MT2562047788H54.775807S';
1617 ^
1618 select interval 'P-0.01MT-2562047788H-54.775808S';
1619 ERROR: interval field value out of range: "P-0.01MT-2562047788H-54.775808S"
1620 LINE 1: select interval 'P-0.01MT-2562047788H-54.775808S';
1621 ^
1622 select interval 'P0.1DT2562047788H54.775807S';
1623 ERROR: interval field value out of range: "P0.1DT2562047788H54.775807S"
1624 LINE 1: select interval 'P0.1DT2562047788H54.775807S';
1625 ^
1626 select interval 'P-0.1DT-2562047788H-54.775808S';
1627 ERROR: interval field value out of range: "P-0.1DT-2562047788H-54.775808S"
1628 LINE 1: select interval 'P-0.1DT-2562047788H-54.775808S';
1629 ^
1630 select interval 'PT2562047788.1H54.775807S';
1631 ERROR: interval field value out of range: "PT2562047788.1H54.775807S"
1632 LINE 1: select interval 'PT2562047788.1H54.775807S';
1633 ^
1634 select interval 'PT-2562047788.1H-54.775808S';
1635 ERROR: interval field value out of range: "PT-2562047788.1H-54.775808S"
1636 LINE 1: select interval 'PT-2562047788.1H-54.775808S';
1637 ^
1638 select interval 'PT2562047788H0.1M54.775807S';
1639 ERROR: interval field value out of range: "PT2562047788H0.1M54.775807S"
1640 LINE 1: select interval 'PT2562047788H0.1M54.775807S';
1641 ^
1642 select interval 'PT-2562047788H-0.1M-54.775808S';
1643 ERROR: interval field value out of range: "PT-2562047788H-0.1M-54.775808S"
1644 LINE 1: select interval 'PT-2562047788H-0.1M-54.775808S';
1645 ^
1646 -- overflowing with fractional fields - ISO8601 alternative format
1647 select interval 'P0.1-2147483647-00';
1648 ERROR: interval field value out of range: "P0.1-2147483647-00"
1649 LINE 1: select interval 'P0.1-2147483647-00';
1650 ^
1651 select interval 'P00-0.1-2147483647';
1652 ERROR: interval field value out of range: "P00-0.1-2147483647"
1653 LINE 1: select interval 'P00-0.1-2147483647';
1654 ^
1655 select interval 'P00-0.01-00T2562047788:00:54.775807';
1656 ERROR: interval field value out of range: "P00-0.01-00T2562047788:00:54.775807"
1657 LINE 1: select interval 'P00-0.01-00T2562047788:00:54.775807';
1658 ^
1659 select interval 'P00-00-0.1T2562047788:00:54.775807';
1660 ERROR: interval field value out of range: "P00-00-0.1T2562047788:00:54.775807"
1661 LINE 1: select interval 'P00-00-0.1T2562047788:00:54.775807';
1662 ^
1663 select interval 'PT2562047788.1:00:54.775807';
1664 ERROR: interval field value out of range: "PT2562047788.1:00:54.775807"
1665 LINE 1: select interval 'PT2562047788.1:00:54.775807';
1666 ^
1667 select interval 'PT2562047788:01.:54.775807';
1668 ERROR: interval field value out of range: "PT2562047788:01.:54.775807"
1669 LINE 1: select interval 'PT2562047788:01.:54.775807';
1670 ^
1671 -- overflowing with fractional fields - SQL standard format
1672 select interval '0.1 2562047788:0:54.775807';
1673 ERROR: interval field value out of range: "0.1 2562047788:0:54.775807"
1674 LINE 1: select interval '0.1 2562047788:0:54.775807';
1675 ^
1676 select interval '0.1 2562047788:0:54.775808 ago';
1677 ERROR: interval field value out of range: "0.1 2562047788:0:54.775808 ago"
1678 LINE 1: select interval '0.1 2562047788:0:54.775808 ago';
1679 ^
1680 select interval '2562047788.1:0:54.775807';
1681 ERROR: interval field value out of range: "2562047788.1:0:54.775807"
1682 LINE 1: select interval '2562047788.1:0:54.775807';
1683 ^
1684 select interval '2562047788.1:0:54.775808 ago';
1685 ERROR: interval field value out of range: "2562047788.1:0:54.775808 ago"
1686 LINE 1: select interval '2562047788.1:0:54.775808 ago';
1687 ^
1688 select interval '2562047788:0.1:54.775807';
1689 ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775807"
1690 LINE 1: select interval '2562047788:0.1:54.775807';
1691 ^
1692 select interval '2562047788:0.1:54.775808 ago';
1693 ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775808 ago"
1694 LINE 1: select interval '2562047788:0.1:54.775808 ago';
1695 ^
1696 -- overflowing using AGO with INT_MIN
1697 select interval '-2147483648 months ago';
1698 ERROR: interval field value out of range: "-2147483648 months ago"
1699 LINE 1: select interval '-2147483648 months ago';
1700 ^
1701 select interval '-2147483648 days ago';
1702 ERROR: interval field value out of range: "-2147483648 days ago"
1703 LINE 1: select interval '-2147483648 days ago';
1704 ^
1705 select interval '-9223372036854775808 microseconds ago';
1706 ERROR: interval field value out of range: "-9223372036854775808 microseconds ago"
1707 LINE 1: select interval '-9223372036854775808 microseconds ago';
1708 ^
1709 select interval '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago';
1710 ERROR: interval field value out of range: "-2147483648 months -2147483648 days -9223372036854775808 microseconds ago"
1711 LINE 1: select interval '-2147483648 months -2147483648 days -922337...
1712 ^
1713 -- overflowing using make_interval
1714 select make_interval(years := 178956971);
1715 ERROR: interval out of range
1716 select make_interval(years := -178956971);
1717 ERROR: interval out of range
1718 select make_interval(years := 1, months := 2147483647);
1719 ERROR: interval out of range
1720 select make_interval(years := -1, months := -2147483648);
1721 ERROR: interval out of range
1722 select make_interval(weeks := 306783379);
1723 ERROR: interval out of range
1724 select make_interval(weeks := -306783379);
1725 ERROR: interval out of range
1726 select make_interval(weeks := 1, days := 2147483647);
1727 ERROR: interval out of range
1728 select make_interval(weeks := -1, days := -2147483648);
1729 ERROR: interval out of range
1730 select make_interval(secs := 1e308);
1731 ERROR: value out of range: overflow
1732 select make_interval(secs := 1e18);
1733 ERROR: interval out of range
1734 select make_interval(secs := -1e18);
1735 ERROR: interval out of range
1736 select make_interval(mins := 1, secs := 9223372036800.0);
1737 ERROR: interval out of range
1738 select make_interval(mins := -1, secs := -9223372036800.0);
1739 ERROR: interval out of range
1740 -- test that INT_MIN number is formatted properly
1741 SET IntervalStyle to postgres;
1742 select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
1743 interval
1744 --------------------------------------------------------------------
1745 -178956970 years -7 mons -2147483648 days -2562047788:00:54.775808
1746 (1 row)
1748 SET IntervalStyle to sql_standard;
1749 select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
1750 interval
1751 ---------------------------------------------------
1752 -178956970-7 -2147483648 -2562047788:00:54.775808
1753 (1 row)
1755 SET IntervalStyle to iso_8601;
1756 select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
1757 interval
1758 -----------------------------------------------------
1759 P-178956970Y-7M-2147483648DT-2562047788H-54.775808S
1760 (1 row)
1762 SET IntervalStyle to postgres_verbose;
1763 select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
1764 interval
1765 ------------------------------------------------------------------------------
1766 @ 178956970 years 7 mons 2147483648 days 2562047788 hours 54.775808 secs ago
1767 (1 row)
1769 -- check that '30 days' equals '1 month' according to the hash function
1770 select '30 days'::interval = '1 month'::interval as t;
1771 t
1772 ---
1773 t
1774 (1 row)
1776 select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t;
1777 t
1778 ---
1779 t
1780 (1 row)
1782 -- numeric constructor
1783 select make_interval(years := 2);
1784 make_interval
1785 ---------------
1786 @ 2 years
1787 (1 row)
1789 select make_interval(years := 1, months := 6);
1790 make_interval
1791 -----------------
1792 @ 1 year 6 mons
1793 (1 row)
1795 select make_interval(years := 1, months := -1, weeks := 5, days := -7, hours := 25, mins := -180);
1796 make_interval
1797 ----------------------------
1798 @ 11 mons 28 days 22 hours
1799 (1 row)
1801 select make_interval() = make_interval(years := 0, months := 0, weeks := 0, days := 0, mins := 0, secs := 0.0);
1802 ?column?
1803 ----------
1804 t
1805 (1 row)
1807 select make_interval(hours := -2, mins := -10, secs := -25.3);
1808 make_interval
1809 ---------------------------------
1810 @ 2 hours 10 mins 25.3 secs ago
1811 (1 row)
1813 select make_interval(years := 'inf'::float::int);
1814 ERROR: integer out of range
1815 select make_interval(months := 'NaN'::float::int);
1816 ERROR: integer out of range
1817 select make_interval(secs := 'inf');
1818 ERROR: interval out of range
1819 select make_interval(secs := 'NaN');
1820 ERROR: interval out of range
1821 select make_interval(secs := 7e12);
1822 make_interval
1823 ------------------------------------
1824 @ 1944444444 hours 26 mins 40 secs
1825 (1 row)
1827 --
1828 -- test EXTRACT
1829 --
1830 SELECT f1,
1831 EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
1832 EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
1833 EXTRACT(SECOND FROM f1) AS SECOND,
1834 EXTRACT(MINUTE FROM f1) AS MINUTE,
1835 EXTRACT(HOUR FROM f1) AS HOUR,
1836 EXTRACT(DAY FROM f1) AS DAY,
1837 EXTRACT(WEEK FROM f1) AS WEEK,
1838 EXTRACT(MONTH FROM f1) AS MONTH,
1839 EXTRACT(QUARTER FROM f1) AS QUARTER,
1840 EXTRACT(YEAR FROM f1) AS YEAR,
1841 EXTRACT(DECADE FROM f1) AS DECADE,
1842 EXTRACT(CENTURY FROM f1) AS CENTURY,
1843 EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
1844 EXTRACT(EPOCH FROM f1) AS EPOCH
1845 FROM INTERVAL_TBL;
1846 f1 | microsecond | millisecond | second | minute | hour | day | week | month | quarter | year | decade | century | millennium | epoch
1847 -------------------------------+-------------+-------------+------------+--------+-----------+-----------+------+-------+---------+-----------+-----------+-----------+------------+-------------------
1848 @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
1849 @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
1850 @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
1851 @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
1852 @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
1853 @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
1854 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
1855 @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
1856 @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
1857 @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
1858 infinity | | | | | Infinity | Infinity | | | | Infinity | Infinity | Infinity | Infinity | Infinity
1859 -infinity | | | | | -Infinity | -Infinity | | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1860 (12 rows)
1862 SELECT -f1,
1863 EXTRACT(MICROSECOND FROM -f1) AS MICROSECOND,
1864 EXTRACT(MILLISECOND FROM -f1) AS MILLISECOND,
1865 EXTRACT(SECOND FROM -f1) AS SECOND,
1866 EXTRACT(MINUTE FROM -f1) AS MINUTE,
1867 EXTRACT(HOUR FROM -f1) AS HOUR,
1868 EXTRACT(DAY FROM -f1) AS DAY,
1869 EXTRACT(WEEK FROM -f1) AS WEEK,
1870 EXTRACT(MONTH FROM -f1) AS MONTH,
1871 EXTRACT(QUARTER FROM -f1) AS QUARTER,
1872 EXTRACT(YEAR FROM -f1) AS YEAR,
1873 EXTRACT(DECADE FROM -f1) AS DECADE,
1874 EXTRACT(CENTURY FROM -f1) AS CENTURY,
1875 EXTRACT(MILLENNIUM FROM -f1) AS MILLENNIUM,
1876 EXTRACT(EPOCH FROM -f1) AS EPOCH
1877 FROM INTERVAL_TBL;
1878 ?column? | microsecond | millisecond | second | minute | hour | day | week | month | quarter | year | decade | century | millennium | epoch
1879 -----------------------------------+-------------+-------------+-----------+--------+-----------+-----------+------+-------+---------+-----------+-----------+-----------+------------+--------------------
1880 @ 1 min ago | 0 | 0.000 | 0.000000 | -1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -60.000000
1881 @ 5 hours ago | 0 | 0.000 | 0.000000 | 0 | -5 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -18000.000000
1882 @ 10 days ago | 0 | 0.000 | 0.000000 | 0 | 0 | -10 | -1 | 0 | 1 | 0 | 0 | 0 | 0 | -864000.000000
1883 @ 34 years ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | -1 | -34 | -3 | 0 | 0 | -1072958400.000000
1884 @ 3 mons ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | -3 | -2 | 0 | 0 | 0 | 0 | -7776000.000000
1885 @ 14 secs | 14000000 | 14000.000 | 14.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 14.000000
1886 @ 1 day 2 hours 3 mins 4 secs ago | -4000000 | -4000.000 | -4.000000 | -3 | -2 | -1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -93784.000000
1887 @ 6 years ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | -1 | -6 | 0 | 0 | 0 | -189345600.000000
1888 @ 5 mons ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | -5 | -2 | 0 | 0 | 0 | 0 | -12960000.000000
1889 @ 5 mons 12 hours ago | 0 | 0.000 | 0.000000 | 0 | -12 | 0 | 0 | -5 | -2 | 0 | 0 | 0 | 0 | -13003200.000000
1890 -infinity | | | | | -Infinity | -Infinity | | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1891 infinity | | | | | Infinity | Infinity | | | | Infinity | Infinity | Infinity | Infinity | Infinity
1892 (12 rows)
1894 SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
1895 ERROR: unit "fortnight" not recognized for type interval
1896 SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
1897 ERROR: unit "timezone" not supported for type interval
1898 SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
1899 extract
1900 ---------
1901 10
1902 (1 row)
1904 SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
1905 extract
1906 ---------
1907 9
1908 (1 row)
1910 SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
1911 extract
1912 ---------
1913 -9
1914 (1 row)
1916 SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
1917 extract
1918 ---------
1919 -10
1920 (1 row)
1922 SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
1923 extract
1924 ---------
1925 1
1926 (1 row)
1928 SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
1929 extract
1930 ---------
1931 0
1932 (1 row)
1934 SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
1935 extract
1936 ---------
1937 0
1938 (1 row)
1940 SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
1941 extract
1942 ---------
1943 -1
1944 (1 row)
1946 -- date_part implementation is mostly the same as extract, so only
1947 -- test a few cases for additional coverage.
1948 SELECT f1,
1949 date_part('microsecond', f1) AS microsecond,
1950 date_part('millisecond', f1) AS millisecond,
1951 date_part('second', f1) AS second,
1952 date_part('epoch', f1) AS epoch
1953 FROM INTERVAL_TBL;
1954 f1 | microsecond | millisecond | second | epoch
1955 -------------------------------+-------------+-------------+--------+------------
1956 @ 1 min | 0 | 0 | 0 | 60
1957 @ 5 hours | 0 | 0 | 0 | 18000
1958 @ 10 days | 0 | 0 | 0 | 864000
1959 @ 34 years | 0 | 0 | 0 | 1072958400
1960 @ 3 mons | 0 | 0 | 0 | 7776000
1961 @ 14 secs ago | -14000000 | -14000 | -14 | -14
1962 @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 93784
1963 @ 6 years | 0 | 0 | 0 | 189345600
1964 @ 5 mons | 0 | 0 | 0 | 12960000
1965 @ 5 mons 12 hours | 0 | 0 | 0 | 13003200
1966 infinity | | | | Infinity
1967 -infinity | | | | -Infinity
1968 (12 rows)
1970 -- internal overflow test case
1971 SELECT extract(epoch from interval '1000000000 days');
1972 extract
1973 -----------------------
1974 86400000000000.000000
1975 (1 row)
1977 --
1978 -- test infinite intervals
1979 --
1980 -- largest finite intervals
1981 SELECT interval '-2147483648 months -2147483648 days -9223372036854775807 us';
1982 interval
1983 ------------------------------------------------------------------------------
1984 @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775807 secs ago
1985 (1 row)
1987 SELECT interval '2147483647 months 2147483647 days 9223372036854775806 us';
1988 interval
1989 --------------------------------------------------------------------------
1990 @ 178956970 years 7 mons 2147483647 days 2562047788 hours 54.775806 secs
1991 (1 row)
1993 -- infinite intervals
1994 SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
1995 interval
1996 -----------
1997 -infinity
1998 (1 row)
2000 SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
2001 interval
2002 ----------
2003 infinity
2004 (1 row)
2006 CREATE TABLE INFINITE_INTERVAL_TBL (i interval);
2007 INSERT INTO INFINITE_INTERVAL_TBL VALUES ('infinity'), ('-infinity'), ('1 year 2 days 3 hours');
2008 SELECT i, isfinite(i) FROM INFINITE_INTERVAL_TBL;
2009 i | isfinite
2010 -------------------------+----------
2011 infinity | f
2012 -infinity | f
2013 @ 1 year 2 days 3 hours | t
2014 (3 rows)
2016 -- test basic arithmetic
2017 CREATE FUNCTION eval(expr text)
2018 RETURNS text AS
2019 $$
2020 DECLARE
2021 result text;
2022 BEGIN
2023 EXECUTE 'select '||expr INTO result;
2024 RETURN result;
2025 EXCEPTION WHEN OTHERS THEN
2026 RETURN SQLERRM;
2027 END
2028 $$
2029 LANGUAGE plpgsql;
2030 SELECT d AS date, i AS interval,
2031 eval(format('date %L + interval %L', d, i)) AS plus,
2032 eval(format('date %L - interval %L', d, i)) AS minus
2033 FROM (VALUES (date '-infinity'),
2034 (date '1995-08-06'),
2035 (date 'infinity')) AS t1(d),
2036 (VALUES (interval '-infinity'),
2037 (interval 'infinity')) AS t2(i);
2038 date | interval | plus | minus
2039 ------------+-----------+------------------------+------------------------
2040 -infinity | -infinity | -infinity | timestamp out of range
2041 -infinity | infinity | timestamp out of range | -infinity
2042 1995-08-06 | -infinity | -infinity | infinity
2043 1995-08-06 | infinity | infinity | -infinity
2044 infinity | -infinity | timestamp out of range | infinity
2045 infinity | infinity | infinity | timestamp out of range
2046 (6 rows)
2048 SELECT i1 AS interval1, i2 AS interval2,
2049 eval(format('interval %L + interval %L', i1, i2)) AS plus,
2050 eval(format('interval %L - interval %L', i1, i2)) AS minus
2051 FROM (VALUES (interval '-infinity'),
2052 (interval '2 months'),
2053 (interval 'infinity')) AS t1(i1),
2054 (VALUES (interval '-infinity'),
2055 (interval '10 days'),
2056 (interval 'infinity')) AS t2(i2);
2057 interval1 | interval2 | plus | minus
2058 -----------+-----------+-----------------------+-----------------------
2059 -infinity | -infinity | -infinity | interval out of range
2060 -infinity | @ 10 days | -infinity | -infinity
2061 -infinity | infinity | interval out of range | -infinity
2062 @ 2 mons | -infinity | -infinity | infinity
2063 @ 2 mons | @ 10 days | @ 2 mons 10 days | @ 2 mons -10 days
2064 @ 2 mons | infinity | infinity | -infinity
2065 infinity | -infinity | interval out of range | infinity
2066 infinity | @ 10 days | infinity | infinity
2067 infinity | infinity | infinity | interval out of range
2068 (9 rows)
2070 SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
2071 ERROR: interval out of range
2072 SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
2073 ERROR: interval out of range
2074 SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
2075 ERROR: interval out of range
2076 SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
2077 ERROR: interval out of range
2078 SELECT t AS timestamp, i AS interval,
2079 eval(format('timestamp %L + interval %L', t, i)) AS plus,
2080 eval(format('timestamp %L - interval %L', t, i)) AS minus
2081 FROM (VALUES (timestamp '-infinity'),
2082 (timestamp '1995-08-06 12:30:15'),
2083 (timestamp 'infinity')) AS t1(t),
2084 (VALUES (interval '-infinity'),
2085 (interval 'infinity')) AS t2(i);
2086 timestamp | interval | plus | minus
2087 ---------------------+-----------+------------------------+------------------------
2088 -infinity | -infinity | -infinity | timestamp out of range
2089 -infinity | infinity | timestamp out of range | -infinity
2090 1995-08-06 12:30:15 | -infinity | -infinity | infinity
2091 1995-08-06 12:30:15 | infinity | infinity | -infinity
2092 infinity | -infinity | timestamp out of range | infinity
2093 infinity | infinity | infinity | timestamp out of range
2094 (6 rows)
2096 SELECT t AT TIME ZONE 'GMT' AS timestamptz, i AS interval,
2097 eval(format('timestamptz %L + interval %L', t, i)) AS plus,
2098 eval(format('timestamptz %L - interval %L', t, i)) AS minus
2099 FROM (VALUES (timestamptz '-infinity'),
2100 (timestamptz '1995-08-06 12:30:15 GMT'),
2101 (timestamptz 'infinity')) AS t1(t),
2102 (VALUES (interval '-infinity'),
2103 (interval 'infinity')) AS t2(i);
2104 timestamptz | interval | plus | minus
2105 ---------------------+-----------+------------------------+------------------------
2106 -infinity | -infinity | -infinity | timestamp out of range
2107 -infinity | infinity | timestamp out of range | -infinity
2108 1995-08-06 12:30:15 | -infinity | -infinity | infinity
2109 1995-08-06 12:30:15 | infinity | infinity | -infinity
2110 infinity | -infinity | timestamp out of range | infinity
2111 infinity | infinity | infinity | timestamp out of range
2112 (6 rows)
2114 -- time +/- infinite interval not supported
2115 SELECT time '11:27:42' + interval 'infinity';
2116 ERROR: cannot add infinite interval to time
2117 SELECT time '11:27:42' + interval '-infinity';
2118 ERROR: cannot add infinite interval to time
2119 SELECT time '11:27:42' - interval 'infinity';
2120 ERROR: cannot subtract infinite interval from time
2121 SELECT time '11:27:42' - interval '-infinity';
2122 ERROR: cannot subtract infinite interval from time
2123 SELECT timetz '11:27:42' + interval 'infinity';
2124 ERROR: cannot add infinite interval to time
2125 SELECT timetz '11:27:42' + interval '-infinity';
2126 ERROR: cannot add infinite interval to time
2127 SELECT timetz '11:27:42' - interval 'infinity';
2128 ERROR: cannot subtract infinite interval from time
2129 SELECT timetz '11:27:42' - interval '-infinity';
2130 ERROR: cannot subtract infinite interval from time
2131 SELECT lhst.i lhs,
2132 rhst.i rhs,
2133 lhst.i < rhst.i AS lt,
2134 lhst.i <= rhst.i AS le,
2135 lhst.i = rhst.i AS eq,
2136 lhst.i > rhst.i AS gt,
2137 lhst.i >= rhst.i AS ge,
2138 lhst.i <> rhst.i AS ne
2139 FROM INFINITE_INTERVAL_TBL lhst CROSS JOIN INFINITE_INTERVAL_TBL rhst
2140 WHERE NOT isfinite(lhst.i);
2141 lhs | rhs | lt | le | eq | gt | ge | ne
2142 -----------+-------------------------+----+----+----+----+----+----
2143 infinity | infinity | f | t | t | f | t | f
2144 -infinity | infinity | t | t | f | f | f | t
2145 infinity | -infinity | f | f | f | t | t | t
2146 -infinity | -infinity | f | t | t | f | t | f
2147 infinity | @ 1 year 2 days 3 hours | f | f | f | t | t | t
2148 -infinity | @ 1 year 2 days 3 hours | t | t | f | f | f | t
2149 (6 rows)
2151 SELECT i AS interval,
2152 -i AS um,
2153 i * 2.0 AS mul,
2154 i * -2.0 AS mul_neg,
2155 i * 'infinity' AS mul_inf,
2156 i * '-infinity' AS mul_inf_neg,
2157 i / 3.0 AS div,
2158 i / -3.0 AS div_neg
2159 FROM INFINITE_INTERVAL_TBL
2160 WHERE NOT isfinite(i);
2161 interval | um | mul | mul_neg | mul_inf | mul_inf_neg | div | div_neg
2162 -----------+-----------+-----------+-----------+-----------+-------------+-----------+-----------
2163 infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity
2164 -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity
2165 (2 rows)
2167 SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
2168 ERROR: interval out of range
2169 SELECT interval 'infinity' * 'nan';
2170 ERROR: interval out of range
2171 SELECT interval '-infinity' * 'nan';
2172 ERROR: interval out of range
2173 SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
2174 ERROR: interval out of range
2175 SELECT interval 'infinity' * 0;
2176 ERROR: interval out of range
2177 SELECT interval '-infinity' * 0;
2178 ERROR: interval out of range
2179 SELECT interval '0 days' * 'infinity'::float;
2180 ERROR: interval out of range
2181 SELECT interval '0 days' * '-infinity'::float;
2182 ERROR: interval out of range
2183 SELECT interval '5 days' * 'infinity'::float;
2184 ?column?
2185 ----------
2186 infinity
2187 (1 row)
2189 SELECT interval '5 days' * '-infinity'::float;
2190 ?column?
2191 -----------
2192 -infinity
2193 (1 row)
2195 SELECT interval 'infinity' / 'infinity';
2196 ERROR: interval out of range
2197 SELECT interval 'infinity' / '-infinity';
2198 ERROR: interval out of range
2199 SELECT interval 'infinity' / 'nan';
2200 ERROR: interval out of range
2201 SELECT interval '-infinity' / 'infinity';
2202 ERROR: interval out of range
2203 SELECT interval '-infinity' / '-infinity';
2204 ERROR: interval out of range
2205 SELECT interval '-infinity' / 'nan';
2206 ERROR: interval out of range
2207 SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
2208 ERROR: interval out of range
2209 SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
2210 ERROR: timestamps cannot be binned into infinite intervals
2211 SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
2212 ERROR: timestamps cannot be binned into infinite intervals
2213 SELECT i AS interval, date_trunc('hour', i)
2214 FROM INFINITE_INTERVAL_TBL
2215 WHERE NOT isfinite(i);
2216 interval | date_trunc
2217 -----------+------------
2218 infinity | infinity
2219 -infinity | -infinity
2220 (2 rows)
2222 SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
2223 FROM INFINITE_INTERVAL_TBL
2224 WHERE NOT isfinite(i);
2225 interval | justify_days | justify_hours | justify_interval
2226 -----------+--------------+---------------+------------------
2227 infinity | infinity | infinity | infinity
2228 -infinity | -infinity | -infinity | -infinity
2229 (2 rows)
2231 SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp);
2232 ERROR: interval time zone "infinity" must be finite
2233 SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp);
2234 ERROR: interval time zone "-infinity" must be finite
2235 SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
2236 ERROR: interval time zone "infinity" must be finite
2237 SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
2238 ERROR: interval time zone "-infinity" must be finite
2239 SELECT timezone('infinity'::interval, '12:12:12'::time);
2240 ERROR: interval time zone "infinity" must be finite
2241 SELECT timezone('-infinity'::interval, '12:12:12'::time);
2242 ERROR: interval time zone "-infinity" must be finite
2243 SELECT timezone('infinity'::interval, '12:12:12'::timetz);
2244 ERROR: interval time zone "infinity" must be finite
2245 SELECT timezone('-infinity'::interval, '12:12:12'::timetz);
2246 ERROR: interval time zone "-infinity" must be finite
2247 SELECT 'infinity'::interval::time;
2248 ERROR: cannot convert infinite interval to time
2249 SELECT '-infinity'::interval::time;
2250 ERROR: cannot convert infinite interval to time
2251 SELECT to_char('infinity'::interval, 'YYYY');
2252 to_char
2253 ---------
2255 (1 row)
2257 SELECT to_char('-infinity'::interval, 'YYYY');
2258 to_char
2259 ---------
2261 (1 row)
2263 -- "ago" can only appear once at the end of an interval.
2264 SELECT INTERVAL '42 days 2 seconds ago ago';
2265 ERROR: invalid input syntax for type interval: "42 days 2 seconds ago ago"
2266 LINE 1: SELECT INTERVAL '42 days 2 seconds ago ago';
2267 ^
2268 SELECT INTERVAL '2 minutes ago 5 days';
2269 ERROR: invalid input syntax for type interval: "2 minutes ago 5 days"
2270 LINE 1: SELECT INTERVAL '2 minutes ago 5 days';
2271 ^
2272 -- consecutive and dangling units are not allowed.
2273 SELECT INTERVAL 'hour 5 months';
2274 ERROR: invalid input syntax for type interval: "hour 5 months"
2275 LINE 1: SELECT INTERVAL 'hour 5 months';
2276 ^
2277 SELECT INTERVAL '1 year months days 5 hours';
2278 ERROR: invalid input syntax for type interval: "1 year months days 5 hours"
2279 LINE 1: SELECT INTERVAL '1 year months days 5 hours';
2280 ^
2281 -- unacceptable reserved words in interval. Only "infinity", "+infinity" and
2282 -- "-infinity" are allowed.
2283 SELECT INTERVAL 'now';
2284 ERROR: invalid input syntax for type interval: "now"
2285 LINE 1: SELECT INTERVAL 'now';
2286 ^
2287 SELECT INTERVAL 'today';
2288 ERROR: invalid input syntax for type interval: "today"
2289 LINE 1: SELECT INTERVAL 'today';
2290 ^
2291 SELECT INTERVAL 'tomorrow';
2292 ERROR: invalid input syntax for type interval: "tomorrow"
2293 LINE 1: SELECT INTERVAL 'tomorrow';
2294 ^
2295 SELECT INTERVAL 'allballs';
2296 ERROR: invalid input syntax for type interval: "allballs"
2297 LINE 1: SELECT INTERVAL 'allballs';
2298 ^
2299 SELECT INTERVAL 'epoch';
2300 ERROR: invalid input syntax for type interval: "epoch"
2301 LINE 1: SELECT INTERVAL 'epoch';
2302 ^
2303 SELECT INTERVAL 'yesterday';
2304 ERROR: invalid input syntax for type interval: "yesterday"
2305 LINE 1: SELECT INTERVAL 'yesterday';
2306 ^
2307 -- infinity specification should be the only thing
2308 SELECT INTERVAL 'infinity years';
2309 ERROR: invalid input syntax for type interval: "infinity years"
2310 LINE 1: SELECT INTERVAL 'infinity years';
2311 ^
2312 SELECT INTERVAL 'infinity ago';
2313 ERROR: invalid input syntax for type interval: "infinity ago"
2314 LINE 1: SELECT INTERVAL 'infinity ago';
2315 ^
2316 SELECT INTERVAL '+infinity -infinity';
2317 ERROR: invalid input syntax for type interval: "+infinity -infinity"
2318 LINE 1: SELECT INTERVAL '+infinity -infinity';
2319 ^