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