1-- 2-- DATE 3-- 4CREATE TABLE DATE_TBL (f1 date); 5INSERT INTO DATE_TBL VALUES ('1957-04-09'); 6INSERT INTO DATE_TBL VALUES ('1957-06-13'); 7INSERT INTO DATE_TBL VALUES ('1996-02-28'); 8INSERT INTO DATE_TBL VALUES ('1996-02-29'); 9INSERT INTO DATE_TBL VALUES ('1996-03-01'); 10INSERT INTO DATE_TBL VALUES ('1996-03-02'); 11INSERT INTO DATE_TBL VALUES ('1997-02-28'); 12INSERT INTO DATE_TBL VALUES ('1997-02-29'); 13ERROR: date/time field value out of range: "1997-02-29" 14LINE 1: INSERT INTO DATE_TBL VALUES ('1997-02-29'); 15 ^ 16INSERT INTO DATE_TBL VALUES ('1997-03-01'); 17INSERT INTO DATE_TBL VALUES ('1997-03-02'); 18INSERT INTO DATE_TBL VALUES ('2000-04-01'); 19INSERT INTO DATE_TBL VALUES ('2000-04-02'); 20INSERT INTO DATE_TBL VALUES ('2000-04-03'); 21INSERT INTO DATE_TBL VALUES ('2038-04-08'); 22INSERT INTO DATE_TBL VALUES ('2039-04-09'); 23INSERT INTO DATE_TBL VALUES ('2040-04-10'); 24SELECT f1 AS "Fifteen" FROM DATE_TBL; 25 Fifteen 26------------ 27 04-09-1957 28 06-13-1957 29 02-28-1996 30 02-29-1996 31 03-01-1996 32 03-02-1996 33 02-28-1997 34 03-01-1997 35 03-02-1997 36 04-01-2000 37 04-02-2000 38 04-03-2000 39 04-08-2038 40 04-09-2039 41 04-10-2040 42(15 rows) 43 44SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01'; 45 Nine 46------------ 47 04-09-1957 48 06-13-1957 49 02-28-1996 50 02-29-1996 51 03-01-1996 52 03-02-1996 53 02-28-1997 54 03-01-1997 55 03-02-1997 56(9 rows) 57 58SELECT f1 AS "Three" FROM DATE_TBL 59 WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01'; 60 Three 61------------ 62 04-01-2000 63 04-02-2000 64 04-03-2000 65(3 rows) 66 67-- 68-- Check all the documented input formats 69-- 70SET datestyle TO iso; -- display results in ISO 71SET datestyle TO ymd; 72SELECT date 'January 8, 1999'; 73 date 74------------ 75 1999-01-08 76(1 row) 77 78SELECT date '1999-01-08'; 79 date 80------------ 81 1999-01-08 82(1 row) 83 84SELECT date '1999-01-18'; 85 date 86------------ 87 1999-01-18 88(1 row) 89 90SELECT date '1/8/1999'; 91ERROR: date/time field value out of range: "1/8/1999" 92LINE 1: SELECT date '1/8/1999'; 93 ^ 94HINT: Perhaps you need a different "datestyle" setting. 95SELECT date '1/18/1999'; 96ERROR: date/time field value out of range: "1/18/1999" 97LINE 1: SELECT date '1/18/1999'; 98 ^ 99HINT: Perhaps you need a different "datestyle" setting. 100SELECT date '18/1/1999'; 101ERROR: date/time field value out of range: "18/1/1999" 102LINE 1: SELECT date '18/1/1999'; 103 ^ 104HINT: Perhaps you need a different "datestyle" setting. 105SELECT date '01/02/03'; 106 date 107------------ 108 2001-02-03 109(1 row) 110 111SELECT date '19990108'; 112 date 113------------ 114 1999-01-08 115(1 row) 116 117SELECT date '990108'; 118 date 119------------ 120 1999-01-08 121(1 row) 122 123SELECT date '1999.008'; 124 date 125------------ 126 1999-01-08 127(1 row) 128 129SELECT date 'J2451187'; 130 date 131------------ 132 1999-01-08 133(1 row) 134 135SELECT date 'January 8, 99 BC'; 136ERROR: date/time field value out of range: "January 8, 99 BC" 137LINE 1: SELECT date 'January 8, 99 BC'; 138 ^ 139HINT: Perhaps you need a different "datestyle" setting. 140SELECT date '99-Jan-08'; 141 date 142------------ 143 1999-01-08 144(1 row) 145 146SELECT date '1999-Jan-08'; 147 date 148------------ 149 1999-01-08 150(1 row) 151 152SELECT date '08-Jan-99'; 153ERROR: date/time field value out of range: "08-Jan-99" 154LINE 1: SELECT date '08-Jan-99'; 155 ^ 156HINT: Perhaps you need a different "datestyle" setting. 157SELECT date '08-Jan-1999'; 158 date 159------------ 160 1999-01-08 161(1 row) 162 163SELECT date 'Jan-08-99'; 164ERROR: date/time field value out of range: "Jan-08-99" 165LINE 1: SELECT date 'Jan-08-99'; 166 ^ 167HINT: Perhaps you need a different "datestyle" setting. 168SELECT date 'Jan-08-1999'; 169 date 170------------ 171 1999-01-08 172(1 row) 173 174SELECT date '99-08-Jan'; 175ERROR: invalid input syntax for type date: "99-08-Jan" 176LINE 1: SELECT date '99-08-Jan'; 177 ^ 178SELECT date '1999-08-Jan'; 179ERROR: invalid input syntax for type date: "1999-08-Jan" 180LINE 1: SELECT date '1999-08-Jan'; 181 ^ 182SELECT date '99 Jan 08'; 183 date 184------------ 185 1999-01-08 186(1 row) 187 188SELECT date '1999 Jan 08'; 189 date 190------------ 191 1999-01-08 192(1 row) 193 194SELECT date '08 Jan 99'; 195ERROR: date/time field value out of range: "08 Jan 99" 196LINE 1: SELECT date '08 Jan 99'; 197 ^ 198HINT: Perhaps you need a different "datestyle" setting. 199SELECT date '08 Jan 1999'; 200 date 201------------ 202 1999-01-08 203(1 row) 204 205SELECT date 'Jan 08 99'; 206ERROR: date/time field value out of range: "Jan 08 99" 207LINE 1: SELECT date 'Jan 08 99'; 208 ^ 209HINT: Perhaps you need a different "datestyle" setting. 210SELECT date 'Jan 08 1999'; 211 date 212------------ 213 1999-01-08 214(1 row) 215 216SELECT date '99 08 Jan'; 217 date 218------------ 219 1999-01-08 220(1 row) 221 222SELECT date '1999 08 Jan'; 223 date 224------------ 225 1999-01-08 226(1 row) 227 228SELECT date '99-01-08'; 229 date 230------------ 231 1999-01-08 232(1 row) 233 234SELECT date '1999-01-08'; 235 date 236------------ 237 1999-01-08 238(1 row) 239 240SELECT date '08-01-99'; 241ERROR: date/time field value out of range: "08-01-99" 242LINE 1: SELECT date '08-01-99'; 243 ^ 244HINT: Perhaps you need a different "datestyle" setting. 245SELECT date '08-01-1999'; 246ERROR: date/time field value out of range: "08-01-1999" 247LINE 1: SELECT date '08-01-1999'; 248 ^ 249HINT: Perhaps you need a different "datestyle" setting. 250SELECT date '01-08-99'; 251ERROR: date/time field value out of range: "01-08-99" 252LINE 1: SELECT date '01-08-99'; 253 ^ 254HINT: Perhaps you need a different "datestyle" setting. 255SELECT date '01-08-1999'; 256ERROR: date/time field value out of range: "01-08-1999" 257LINE 1: SELECT date '01-08-1999'; 258 ^ 259HINT: Perhaps you need a different "datestyle" setting. 260SELECT date '99-08-01'; 261 date 262------------ 263 1999-08-01 264(1 row) 265 266SELECT date '1999-08-01'; 267 date 268------------ 269 1999-08-01 270(1 row) 271 272SELECT date '99 01 08'; 273 date 274------------ 275 1999-01-08 276(1 row) 277 278SELECT date '1999 01 08'; 279 date 280------------ 281 1999-01-08 282(1 row) 283 284SELECT date '08 01 99'; 285ERROR: date/time field value out of range: "08 01 99" 286LINE 1: SELECT date '08 01 99'; 287 ^ 288HINT: Perhaps you need a different "datestyle" setting. 289SELECT date '08 01 1999'; 290ERROR: date/time field value out of range: "08 01 1999" 291LINE 1: SELECT date '08 01 1999'; 292 ^ 293HINT: Perhaps you need a different "datestyle" setting. 294SELECT date '01 08 99'; 295ERROR: date/time field value out of range: "01 08 99" 296LINE 1: SELECT date '01 08 99'; 297 ^ 298HINT: Perhaps you need a different "datestyle" setting. 299SELECT date '01 08 1999'; 300ERROR: date/time field value out of range: "01 08 1999" 301LINE 1: SELECT date '01 08 1999'; 302 ^ 303HINT: Perhaps you need a different "datestyle" setting. 304SELECT date '99 08 01'; 305 date 306------------ 307 1999-08-01 308(1 row) 309 310SELECT date '1999 08 01'; 311 date 312------------ 313 1999-08-01 314(1 row) 315 316SET datestyle TO dmy; 317SELECT date 'January 8, 1999'; 318 date 319------------ 320 1999-01-08 321(1 row) 322 323SELECT date '1999-01-08'; 324 date 325------------ 326 1999-01-08 327(1 row) 328 329SELECT date '1999-01-18'; 330 date 331------------ 332 1999-01-18 333(1 row) 334 335SELECT date '1/8/1999'; 336 date 337------------ 338 1999-08-01 339(1 row) 340 341SELECT date '1/18/1999'; 342ERROR: date/time field value out of range: "1/18/1999" 343LINE 1: SELECT date '1/18/1999'; 344 ^ 345HINT: Perhaps you need a different "datestyle" setting. 346SELECT date '18/1/1999'; 347 date 348------------ 349 1999-01-18 350(1 row) 351 352SELECT date '01/02/03'; 353 date 354------------ 355 2003-02-01 356(1 row) 357 358SELECT date '19990108'; 359 date 360------------ 361 1999-01-08 362(1 row) 363 364SELECT date '990108'; 365 date 366------------ 367 1999-01-08 368(1 row) 369 370SELECT date '1999.008'; 371 date 372------------ 373 1999-01-08 374(1 row) 375 376SELECT date 'J2451187'; 377 date 378------------ 379 1999-01-08 380(1 row) 381 382SELECT date 'January 8, 99 BC'; 383 date 384--------------- 385 0099-01-08 BC 386(1 row) 387 388SELECT date '99-Jan-08'; 389ERROR: date/time field value out of range: "99-Jan-08" 390LINE 1: SELECT date '99-Jan-08'; 391 ^ 392HINT: Perhaps you need a different "datestyle" setting. 393SELECT date '1999-Jan-08'; 394 date 395------------ 396 1999-01-08 397(1 row) 398 399SELECT date '08-Jan-99'; 400 date 401------------ 402 1999-01-08 403(1 row) 404 405SELECT date '08-Jan-1999'; 406 date 407------------ 408 1999-01-08 409(1 row) 410 411SELECT date 'Jan-08-99'; 412 date 413------------ 414 1999-01-08 415(1 row) 416 417SELECT date 'Jan-08-1999'; 418 date 419------------ 420 1999-01-08 421(1 row) 422 423SELECT date '99-08-Jan'; 424ERROR: invalid input syntax for type date: "99-08-Jan" 425LINE 1: SELECT date '99-08-Jan'; 426 ^ 427SELECT date '1999-08-Jan'; 428ERROR: invalid input syntax for type date: "1999-08-Jan" 429LINE 1: SELECT date '1999-08-Jan'; 430 ^ 431SELECT date '99 Jan 08'; 432ERROR: date/time field value out of range: "99 Jan 08" 433LINE 1: SELECT date '99 Jan 08'; 434 ^ 435HINT: Perhaps you need a different "datestyle" setting. 436SELECT date '1999 Jan 08'; 437 date 438------------ 439 1999-01-08 440(1 row) 441 442SELECT date '08 Jan 99'; 443 date 444------------ 445 1999-01-08 446(1 row) 447 448SELECT date '08 Jan 1999'; 449 date 450------------ 451 1999-01-08 452(1 row) 453 454SELECT date 'Jan 08 99'; 455 date 456------------ 457 1999-01-08 458(1 row) 459 460SELECT date 'Jan 08 1999'; 461 date 462------------ 463 1999-01-08 464(1 row) 465 466SELECT date '99 08 Jan'; 467ERROR: invalid input syntax for type date: "99 08 Jan" 468LINE 1: SELECT date '99 08 Jan'; 469 ^ 470SELECT date '1999 08 Jan'; 471 date 472------------ 473 1999-01-08 474(1 row) 475 476SELECT date '99-01-08'; 477ERROR: date/time field value out of range: "99-01-08" 478LINE 1: SELECT date '99-01-08'; 479 ^ 480HINT: Perhaps you need a different "datestyle" setting. 481SELECT date '1999-01-08'; 482 date 483------------ 484 1999-01-08 485(1 row) 486 487SELECT date '08-01-99'; 488 date 489------------ 490 1999-01-08 491(1 row) 492 493SELECT date '08-01-1999'; 494 date 495------------ 496 1999-01-08 497(1 row) 498 499SELECT date '01-08-99'; 500 date 501------------ 502 1999-08-01 503(1 row) 504 505SELECT date '01-08-1999'; 506 date 507------------ 508 1999-08-01 509(1 row) 510 511SELECT date '99-08-01'; 512ERROR: date/time field value out of range: "99-08-01" 513LINE 1: SELECT date '99-08-01'; 514 ^ 515HINT: Perhaps you need a different "datestyle" setting. 516SELECT date '1999-08-01'; 517 date 518------------ 519 1999-08-01 520(1 row) 521 522SELECT date '99 01 08'; 523ERROR: date/time field value out of range: "99 01 08" 524LINE 1: SELECT date '99 01 08'; 525 ^ 526HINT: Perhaps you need a different "datestyle" setting. 527SELECT date '1999 01 08'; 528 date 529------------ 530 1999-01-08 531(1 row) 532 533SELECT date '08 01 99'; 534 date 535------------ 536 1999-01-08 537(1 row) 538 539SELECT date '08 01 1999'; 540 date 541------------ 542 1999-01-08 543(1 row) 544 545SELECT date '01 08 99'; 546 date 547------------ 548 1999-08-01 549(1 row) 550 551SELECT date '01 08 1999'; 552 date 553------------ 554 1999-08-01 555(1 row) 556 557SELECT date '99 08 01'; 558ERROR: date/time field value out of range: "99 08 01" 559LINE 1: SELECT date '99 08 01'; 560 ^ 561HINT: Perhaps you need a different "datestyle" setting. 562SELECT date '1999 08 01'; 563 date 564------------ 565 1999-08-01 566(1 row) 567 568SET datestyle TO mdy; 569SELECT date 'January 8, 1999'; 570 date 571------------ 572 1999-01-08 573(1 row) 574 575SELECT date '1999-01-08'; 576 date 577------------ 578 1999-01-08 579(1 row) 580 581SELECT date '1999-01-18'; 582 date 583------------ 584 1999-01-18 585(1 row) 586 587SELECT date '1/8/1999'; 588 date 589------------ 590 1999-01-08 591(1 row) 592 593SELECT date '1/18/1999'; 594 date 595------------ 596 1999-01-18 597(1 row) 598 599SELECT date '18/1/1999'; 600ERROR: date/time field value out of range: "18/1/1999" 601LINE 1: SELECT date '18/1/1999'; 602 ^ 603HINT: Perhaps you need a different "datestyle" setting. 604SELECT date '01/02/03'; 605 date 606------------ 607 2003-01-02 608(1 row) 609 610SELECT date '19990108'; 611 date 612------------ 613 1999-01-08 614(1 row) 615 616SELECT date '990108'; 617 date 618------------ 619 1999-01-08 620(1 row) 621 622SELECT date '1999.008'; 623 date 624------------ 625 1999-01-08 626(1 row) 627 628SELECT date 'J2451187'; 629 date 630------------ 631 1999-01-08 632(1 row) 633 634SELECT date 'January 8, 99 BC'; 635 date 636--------------- 637 0099-01-08 BC 638(1 row) 639 640SELECT date '99-Jan-08'; 641ERROR: date/time field value out of range: "99-Jan-08" 642LINE 1: SELECT date '99-Jan-08'; 643 ^ 644HINT: Perhaps you need a different "datestyle" setting. 645SELECT date '1999-Jan-08'; 646 date 647------------ 648 1999-01-08 649(1 row) 650 651SELECT date '08-Jan-99'; 652 date 653------------ 654 1999-01-08 655(1 row) 656 657SELECT date '08-Jan-1999'; 658 date 659------------ 660 1999-01-08 661(1 row) 662 663SELECT date 'Jan-08-99'; 664 date 665------------ 666 1999-01-08 667(1 row) 668 669SELECT date 'Jan-08-1999'; 670 date 671------------ 672 1999-01-08 673(1 row) 674 675SELECT date '99-08-Jan'; 676ERROR: invalid input syntax for type date: "99-08-Jan" 677LINE 1: SELECT date '99-08-Jan'; 678 ^ 679SELECT date '1999-08-Jan'; 680ERROR: invalid input syntax for type date: "1999-08-Jan" 681LINE 1: SELECT date '1999-08-Jan'; 682 ^ 683SELECT date '99 Jan 08'; 684ERROR: invalid input syntax for type date: "99 Jan 08" 685LINE 1: SELECT date '99 Jan 08'; 686 ^ 687SELECT date '1999 Jan 08'; 688 date 689------------ 690 1999-01-08 691(1 row) 692 693SELECT date '08 Jan 99'; 694 date 695------------ 696 1999-01-08 697(1 row) 698 699SELECT date '08 Jan 1999'; 700 date 701------------ 702 1999-01-08 703(1 row) 704 705SELECT date 'Jan 08 99'; 706 date 707------------ 708 1999-01-08 709(1 row) 710 711SELECT date 'Jan 08 1999'; 712 date 713------------ 714 1999-01-08 715(1 row) 716 717SELECT date '99 08 Jan'; 718ERROR: invalid input syntax for type date: "99 08 Jan" 719LINE 1: SELECT date '99 08 Jan'; 720 ^ 721SELECT date '1999 08 Jan'; 722 date 723------------ 724 1999-01-08 725(1 row) 726 727SELECT date '99-01-08'; 728ERROR: date/time field value out of range: "99-01-08" 729LINE 1: SELECT date '99-01-08'; 730 ^ 731HINT: Perhaps you need a different "datestyle" setting. 732SELECT date '1999-01-08'; 733 date 734------------ 735 1999-01-08 736(1 row) 737 738SELECT date '08-01-99'; 739 date 740------------ 741 1999-08-01 742(1 row) 743 744SELECT date '08-01-1999'; 745 date 746------------ 747 1999-08-01 748(1 row) 749 750SELECT date '01-08-99'; 751 date 752------------ 753 1999-01-08 754(1 row) 755 756SELECT date '01-08-1999'; 757 date 758------------ 759 1999-01-08 760(1 row) 761 762SELECT date '99-08-01'; 763ERROR: date/time field value out of range: "99-08-01" 764LINE 1: SELECT date '99-08-01'; 765 ^ 766HINT: Perhaps you need a different "datestyle" setting. 767SELECT date '1999-08-01'; 768 date 769------------ 770 1999-08-01 771(1 row) 772 773SELECT date '99 01 08'; 774ERROR: date/time field value out of range: "99 01 08" 775LINE 1: SELECT date '99 01 08'; 776 ^ 777HINT: Perhaps you need a different "datestyle" setting. 778SELECT date '1999 01 08'; 779 date 780------------ 781 1999-01-08 782(1 row) 783 784SELECT date '08 01 99'; 785 date 786------------ 787 1999-08-01 788(1 row) 789 790SELECT date '08 01 1999'; 791 date 792------------ 793 1999-08-01 794(1 row) 795 796SELECT date '01 08 99'; 797 date 798------------ 799 1999-01-08 800(1 row) 801 802SELECT date '01 08 1999'; 803 date 804------------ 805 1999-01-08 806(1 row) 807 808SELECT date '99 08 01'; 809ERROR: date/time field value out of range: "99 08 01" 810LINE 1: SELECT date '99 08 01'; 811 ^ 812HINT: Perhaps you need a different "datestyle" setting. 813SELECT date '1999 08 01'; 814 date 815------------ 816 1999-08-01 817(1 row) 818 819-- Check upper and lower limits of date range 820SELECT date '4714-11-24 BC'; 821 date 822--------------- 823 4714-11-24 BC 824(1 row) 825 826SELECT date '4714-11-23 BC'; -- out of range 827ERROR: date out of range: "4714-11-23 BC" 828LINE 1: SELECT date '4714-11-23 BC'; 829 ^ 830SELECT date '5874897-12-31'; 831 date 832--------------- 833 5874897-12-31 834(1 row) 835 836SELECT date '5874898-01-01'; -- out of range 837ERROR: date out of range: "5874898-01-01" 838LINE 1: SELECT date '5874898-01-01'; 839 ^ 840RESET datestyle; 841-- 842-- Simple math 843-- Leave most of it for the horology tests 844-- 845SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL; 846 Days From 2K 847-------------- 848 -15607 849 -15542 850 -1403 851 -1402 852 -1401 853 -1400 854 -1037 855 -1036 856 -1035 857 91 858 92 859 93 860 13977 861 14343 862 14710 863(15 rows) 864 865SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL; 866 Days From Epoch 867----------------- 868 -4650 869 -4585 870 9554 871 9555 872 9556 873 9557 874 9920 875 9921 876 9922 877 11048 878 11049 879 11050 880 24934 881 25300 882 25667 883(15 rows) 884 885SELECT date 'yesterday' - date 'today' AS "One day"; 886 One day 887--------- 888 -1 889(1 row) 890 891SELECT date 'today' - date 'tomorrow' AS "One day"; 892 One day 893--------- 894 -1 895(1 row) 896 897SELECT date 'yesterday' - date 'tomorrow' AS "Two days"; 898 Two days 899---------- 900 -2 901(1 row) 902 903SELECT date 'tomorrow' - date 'today' AS "One day"; 904 One day 905--------- 906 1 907(1 row) 908 909SELECT date 'today' - date 'yesterday' AS "One day"; 910 One day 911--------- 912 1 913(1 row) 914 915SELECT date 'tomorrow' - date 'yesterday' AS "Two days"; 916 Two days 917---------- 918 2 919(1 row) 920 921-- 922-- test extract! 923-- 924-- epoch 925-- 926SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0 927 date_part 928----------- 929 0 930(1 row) 931 932SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0 933 date_part 934----------- 935 0 936(1 row) 937 938SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0 939 date_part 940----------- 941 0 942(1 row) 943 944-- 945-- century 946-- 947SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 948 date_part 949----------- 950 -2 951(1 row) 952 953SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 954 date_part 955----------- 956 -1 957(1 row) 958 959SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 960 date_part 961----------- 962 -1 963(1 row) 964 965SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 966 date_part 967----------- 968 1 969(1 row) 970 971SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 972 date_part 973----------- 974 1 975(1 row) 976 977SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 978 date_part 979----------- 980 19 981(1 row) 982 983SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 984 date_part 985----------- 986 20 987(1 row) 988 989SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 990 date_part 991----------- 992 20 993(1 row) 994 995SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 996 date_part 997----------- 998 21 999(1 row) 1000 1001SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true 1002 true 1003------ 1004 t 1005(1 row) 1006 1007-- 1008-- millennium 1009-- 1010SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 1011 date_part 1012----------- 1013 -1 1014(1 row) 1015 1016SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 1017 date_part 1018----------- 1019 1 1020(1 row) 1021 1022SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 1023 date_part 1024----------- 1025 1 1026(1 row) 1027 1028SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 1029 date_part 1030----------- 1031 2 1032(1 row) 1033 1034SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 1035 date_part 1036----------- 1037 2 1038(1 row) 1039 1040SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 1041 date_part 1042----------- 1043 3 1044(1 row) 1045 1046-- next test to be fixed on the turn of the next millennium;-) 1047SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 1048 date_part 1049----------- 1050 3 1051(1 row) 1052 1053-- 1054-- decade 1055-- 1056SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199 1057 date_part 1058----------- 1059 199 1060(1 row) 1061 1062SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1 1063 date_part 1064----------- 1065 1 1066(1 row) 1067 1068SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0 1069 date_part 1070----------- 1071 0 1072(1 row) 1073 1074SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0 1075 date_part 1076----------- 1077 0 1078(1 row) 1079 1080SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1 1081 date_part 1082----------- 1083 -1 1084(1 row) 1085 1086SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1 1087 date_part 1088----------- 1089 -1 1090(1 row) 1091 1092SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2 1093 date_part 1094----------- 1095 -2 1096(1 row) 1097 1098-- 1099-- some other types: 1100-- 1101-- on a timestamp. 1102SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true 1103 true 1104------ 1105 t 1106(1 row) 1107 1108SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20 1109 date_part 1110----------- 1111 20 1112(1 row) 1113 1114-- on an interval 1115SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1 1116 date_part 1117----------- 1118 1 1119(1 row) 1120 1121SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0 1122 date_part 1123----------- 1124 0 1125(1 row) 1126 1127SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0 1128 date_part 1129----------- 1130 0 1131(1 row) 1132 1133SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1 1134 date_part 1135----------- 1136 -1 1137(1 row) 1138 1139-- 1140-- test trunc function! 1141-- 1142SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001 1143 date_trunc 1144-------------------------- 1145 Thu Jan 01 00:00:00 1001 1146(1 row) 1147 1148SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01 1149 date_trunc 1150------------------------------ 1151 Thu Jan 01 00:00:00 1001 PST 1152(1 row) 1153 1154SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901 1155 date_trunc 1156-------------------------- 1157 Tue Jan 01 00:00:00 1901 1158(1 row) 1159 1160SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901 1161 date_trunc 1162------------------------------ 1163 Tue Jan 01 00:00:00 1901 PST 1164(1 row) 1165 1166SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01 1167 date_trunc 1168------------------------------ 1169 Mon Jan 01 00:00:00 2001 PST 1170(1 row) 1171 1172SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01 1173 date_trunc 1174------------------------------ 1175 Mon Jan 01 00:00:00 0001 PST 1176(1 row) 1177 1178SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC 1179 date_trunc 1180--------------------------------- 1181 Tue Jan 01 00:00:00 0100 PST BC 1182(1 row) 1183 1184SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01 1185 date_trunc 1186------------------------------ 1187 Mon Jan 01 00:00:00 1990 PST 1188(1 row) 1189 1190SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC 1191 date_trunc 1192--------------------------------- 1193 Sat Jan 01 00:00:00 0001 PST BC 1194(1 row) 1195 1196SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC 1197 date_trunc 1198--------------------------------- 1199 Mon Jan 01 00:00:00 0011 PST BC 1200(1 row) 1201 1202-- 1203-- test infinity 1204-- 1205select 'infinity'::date, '-infinity'::date; 1206 date | date 1207----------+----------- 1208 infinity | -infinity 1209(1 row) 1210 1211select 'infinity'::date > 'today'::date as t; 1212 t 1213--- 1214 t 1215(1 row) 1216 1217select '-infinity'::date < 'today'::date as t; 1218 t 1219--- 1220 t 1221(1 row) 1222 1223select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date); 1224 isfinite | isfinite | isfinite 1225----------+----------+---------- 1226 f | f | t 1227(1 row) 1228 1229-- 1230-- oscillating fields from non-finite date/timestamptz: 1231-- 1232SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL 1233 date_part 1234----------- 1235 1236(1 row) 1237 1238SELECT EXTRACT(HOUR FROM DATE '-infinity'); -- NULL 1239 date_part 1240----------- 1241 1242(1 row) 1243 1244SELECT EXTRACT(HOUR FROM TIMESTAMP 'infinity'); -- NULL 1245 date_part 1246----------- 1247 1248(1 row) 1249 1250SELECT EXTRACT(HOUR FROM TIMESTAMP '-infinity'); -- NULL 1251 date_part 1252----------- 1253 1254(1 row) 1255 1256SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity'); -- NULL 1257 date_part 1258----------- 1259 1260(1 row) 1261 1262SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity'); -- NULL 1263 date_part 1264----------- 1265 1266(1 row) 1267 1268-- all possible fields 1269SELECT EXTRACT(MICROSECONDS FROM DATE 'infinity'); -- NULL 1270 date_part 1271----------- 1272 1273(1 row) 1274 1275SELECT EXTRACT(MILLISECONDS FROM DATE 'infinity'); -- NULL 1276 date_part 1277----------- 1278 1279(1 row) 1280 1281SELECT EXTRACT(SECOND FROM DATE 'infinity'); -- NULL 1282 date_part 1283----------- 1284 1285(1 row) 1286 1287SELECT EXTRACT(MINUTE FROM DATE 'infinity'); -- NULL 1288 date_part 1289----------- 1290 1291(1 row) 1292 1293SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL 1294 date_part 1295----------- 1296 1297(1 row) 1298 1299SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL 1300 date_part 1301----------- 1302 1303(1 row) 1304 1305SELECT EXTRACT(MONTH FROM DATE 'infinity'); -- NULL 1306 date_part 1307----------- 1308 1309(1 row) 1310 1311SELECT EXTRACT(QUARTER FROM DATE 'infinity'); -- NULL 1312 date_part 1313----------- 1314 1315(1 row) 1316 1317SELECT EXTRACT(WEEK FROM DATE 'infinity'); -- NULL 1318 date_part 1319----------- 1320 1321(1 row) 1322 1323SELECT EXTRACT(DOW FROM DATE 'infinity'); -- NULL 1324 date_part 1325----------- 1326 1327(1 row) 1328 1329SELECT EXTRACT(ISODOW FROM DATE 'infinity'); -- NULL 1330 date_part 1331----------- 1332 1333(1 row) 1334 1335SELECT EXTRACT(DOY FROM DATE 'infinity'); -- NULL 1336 date_part 1337----------- 1338 1339(1 row) 1340 1341SELECT EXTRACT(TIMEZONE FROM DATE 'infinity'); -- NULL 1342 date_part 1343----------- 1344 1345(1 row) 1346 1347SELECT EXTRACT(TIMEZONE_M FROM DATE 'infinity'); -- NULL 1348 date_part 1349----------- 1350 1351(1 row) 1352 1353SELECT EXTRACT(TIMEZONE_H FROM DATE 'infinity'); -- NULL 1354 date_part 1355----------- 1356 1357(1 row) 1358 1359-- 1360-- monotonic fields from non-finite date/timestamptz: 1361-- 1362SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity 1363 date_part 1364----------- 1365 Infinity 1366(1 row) 1367 1368SELECT EXTRACT(EPOCH FROM DATE '-infinity'); -- -Infinity 1369 date_part 1370----------- 1371 -Infinity 1372(1 row) 1373 1374SELECT EXTRACT(EPOCH FROM TIMESTAMP 'infinity'); -- Infinity 1375 date_part 1376----------- 1377 Infinity 1378(1 row) 1379 1380SELECT EXTRACT(EPOCH FROM TIMESTAMP '-infinity'); -- -Infinity 1381 date_part 1382----------- 1383 -Infinity 1384(1 row) 1385 1386SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity'); -- Infinity 1387 date_part 1388----------- 1389 Infinity 1390(1 row) 1391 1392SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity 1393 date_part 1394----------- 1395 -Infinity 1396(1 row) 1397 1398-- all possible fields 1399SELECT EXTRACT(YEAR FROM DATE 'infinity'); -- Infinity 1400 date_part 1401----------- 1402 Infinity 1403(1 row) 1404 1405SELECT EXTRACT(DECADE FROM DATE 'infinity'); -- Infinity 1406 date_part 1407----------- 1408 Infinity 1409(1 row) 1410 1411SELECT EXTRACT(CENTURY FROM DATE 'infinity'); -- Infinity 1412 date_part 1413----------- 1414 Infinity 1415(1 row) 1416 1417SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity'); -- Infinity 1418 date_part 1419----------- 1420 Infinity 1421(1 row) 1422 1423SELECT EXTRACT(JULIAN FROM DATE 'infinity'); -- Infinity 1424 date_part 1425----------- 1426 Infinity 1427(1 row) 1428 1429SELECT EXTRACT(ISOYEAR FROM DATE 'infinity'); -- Infinity 1430 date_part 1431----------- 1432 Infinity 1433(1 row) 1434 1435SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity 1436 date_part 1437----------- 1438 Infinity 1439(1 row) 1440 1441-- 1442-- wrong fields from non-finite date: 1443-- 1444SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR: timestamp units "microsec" not recognized 1445ERROR: timestamp units "microsec" not recognized 1446CONTEXT: SQL function "date_part" statement 1 1447SELECT EXTRACT(UNDEFINED FROM DATE 'infinity'); -- ERROR: timestamp units "undefined" not supported 1448ERROR: timestamp units "undefined" not supported 1449CONTEXT: SQL function "date_part" statement 1 1450-- test constructors 1451select make_date(2013, 7, 15); 1452 make_date 1453------------ 1454 07-15-2013 1455(1 row) 1456 1457select make_date(-44, 3, 15); 1458 make_date 1459--------------- 1460 03-15-0044 BC 1461(1 row) 1462 1463select make_time(8, 20, 0.0); 1464 make_time 1465----------- 1466 08:20:00 1467(1 row) 1468 1469-- should fail 1470select make_date(2013, 2, 30); 1471ERROR: date field value out of range: 2013-02-30 1472select make_date(2013, 13, 1); 1473ERROR: date field value out of range: 2013-13-01 1474select make_date(2013, 11, -1); 1475ERROR: date field value out of range: 2013-11--1 1476select make_time(10, 55, 100.1); 1477ERROR: time field value out of range: 10:55:100.1 1478select make_time(24, 0, 2.1); 1479ERROR: time field value out of range: 24:00:2.1 1480