1-- 2-- HOROLOGY 3-- 4SET DateStyle = 'Postgres, MDY'; 5SHOW TimeZone; -- Many of these tests depend on the prevailing setting 6 TimeZone 7---------- 8 PST8PDT 9(1 row) 10 11-- 12-- Test various input formats 13-- 14SELECT timestamp with time zone '20011227 040506+08'; 15 timestamptz 16------------------------------ 17 Wed Dec 26 12:05:06 2001 PST 18(1 row) 19 20SELECT timestamp with time zone '20011227 040506-08'; 21 timestamptz 22------------------------------ 23 Thu Dec 27 04:05:06 2001 PST 24(1 row) 25 26SELECT timestamp with time zone '20011227 040506.789+08'; 27 timestamptz 28---------------------------------- 29 Wed Dec 26 12:05:06.789 2001 PST 30(1 row) 31 32SELECT timestamp with time zone '20011227 040506.789-08'; 33 timestamptz 34---------------------------------- 35 Thu Dec 27 04:05:06.789 2001 PST 36(1 row) 37 38SELECT timestamp with time zone '20011227T040506+08'; 39 timestamptz 40------------------------------ 41 Wed Dec 26 12:05:06 2001 PST 42(1 row) 43 44SELECT timestamp with time zone '20011227T040506-08'; 45 timestamptz 46------------------------------ 47 Thu Dec 27 04:05:06 2001 PST 48(1 row) 49 50SELECT timestamp with time zone '20011227T040506.789+08'; 51 timestamptz 52---------------------------------- 53 Wed Dec 26 12:05:06.789 2001 PST 54(1 row) 55 56SELECT timestamp with time zone '20011227T040506.789-08'; 57 timestamptz 58---------------------------------- 59 Thu Dec 27 04:05:06.789 2001 PST 60(1 row) 61 62SELECT timestamp with time zone '2001-12-27 04:05:06.789-08'; 63 timestamptz 64---------------------------------- 65 Thu Dec 27 04:05:06.789 2001 PST 66(1 row) 67 68SELECT timestamp with time zone '2001.12.27 04:05:06.789-08'; 69 timestamptz 70---------------------------------- 71 Thu Dec 27 04:05:06.789 2001 PST 72(1 row) 73 74SELECT timestamp with time zone '2001/12/27 04:05:06.789-08'; 75 timestamptz 76---------------------------------- 77 Thu Dec 27 04:05:06.789 2001 PST 78(1 row) 79 80SELECT timestamp with time zone '12/27/2001 04:05:06.789-08'; 81 timestamptz 82---------------------------------- 83 Thu Dec 27 04:05:06.789 2001 PST 84(1 row) 85 86-- should fail in mdy mode: 87SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'; 88ERROR: date/time field value out of range: "27/12/2001 04:05:06.789-08" 89LINE 1: SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'... 90 ^ 91HINT: Perhaps you need a different "datestyle" setting. 92set datestyle to dmy; 93SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'; 94 timestamptz 95---------------------------------- 96 Thu 27 Dec 04:05:06.789 2001 PST 97(1 row) 98 99reset datestyle; 100SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08'; 101 timestamptz 102---------------------------------- 103 Wed Dec 26 12:05:06.789 2001 PST 104(1 row) 105 106SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08'; 107 timestamptz 108---------------------------------- 109 Thu Dec 27 04:05:06.789 2001 PST 110(1 row) 111 112SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08'; 113 timestamptz 114---------------------------------- 115 Wed Dec 26 12:05:06.789 2001 PST 116(1 row) 117 118SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789-08'; 119 timestamptz 120---------------------------------- 121 Thu Dec 27 04:05:06.789 2001 PST 122(1 row) 123 124SELECT timestamp with time zone 'J2452271+08'; 125 timestamptz 126------------------------------ 127 Wed Dec 26 08:00:00 2001 PST 128(1 row) 129 130SELECT timestamp with time zone 'J2452271-08'; 131 timestamptz 132------------------------------ 133 Thu Dec 27 00:00:00 2001 PST 134(1 row) 135 136SELECT timestamp with time zone 'J2452271.5+08'; 137 timestamptz 138------------------------------ 139 Wed Dec 26 20:00:00 2001 PST 140(1 row) 141 142SELECT timestamp with time zone 'J2452271.5-08'; 143 timestamptz 144------------------------------ 145 Thu Dec 27 12:00:00 2001 PST 146(1 row) 147 148SELECT timestamp with time zone 'J2452271 04:05:06+08'; 149 timestamptz 150------------------------------ 151 Wed Dec 26 12:05:06 2001 PST 152(1 row) 153 154SELECT timestamp with time zone 'J2452271 04:05:06-08'; 155 timestamptz 156------------------------------ 157 Thu Dec 27 04:05:06 2001 PST 158(1 row) 159 160SELECT timestamp with time zone 'J2452271T040506+08'; 161 timestamptz 162------------------------------ 163 Wed Dec 26 12:05:06 2001 PST 164(1 row) 165 166SELECT timestamp with time zone 'J2452271T040506-08'; 167 timestamptz 168------------------------------ 169 Thu Dec 27 04:05:06 2001 PST 170(1 row) 171 172SELECT timestamp with time zone 'J2452271T040506.789+08'; 173 timestamptz 174---------------------------------- 175 Wed Dec 26 12:05:06.789 2001 PST 176(1 row) 177 178SELECT timestamp with time zone 'J2452271T040506.789-08'; 179 timestamptz 180---------------------------------- 181 Thu Dec 27 04:05:06.789 2001 PST 182(1 row) 183 184-- German/European-style dates with periods as delimiters 185SELECT timestamp with time zone '12.27.2001 04:05:06.789+08'; 186 timestamptz 187---------------------------------- 188 Wed Dec 26 12:05:06.789 2001 PST 189(1 row) 190 191SELECT timestamp with time zone '12.27.2001 04:05:06.789-08'; 192 timestamptz 193---------------------------------- 194 Thu Dec 27 04:05:06.789 2001 PST 195(1 row) 196 197SET DateStyle = 'German'; 198SELECT timestamp with time zone '27.12.2001 04:05:06.789+08'; 199 timestamptz 200----------------------------- 201 26.12.2001 12:05:06.789 PST 202(1 row) 203 204SELECT timestamp with time zone '27.12.2001 04:05:06.789-08'; 205 timestamptz 206----------------------------- 207 27.12.2001 04:05:06.789 PST 208(1 row) 209 210SET DateStyle = 'ISO'; 211-- As of 7.4, allow time without time zone having a time zone specified 212SELECT time without time zone '040506.789+08'; 213 time 214-------------- 215 04:05:06.789 216(1 row) 217 218SELECT time without time zone '040506.789-08'; 219 time 220-------------- 221 04:05:06.789 222(1 row) 223 224SELECT time without time zone 'T040506.789+08'; 225 time 226-------------- 227 04:05:06.789 228(1 row) 229 230SELECT time without time zone 'T040506.789-08'; 231 time 232-------------- 233 04:05:06.789 234(1 row) 235 236SELECT time with time zone '040506.789+08'; 237 timetz 238----------------- 239 04:05:06.789+08 240(1 row) 241 242SELECT time with time zone '040506.789-08'; 243 timetz 244----------------- 245 04:05:06.789-08 246(1 row) 247 248SELECT time with time zone 'T040506.789+08'; 249 timetz 250----------------- 251 04:05:06.789+08 252(1 row) 253 254SELECT time with time zone 'T040506.789-08'; 255 timetz 256----------------- 257 04:05:06.789-08 258(1 row) 259 260SELECT time with time zone 'T040506.789 +08'; 261 timetz 262----------------- 263 04:05:06.789+08 264(1 row) 265 266SELECT time with time zone 'T040506.789 -08'; 267 timetz 268----------------- 269 04:05:06.789-08 270(1 row) 271 272SET DateStyle = 'Postgres, MDY'; 273-- Check Julian dates BC 274SELECT date 'J1520447' AS "Confucius' Birthday"; 275 Confucius' Birthday 276--------------------- 277 09-28-0551 BC 278(1 row) 279 280SELECT date 'J0' AS "Julian Epoch"; 281 Julian Epoch 282--------------- 283 11-24-4714 BC 284(1 row) 285 286-- 287-- date, time arithmetic 288-- 289SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time"; 290 Date + Time 291-------------------------- 292 Tue Feb 03 04:05:06 1981 293(1 row) 294 295SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST"; 296 Date + Time PST 297------------------------------ 298 Sun Feb 03 04:05:06 1991 PST 299(1 row) 300 301SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC"; 302 Date + Time UTC 303------------------------------ 304 Fri Feb 02 20:05:06 2001 PST 305(1 row) 306 307SELECT date '1991-02-03' + interval '2 years' AS "Add Two Years"; 308 Add Two Years 309-------------------------- 310 Wed Feb 03 00:00:00 1993 311(1 row) 312 313SELECT date '2001-12-13' - interval '2 years' AS "Subtract Two Years"; 314 Subtract Two Years 315-------------------------- 316 Mon Dec 13 00:00:00 1999 317(1 row) 318 319-- subtract time from date should not make sense; use interval instead 320SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time"; 321 Subtract Time 322-------------------------- 323 Sat Feb 02 19:54:54 1991 324(1 row) 325 326SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC"; 327ERROR: operator does not exist: date - time with time zone 328LINE 1: SELECT date '1991-02-03' - time with time zone '04:05:06 UTC... 329 ^ 330HINT: No operator matches the given name and argument types. You might need to add explicit type casts. 331-- 332-- timestamp, interval arithmetic 333-- 334SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29"; 335 Feb 29 336-------------------------- 337 Thu Feb 29 23:59:59 1996 338(1 row) 339 340SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28"; 341 Feb 28 342-------------------------- 343 Sun Feb 28 23:59:59 1999 344(1 row) 345 346SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29"; 347 Feb 29 348-------------------------- 349 Tue Feb 29 23:59:59 2000 350(1 row) 351 352SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; 353 Dec 31 354-------------------------- 355 Fri Dec 31 23:59:59 1999 356(1 row) 357 358SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506"; 359 Feb 23, 285506 360---------------------------- 361 Fri Feb 23 00:00:00 285506 362(1 row) 363 364SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244"; 365 Jan 20, 288244 366---------------------------- 367 Sat Jan 20 00:00:00 288244 368(1 row) 369 370SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276"; 371 Dec 31, 294276 372---------------------------- 373 Sun Dec 31 00:00:00 294276 374(1 row) 375 376SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; 377 106751991 Days 378------------------ 379 @ 106751991 days 380(1 row) 381 382-- Shorthand values 383-- Not directly usable for regression testing since these are not constants. 384-- So, just try to test parser and hope for the best - thomas 97/04/26 385SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '1 day')) as "True"; 386 True 387------ 388 t 389(1 row) 390 391SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1 day')) as "True"; 392 True 393------ 394 t 395(1 row) 396 397SELECT (timestamp without time zone 'today 10:30' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True"; 398 True 399------ 400 t 401(1 row) 402 403SELECT (timestamp without time zone '10:30 today' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True"; 404 True 405------ 406 t 407(1 row) 408 409SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interval '2 days')) as "True"; 410 True 411------ 412 t 413(1 row) 414 415SELECT (timestamp without time zone 'tomorrow 16:00:00' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True"; 416 True 417------ 418 t 419(1 row) 420 421SELECT (timestamp without time zone '16:00:00 tomorrow' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True"; 422 True 423------ 424 t 425(1 row) 426 427SELECT (timestamp without time zone 'yesterday 12:34:56' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True"; 428 True 429------ 430 t 431(1 row) 432 433SELECT (timestamp without time zone '12:34:56 yesterday' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True"; 434 True 435------ 436 t 437(1 row) 438 439SELECT (timestamp without time zone 'tomorrow' > 'now') as "True"; 440 True 441------ 442 t 443(1 row) 444 445-- Convert from date and time to timestamp 446-- This test used to be timestamp(date,time) but no longer allowed by grammar 447-- to enable support for SQL99 timestamp type syntax. 448SELECT date '1994-01-01' + time '11:00' AS "Jan_01_1994_11am"; 449 Jan_01_1994_11am 450-------------------------- 451 Sat Jan 01 11:00:00 1994 452(1 row) 453 454SELECT date '1994-01-01' + time '10:00' AS "Jan_01_1994_10am"; 455 Jan_01_1994_10am 456-------------------------- 457 Sat Jan 01 10:00:00 1994 458(1 row) 459 460SELECT date '1994-01-01' + timetz '11:00-5' AS "Jan_01_1994_8am"; 461 Jan_01_1994_8am 462------------------------------ 463 Sat Jan 01 08:00:00 1994 PST 464(1 row) 465 466SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am"; 467 Jan_01_1994_8am 468------------------------------ 469 Sat Jan 01 08:00:00 1994 PST 470(1 row) 471 472SELECT d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL; 473 one_year 474----------------------------- 475 -infinity 476 infinity 477 Fri Jan 01 00:00:00 1971 478 Tue Feb 10 17:32:01 1998 479 Tue Feb 10 17:32:01 1998 480 Tue Feb 10 17:32:02 1998 481 Tue Feb 10 17:32:01.4 1998 482 Tue Feb 10 17:32:01.5 1998 483 Tue Feb 10 17:32:01.6 1998 484 Fri Jan 02 00:00:00 1998 485 Fri Jan 02 03:04:05 1998 486 Tue Feb 10 17:32:01 1998 487 Tue Feb 10 17:32:01 1998 488 Tue Feb 10 17:32:01 1998 489 Tue Feb 10 17:32:01 1998 490 Wed Jun 10 17:32:01 1998 491 Sun Sep 22 18:19:20 2002 492 Thu Mar 15 08:14:01 2001 493 Thu Mar 15 13:14:02 2001 494 Thu Mar 15 12:14:03 2001 495 Thu Mar 15 03:14:04 2001 496 Thu Mar 15 02:14:05 2001 497 Tue Feb 10 17:32:01 1998 498 Tue Feb 10 17:32:01 1998 499 Tue Feb 10 17:32:00 1998 500 Tue Feb 10 17:32:01 1998 501 Tue Feb 10 17:32:01 1998 502 Tue Feb 10 17:32:01 1998 503 Tue Feb 10 17:32:01 1998 504 Tue Feb 10 17:32:01 1998 505 Tue Feb 10 17:32:01 1998 506 Tue Feb 10 17:32:01 1998 507 Tue Feb 10 17:32:01 1998 508 Tue Feb 10 17:32:01 1998 509 Wed Jun 10 18:32:01 1998 510 Tue Feb 10 17:32:01 1998 511 Wed Feb 11 17:32:01 1998 512 Thu Feb 12 17:32:01 1998 513 Fri Feb 13 17:32:01 1998 514 Sat Feb 14 17:32:01 1998 515 Sun Feb 15 17:32:01 1998 516 Mon Feb 16 17:32:01 1998 517 Thu Feb 16 17:32:01 0096 BC 518 Sun Feb 16 17:32:01 0098 519 Fri Feb 16 17:32:01 0598 520 Wed Feb 16 17:32:01 1098 521 Sun Feb 16 17:32:01 1698 522 Fri Feb 16 17:32:01 1798 523 Wed Feb 16 17:32:01 1898 524 Mon Feb 16 17:32:01 1998 525 Sun Feb 16 17:32:01 2098 526 Fri Feb 28 17:32:01 1997 527 Fri Feb 28 17:32:01 1997 528 Sat Mar 01 17:32:01 1997 529 Tue Dec 30 17:32:01 1997 530 Wed Dec 31 17:32:01 1997 531 Thu Jan 01 17:32:01 1998 532 Sat Feb 28 17:32:01 1998 533 Sun Mar 01 17:32:01 1998 534 Wed Dec 30 17:32:01 1998 535 Thu Dec 31 17:32:01 1998 536 Sun Dec 31 17:32:01 2000 537 Mon Jan 01 17:32:01 2001 538 Mon Dec 31 17:32:01 2001 539 Tue Jan 01 17:32:01 2002 540(65 rows) 541 542SELECT d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL; 543 one_year 544----------------------------- 545 -infinity 546 infinity 547 Wed Jan 01 00:00:00 1969 548 Sat Feb 10 17:32:01 1996 549 Sat Feb 10 17:32:01 1996 550 Sat Feb 10 17:32:02 1996 551 Sat Feb 10 17:32:01.4 1996 552 Sat Feb 10 17:32:01.5 1996 553 Sat Feb 10 17:32:01.6 1996 554 Tue Jan 02 00:00:00 1996 555 Tue Jan 02 03:04:05 1996 556 Sat Feb 10 17:32:01 1996 557 Sat Feb 10 17:32:01 1996 558 Sat Feb 10 17:32:01 1996 559 Sat Feb 10 17:32:01 1996 560 Mon Jun 10 17:32:01 1996 561 Fri Sep 22 18:19:20 2000 562 Mon Mar 15 08:14:01 1999 563 Mon Mar 15 13:14:02 1999 564 Mon Mar 15 12:14:03 1999 565 Mon Mar 15 03:14:04 1999 566 Mon Mar 15 02:14:05 1999 567 Sat Feb 10 17:32:01 1996 568 Sat Feb 10 17:32:01 1996 569 Sat Feb 10 17:32:00 1996 570 Sat Feb 10 17:32:01 1996 571 Sat Feb 10 17:32:01 1996 572 Sat Feb 10 17:32:01 1996 573 Sat Feb 10 17:32:01 1996 574 Sat Feb 10 17:32:01 1996 575 Sat Feb 10 17:32:01 1996 576 Sat Feb 10 17:32:01 1996 577 Sat Feb 10 17:32:01 1996 578 Sat Feb 10 17:32:01 1996 579 Mon Jun 10 18:32:01 1996 580 Sat Feb 10 17:32:01 1996 581 Sun Feb 11 17:32:01 1996 582 Mon Feb 12 17:32:01 1996 583 Tue Feb 13 17:32:01 1996 584 Wed Feb 14 17:32:01 1996 585 Thu Feb 15 17:32:01 1996 586 Fri Feb 16 17:32:01 1996 587 Mon Feb 16 17:32:01 0098 BC 588 Thu Feb 16 17:32:01 0096 589 Tue Feb 16 17:32:01 0596 590 Sun Feb 16 17:32:01 1096 591 Thu Feb 16 17:32:01 1696 592 Tue Feb 16 17:32:01 1796 593 Sun Feb 16 17:32:01 1896 594 Fri Feb 16 17:32:01 1996 595 Thu Feb 16 17:32:01 2096 596 Tue Feb 28 17:32:01 1995 597 Tue Feb 28 17:32:01 1995 598 Wed Mar 01 17:32:01 1995 599 Sat Dec 30 17:32:01 1995 600 Sun Dec 31 17:32:01 1995 601 Mon Jan 01 17:32:01 1996 602 Wed Feb 28 17:32:01 1996 603 Fri Mar 01 17:32:01 1996 604 Mon Dec 30 17:32:01 1996 605 Tue Dec 31 17:32:01 1996 606 Thu Dec 31 17:32:01 1998 607 Fri Jan 01 17:32:01 1999 608 Fri Dec 31 17:32:01 1999 609 Sat Jan 01 17:32:01 2000 610(65 rows) 611 612SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29"; 613 Feb 29 614------------------------------ 615 Thu Feb 29 23:59:59 1996 PST 616(1 row) 617 618SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28"; 619 Feb 28 620------------------------------ 621 Sun Feb 28 23:59:59 1999 PST 622(1 row) 623 624SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29"; 625 Feb 29 626------------------------------ 627 Tue Feb 29 23:59:59 2000 PST 628(1 row) 629 630SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; 631 Dec 31 632------------------------------ 633 Fri Dec 31 23:59:59 1999 PST 634(1 row) 635 636SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; 637 True 638------ 639 t 640(1 row) 641 642SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; 643 True 644------ 645 t 646(1 row) 647 648SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True"; 649 True 650------ 651 t 652(1 row) 653 654SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; 655 True 656------ 657 t 658(1 row) 659 660-- timestamp with time zone, interval arithmetic around DST change 661-- (just for fun, let's use an intentionally nonstandard POSIX zone spec) 662SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0'; 663SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00"; 664 Apr 3, 12:00 665------------------------------ 666 Sun Apr 03 12:00:00 2005 CDT 667(1 row) 668 669SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00"; 670 Apr 3, 13:00 671------------------------------ 672 Sun Apr 03 13:00:00 2005 CDT 673(1 row) 674 675SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00"; 676 Apr 2, 12:00 677------------------------------ 678 Sat Apr 02 12:00:00 2005 CST 679(1 row) 680 681SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00"; 682 Apr 2, 11:00 683------------------------------ 684 Sat Apr 02 11:00:00 2005 CST 685(1 row) 686 687RESET TIME ZONE; 688SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am"; 689 Jan_01_1994_10am 690------------------------------ 691 Sat Jan 01 11:00:00 1994 PST 692(1 row) 693 694SELECT timestamptz(date '1994-01-01', time '10:00') AS "Jan_01_1994_9am"; 695 Jan_01_1994_9am 696------------------------------ 697 Sat Jan 01 10:00:00 1994 PST 698(1 row) 699 700SELECT timestamptz(date '1994-01-01', time with time zone '11:00-8') AS "Jan_01_1994_11am"; 701 Jan_01_1994_11am 702------------------------------ 703 Sat Jan 01 11:00:00 1994 PST 704(1 row) 705 706SELECT timestamptz(date '1994-01-01', time with time zone '10:00-8') AS "Jan_01_1994_10am"; 707 Jan_01_1994_10am 708------------------------------ 709 Sat Jan 01 10:00:00 1994 PST 710(1 row) 711 712SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am"; 713 Jan_01_1994_8am 714------------------------------ 715 Sat Jan 01 08:00:00 1994 PST 716(1 row) 717 718SELECT d1 + interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 719 one_year 720--------------------------------- 721 -infinity 722 infinity 723 Thu Dec 31 16:00:00 1970 PST 724 Tue Feb 10 17:32:01 1998 PST 725 Tue Feb 10 17:32:01 1998 PST 726 Tue Feb 10 17:32:02 1998 PST 727 Tue Feb 10 17:32:01.4 1998 PST 728 Tue Feb 10 17:32:01.5 1998 PST 729 Tue Feb 10 17:32:01.6 1998 PST 730 Fri Jan 02 00:00:00 1998 PST 731 Fri Jan 02 03:04:05 1998 PST 732 Tue Feb 10 17:32:01 1998 PST 733 Tue Feb 10 17:32:01 1998 PST 734 Tue Feb 10 17:32:01 1998 PST 735 Tue Feb 10 17:32:01 1998 PST 736 Wed Jun 10 17:32:01 1998 PDT 737 Sun Sep 22 18:19:20 2002 PDT 738 Thu Mar 15 08:14:01 2001 PST 739 Thu Mar 15 04:14:02 2001 PST 740 Thu Mar 15 02:14:03 2001 PST 741 Thu Mar 15 03:14:04 2001 PST 742 Thu Mar 15 01:14:05 2001 PST 743 Tue Feb 10 17:32:01 1998 PST 744 Tue Feb 10 17:32:01 1998 PST 745 Tue Feb 10 17:32:00 1998 PST 746 Tue Feb 10 17:32:01 1998 PST 747 Tue Feb 10 17:32:01 1998 PST 748 Tue Feb 10 17:32:01 1998 PST 749 Tue Feb 10 17:32:01 1998 PST 750 Tue Feb 10 17:32:01 1998 PST 751 Tue Feb 10 09:32:01 1998 PST 752 Tue Feb 10 09:32:01 1998 PST 753 Tue Feb 10 09:32:01 1998 PST 754 Tue Feb 10 14:32:01 1998 PST 755 Fri Jul 10 14:32:01 1998 PDT 756 Wed Jun 10 18:32:01 1998 PDT 757 Tue Feb 10 17:32:01 1998 PST 758 Wed Feb 11 17:32:01 1998 PST 759 Thu Feb 12 17:32:01 1998 PST 760 Fri Feb 13 17:32:01 1998 PST 761 Sat Feb 14 17:32:01 1998 PST 762 Sun Feb 15 17:32:01 1998 PST 763 Mon Feb 16 17:32:01 1998 PST 764 Thu Feb 16 17:32:01 0096 PST BC 765 Sun Feb 16 17:32:01 0098 PST 766 Fri Feb 16 17:32:01 0598 PST 767 Wed Feb 16 17:32:01 1098 PST 768 Sun Feb 16 17:32:01 1698 PST 769 Fri Feb 16 17:32:01 1798 PST 770 Wed Feb 16 17:32:01 1898 PST 771 Mon Feb 16 17:32:01 1998 PST 772 Sun Feb 16 17:32:01 2098 PST 773 Fri Feb 28 17:32:01 1997 PST 774 Fri Feb 28 17:32:01 1997 PST 775 Sat Mar 01 17:32:01 1997 PST 776 Tue Dec 30 17:32:01 1997 PST 777 Wed Dec 31 17:32:01 1997 PST 778 Thu Jan 01 17:32:01 1998 PST 779 Sat Feb 28 17:32:01 1998 PST 780 Sun Mar 01 17:32:01 1998 PST 781 Wed Dec 30 17:32:01 1998 PST 782 Thu Dec 31 17:32:01 1998 PST 783 Sun Dec 31 17:32:01 2000 PST 784 Mon Jan 01 17:32:01 2001 PST 785 Mon Dec 31 17:32:01 2001 PST 786 Tue Jan 01 17:32:01 2002 PST 787(66 rows) 788 789SELECT d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 790 one_year 791--------------------------------- 792 -infinity 793 infinity 794 Tue Dec 31 16:00:00 1968 PST 795 Sat Feb 10 17:32:01 1996 PST 796 Sat Feb 10 17:32:01 1996 PST 797 Sat Feb 10 17:32:02 1996 PST 798 Sat Feb 10 17:32:01.4 1996 PST 799 Sat Feb 10 17:32:01.5 1996 PST 800 Sat Feb 10 17:32:01.6 1996 PST 801 Tue Jan 02 00:00:00 1996 PST 802 Tue Jan 02 03:04:05 1996 PST 803 Sat Feb 10 17:32:01 1996 PST 804 Sat Feb 10 17:32:01 1996 PST 805 Sat Feb 10 17:32:01 1996 PST 806 Sat Feb 10 17:32:01 1996 PST 807 Mon Jun 10 17:32:01 1996 PDT 808 Fri Sep 22 18:19:20 2000 PDT 809 Mon Mar 15 08:14:01 1999 PST 810 Mon Mar 15 04:14:02 1999 PST 811 Mon Mar 15 02:14:03 1999 PST 812 Mon Mar 15 03:14:04 1999 PST 813 Mon Mar 15 01:14:05 1999 PST 814 Sat Feb 10 17:32:01 1996 PST 815 Sat Feb 10 17:32:01 1996 PST 816 Sat Feb 10 17:32:00 1996 PST 817 Sat Feb 10 17:32:01 1996 PST 818 Sat Feb 10 17:32:01 1996 PST 819 Sat Feb 10 17:32:01 1996 PST 820 Sat Feb 10 17:32:01 1996 PST 821 Sat Feb 10 17:32:01 1996 PST 822 Sat Feb 10 09:32:01 1996 PST 823 Sat Feb 10 09:32:01 1996 PST 824 Sat Feb 10 09:32:01 1996 PST 825 Sat Feb 10 14:32:01 1996 PST 826 Wed Jul 10 14:32:01 1996 PDT 827 Mon Jun 10 18:32:01 1996 PDT 828 Sat Feb 10 17:32:01 1996 PST 829 Sun Feb 11 17:32:01 1996 PST 830 Mon Feb 12 17:32:01 1996 PST 831 Tue Feb 13 17:32:01 1996 PST 832 Wed Feb 14 17:32:01 1996 PST 833 Thu Feb 15 17:32:01 1996 PST 834 Fri Feb 16 17:32:01 1996 PST 835 Mon Feb 16 17:32:01 0098 PST BC 836 Thu Feb 16 17:32:01 0096 PST 837 Tue Feb 16 17:32:01 0596 PST 838 Sun Feb 16 17:32:01 1096 PST 839 Thu Feb 16 17:32:01 1696 PST 840 Tue Feb 16 17:32:01 1796 PST 841 Sun Feb 16 17:32:01 1896 PST 842 Fri Feb 16 17:32:01 1996 PST 843 Thu Feb 16 17:32:01 2096 PST 844 Tue Feb 28 17:32:01 1995 PST 845 Tue Feb 28 17:32:01 1995 PST 846 Wed Mar 01 17:32:01 1995 PST 847 Sat Dec 30 17:32:01 1995 PST 848 Sun Dec 31 17:32:01 1995 PST 849 Mon Jan 01 17:32:01 1996 PST 850 Wed Feb 28 17:32:01 1996 PST 851 Fri Mar 01 17:32:01 1996 PST 852 Mon Dec 30 17:32:01 1996 PST 853 Tue Dec 31 17:32:01 1996 PST 854 Thu Dec 31 17:32:01 1998 PST 855 Fri Jan 01 17:32:01 1999 PST 856 Fri Dec 31 17:32:01 1999 PST 857 Sat Jan 01 17:32:01 2000 PST 858(66 rows) 859 860-- 861-- time, interval arithmetic 862-- 863SELECT CAST(time '01:02' AS interval) AS "+01:02"; 864 +01:02 865----------------- 866 @ 1 hour 2 mins 867(1 row) 868 869SELECT CAST(interval '02:03' AS time) AS "02:03:00"; 870 02:03:00 871---------- 872 02:03:00 873(1 row) 874 875SELECT time '01:30' + interval '02:01' AS "03:31:00"; 876 03:31:00 877---------- 878 03:31:00 879(1 row) 880 881SELECT time '01:30' - interval '02:01' AS "23:29:00"; 882 23:29:00 883---------- 884 23:29:00 885(1 row) 886 887SELECT time '02:30' + interval '36:01' AS "14:31:00"; 888 14:31:00 889---------- 890 14:31:00 891(1 row) 892 893SELECT time '03:30' + interval '1 month 04:01' AS "07:31:00"; 894 07:31:00 895---------- 896 07:31:00 897(1 row) 898 899SELECT CAST(time with time zone '01:02-08' AS interval) AS "+00:01"; 900ERROR: cannot cast type time with time zone to interval 901LINE 1: SELECT CAST(time with time zone '01:02-08' AS interval) AS "... 902 ^ 903SELECT CAST(interval '02:03' AS time with time zone) AS "02:03:00-08"; 904ERROR: cannot cast type interval to time with time zone 905LINE 1: SELECT CAST(interval '02:03' AS time with time zone) AS "02:... 906 ^ 907SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08"; 908 23:29:00-08 909------------- 910 23:29:00-08 911(1 row) 912 913SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08"; 914 14:31:00-08 915------------- 916 14:31:00-08 917(1 row) 918 919-- These two tests cannot be used because they default to current timezone, 920-- which may be either -08 or -07 depending on the time of year. 921-- SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08"; 922-- SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08"; 923-- Try the following two tests instead, as a poor substitute 924SELECT CAST(CAST(date 'today' + time with time zone '05:30' 925 + interval '02:01' AS time with time zone) AS time) AS "07:31:00"; 926 07:31:00 927---------- 928 07:31:00 929(1 row) 930 931SELECT CAST(cast(date 'today' + time with time zone '03:30' 932 + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00"; 933 07:31:00 934---------- 935 07:31:00 936(1 row) 937 938SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract" 939 FROM TIMESTAMP_TBL t, INTERVAL_TBL i 940 WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01' 941 AND i.f1 BETWEEN '00:00' AND '23:00' 942 ORDER BY 1,2; 943 t | i | add | subtract 944----------------------------+-----------+----------------------------+---------------------------- 945 Wed Feb 28 17:32:01 1996 | @ 1 min | Wed Feb 28 17:33:01 1996 | Wed Feb 28 17:31:01 1996 946 Wed Feb 28 17:32:01 1996 | @ 5 hours | Wed Feb 28 22:32:01 1996 | Wed Feb 28 12:32:01 1996 947 Thu Feb 29 17:32:01 1996 | @ 1 min | Thu Feb 29 17:33:01 1996 | Thu Feb 29 17:31:01 1996 948 Thu Feb 29 17:32:01 1996 | @ 5 hours | Thu Feb 29 22:32:01 1996 | Thu Feb 29 12:32:01 1996 949 Fri Mar 01 17:32:01 1996 | @ 1 min | Fri Mar 01 17:33:01 1996 | Fri Mar 01 17:31:01 1996 950 Fri Mar 01 17:32:01 1996 | @ 5 hours | Fri Mar 01 22:32:01 1996 | Fri Mar 01 12:32:01 1996 951 Mon Dec 30 17:32:01 1996 | @ 1 min | Mon Dec 30 17:33:01 1996 | Mon Dec 30 17:31:01 1996 952 Mon Dec 30 17:32:01 1996 | @ 5 hours | Mon Dec 30 22:32:01 1996 | Mon Dec 30 12:32:01 1996 953 Tue Dec 31 17:32:01 1996 | @ 1 min | Tue Dec 31 17:33:01 1996 | Tue Dec 31 17:31:01 1996 954 Tue Dec 31 17:32:01 1996 | @ 5 hours | Tue Dec 31 22:32:01 1996 | Tue Dec 31 12:32:01 1996 955 Wed Jan 01 17:32:01 1997 | @ 1 min | Wed Jan 01 17:33:01 1997 | Wed Jan 01 17:31:01 1997 956 Wed Jan 01 17:32:01 1997 | @ 5 hours | Wed Jan 01 22:32:01 1997 | Wed Jan 01 12:32:01 1997 957 Thu Jan 02 00:00:00 1997 | @ 1 min | Thu Jan 02 00:01:00 1997 | Wed Jan 01 23:59:00 1997 958 Thu Jan 02 00:00:00 1997 | @ 5 hours | Thu Jan 02 05:00:00 1997 | Wed Jan 01 19:00:00 1997 959 Thu Jan 02 03:04:05 1997 | @ 1 min | Thu Jan 02 03:05:05 1997 | Thu Jan 02 03:03:05 1997 960 Thu Jan 02 03:04:05 1997 | @ 5 hours | Thu Jan 02 08:04:05 1997 | Wed Jan 01 22:04:05 1997 961 Mon Feb 10 17:32:00 1997 | @ 1 min | Mon Feb 10 17:33:00 1997 | Mon Feb 10 17:31:00 1997 962 Mon Feb 10 17:32:00 1997 | @ 5 hours | Mon Feb 10 22:32:00 1997 | Mon Feb 10 12:32:00 1997 963 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 964 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 965 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 966 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 967 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 968 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 969 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 970 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 971 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 972 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 973 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 974 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 975 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 976 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 977 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 978 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 979 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 980 Mon Feb 10 17:32:01 1997 | @ 1 min | Mon Feb 10 17:33:01 1997 | Mon Feb 10 17:31:01 1997 981 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 982 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 983 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 984 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 985 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 986 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 987 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 988 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 989 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 990 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 991 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 992 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 993 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 994 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 995 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 996 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 997 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 998 Mon Feb 10 17:32:01 1997 | @ 5 hours | Mon Feb 10 22:32:01 1997 | Mon Feb 10 12:32:01 1997 999 Mon Feb 10 17:32:01.4 1997 | @ 1 min | Mon Feb 10 17:33:01.4 1997 | Mon Feb 10 17:31:01.4 1997 1000 Mon Feb 10 17:32:01.4 1997 | @ 5 hours | Mon Feb 10 22:32:01.4 1997 | Mon Feb 10 12:32:01.4 1997 1001 Mon Feb 10 17:32:01.5 1997 | @ 1 min | Mon Feb 10 17:33:01.5 1997 | Mon Feb 10 17:31:01.5 1997 1002 Mon Feb 10 17:32:01.5 1997 | @ 5 hours | Mon Feb 10 22:32:01.5 1997 | Mon Feb 10 12:32:01.5 1997 1003 Mon Feb 10 17:32:01.6 1997 | @ 1 min | Mon Feb 10 17:33:01.6 1997 | Mon Feb 10 17:31:01.6 1997 1004 Mon Feb 10 17:32:01.6 1997 | @ 5 hours | Mon Feb 10 22:32:01.6 1997 | Mon Feb 10 12:32:01.6 1997 1005 Mon Feb 10 17:32:02 1997 | @ 1 min | Mon Feb 10 17:33:02 1997 | Mon Feb 10 17:31:02 1997 1006 Mon Feb 10 17:32:02 1997 | @ 5 hours | Mon Feb 10 22:32:02 1997 | Mon Feb 10 12:32:02 1997 1007 Tue Feb 11 17:32:01 1997 | @ 1 min | Tue Feb 11 17:33:01 1997 | Tue Feb 11 17:31:01 1997 1008 Tue Feb 11 17:32:01 1997 | @ 5 hours | Tue Feb 11 22:32:01 1997 | Tue Feb 11 12:32:01 1997 1009 Wed Feb 12 17:32:01 1997 | @ 1 min | Wed Feb 12 17:33:01 1997 | Wed Feb 12 17:31:01 1997 1010 Wed Feb 12 17:32:01 1997 | @ 5 hours | Wed Feb 12 22:32:01 1997 | Wed Feb 12 12:32:01 1997 1011 Thu Feb 13 17:32:01 1997 | @ 1 min | Thu Feb 13 17:33:01 1997 | Thu Feb 13 17:31:01 1997 1012 Thu Feb 13 17:32:01 1997 | @ 5 hours | Thu Feb 13 22:32:01 1997 | Thu Feb 13 12:32:01 1997 1013 Fri Feb 14 17:32:01 1997 | @ 1 min | Fri Feb 14 17:33:01 1997 | Fri Feb 14 17:31:01 1997 1014 Fri Feb 14 17:32:01 1997 | @ 5 hours | Fri Feb 14 22:32:01 1997 | Fri Feb 14 12:32:01 1997 1015 Sat Feb 15 17:32:01 1997 | @ 1 min | Sat Feb 15 17:33:01 1997 | Sat Feb 15 17:31:01 1997 1016 Sat Feb 15 17:32:01 1997 | @ 5 hours | Sat Feb 15 22:32:01 1997 | Sat Feb 15 12:32:01 1997 1017 Sun Feb 16 17:32:01 1997 | @ 1 min | Sun Feb 16 17:33:01 1997 | Sun Feb 16 17:31:01 1997 1018 Sun Feb 16 17:32:01 1997 | @ 1 min | Sun Feb 16 17:33:01 1997 | Sun Feb 16 17:31:01 1997 1019 Sun Feb 16 17:32:01 1997 | @ 5 hours | Sun Feb 16 22:32:01 1997 | Sun Feb 16 12:32:01 1997 1020 Sun Feb 16 17:32:01 1997 | @ 5 hours | Sun Feb 16 22:32:01 1997 | Sun Feb 16 12:32:01 1997 1021 Fri Feb 28 17:32:01 1997 | @ 1 min | Fri Feb 28 17:33:01 1997 | Fri Feb 28 17:31:01 1997 1022 Fri Feb 28 17:32:01 1997 | @ 5 hours | Fri Feb 28 22:32:01 1997 | Fri Feb 28 12:32:01 1997 1023 Sat Mar 01 17:32:01 1997 | @ 1 min | Sat Mar 01 17:33:01 1997 | Sat Mar 01 17:31:01 1997 1024 Sat Mar 01 17:32:01 1997 | @ 5 hours | Sat Mar 01 22:32:01 1997 | Sat Mar 01 12:32:01 1997 1025 Tue Jun 10 17:32:01 1997 | @ 1 min | Tue Jun 10 17:33:01 1997 | Tue Jun 10 17:31:01 1997 1026 Tue Jun 10 17:32:01 1997 | @ 5 hours | Tue Jun 10 22:32:01 1997 | Tue Jun 10 12:32:01 1997 1027 Tue Jun 10 18:32:01 1997 | @ 1 min | Tue Jun 10 18:33:01 1997 | Tue Jun 10 18:31:01 1997 1028 Tue Jun 10 18:32:01 1997 | @ 5 hours | Tue Jun 10 23:32:01 1997 | Tue Jun 10 13:32:01 1997 1029 Tue Dec 30 17:32:01 1997 | @ 1 min | Tue Dec 30 17:33:01 1997 | Tue Dec 30 17:31:01 1997 1030 Tue Dec 30 17:32:01 1997 | @ 5 hours | Tue Dec 30 22:32:01 1997 | Tue Dec 30 12:32:01 1997 1031 Wed Dec 31 17:32:01 1997 | @ 1 min | Wed Dec 31 17:33:01 1997 | Wed Dec 31 17:31:01 1997 1032 Wed Dec 31 17:32:01 1997 | @ 5 hours | Wed Dec 31 22:32:01 1997 | Wed Dec 31 12:32:01 1997 1033 Fri Dec 31 17:32:01 1999 | @ 1 min | Fri Dec 31 17:33:01 1999 | Fri Dec 31 17:31:01 1999 1034 Fri Dec 31 17:32:01 1999 | @ 5 hours | Fri Dec 31 22:32:01 1999 | Fri Dec 31 12:32:01 1999 1035 Sat Jan 01 17:32:01 2000 | @ 1 min | Sat Jan 01 17:33:01 2000 | Sat Jan 01 17:31:01 2000 1036 Sat Jan 01 17:32:01 2000 | @ 5 hours | Sat Jan 01 22:32:01 2000 | Sat Jan 01 12:32:01 2000 1037 Wed Mar 15 02:14:05 2000 | @ 1 min | Wed Mar 15 02:15:05 2000 | Wed Mar 15 02:13:05 2000 1038 Wed Mar 15 02:14:05 2000 | @ 5 hours | Wed Mar 15 07:14:05 2000 | Tue Mar 14 21:14:05 2000 1039 Wed Mar 15 03:14:04 2000 | @ 1 min | Wed Mar 15 03:15:04 2000 | Wed Mar 15 03:13:04 2000 1040 Wed Mar 15 03:14:04 2000 | @ 5 hours | Wed Mar 15 08:14:04 2000 | Tue Mar 14 22:14:04 2000 1041 Wed Mar 15 08:14:01 2000 | @ 1 min | Wed Mar 15 08:15:01 2000 | Wed Mar 15 08:13:01 2000 1042 Wed Mar 15 08:14:01 2000 | @ 5 hours | Wed Mar 15 13:14:01 2000 | Wed Mar 15 03:14:01 2000 1043 Wed Mar 15 12:14:03 2000 | @ 1 min | Wed Mar 15 12:15:03 2000 | Wed Mar 15 12:13:03 2000 1044 Wed Mar 15 12:14:03 2000 | @ 5 hours | Wed Mar 15 17:14:03 2000 | Wed Mar 15 07:14:03 2000 1045 Wed Mar 15 13:14:02 2000 | @ 1 min | Wed Mar 15 13:15:02 2000 | Wed Mar 15 13:13:02 2000 1046 Wed Mar 15 13:14:02 2000 | @ 5 hours | Wed Mar 15 18:14:02 2000 | Wed Mar 15 08:14:02 2000 1047 Sun Dec 31 17:32:01 2000 | @ 1 min | Sun Dec 31 17:33:01 2000 | Sun Dec 31 17:31:01 2000 1048 Sun Dec 31 17:32:01 2000 | @ 5 hours | Sun Dec 31 22:32:01 2000 | Sun Dec 31 12:32:01 2000 1049(104 rows) 1050 1051SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 1052 FROM TIME_TBL t, INTERVAL_TBL i 1053 ORDER BY 1,2; 1054 t | i | add | subtract 1055-------------+-------------------------------+-------------+------------- 1056 00:00:00 | @ 14 secs ago | 23:59:46 | 00:00:14 1057 00:00:00 | @ 1 min | 00:01:00 | 23:59:00 1058 00:00:00 | @ 5 hours | 05:00:00 | 19:00:00 1059 00:00:00 | @ 1 day 2 hours 3 mins 4 secs | 02:03:04 | 21:56:56 1060 00:00:00 | @ 10 days | 00:00:00 | 00:00:00 1061 00:00:00 | @ 3 mons | 00:00:00 | 00:00:00 1062 00:00:00 | @ 5 mons | 00:00:00 | 00:00:00 1063 00:00:00 | @ 5 mons 12 hours | 12:00:00 | 12:00:00 1064 00:00:00 | @ 6 years | 00:00:00 | 00:00:00 1065 00:00:00 | @ 34 years | 00:00:00 | 00:00:00 1066 01:00:00 | @ 14 secs ago | 00:59:46 | 01:00:14 1067 01:00:00 | @ 1 min | 01:01:00 | 00:59:00 1068 01:00:00 | @ 5 hours | 06:00:00 | 20:00:00 1069 01:00:00 | @ 1 day 2 hours 3 mins 4 secs | 03:03:04 | 22:56:56 1070 01:00:00 | @ 10 days | 01:00:00 | 01:00:00 1071 01:00:00 | @ 3 mons | 01:00:00 | 01:00:00 1072 01:00:00 | @ 5 mons | 01:00:00 | 01:00:00 1073 01:00:00 | @ 5 mons 12 hours | 13:00:00 | 13:00:00 1074 01:00:00 | @ 6 years | 01:00:00 | 01:00:00 1075 01:00:00 | @ 34 years | 01:00:00 | 01:00:00 1076 02:03:00 | @ 14 secs ago | 02:02:46 | 02:03:14 1077 02:03:00 | @ 1 min | 02:04:00 | 02:02:00 1078 02:03:00 | @ 5 hours | 07:03:00 | 21:03:00 1079 02:03:00 | @ 1 day 2 hours 3 mins 4 secs | 04:06:04 | 23:59:56 1080 02:03:00 | @ 10 days | 02:03:00 | 02:03:00 1081 02:03:00 | @ 3 mons | 02:03:00 | 02:03:00 1082 02:03:00 | @ 5 mons | 02:03:00 | 02:03:00 1083 02:03:00 | @ 5 mons 12 hours | 14:03:00 | 14:03:00 1084 02:03:00 | @ 6 years | 02:03:00 | 02:03:00 1085 02:03:00 | @ 34 years | 02:03:00 | 02:03:00 1086 11:59:00 | @ 14 secs ago | 11:58:46 | 11:59:14 1087 11:59:00 | @ 1 min | 12:00:00 | 11:58:00 1088 11:59:00 | @ 5 hours | 16:59:00 | 06:59:00 1089 11:59:00 | @ 1 day 2 hours 3 mins 4 secs | 14:02:04 | 09:55:56 1090 11:59:00 | @ 10 days | 11:59:00 | 11:59:00 1091 11:59:00 | @ 3 mons | 11:59:00 | 11:59:00 1092 11:59:00 | @ 5 mons | 11:59:00 | 11:59:00 1093 11:59:00 | @ 5 mons 12 hours | 23:59:00 | 23:59:00 1094 11:59:00 | @ 6 years | 11:59:00 | 11:59:00 1095 11:59:00 | @ 34 years | 11:59:00 | 11:59:00 1096 12:00:00 | @ 14 secs ago | 11:59:46 | 12:00:14 1097 12:00:00 | @ 1 min | 12:01:00 | 11:59:00 1098 12:00:00 | @ 5 hours | 17:00:00 | 07:00:00 1099 12:00:00 | @ 1 day 2 hours 3 mins 4 secs | 14:03:04 | 09:56:56 1100 12:00:00 | @ 10 days | 12:00:00 | 12:00:00 1101 12:00:00 | @ 3 mons | 12:00:00 | 12:00:00 1102 12:00:00 | @ 5 mons | 12:00:00 | 12:00:00 1103 12:00:00 | @ 5 mons 12 hours | 00:00:00 | 00:00:00 1104 12:00:00 | @ 6 years | 12:00:00 | 12:00:00 1105 12:00:00 | @ 34 years | 12:00:00 | 12:00:00 1106 12:01:00 | @ 14 secs ago | 12:00:46 | 12:01:14 1107 12:01:00 | @ 1 min | 12:02:00 | 12:00:00 1108 12:01:00 | @ 5 hours | 17:01:00 | 07:01:00 1109 12:01:00 | @ 1 day 2 hours 3 mins 4 secs | 14:04:04 | 09:57:56 1110 12:01:00 | @ 10 days | 12:01:00 | 12:01:00 1111 12:01:00 | @ 3 mons | 12:01:00 | 12:01:00 1112 12:01:00 | @ 5 mons | 12:01:00 | 12:01:00 1113 12:01:00 | @ 5 mons 12 hours | 00:01:00 | 00:01:00 1114 12:01:00 | @ 6 years | 12:01:00 | 12:01:00 1115 12:01:00 | @ 34 years | 12:01:00 | 12:01:00 1116 15:36:39 | @ 14 secs ago | 15:36:25 | 15:36:53 1117 15:36:39 | @ 14 secs ago | 15:36:25 | 15:36:53 1118 15:36:39 | @ 1 min | 15:37:39 | 15:35:39 1119 15:36:39 | @ 1 min | 15:37:39 | 15:35:39 1120 15:36:39 | @ 5 hours | 20:36:39 | 10:36:39 1121 15:36:39 | @ 5 hours | 20:36:39 | 10:36:39 1122 15:36:39 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43 | 13:33:35 1123 15:36:39 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43 | 13:33:35 1124 15:36:39 | @ 10 days | 15:36:39 | 15:36:39 1125 15:36:39 | @ 10 days | 15:36:39 | 15:36:39 1126 15:36:39 | @ 3 mons | 15:36:39 | 15:36:39 1127 15:36:39 | @ 3 mons | 15:36:39 | 15:36:39 1128 15:36:39 | @ 5 mons | 15:36:39 | 15:36:39 1129 15:36:39 | @ 5 mons | 15:36:39 | 15:36:39 1130 15:36:39 | @ 5 mons 12 hours | 03:36:39 | 03:36:39 1131 15:36:39 | @ 5 mons 12 hours | 03:36:39 | 03:36:39 1132 15:36:39 | @ 6 years | 15:36:39 | 15:36:39 1133 15:36:39 | @ 6 years | 15:36:39 | 15:36:39 1134 15:36:39 | @ 34 years | 15:36:39 | 15:36:39 1135 15:36:39 | @ 34 years | 15:36:39 | 15:36:39 1136 23:59:00 | @ 14 secs ago | 23:58:46 | 23:59:14 1137 23:59:00 | @ 1 min | 00:00:00 | 23:58:00 1138 23:59:00 | @ 5 hours | 04:59:00 | 18:59:00 1139 23:59:00 | @ 1 day 2 hours 3 mins 4 secs | 02:02:04 | 21:55:56 1140 23:59:00 | @ 10 days | 23:59:00 | 23:59:00 1141 23:59:00 | @ 3 mons | 23:59:00 | 23:59:00 1142 23:59:00 | @ 5 mons | 23:59:00 | 23:59:00 1143 23:59:00 | @ 5 mons 12 hours | 11:59:00 | 11:59:00 1144 23:59:00 | @ 6 years | 23:59:00 | 23:59:00 1145 23:59:00 | @ 34 years | 23:59:00 | 23:59:00 1146 23:59:59.99 | @ 14 secs ago | 23:59:45.99 | 00:00:13.99 1147 23:59:59.99 | @ 1 min | 00:00:59.99 | 23:58:59.99 1148 23:59:59.99 | @ 5 hours | 04:59:59.99 | 18:59:59.99 1149 23:59:59.99 | @ 1 day 2 hours 3 mins 4 secs | 02:03:03.99 | 21:56:55.99 1150 23:59:59.99 | @ 10 days | 23:59:59.99 | 23:59:59.99 1151 23:59:59.99 | @ 3 mons | 23:59:59.99 | 23:59:59.99 1152 23:59:59.99 | @ 5 mons | 23:59:59.99 | 23:59:59.99 1153 23:59:59.99 | @ 5 mons 12 hours | 11:59:59.99 | 11:59:59.99 1154 23:59:59.99 | @ 6 years | 23:59:59.99 | 23:59:59.99 1155 23:59:59.99 | @ 34 years | 23:59:59.99 | 23:59:59.99 1156(100 rows) 1157 1158SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 1159 FROM TIMETZ_TBL t, INTERVAL_TBL i 1160 ORDER BY 1,2; 1161 t | i | add | subtract 1162----------------+-------------------------------+----------------+---------------- 1163 00:01:00-07 | @ 14 secs ago | 00:00:46-07 | 00:01:14-07 1164 00:01:00-07 | @ 1 min | 00:02:00-07 | 00:00:00-07 1165 00:01:00-07 | @ 5 hours | 05:01:00-07 | 19:01:00-07 1166 00:01:00-07 | @ 1 day 2 hours 3 mins 4 secs | 02:04:04-07 | 21:57:56-07 1167 00:01:00-07 | @ 10 days | 00:01:00-07 | 00:01:00-07 1168 00:01:00-07 | @ 3 mons | 00:01:00-07 | 00:01:00-07 1169 00:01:00-07 | @ 5 mons | 00:01:00-07 | 00:01:00-07 1170 00:01:00-07 | @ 5 mons 12 hours | 12:01:00-07 | 12:01:00-07 1171 00:01:00-07 | @ 6 years | 00:01:00-07 | 00:01:00-07 1172 00:01:00-07 | @ 34 years | 00:01:00-07 | 00:01:00-07 1173 01:00:00-07 | @ 14 secs ago | 00:59:46-07 | 01:00:14-07 1174 01:00:00-07 | @ 1 min | 01:01:00-07 | 00:59:00-07 1175 01:00:00-07 | @ 5 hours | 06:00:00-07 | 20:00:00-07 1176 01:00:00-07 | @ 1 day 2 hours 3 mins 4 secs | 03:03:04-07 | 22:56:56-07 1177 01:00:00-07 | @ 10 days | 01:00:00-07 | 01:00:00-07 1178 01:00:00-07 | @ 3 mons | 01:00:00-07 | 01:00:00-07 1179 01:00:00-07 | @ 5 mons | 01:00:00-07 | 01:00:00-07 1180 01:00:00-07 | @ 5 mons 12 hours | 13:00:00-07 | 13:00:00-07 1181 01:00:00-07 | @ 6 years | 01:00:00-07 | 01:00:00-07 1182 01:00:00-07 | @ 34 years | 01:00:00-07 | 01:00:00-07 1183 02:03:00-07 | @ 14 secs ago | 02:02:46-07 | 02:03:14-07 1184 02:03:00-07 | @ 1 min | 02:04:00-07 | 02:02:00-07 1185 02:03:00-07 | @ 5 hours | 07:03:00-07 | 21:03:00-07 1186 02:03:00-07 | @ 1 day 2 hours 3 mins 4 secs | 04:06:04-07 | 23:59:56-07 1187 02:03:00-07 | @ 10 days | 02:03:00-07 | 02:03:00-07 1188 02:03:00-07 | @ 3 mons | 02:03:00-07 | 02:03:00-07 1189 02:03:00-07 | @ 5 mons | 02:03:00-07 | 02:03:00-07 1190 02:03:00-07 | @ 5 mons 12 hours | 14:03:00-07 | 14:03:00-07 1191 02:03:00-07 | @ 6 years | 02:03:00-07 | 02:03:00-07 1192 02:03:00-07 | @ 34 years | 02:03:00-07 | 02:03:00-07 1193 08:08:00-04 | @ 14 secs ago | 08:07:46-04 | 08:08:14-04 1194 08:08:00-04 | @ 1 min | 08:09:00-04 | 08:07:00-04 1195 08:08:00-04 | @ 5 hours | 13:08:00-04 | 03:08:00-04 1196 08:08:00-04 | @ 1 day 2 hours 3 mins 4 secs | 10:11:04-04 | 06:04:56-04 1197 08:08:00-04 | @ 10 days | 08:08:00-04 | 08:08:00-04 1198 08:08:00-04 | @ 3 mons | 08:08:00-04 | 08:08:00-04 1199 08:08:00-04 | @ 5 mons | 08:08:00-04 | 08:08:00-04 1200 08:08:00-04 | @ 5 mons 12 hours | 20:08:00-04 | 20:08:00-04 1201 08:08:00-04 | @ 6 years | 08:08:00-04 | 08:08:00-04 1202 08:08:00-04 | @ 34 years | 08:08:00-04 | 08:08:00-04 1203 07:07:00-08 | @ 14 secs ago | 07:06:46-08 | 07:07:14-08 1204 07:07:00-08 | @ 1 min | 07:08:00-08 | 07:06:00-08 1205 07:07:00-08 | @ 5 hours | 12:07:00-08 | 02:07:00-08 1206 07:07:00-08 | @ 1 day 2 hours 3 mins 4 secs | 09:10:04-08 | 05:03:56-08 1207 07:07:00-08 | @ 10 days | 07:07:00-08 | 07:07:00-08 1208 07:07:00-08 | @ 3 mons | 07:07:00-08 | 07:07:00-08 1209 07:07:00-08 | @ 5 mons | 07:07:00-08 | 07:07:00-08 1210 07:07:00-08 | @ 5 mons 12 hours | 19:07:00-08 | 19:07:00-08 1211 07:07:00-08 | @ 6 years | 07:07:00-08 | 07:07:00-08 1212 07:07:00-08 | @ 34 years | 07:07:00-08 | 07:07:00-08 1213 11:59:00-07 | @ 14 secs ago | 11:58:46-07 | 11:59:14-07 1214 11:59:00-07 | @ 1 min | 12:00:00-07 | 11:58:00-07 1215 11:59:00-07 | @ 5 hours | 16:59:00-07 | 06:59:00-07 1216 11:59:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:02:04-07 | 09:55:56-07 1217 11:59:00-07 | @ 10 days | 11:59:00-07 | 11:59:00-07 1218 11:59:00-07 | @ 3 mons | 11:59:00-07 | 11:59:00-07 1219 11:59:00-07 | @ 5 mons | 11:59:00-07 | 11:59:00-07 1220 11:59:00-07 | @ 5 mons 12 hours | 23:59:00-07 | 23:59:00-07 1221 11:59:00-07 | @ 6 years | 11:59:00-07 | 11:59:00-07 1222 11:59:00-07 | @ 34 years | 11:59:00-07 | 11:59:00-07 1223 12:00:00-07 | @ 14 secs ago | 11:59:46-07 | 12:00:14-07 1224 12:00:00-07 | @ 1 min | 12:01:00-07 | 11:59:00-07 1225 12:00:00-07 | @ 5 hours | 17:00:00-07 | 07:00:00-07 1226 12:00:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:03:04-07 | 09:56:56-07 1227 12:00:00-07 | @ 10 days | 12:00:00-07 | 12:00:00-07 1228 12:00:00-07 | @ 3 mons | 12:00:00-07 | 12:00:00-07 1229 12:00:00-07 | @ 5 mons | 12:00:00-07 | 12:00:00-07 1230 12:00:00-07 | @ 5 mons 12 hours | 00:00:00-07 | 00:00:00-07 1231 12:00:00-07 | @ 6 years | 12:00:00-07 | 12:00:00-07 1232 12:00:00-07 | @ 34 years | 12:00:00-07 | 12:00:00-07 1233 12:01:00-07 | @ 14 secs ago | 12:00:46-07 | 12:01:14-07 1234 12:01:00-07 | @ 1 min | 12:02:00-07 | 12:00:00-07 1235 12:01:00-07 | @ 5 hours | 17:01:00-07 | 07:01:00-07 1236 12:01:00-07 | @ 1 day 2 hours 3 mins 4 secs | 14:04:04-07 | 09:57:56-07 1237 12:01:00-07 | @ 10 days | 12:01:00-07 | 12:01:00-07 1238 12:01:00-07 | @ 3 mons | 12:01:00-07 | 12:01:00-07 1239 12:01:00-07 | @ 5 mons | 12:01:00-07 | 12:01:00-07 1240 12:01:00-07 | @ 5 mons 12 hours | 00:01:00-07 | 00:01:00-07 1241 12:01:00-07 | @ 6 years | 12:01:00-07 | 12:01:00-07 1242 12:01:00-07 | @ 34 years | 12:01:00-07 | 12:01:00-07 1243 15:36:39-04 | @ 14 secs ago | 15:36:25-04 | 15:36:53-04 1244 15:36:39-04 | @ 1 min | 15:37:39-04 | 15:35:39-04 1245 15:36:39-04 | @ 5 hours | 20:36:39-04 | 10:36:39-04 1246 15:36:39-04 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43-04 | 13:33:35-04 1247 15:36:39-04 | @ 10 days | 15:36:39-04 | 15:36:39-04 1248 15:36:39-04 | @ 3 mons | 15:36:39-04 | 15:36:39-04 1249 15:36:39-04 | @ 5 mons | 15:36:39-04 | 15:36:39-04 1250 15:36:39-04 | @ 5 mons 12 hours | 03:36:39-04 | 03:36:39-04 1251 15:36:39-04 | @ 6 years | 15:36:39-04 | 15:36:39-04 1252 15:36:39-04 | @ 34 years | 15:36:39-04 | 15:36:39-04 1253 15:36:39-05 | @ 14 secs ago | 15:36:25-05 | 15:36:53-05 1254 15:36:39-05 | @ 1 min | 15:37:39-05 | 15:35:39-05 1255 15:36:39-05 | @ 5 hours | 20:36:39-05 | 10:36:39-05 1256 15:36:39-05 | @ 1 day 2 hours 3 mins 4 secs | 17:39:43-05 | 13:33:35-05 1257 15:36:39-05 | @ 10 days | 15:36:39-05 | 15:36:39-05 1258 15:36:39-05 | @ 3 mons | 15:36:39-05 | 15:36:39-05 1259 15:36:39-05 | @ 5 mons | 15:36:39-05 | 15:36:39-05 1260 15:36:39-05 | @ 5 mons 12 hours | 03:36:39-05 | 03:36:39-05 1261 15:36:39-05 | @ 6 years | 15:36:39-05 | 15:36:39-05 1262 15:36:39-05 | @ 34 years | 15:36:39-05 | 15:36:39-05 1263 23:59:00-07 | @ 14 secs ago | 23:58:46-07 | 23:59:14-07 1264 23:59:00-07 | @ 1 min | 00:00:00-07 | 23:58:00-07 1265 23:59:00-07 | @ 5 hours | 04:59:00-07 | 18:59:00-07 1266 23:59:00-07 | @ 1 day 2 hours 3 mins 4 secs | 02:02:04-07 | 21:55:56-07 1267 23:59:00-07 | @ 10 days | 23:59:00-07 | 23:59:00-07 1268 23:59:00-07 | @ 3 mons | 23:59:00-07 | 23:59:00-07 1269 23:59:00-07 | @ 5 mons | 23:59:00-07 | 23:59:00-07 1270 23:59:00-07 | @ 5 mons 12 hours | 11:59:00-07 | 11:59:00-07 1271 23:59:00-07 | @ 6 years | 23:59:00-07 | 23:59:00-07 1272 23:59:00-07 | @ 34 years | 23:59:00-07 | 23:59:00-07 1273 23:59:59.99-07 | @ 14 secs ago | 23:59:45.99-07 | 00:00:13.99-07 1274 23:59:59.99-07 | @ 1 min | 00:00:59.99-07 | 23:58:59.99-07 1275 23:59:59.99-07 | @ 5 hours | 04:59:59.99-07 | 18:59:59.99-07 1276 23:59:59.99-07 | @ 1 day 2 hours 3 mins 4 secs | 02:03:03.99-07 | 21:56:55.99-07 1277 23:59:59.99-07 | @ 10 days | 23:59:59.99-07 | 23:59:59.99-07 1278 23:59:59.99-07 | @ 3 mons | 23:59:59.99-07 | 23:59:59.99-07 1279 23:59:59.99-07 | @ 5 mons | 23:59:59.99-07 | 23:59:59.99-07 1280 23:59:59.99-07 | @ 5 mons 12 hours | 11:59:59.99-07 | 11:59:59.99-07 1281 23:59:59.99-07 | @ 6 years | 23:59:59.99-07 | 23:59:59.99-07 1282 23:59:59.99-07 | @ 34 years | 23:59:59.99-07 | 23:59:59.99-07 1283(120 rows) 1284 1285-- SQL9x OVERLAPS operator 1286-- test with time zone 1287SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 1288 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "True"; 1289 True 1290------ 1291 t 1292(1 row) 1293 1294SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27') 1295 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 1296 False 1297------- 1298 f 1299(1 row) 1300 1301SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 1302 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '1 day') AS "True"; 1303 True 1304------ 1305 t 1306(1 row) 1307 1308SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 1309 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 1310 False 1311------- 1312 f 1313(1 row) 1314 1315SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 1316 OVERLAPS (timestamp with time zone '2000-11-27', interval '12 hours') AS "True"; 1317 True 1318------ 1319 t 1320(1 row) 1321 1322SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 1323 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 1324 False 1325------- 1326 f 1327(1 row) 1328 1329-- test without time zone 1330SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 1331 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "True"; 1332 True 1333------ 1334 t 1335(1 row) 1336 1337SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27') 1338 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 1339 False 1340------- 1341 f 1342(1 row) 1343 1344SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 1345 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '1 day') AS "True"; 1346 True 1347------ 1348 t 1349(1 row) 1350 1351SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 1352 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 1353 False 1354------- 1355 f 1356(1 row) 1357 1358SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 1359 OVERLAPS (timestamp without time zone '2000-11-27', interval '12 hours') AS "True"; 1360 True 1361------ 1362 t 1363(1 row) 1364 1365SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 1366 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 1367 False 1368------- 1369 f 1370(1 row) 1371 1372-- test time and interval 1373SELECT (time '00:00', time '01:00') 1374 OVERLAPS (time '00:30', time '01:30') AS "True"; 1375 True 1376------ 1377 t 1378(1 row) 1379 1380SELECT (time '00:00', interval '1 hour') 1381 OVERLAPS (time '00:30', interval '1 hour') AS "True"; 1382 True 1383------ 1384 t 1385(1 row) 1386 1387SELECT (time '00:00', interval '1 hour') 1388 OVERLAPS (time '01:30', interval '1 hour') AS "False"; 1389 False 1390------- 1391 f 1392(1 row) 1393 1394-- SQL99 seems to want this to be false (and we conform to the spec). 1395-- istm that this *should* return true, on the theory that time 1396-- intervals can wrap around the day boundary - thomas 2001-09-25 1397SELECT (time '00:00', interval '1 hour') 1398 OVERLAPS (time '01:30', interval '1 day') AS "False"; 1399 False 1400------- 1401 f 1402(1 row) 1403 1404CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone); 1405-- get some candidate input values 1406INSERT INTO TEMP_TIMESTAMP (f1) 1407 SELECT d1 FROM TIMESTAMP_TBL 1408 WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' 1409 OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; 1410SELECT f1 AS "timestamp" 1411 FROM TEMP_TIMESTAMP 1412 ORDER BY "timestamp"; 1413 timestamp 1414------------------------------ 1415 Thu Jan 01 00:00:00 1970 PST 1416 Wed Feb 28 17:32:01 1996 PST 1417 Thu Feb 29 17:32:01 1996 PST 1418 Fri Mar 01 17:32:01 1996 PST 1419 Mon Dec 30 17:32:01 1996 PST 1420 Tue Dec 31 17:32:01 1996 PST 1421 Fri Dec 31 17:32:01 1999 PST 1422 Sat Jan 01 17:32:01 2000 PST 1423 Wed Mar 15 02:14:05 2000 PST 1424 Wed Mar 15 03:14:04 2000 PST 1425 Wed Mar 15 08:14:01 2000 PST 1426 Wed Mar 15 12:14:03 2000 PST 1427 Wed Mar 15 13:14:02 2000 PST 1428 Sun Dec 31 17:32:01 2000 PST 1429 Mon Jan 01 17:32:01 2001 PST 1430 Sat Sep 22 18:19:20 2001 PDT 1431(16 rows) 1432 1433SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus 1434 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 1435 ORDER BY plus, "timestamp", "interval"; 1436 timestamp | interval | plus 1437------------------------------+-------------------------------+------------------------------ 1438 Thu Jan 01 00:00:00 1970 PST | @ 14 secs ago | Wed Dec 31 23:59:46 1969 PST 1439 Thu Jan 01 00:00:00 1970 PST | @ 1 min | Thu Jan 01 00:01:00 1970 PST 1440 Thu Jan 01 00:00:00 1970 PST | @ 5 hours | Thu Jan 01 05:00:00 1970 PST 1441 Thu Jan 01 00:00:00 1970 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Jan 02 02:03:04 1970 PST 1442 Thu Jan 01 00:00:00 1970 PST | @ 10 days | Sun Jan 11 00:00:00 1970 PST 1443 Thu Jan 01 00:00:00 1970 PST | @ 3 mons | Wed Apr 01 00:00:00 1970 PST 1444 Thu Jan 01 00:00:00 1970 PST | @ 5 mons | Mon Jun 01 00:00:00 1970 PDT 1445 Thu Jan 01 00:00:00 1970 PST | @ 5 mons 12 hours | Mon Jun 01 12:00:00 1970 PDT 1446 Thu Jan 01 00:00:00 1970 PST | @ 6 years | Thu Jan 01 00:00:00 1976 PST 1447 Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST 1448 Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST 1449 Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST 1450 Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST 1451 Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST 1452 Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST 1453 Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST 1454 Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST 1455 Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST 1456 Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST 1457 Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST 1458 Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST 1459 Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST 1460 Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST 1461 Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST 1462 Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 17:32:01 1996 PDT 1463 Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 17:32:01 1996 PDT 1464 Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 17:32:01 1996 PDT 1465 Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 17:32:01 1996 PDT 1466 Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 05:32:01 1996 PDT 1467 Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 17:32:01 1996 PDT 1468 Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 05:32:01 1996 PDT 1469 Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 17:32:01 1996 PDT 1470 Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 05:32:01 1996 PDT 1471 Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST 1472 Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST 1473 Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST 1474 Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST 1475 Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST 1476 Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST 1477 Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST 1478 Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST 1479 Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST 1480 Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST 1481 Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST 1482 Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST 1483 Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 17:32:01 1997 PDT 1484 Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 05:32:01 1997 PDT 1485 Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 17:32:01 1997 PDT 1486 Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 05:32:01 1997 PDT 1487 Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST 1488 Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST 1489 Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST 1490 Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST 1491 Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST 1492 Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST 1493 Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST 1494 Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST 1495 Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST 1496 Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST 1497 Wed Mar 15 02:14:05 2000 PST | @ 14 secs ago | Wed Mar 15 02:13:51 2000 PST 1498 Wed Mar 15 02:14:05 2000 PST | @ 1 min | Wed Mar 15 02:15:05 2000 PST 1499 Wed Mar 15 03:14:04 2000 PST | @ 14 secs ago | Wed Mar 15 03:13:50 2000 PST 1500 Wed Mar 15 03:14:04 2000 PST | @ 1 min | Wed Mar 15 03:15:04 2000 PST 1501 Wed Mar 15 02:14:05 2000 PST | @ 5 hours | Wed Mar 15 07:14:05 2000 PST 1502 Wed Mar 15 08:14:01 2000 PST | @ 14 secs ago | Wed Mar 15 08:13:47 2000 PST 1503 Wed Mar 15 03:14:04 2000 PST | @ 5 hours | Wed Mar 15 08:14:04 2000 PST 1504 Wed Mar 15 08:14:01 2000 PST | @ 1 min | Wed Mar 15 08:15:01 2000 PST 1505 Wed Mar 15 12:14:03 2000 PST | @ 14 secs ago | Wed Mar 15 12:13:49 2000 PST 1506 Wed Mar 15 12:14:03 2000 PST | @ 1 min | Wed Mar 15 12:15:03 2000 PST 1507 Wed Mar 15 13:14:02 2000 PST | @ 14 secs ago | Wed Mar 15 13:13:48 2000 PST 1508 Wed Mar 15 08:14:01 2000 PST | @ 5 hours | Wed Mar 15 13:14:01 2000 PST 1509 Wed Mar 15 13:14:02 2000 PST | @ 1 min | Wed Mar 15 13:15:02 2000 PST 1510 Wed Mar 15 12:14:03 2000 PST | @ 5 hours | Wed Mar 15 17:14:03 2000 PST 1511 Wed Mar 15 13:14:02 2000 PST | @ 5 hours | Wed Mar 15 18:14:02 2000 PST 1512 Wed Mar 15 02:14:05 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 04:17:09 2000 PST 1513 Wed Mar 15 03:14:04 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 05:17:08 2000 PST 1514 Wed Mar 15 08:14:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 10:17:05 2000 PST 1515 Wed Mar 15 12:14:03 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 14:17:07 2000 PST 1516 Wed Mar 15 13:14:02 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Mar 16 15:17:06 2000 PST 1517 Wed Mar 15 02:14:05 2000 PST | @ 10 days | Sat Mar 25 02:14:05 2000 PST 1518 Wed Mar 15 03:14:04 2000 PST | @ 10 days | Sat Mar 25 03:14:04 2000 PST 1519 Wed Mar 15 08:14:01 2000 PST | @ 10 days | Sat Mar 25 08:14:01 2000 PST 1520 Wed Mar 15 12:14:03 2000 PST | @ 10 days | Sat Mar 25 12:14:03 2000 PST 1521 Wed Mar 15 13:14:02 2000 PST | @ 10 days | Sat Mar 25 13:14:02 2000 PST 1522 Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST 1523 Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST 1524 Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 17:32:01 2000 PDT 1525 Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 05:32:01 2000 PDT 1526 Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 17:32:01 2000 PDT 1527 Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 05:32:01 2000 PDT 1528 Wed Mar 15 02:14:05 2000 PST | @ 3 mons | Thu Jun 15 02:14:05 2000 PDT 1529 Wed Mar 15 03:14:04 2000 PST | @ 3 mons | Thu Jun 15 03:14:04 2000 PDT 1530 Wed Mar 15 08:14:01 2000 PST | @ 3 mons | Thu Jun 15 08:14:01 2000 PDT 1531 Wed Mar 15 12:14:03 2000 PST | @ 3 mons | Thu Jun 15 12:14:03 2000 PDT 1532 Wed Mar 15 13:14:02 2000 PST | @ 3 mons | Thu Jun 15 13:14:02 2000 PDT 1533 Wed Mar 15 02:14:05 2000 PST | @ 5 mons | Tue Aug 15 02:14:05 2000 PDT 1534 Wed Mar 15 03:14:04 2000 PST | @ 5 mons | Tue Aug 15 03:14:04 2000 PDT 1535 Wed Mar 15 08:14:01 2000 PST | @ 5 mons | Tue Aug 15 08:14:01 2000 PDT 1536 Wed Mar 15 12:14:03 2000 PST | @ 5 mons | Tue Aug 15 12:14:03 2000 PDT 1537 Wed Mar 15 13:14:02 2000 PST | @ 5 mons | Tue Aug 15 13:14:02 2000 PDT 1538 Wed Mar 15 02:14:05 2000 PST | @ 5 mons 12 hours | Tue Aug 15 14:14:05 2000 PDT 1539 Wed Mar 15 03:14:04 2000 PST | @ 5 mons 12 hours | Tue Aug 15 15:14:04 2000 PDT 1540 Wed Mar 15 08:14:01 2000 PST | @ 5 mons 12 hours | Tue Aug 15 20:14:01 2000 PDT 1541 Wed Mar 15 12:14:03 2000 PST | @ 5 mons 12 hours | Wed Aug 16 00:14:03 2000 PDT 1542 Wed Mar 15 13:14:02 2000 PST | @ 5 mons 12 hours | Wed Aug 16 01:14:02 2000 PDT 1543 Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST 1544 Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST 1545 Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST 1546 Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST 1547 Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST 1548 Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST 1549 Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST 1550 Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST 1551 Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST 1552 Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST 1553 Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST 1554 Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 17:32:01 2001 PDT 1555 Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 17:32:01 2001 PDT 1556 Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 05:32:01 2001 PDT 1557 Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 17:32:01 2001 PDT 1558 Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 05:32:01 2001 PDT 1559 Sat Sep 22 18:19:20 2001 PDT | @ 14 secs ago | Sat Sep 22 18:19:06 2001 PDT 1560 Sat Sep 22 18:19:20 2001 PDT | @ 1 min | Sat Sep 22 18:20:20 2001 PDT 1561 Sat Sep 22 18:19:20 2001 PDT | @ 5 hours | Sat Sep 22 23:19:20 2001 PDT 1562 Sat Sep 22 18:19:20 2001 PDT | @ 1 day 2 hours 3 mins 4 secs | Sun Sep 23 20:22:24 2001 PDT 1563 Sat Sep 22 18:19:20 2001 PDT | @ 10 days | Tue Oct 02 18:19:20 2001 PDT 1564 Sat Sep 22 18:19:20 2001 PDT | @ 3 mons | Sat Dec 22 18:19:20 2001 PST 1565 Sat Sep 22 18:19:20 2001 PDT | @ 5 mons | Fri Feb 22 18:19:20 2002 PST 1566 Sat Sep 22 18:19:20 2001 PDT | @ 5 mons 12 hours | Sat Feb 23 06:19:20 2002 PST 1567 Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST 1568 Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST 1569 Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST 1570 Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST 1571 Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST 1572 Thu Jan 01 00:00:00 1970 PST | @ 34 years | Thu Jan 01 00:00:00 2004 PST 1573 Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST 1574 Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST 1575 Wed Mar 15 02:14:05 2000 PST | @ 6 years | Wed Mar 15 02:14:05 2006 PST 1576 Wed Mar 15 03:14:04 2000 PST | @ 6 years | Wed Mar 15 03:14:04 2006 PST 1577 Wed Mar 15 08:14:01 2000 PST | @ 6 years | Wed Mar 15 08:14:01 2006 PST 1578 Wed Mar 15 12:14:03 2000 PST | @ 6 years | Wed Mar 15 12:14:03 2006 PST 1579 Wed Mar 15 13:14:02 2000 PST | @ 6 years | Wed Mar 15 13:14:02 2006 PST 1580 Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST 1581 Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST 1582 Sat Sep 22 18:19:20 2001 PDT | @ 6 years | Sat Sep 22 18:19:20 2007 PDT 1583 Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST 1584 Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST 1585 Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST 1586 Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST 1587 Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST 1588 Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST 1589 Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST 1590 Wed Mar 15 02:14:05 2000 PST | @ 34 years | Wed Mar 15 02:14:05 2034 PDT 1591 Wed Mar 15 03:14:04 2000 PST | @ 34 years | Wed Mar 15 03:14:04 2034 PDT 1592 Wed Mar 15 08:14:01 2000 PST | @ 34 years | Wed Mar 15 08:14:01 2034 PDT 1593 Wed Mar 15 12:14:03 2000 PST | @ 34 years | Wed Mar 15 12:14:03 2034 PDT 1594 Wed Mar 15 13:14:02 2000 PST | @ 34 years | Wed Mar 15 13:14:02 2034 PDT 1595 Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST 1596 Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST 1597 Sat Sep 22 18:19:20 2001 PDT | @ 34 years | Sat Sep 22 18:19:20 2035 PDT 1598(160 rows) 1599 1600SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus 1601 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 1602 WHERE isfinite(d.f1) 1603 ORDER BY minus, "timestamp", "interval"; 1604 timestamp | interval | minus 1605------------------------------+-------------------------------+------------------------------ 1606 Thu Jan 01 00:00:00 1970 PST | @ 34 years | Wed Jan 01 00:00:00 1936 PST 1607 Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST 1608 Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST 1609 Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST 1610 Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST 1611 Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST 1612 Thu Jan 01 00:00:00 1970 PST | @ 6 years | Wed Jan 01 00:00:00 1964 PST 1613 Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST 1614 Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST 1615 Wed Mar 15 02:14:05 2000 PST | @ 34 years | Tue Mar 15 02:14:05 1966 PST 1616 Wed Mar 15 03:14:04 2000 PST | @ 34 years | Tue Mar 15 03:14:04 1966 PST 1617 Wed Mar 15 08:14:01 2000 PST | @ 34 years | Tue Mar 15 08:14:01 1966 PST 1618 Wed Mar 15 12:14:03 2000 PST | @ 34 years | Tue Mar 15 12:14:03 1966 PST 1619 Wed Mar 15 13:14:02 2000 PST | @ 34 years | Tue Mar 15 13:14:02 1966 PST 1620 Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST 1621 Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST 1622 Sat Sep 22 18:19:20 2001 PDT | @ 34 years | Fri Sep 22 18:19:20 1967 PDT 1623 Thu Jan 01 00:00:00 1970 PST | @ 5 mons 12 hours | Thu Jul 31 12:00:00 1969 PDT 1624 Thu Jan 01 00:00:00 1970 PST | @ 5 mons | Fri Aug 01 00:00:00 1969 PDT 1625 Thu Jan 01 00:00:00 1970 PST | @ 3 mons | Wed Oct 01 00:00:00 1969 PDT 1626 Thu Jan 01 00:00:00 1970 PST | @ 10 days | Mon Dec 22 00:00:00 1969 PST 1627 Thu Jan 01 00:00:00 1970 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 21:56:56 1969 PST 1628 Thu Jan 01 00:00:00 1970 PST | @ 5 hours | Wed Dec 31 19:00:00 1969 PST 1629 Thu Jan 01 00:00:00 1970 PST | @ 1 min | Wed Dec 31 23:59:00 1969 PST 1630 Thu Jan 01 00:00:00 1970 PST | @ 14 secs ago | Thu Jan 01 00:00:14 1970 PST 1631 Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST 1632 Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST 1633 Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST 1634 Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST 1635 Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST 1636 Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST 1637 Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST 1638 Wed Mar 15 02:14:05 2000 PST | @ 6 years | Tue Mar 15 02:14:05 1994 PST 1639 Wed Mar 15 03:14:04 2000 PST | @ 6 years | Tue Mar 15 03:14:04 1994 PST 1640 Wed Mar 15 08:14:01 2000 PST | @ 6 years | Tue Mar 15 08:14:01 1994 PST 1641 Wed Mar 15 12:14:03 2000 PST | @ 6 years | Tue Mar 15 12:14:03 1994 PST 1642 Wed Mar 15 13:14:02 2000 PST | @ 6 years | Tue Mar 15 13:14:02 1994 PST 1643 Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST 1644 Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST 1645 Sat Sep 22 18:19:20 2001 PDT | @ 6 years | Fri Sep 22 18:19:20 1995 PDT 1646 Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 05:32:01 1995 PDT 1647 Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 17:32:01 1995 PDT 1648 Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 05:32:01 1995 PDT 1649 Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 17:32:01 1995 PDT 1650 Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 05:32:01 1995 PDT 1651 Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 17:32:01 1995 PDT 1652 Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST 1653 Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST 1654 Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST 1655 Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST 1656 Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST 1657 Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST 1658 Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST 1659 Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST 1660 Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST 1661 Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST 1662 Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST 1663 Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST 1664 Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST 1665 Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST 1666 Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST 1667 Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST 1668 Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST 1669 Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST 1670 Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 05:32:01 1996 PDT 1671 Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 17:32:01 1996 PDT 1672 Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 05:32:01 1996 PDT 1673 Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 17:32:01 1996 PDT 1674 Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 17:32:01 1996 PDT 1675 Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 17:32:01 1996 PDT 1676 Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST 1677 Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST 1678 Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST 1679 Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST 1680 Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST 1681 Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST 1682 Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST 1683 Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST 1684 Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST 1685 Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST 1686 Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 05:32:01 1999 PDT 1687 Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 17:32:01 1999 PDT 1688 Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 05:32:01 1999 PDT 1689 Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 17:32:01 1999 PDT 1690 Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 17:32:01 1999 PDT 1691 Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 17:32:01 1999 PDT 1692 Wed Mar 15 02:14:05 2000 PST | @ 5 mons 12 hours | Thu Oct 14 14:14:05 1999 PDT 1693 Wed Mar 15 03:14:04 2000 PST | @ 5 mons 12 hours | Thu Oct 14 15:14:04 1999 PDT 1694 Wed Mar 15 08:14:01 2000 PST | @ 5 mons 12 hours | Thu Oct 14 20:14:01 1999 PDT 1695 Wed Mar 15 12:14:03 2000 PST | @ 5 mons 12 hours | Fri Oct 15 00:14:03 1999 PDT 1696 Wed Mar 15 13:14:02 2000 PST | @ 5 mons 12 hours | Fri Oct 15 01:14:02 1999 PDT 1697 Wed Mar 15 02:14:05 2000 PST | @ 5 mons | Fri Oct 15 02:14:05 1999 PDT 1698 Wed Mar 15 03:14:04 2000 PST | @ 5 mons | Fri Oct 15 03:14:04 1999 PDT 1699 Wed Mar 15 08:14:01 2000 PST | @ 5 mons | Fri Oct 15 08:14:01 1999 PDT 1700 Wed Mar 15 12:14:03 2000 PST | @ 5 mons | Fri Oct 15 12:14:03 1999 PDT 1701 Wed Mar 15 13:14:02 2000 PST | @ 5 mons | Fri Oct 15 13:14:02 1999 PDT 1702 Wed Mar 15 02:14:05 2000 PST | @ 3 mons | Wed Dec 15 02:14:05 1999 PST 1703 Wed Mar 15 03:14:04 2000 PST | @ 3 mons | Wed Dec 15 03:14:04 1999 PST 1704 Wed Mar 15 08:14:01 2000 PST | @ 3 mons | Wed Dec 15 08:14:01 1999 PST 1705 Wed Mar 15 12:14:03 2000 PST | @ 3 mons | Wed Dec 15 12:14:03 1999 PST 1706 Wed Mar 15 13:14:02 2000 PST | @ 3 mons | Wed Dec 15 13:14:02 1999 PST 1707 Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST 1708 Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST 1709 Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST 1710 Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST 1711 Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST 1712 Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST 1713 Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST 1714 Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST 1715 Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST 1716 Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST 1717 Wed Mar 15 02:14:05 2000 PST | @ 10 days | Sun Mar 05 02:14:05 2000 PST 1718 Wed Mar 15 03:14:04 2000 PST | @ 10 days | Sun Mar 05 03:14:04 2000 PST 1719 Wed Mar 15 08:14:01 2000 PST | @ 10 days | Sun Mar 05 08:14:01 2000 PST 1720 Wed Mar 15 12:14:03 2000 PST | @ 10 days | Sun Mar 05 12:14:03 2000 PST 1721 Wed Mar 15 13:14:02 2000 PST | @ 10 days | Sun Mar 05 13:14:02 2000 PST 1722 Wed Mar 15 02:14:05 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 00:11:01 2000 PST 1723 Wed Mar 15 03:14:04 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 01:11:00 2000 PST 1724 Wed Mar 15 08:14:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 06:10:57 2000 PST 1725 Wed Mar 15 12:14:03 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 10:10:59 2000 PST 1726 Wed Mar 15 13:14:02 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Mar 14 11:10:58 2000 PST 1727 Wed Mar 15 02:14:05 2000 PST | @ 5 hours | Tue Mar 14 21:14:05 2000 PST 1728 Wed Mar 15 03:14:04 2000 PST | @ 5 hours | Tue Mar 14 22:14:04 2000 PST 1729 Wed Mar 15 02:14:05 2000 PST | @ 1 min | Wed Mar 15 02:13:05 2000 PST 1730 Wed Mar 15 02:14:05 2000 PST | @ 14 secs ago | Wed Mar 15 02:14:19 2000 PST 1731 Wed Mar 15 03:14:04 2000 PST | @ 1 min | Wed Mar 15 03:13:04 2000 PST 1732 Wed Mar 15 08:14:01 2000 PST | @ 5 hours | Wed Mar 15 03:14:01 2000 PST 1733 Wed Mar 15 03:14:04 2000 PST | @ 14 secs ago | Wed Mar 15 03:14:18 2000 PST 1734 Wed Mar 15 12:14:03 2000 PST | @ 5 hours | Wed Mar 15 07:14:03 2000 PST 1735 Wed Mar 15 08:14:01 2000 PST | @ 1 min | Wed Mar 15 08:13:01 2000 PST 1736 Wed Mar 15 13:14:02 2000 PST | @ 5 hours | Wed Mar 15 08:14:02 2000 PST 1737 Wed Mar 15 08:14:01 2000 PST | @ 14 secs ago | Wed Mar 15 08:14:15 2000 PST 1738 Wed Mar 15 12:14:03 2000 PST | @ 1 min | Wed Mar 15 12:13:03 2000 PST 1739 Wed Mar 15 12:14:03 2000 PST | @ 14 secs ago | Wed Mar 15 12:14:17 2000 PST 1740 Wed Mar 15 13:14:02 2000 PST | @ 1 min | Wed Mar 15 13:13:02 2000 PST 1741 Wed Mar 15 13:14:02 2000 PST | @ 14 secs ago | Wed Mar 15 13:14:16 2000 PST 1742 Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 05:32:01 2000 PDT 1743 Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 17:32:01 2000 PDT 1744 Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 05:32:01 2000 PDT 1745 Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 17:32:01 2000 PDT 1746 Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 17:32:01 2000 PDT 1747 Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 17:32:01 2000 PDT 1748 Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST 1749 Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST 1750 Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST 1751 Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST 1752 Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST 1753 Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST 1754 Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST 1755 Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST 1756 Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST 1757 Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST 1758 Sat Sep 22 18:19:20 2001 PDT | @ 5 mons 12 hours | Sun Apr 22 06:19:20 2001 PDT 1759 Sat Sep 22 18:19:20 2001 PDT | @ 5 mons | Sun Apr 22 18:19:20 2001 PDT 1760 Sat Sep 22 18:19:20 2001 PDT | @ 3 mons | Fri Jun 22 18:19:20 2001 PDT 1761 Sat Sep 22 18:19:20 2001 PDT | @ 10 days | Wed Sep 12 18:19:20 2001 PDT 1762 Sat Sep 22 18:19:20 2001 PDT | @ 1 day 2 hours 3 mins 4 secs | Fri Sep 21 16:16:16 2001 PDT 1763 Sat Sep 22 18:19:20 2001 PDT | @ 5 hours | Sat Sep 22 13:19:20 2001 PDT 1764 Sat Sep 22 18:19:20 2001 PDT | @ 1 min | Sat Sep 22 18:18:20 2001 PDT 1765 Sat Sep 22 18:19:20 2001 PDT | @ 14 secs ago | Sat Sep 22 18:19:34 2001 PDT 1766(160 rows) 1767 1768SELECT d.f1 AS "timestamp", 1769 timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero, 1770 d.f1 - timestamp with time zone '1980-01-06 00:00 GMT' AS difference 1771 FROM TEMP_TIMESTAMP d 1772 ORDER BY difference; 1773 timestamp | gpstime_zero | difference 1774------------------------------+------------------------------+------------------------------------- 1775 Thu Jan 01 00:00:00 1970 PST | Sat Jan 05 16:00:00 1980 PST | @ 3656 days 16 hours ago 1776 Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec 1777 Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec 1778 Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec 1779 Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec 1780 Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec 1781 Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec 1782 Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec 1783 Wed Mar 15 02:14:05 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 10 hours 14 mins 5 secs 1784 Wed Mar 15 03:14:04 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 11 hours 14 mins 4 secs 1785 Wed Mar 15 08:14:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 16 hours 14 mins 1 sec 1786 Wed Mar 15 12:14:03 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 20 hours 14 mins 3 secs 1787 Wed Mar 15 13:14:02 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7374 days 21 hours 14 mins 2 secs 1788 Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec 1789 Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec 1790 Sat Sep 22 18:19:20 2001 PDT | Sat Jan 05 16:00:00 1980 PST | @ 7931 days 1 hour 19 mins 20 secs 1791(16 rows) 1792 1793SELECT d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference 1794 FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2 1795 ORDER BY timestamp1, timestamp2, difference; 1796 timestamp1 | timestamp2 | difference 1797------------------------------+------------------------------+------------------------------------------- 1798 Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 PST | @ 0 1799 Thu Jan 01 00:00:00 1970 PST | Wed Feb 28 17:32:01 1996 PST | @ 9554 days 17 hours 32 mins 1 sec ago 1800 Thu Jan 01 00:00:00 1970 PST | Thu Feb 29 17:32:01 1996 PST | @ 9555 days 17 hours 32 mins 1 sec ago 1801 Thu Jan 01 00:00:00 1970 PST | Fri Mar 01 17:32:01 1996 PST | @ 9556 days 17 hours 32 mins 1 sec ago 1802 Thu Jan 01 00:00:00 1970 PST | Mon Dec 30 17:32:01 1996 PST | @ 9860 days 17 hours 32 mins 1 sec ago 1803 Thu Jan 01 00:00:00 1970 PST | Tue Dec 31 17:32:01 1996 PST | @ 9861 days 17 hours 32 mins 1 sec ago 1804 Thu Jan 01 00:00:00 1970 PST | Fri Dec 31 17:32:01 1999 PST | @ 10956 days 17 hours 32 mins 1 sec ago 1805 Thu Jan 01 00:00:00 1970 PST | Sat Jan 01 17:32:01 2000 PST | @ 10957 days 17 hours 32 mins 1 sec ago 1806 Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 02:14:05 2000 PST | @ 11031 days 2 hours 14 mins 5 secs ago 1807 Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 03:14:04 2000 PST | @ 11031 days 3 hours 14 mins 4 secs ago 1808 Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 08:14:01 2000 PST | @ 11031 days 8 hours 14 mins 1 sec ago 1809 Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 12:14:03 2000 PST | @ 11031 days 12 hours 14 mins 3 secs ago 1810 Thu Jan 01 00:00:00 1970 PST | Wed Mar 15 13:14:02 2000 PST | @ 11031 days 13 hours 14 mins 2 secs ago 1811 Thu Jan 01 00:00:00 1970 PST | Sun Dec 31 17:32:01 2000 PST | @ 11322 days 17 hours 32 mins 1 sec ago 1812 Thu Jan 01 00:00:00 1970 PST | Mon Jan 01 17:32:01 2001 PST | @ 11323 days 17 hours 32 mins 1 sec ago 1813 Thu Jan 01 00:00:00 1970 PST | Sat Sep 22 18:19:20 2001 PDT | @ 11587 days 17 hours 19 mins 20 secs ago 1814 Wed Feb 28 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9554 days 17 hours 32 mins 1 sec 1815 Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0 1816 Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago 1817 Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago 1818 Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago 1819 Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago 1820 Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago 1821 Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago 1822 Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1476 days 8 hours 42 mins 4 secs ago 1823 Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1476 days 9 hours 42 mins 3 secs ago 1824 Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1476 days 14 hours 42 mins ago 1825 Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1476 days 18 hours 42 mins 2 secs ago 1826 Wed Feb 28 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1476 days 19 hours 42 mins 1 sec ago 1827 Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago 1828 Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago 1829 Wed Feb 28 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2032 days 23 hours 47 mins 19 secs ago 1830 Thu Feb 29 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9555 days 17 hours 32 mins 1 sec 1831 Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day 1832 Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0 1833 Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago 1834 Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago 1835 Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago 1836 Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago 1837 Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago 1838 Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1475 days 8 hours 42 mins 4 secs ago 1839 Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1475 days 9 hours 42 mins 3 secs ago 1840 Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1475 days 14 hours 42 mins ago 1841 Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1475 days 18 hours 42 mins 2 secs ago 1842 Thu Feb 29 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1475 days 19 hours 42 mins 1 sec ago 1843 Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago 1844 Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago 1845 Thu Feb 29 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2031 days 23 hours 47 mins 19 secs ago 1846 Fri Mar 01 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9556 days 17 hours 32 mins 1 sec 1847 Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days 1848 Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day 1849 Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0 1850 Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago 1851 Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago 1852 Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago 1853 Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago 1854 Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1474 days 8 hours 42 mins 4 secs ago 1855 Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1474 days 9 hours 42 mins 3 secs ago 1856 Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1474 days 14 hours 42 mins ago 1857 Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1474 days 18 hours 42 mins 2 secs ago 1858 Fri Mar 01 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1474 days 19 hours 42 mins 1 sec ago 1859 Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago 1860 Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago 1861 Fri Mar 01 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 2030 days 23 hours 47 mins 19 secs ago 1862 Mon Dec 30 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9860 days 17 hours 32 mins 1 sec 1863 Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days 1864 Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days 1865 Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days 1866 Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0 1867 Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago 1868 Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago 1869 Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago 1870 Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1170 days 8 hours 42 mins 4 secs ago 1871 Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1170 days 9 hours 42 mins 3 secs ago 1872 Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1170 days 14 hours 42 mins ago 1873 Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1170 days 18 hours 42 mins 2 secs ago 1874 Mon Dec 30 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1170 days 19 hours 42 mins 1 sec ago 1875 Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago 1876 Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago 1877 Mon Dec 30 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1726 days 23 hours 47 mins 19 secs ago 1878 Tue Dec 31 17:32:01 1996 PST | Thu Jan 01 00:00:00 1970 PST | @ 9861 days 17 hours 32 mins 1 sec 1879 Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days 1880 Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days 1881 Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days 1882 Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day 1883 Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0 1884 Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago 1885 Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago 1886 Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 02:14:05 2000 PST | @ 1169 days 8 hours 42 mins 4 secs ago 1887 Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 03:14:04 2000 PST | @ 1169 days 9 hours 42 mins 3 secs ago 1888 Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 08:14:01 2000 PST | @ 1169 days 14 hours 42 mins ago 1889 Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 12:14:03 2000 PST | @ 1169 days 18 hours 42 mins 2 secs ago 1890 Tue Dec 31 17:32:01 1996 PST | Wed Mar 15 13:14:02 2000 PST | @ 1169 days 19 hours 42 mins 1 sec ago 1891 Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago 1892 Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago 1893 Tue Dec 31 17:32:01 1996 PST | Sat Sep 22 18:19:20 2001 PDT | @ 1725 days 23 hours 47 mins 19 secs ago 1894 Fri Dec 31 17:32:01 1999 PST | Thu Jan 01 00:00:00 1970 PST | @ 10956 days 17 hours 32 mins 1 sec 1895 Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days 1896 Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days 1897 Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days 1898 Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days 1899 Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days 1900 Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0 1901 Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago 1902 Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 02:14:05 2000 PST | @ 74 days 8 hours 42 mins 4 secs ago 1903 Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 03:14:04 2000 PST | @ 74 days 9 hours 42 mins 3 secs ago 1904 Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 08:14:01 2000 PST | @ 74 days 14 hours 42 mins ago 1905 Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 12:14:03 2000 PST | @ 74 days 18 hours 42 mins 2 secs ago 1906 Fri Dec 31 17:32:01 1999 PST | Wed Mar 15 13:14:02 2000 PST | @ 74 days 19 hours 42 mins 1 sec ago 1907 Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago 1908 Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago 1909 Fri Dec 31 17:32:01 1999 PST | Sat Sep 22 18:19:20 2001 PDT | @ 630 days 23 hours 47 mins 19 secs ago 1910 Sat Jan 01 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 10957 days 17 hours 32 mins 1 sec 1911 Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days 1912 Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days 1913 Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days 1914 Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days 1915 Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days 1916 Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day 1917 Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0 1918 Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 73 days 8 hours 42 mins 4 secs ago 1919 Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 73 days 9 hours 42 mins 3 secs ago 1920 Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 73 days 14 hours 42 mins ago 1921 Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 73 days 18 hours 42 mins 2 secs ago 1922 Sat Jan 01 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 73 days 19 hours 42 mins 1 sec ago 1923 Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago 1924 Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago 1925 Sat Jan 01 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 629 days 23 hours 47 mins 19 secs ago 1926 Wed Mar 15 02:14:05 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 2 hours 14 mins 5 secs 1927 Wed Mar 15 02:14:05 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 8 hours 42 mins 4 secs 1928 Wed Mar 15 02:14:05 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 8 hours 42 mins 4 secs 1929 Wed Mar 15 02:14:05 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 8 hours 42 mins 4 secs 1930 Wed Mar 15 02:14:05 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 8 hours 42 mins 4 secs 1931 Wed Mar 15 02:14:05 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 8 hours 42 mins 4 secs 1932 Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs 1933 Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs 1934 Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0 1935 Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago 1936 Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago 1937 Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago 1938 Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago 1939 Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago 1940 Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago 1941 Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago 1942 Wed Mar 15 03:14:04 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 3 hours 14 mins 4 secs 1943 Wed Mar 15 03:14:04 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 9 hours 42 mins 3 secs 1944 Wed Mar 15 03:14:04 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 9 hours 42 mins 3 secs 1945 Wed Mar 15 03:14:04 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 9 hours 42 mins 3 secs 1946 Wed Mar 15 03:14:04 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 9 hours 42 mins 3 secs 1947 Wed Mar 15 03:14:04 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 9 hours 42 mins 3 secs 1948 Wed Mar 15 03:14:04 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 9 hours 42 mins 3 secs 1949 Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs 1950 Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs 1951 Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0 1952 Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago 1953 Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago 1954 Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago 1955 Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago 1956 Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago 1957 Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago 1958 Wed Mar 15 08:14:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 8 hours 14 mins 1 sec 1959 Wed Mar 15 08:14:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 14 hours 42 mins 1960 Wed Mar 15 08:14:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 14 hours 42 mins 1961 Wed Mar 15 08:14:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 14 hours 42 mins 1962 Wed Mar 15 08:14:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 14 hours 42 mins 1963 Wed Mar 15 08:14:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 14 hours 42 mins 1964 Wed Mar 15 08:14:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 14 hours 42 mins 1965 Wed Mar 15 08:14:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 14 hours 42 mins 1966 Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs 1967 Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs 1968 Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0 1969 Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago 1970 Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago 1971 Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago 1972 Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago 1973 Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago 1974 Wed Mar 15 12:14:03 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 12 hours 14 mins 3 secs 1975 Wed Mar 15 12:14:03 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 18 hours 42 mins 2 secs 1976 Wed Mar 15 12:14:03 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 18 hours 42 mins 2 secs 1977 Wed Mar 15 12:14:03 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 18 hours 42 mins 2 secs 1978 Wed Mar 15 12:14:03 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 18 hours 42 mins 2 secs 1979 Wed Mar 15 12:14:03 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 18 hours 42 mins 2 secs 1980 Wed Mar 15 12:14:03 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 18 hours 42 mins 2 secs 1981 Wed Mar 15 12:14:03 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 18 hours 42 mins 2 secs 1982 Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 9 hours 59 mins 58 secs 1983 Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs 1984 Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs 1985 Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0 1986 Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago 1987 Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago 1988 Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago 1989 Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago 1990 Wed Mar 15 13:14:02 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11031 days 13 hours 14 mins 2 secs 1991 Wed Mar 15 13:14:02 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1476 days 19 hours 42 mins 1 sec 1992 Wed Mar 15 13:14:02 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1475 days 19 hours 42 mins 1 sec 1993 Wed Mar 15 13:14:02 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1474 days 19 hours 42 mins 1 sec 1994 Wed Mar 15 13:14:02 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1170 days 19 hours 42 mins 1 sec 1995 Wed Mar 15 13:14:02 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1169 days 19 hours 42 mins 1 sec 1996 Wed Mar 15 13:14:02 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 19 hours 42 mins 1 sec 1997 Wed Mar 15 13:14:02 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 19 hours 42 mins 1 sec 1998 Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 10 hours 59 mins 57 secs 1999 Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 9 hours 59 mins 58 secs 2000 Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 1 sec 2001 Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 59 mins 59 secs 2002 Wed Mar 15 13:14:02 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 0 2003 Wed Mar 15 13:14:02 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 4 hours 17 mins 59 secs ago 2004 Wed Mar 15 13:14:02 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 4 hours 17 mins 59 secs ago 2005 Wed Mar 15 13:14:02 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 4 hours 5 mins 18 secs ago 2006 Sun Dec 31 17:32:01 2000 PST | Thu Jan 01 00:00:00 1970 PST | @ 11322 days 17 hours 32 mins 1 sec 2007 Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days 2008 Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days 2009 Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days 2010 Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days 2011 Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days 2012 Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days 2013 Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days 2014 Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 291 days 15 hours 17 mins 56 secs 2015 Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 291 days 14 hours 17 mins 57 secs 2016 Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 291 days 9 hours 18 mins 2017 Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 291 days 5 hours 17 mins 58 secs 2018 Sun Dec 31 17:32:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 291 days 4 hours 17 mins 59 secs 2019 Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0 2020 Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago 2021 Sun Dec 31 17:32:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 264 days 23 hours 47 mins 19 secs ago 2022 Mon Jan 01 17:32:01 2001 PST | Thu Jan 01 00:00:00 1970 PST | @ 11323 days 17 hours 32 mins 1 sec 2023 Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days 2024 Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days 2025 Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days 2026 Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days 2027 Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days 2028 Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days 2029 Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days 2030 Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 02:14:05 2000 PST | @ 292 days 15 hours 17 mins 56 secs 2031 Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 03:14:04 2000 PST | @ 292 days 14 hours 17 mins 57 secs 2032 Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 08:14:01 2000 PST | @ 292 days 9 hours 18 mins 2033 Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 12:14:03 2000 PST | @ 292 days 5 hours 17 mins 58 secs 2034 Mon Jan 01 17:32:01 2001 PST | Wed Mar 15 13:14:02 2000 PST | @ 292 days 4 hours 17 mins 59 secs 2035 Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2036 Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0 2037 Mon Jan 01 17:32:01 2001 PST | Sat Sep 22 18:19:20 2001 PDT | @ 263 days 23 hours 47 mins 19 secs ago 2038 Sat Sep 22 18:19:20 2001 PDT | Thu Jan 01 00:00:00 1970 PST | @ 11587 days 17 hours 19 mins 20 secs 2039 Sat Sep 22 18:19:20 2001 PDT | Wed Feb 28 17:32:01 1996 PST | @ 2032 days 23 hours 47 mins 19 secs 2040 Sat Sep 22 18:19:20 2001 PDT | Thu Feb 29 17:32:01 1996 PST | @ 2031 days 23 hours 47 mins 19 secs 2041 Sat Sep 22 18:19:20 2001 PDT | Fri Mar 01 17:32:01 1996 PST | @ 2030 days 23 hours 47 mins 19 secs 2042 Sat Sep 22 18:19:20 2001 PDT | Mon Dec 30 17:32:01 1996 PST | @ 1726 days 23 hours 47 mins 19 secs 2043 Sat Sep 22 18:19:20 2001 PDT | Tue Dec 31 17:32:01 1996 PST | @ 1725 days 23 hours 47 mins 19 secs 2044 Sat Sep 22 18:19:20 2001 PDT | Fri Dec 31 17:32:01 1999 PST | @ 630 days 23 hours 47 mins 19 secs 2045 Sat Sep 22 18:19:20 2001 PDT | Sat Jan 01 17:32:01 2000 PST | @ 629 days 23 hours 47 mins 19 secs 2046 Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 02:14:05 2000 PST | @ 556 days 15 hours 5 mins 15 secs 2047 Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 03:14:04 2000 PST | @ 556 days 14 hours 5 mins 16 secs 2048 Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 08:14:01 2000 PST | @ 556 days 9 hours 5 mins 19 secs 2049 Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 12:14:03 2000 PST | @ 556 days 5 hours 5 mins 17 secs 2050 Sat Sep 22 18:19:20 2001 PDT | Wed Mar 15 13:14:02 2000 PST | @ 556 days 4 hours 5 mins 18 secs 2051 Sat Sep 22 18:19:20 2001 PDT | Sun Dec 31 17:32:01 2000 PST | @ 264 days 23 hours 47 mins 19 secs 2052 Sat Sep 22 18:19:20 2001 PDT | Mon Jan 01 17:32:01 2001 PST | @ 263 days 23 hours 47 mins 19 secs 2053 Sat Sep 22 18:19:20 2001 PDT | Sat Sep 22 18:19:20 2001 PDT | @ 0 2054(256 rows) 2055 2056-- 2057-- Conversions 2058-- 2059SELECT f1 AS "timestamp", date(f1) AS date 2060 FROM TEMP_TIMESTAMP 2061 WHERE f1 <> timestamp 'now' 2062 ORDER BY date, "timestamp"; 2063 timestamp | date 2064------------------------------+------------ 2065 Thu Jan 01 00:00:00 1970 PST | 01-01-1970 2066 Wed Feb 28 17:32:01 1996 PST | 02-28-1996 2067 Thu Feb 29 17:32:01 1996 PST | 02-29-1996 2068 Fri Mar 01 17:32:01 1996 PST | 03-01-1996 2069 Mon Dec 30 17:32:01 1996 PST | 12-30-1996 2070 Tue Dec 31 17:32:01 1996 PST | 12-31-1996 2071 Fri Dec 31 17:32:01 1999 PST | 12-31-1999 2072 Sat Jan 01 17:32:01 2000 PST | 01-01-2000 2073 Wed Mar 15 02:14:05 2000 PST | 03-15-2000 2074 Wed Mar 15 03:14:04 2000 PST | 03-15-2000 2075 Wed Mar 15 08:14:01 2000 PST | 03-15-2000 2076 Wed Mar 15 12:14:03 2000 PST | 03-15-2000 2077 Wed Mar 15 13:14:02 2000 PST | 03-15-2000 2078 Sun Dec 31 17:32:01 2000 PST | 12-31-2000 2079 Mon Jan 01 17:32:01 2001 PST | 01-01-2001 2080 Sat Sep 22 18:19:20 2001 PDT | 09-22-2001 2081(16 rows) 2082 2083DROP TABLE TEMP_TIMESTAMP; 2084-- 2085-- Comparisons between datetime types, especially overflow cases 2086--- 2087SELECT '2202020-10-05'::date::timestamp; -- fail 2088ERROR: date out of range for timestamp 2089SELECT '2202020-10-05'::date > '2020-10-05'::timestamp as t; 2090 t 2091--- 2092 t 2093(1 row) 2094 2095SELECT '2020-10-05'::timestamp > '2202020-10-05'::date as f; 2096 f 2097--- 2098 f 2099(1 row) 2100 2101SELECT '2202020-10-05'::date::timestamptz; -- fail 2102ERROR: date out of range for timestamp 2103SELECT '2202020-10-05'::date > '2020-10-05'::timestamptz as t; 2104 t 2105--- 2106 t 2107(1 row) 2108 2109SELECT '2020-10-05'::timestamptz > '2202020-10-05'::date as f; 2110 f 2111--- 2112 f 2113(1 row) 2114 2115-- This conversion may work depending on timezone 2116SELECT '4714-11-24 BC'::date::timestamptz; 2117 timestamptz 2118--------------------------------- 2119 Mon Nov 24 00:00:00 4714 PST BC 2120(1 row) 2121 2122SET TimeZone = 'UTC-2'; 2123SELECT '4714-11-24 BC'::date::timestamptz; -- fail 2124ERROR: date out of range for timestamp 2125SELECT '4714-11-24 BC'::date < '2020-10-05'::timestamptz as t; 2126 t 2127--- 2128 t 2129(1 row) 2130 2131SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::date as t; 2132 t 2133--- 2134 t 2135(1 row) 2136 2137SELECT '4714-11-24 BC'::timestamp < '2020-10-05'::timestamptz as t; 2138 t 2139--- 2140 t 2141(1 row) 2142 2143SELECT '2020-10-05'::timestamptz >= '4714-11-24 BC'::timestamp as t; 2144 t 2145--- 2146 t 2147(1 row) 2148 2149RESET TimeZone; 2150-- 2151-- Formats 2152-- 2153SET DateStyle TO 'US,Postgres'; 2154SHOW DateStyle; 2155 DateStyle 2156--------------- 2157 Postgres, MDY 2158(1 row) 2159 2160SELECT d1 AS us_postgres FROM TIMESTAMP_TBL; 2161 us_postgres 2162----------------------------- 2163 -infinity 2164 infinity 2165 Thu Jan 01 00:00:00 1970 2166 Mon Feb 10 17:32:01 1997 2167 Mon Feb 10 17:32:01 1997 2168 Mon Feb 10 17:32:02 1997 2169 Mon Feb 10 17:32:01.4 1997 2170 Mon Feb 10 17:32:01.5 1997 2171 Mon Feb 10 17:32:01.6 1997 2172 Thu Jan 02 00:00:00 1997 2173 Thu Jan 02 03:04:05 1997 2174 Mon Feb 10 17:32:01 1997 2175 Mon Feb 10 17:32:01 1997 2176 Mon Feb 10 17:32:01 1997 2177 Mon Feb 10 17:32:01 1997 2178 Tue Jun 10 17:32:01 1997 2179 Sat Sep 22 18:19:20 2001 2180 Wed Mar 15 08:14:01 2000 2181 Wed Mar 15 13:14:02 2000 2182 Wed Mar 15 12:14:03 2000 2183 Wed Mar 15 03:14:04 2000 2184 Wed Mar 15 02:14:05 2000 2185 Mon Feb 10 17:32:01 1997 2186 Mon Feb 10 17:32:01 1997 2187 Mon Feb 10 17:32:00 1997 2188 Mon Feb 10 17:32:01 1997 2189 Mon Feb 10 17:32:01 1997 2190 Mon Feb 10 17:32:01 1997 2191 Mon Feb 10 17:32:01 1997 2192 Mon Feb 10 17:32:01 1997 2193 Mon Feb 10 17:32:01 1997 2194 Mon Feb 10 17:32:01 1997 2195 Mon Feb 10 17:32:01 1997 2196 Mon Feb 10 17:32:01 1997 2197 Tue Jun 10 18:32:01 1997 2198 Mon Feb 10 17:32:01 1997 2199 Tue Feb 11 17:32:01 1997 2200 Wed Feb 12 17:32:01 1997 2201 Thu Feb 13 17:32:01 1997 2202 Fri Feb 14 17:32:01 1997 2203 Sat Feb 15 17:32:01 1997 2204 Sun Feb 16 17:32:01 1997 2205 Tue Feb 16 17:32:01 0097 BC 2206 Sat Feb 16 17:32:01 0097 2207 Thu Feb 16 17:32:01 0597 2208 Tue Feb 16 17:32:01 1097 2209 Sat Feb 16 17:32:01 1697 2210 Thu Feb 16 17:32:01 1797 2211 Tue Feb 16 17:32:01 1897 2212 Sun Feb 16 17:32:01 1997 2213 Sat Feb 16 17:32:01 2097 2214 Wed Feb 28 17:32:01 1996 2215 Thu Feb 29 17:32:01 1996 2216 Fri Mar 01 17:32:01 1996 2217 Mon Dec 30 17:32:01 1996 2218 Tue Dec 31 17:32:01 1996 2219 Wed Jan 01 17:32:01 1997 2220 Fri Feb 28 17:32:01 1997 2221 Sat Mar 01 17:32:01 1997 2222 Tue Dec 30 17:32:01 1997 2223 Wed Dec 31 17:32:01 1997 2224 Fri Dec 31 17:32:01 1999 2225 Sat Jan 01 17:32:01 2000 2226 Sun Dec 31 17:32:01 2000 2227 Mon Jan 01 17:32:01 2001 2228(65 rows) 2229 2230SET DateStyle TO 'US,ISO'; 2231SELECT d1 AS us_iso FROM TIMESTAMP_TBL; 2232 us_iso 2233------------------------ 2234 -infinity 2235 infinity 2236 1970-01-01 00:00:00 2237 1997-02-10 17:32:01 2238 1997-02-10 17:32:01 2239 1997-02-10 17:32:02 2240 1997-02-10 17:32:01.4 2241 1997-02-10 17:32:01.5 2242 1997-02-10 17:32:01.6 2243 1997-01-02 00:00:00 2244 1997-01-02 03:04:05 2245 1997-02-10 17:32:01 2246 1997-02-10 17:32:01 2247 1997-02-10 17:32:01 2248 1997-02-10 17:32:01 2249 1997-06-10 17:32:01 2250 2001-09-22 18:19:20 2251 2000-03-15 08:14:01 2252 2000-03-15 13:14:02 2253 2000-03-15 12:14:03 2254 2000-03-15 03:14:04 2255 2000-03-15 02:14:05 2256 1997-02-10 17:32:01 2257 1997-02-10 17:32:01 2258 1997-02-10 17:32:00 2259 1997-02-10 17:32:01 2260 1997-02-10 17:32:01 2261 1997-02-10 17:32:01 2262 1997-02-10 17:32:01 2263 1997-02-10 17:32:01 2264 1997-02-10 17:32:01 2265 1997-02-10 17:32:01 2266 1997-02-10 17:32:01 2267 1997-02-10 17:32:01 2268 1997-06-10 18:32:01 2269 1997-02-10 17:32:01 2270 1997-02-11 17:32:01 2271 1997-02-12 17:32:01 2272 1997-02-13 17:32:01 2273 1997-02-14 17:32:01 2274 1997-02-15 17:32:01 2275 1997-02-16 17:32:01 2276 0097-02-16 17:32:01 BC 2277 0097-02-16 17:32:01 2278 0597-02-16 17:32:01 2279 1097-02-16 17:32:01 2280 1697-02-16 17:32:01 2281 1797-02-16 17:32:01 2282 1897-02-16 17:32:01 2283 1997-02-16 17:32:01 2284 2097-02-16 17:32:01 2285 1996-02-28 17:32:01 2286 1996-02-29 17:32:01 2287 1996-03-01 17:32:01 2288 1996-12-30 17:32:01 2289 1996-12-31 17:32:01 2290 1997-01-01 17:32:01 2291 1997-02-28 17:32:01 2292 1997-03-01 17:32:01 2293 1997-12-30 17:32:01 2294 1997-12-31 17:32:01 2295 1999-12-31 17:32:01 2296 2000-01-01 17:32:01 2297 2000-12-31 17:32:01 2298 2001-01-01 17:32:01 2299(65 rows) 2300 2301SET DateStyle TO 'US,SQL'; 2302SHOW DateStyle; 2303 DateStyle 2304----------- 2305 SQL, MDY 2306(1 row) 2307 2308SELECT d1 AS us_sql FROM TIMESTAMP_TBL; 2309 us_sql 2310------------------------ 2311 -infinity 2312 infinity 2313 01/01/1970 00:00:00 2314 02/10/1997 17:32:01 2315 02/10/1997 17:32:01 2316 02/10/1997 17:32:02 2317 02/10/1997 17:32:01.4 2318 02/10/1997 17:32:01.5 2319 02/10/1997 17:32:01.6 2320 01/02/1997 00:00:00 2321 01/02/1997 03:04:05 2322 02/10/1997 17:32:01 2323 02/10/1997 17:32:01 2324 02/10/1997 17:32:01 2325 02/10/1997 17:32:01 2326 06/10/1997 17:32:01 2327 09/22/2001 18:19:20 2328 03/15/2000 08:14:01 2329 03/15/2000 13:14:02 2330 03/15/2000 12:14:03 2331 03/15/2000 03:14:04 2332 03/15/2000 02:14:05 2333 02/10/1997 17:32:01 2334 02/10/1997 17:32:01 2335 02/10/1997 17:32:00 2336 02/10/1997 17:32:01 2337 02/10/1997 17:32:01 2338 02/10/1997 17:32:01 2339 02/10/1997 17:32:01 2340 02/10/1997 17:32:01 2341 02/10/1997 17:32:01 2342 02/10/1997 17:32:01 2343 02/10/1997 17:32:01 2344 02/10/1997 17:32:01 2345 06/10/1997 18:32:01 2346 02/10/1997 17:32:01 2347 02/11/1997 17:32:01 2348 02/12/1997 17:32:01 2349 02/13/1997 17:32:01 2350 02/14/1997 17:32:01 2351 02/15/1997 17:32:01 2352 02/16/1997 17:32:01 2353 02/16/0097 17:32:01 BC 2354 02/16/0097 17:32:01 2355 02/16/0597 17:32:01 2356 02/16/1097 17:32:01 2357 02/16/1697 17:32:01 2358 02/16/1797 17:32:01 2359 02/16/1897 17:32:01 2360 02/16/1997 17:32:01 2361 02/16/2097 17:32:01 2362 02/28/1996 17:32:01 2363 02/29/1996 17:32:01 2364 03/01/1996 17:32:01 2365 12/30/1996 17:32:01 2366 12/31/1996 17:32:01 2367 01/01/1997 17:32:01 2368 02/28/1997 17:32:01 2369 03/01/1997 17:32:01 2370 12/30/1997 17:32:01 2371 12/31/1997 17:32:01 2372 12/31/1999 17:32:01 2373 01/01/2000 17:32:01 2374 12/31/2000 17:32:01 2375 01/01/2001 17:32:01 2376(65 rows) 2377 2378SET DateStyle TO 'European,Postgres'; 2379SHOW DateStyle; 2380 DateStyle 2381--------------- 2382 Postgres, DMY 2383(1 row) 2384 2385INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957'); 2386SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957'; 2387 one 2388----- 2389 1 2390(1 row) 2391 2392SELECT d1 AS european_postgres FROM TIMESTAMP_TBL; 2393 european_postgres 2394----------------------------- 2395 -infinity 2396 infinity 2397 Thu 01 Jan 00:00:00 1970 2398 Mon 10 Feb 17:32:01 1997 2399 Mon 10 Feb 17:32:01 1997 2400 Mon 10 Feb 17:32:02 1997 2401 Mon 10 Feb 17:32:01.4 1997 2402 Mon 10 Feb 17:32:01.5 1997 2403 Mon 10 Feb 17:32:01.6 1997 2404 Thu 02 Jan 00:00:00 1997 2405 Thu 02 Jan 03:04:05 1997 2406 Mon 10 Feb 17:32:01 1997 2407 Mon 10 Feb 17:32:01 1997 2408 Mon 10 Feb 17:32:01 1997 2409 Mon 10 Feb 17:32:01 1997 2410 Tue 10 Jun 17:32:01 1997 2411 Sat 22 Sep 18:19:20 2001 2412 Wed 15 Mar 08:14:01 2000 2413 Wed 15 Mar 13:14:02 2000 2414 Wed 15 Mar 12:14:03 2000 2415 Wed 15 Mar 03:14:04 2000 2416 Wed 15 Mar 02:14:05 2000 2417 Mon 10 Feb 17:32:01 1997 2418 Mon 10 Feb 17:32:01 1997 2419 Mon 10 Feb 17:32:00 1997 2420 Mon 10 Feb 17:32:01 1997 2421 Mon 10 Feb 17:32:01 1997 2422 Mon 10 Feb 17:32:01 1997 2423 Mon 10 Feb 17:32:01 1997 2424 Mon 10 Feb 17:32:01 1997 2425 Mon 10 Feb 17:32:01 1997 2426 Mon 10 Feb 17:32:01 1997 2427 Mon 10 Feb 17:32:01 1997 2428 Mon 10 Feb 17:32:01 1997 2429 Tue 10 Jun 18:32:01 1997 2430 Mon 10 Feb 17:32:01 1997 2431 Tue 11 Feb 17:32:01 1997 2432 Wed 12 Feb 17:32:01 1997 2433 Thu 13 Feb 17:32:01 1997 2434 Fri 14 Feb 17:32:01 1997 2435 Sat 15 Feb 17:32:01 1997 2436 Sun 16 Feb 17:32:01 1997 2437 Tue 16 Feb 17:32:01 0097 BC 2438 Sat 16 Feb 17:32:01 0097 2439 Thu 16 Feb 17:32:01 0597 2440 Tue 16 Feb 17:32:01 1097 2441 Sat 16 Feb 17:32:01 1697 2442 Thu 16 Feb 17:32:01 1797 2443 Tue 16 Feb 17:32:01 1897 2444 Sun 16 Feb 17:32:01 1997 2445 Sat 16 Feb 17:32:01 2097 2446 Wed 28 Feb 17:32:01 1996 2447 Thu 29 Feb 17:32:01 1996 2448 Fri 01 Mar 17:32:01 1996 2449 Mon 30 Dec 17:32:01 1996 2450 Tue 31 Dec 17:32:01 1996 2451 Wed 01 Jan 17:32:01 1997 2452 Fri 28 Feb 17:32:01 1997 2453 Sat 01 Mar 17:32:01 1997 2454 Tue 30 Dec 17:32:01 1997 2455 Wed 31 Dec 17:32:01 1997 2456 Fri 31 Dec 17:32:01 1999 2457 Sat 01 Jan 17:32:01 2000 2458 Sun 31 Dec 17:32:01 2000 2459 Mon 01 Jan 17:32:01 2001 2460 Thu 13 Jun 00:00:00 1957 2461(66 rows) 2462 2463SET DateStyle TO 'European,ISO'; 2464SHOW DateStyle; 2465 DateStyle 2466----------- 2467 ISO, DMY 2468(1 row) 2469 2470SELECT d1 AS european_iso FROM TIMESTAMP_TBL; 2471 european_iso 2472------------------------ 2473 -infinity 2474 infinity 2475 1970-01-01 00:00:00 2476 1997-02-10 17:32:01 2477 1997-02-10 17:32:01 2478 1997-02-10 17:32:02 2479 1997-02-10 17:32:01.4 2480 1997-02-10 17:32:01.5 2481 1997-02-10 17:32:01.6 2482 1997-01-02 00:00:00 2483 1997-01-02 03:04:05 2484 1997-02-10 17:32:01 2485 1997-02-10 17:32:01 2486 1997-02-10 17:32:01 2487 1997-02-10 17:32:01 2488 1997-06-10 17:32:01 2489 2001-09-22 18:19:20 2490 2000-03-15 08:14:01 2491 2000-03-15 13:14:02 2492 2000-03-15 12:14:03 2493 2000-03-15 03:14:04 2494 2000-03-15 02:14:05 2495 1997-02-10 17:32:01 2496 1997-02-10 17:32:01 2497 1997-02-10 17:32:00 2498 1997-02-10 17:32:01 2499 1997-02-10 17:32:01 2500 1997-02-10 17:32:01 2501 1997-02-10 17:32:01 2502 1997-02-10 17:32:01 2503 1997-02-10 17:32:01 2504 1997-02-10 17:32:01 2505 1997-02-10 17:32:01 2506 1997-02-10 17:32:01 2507 1997-06-10 18:32:01 2508 1997-02-10 17:32:01 2509 1997-02-11 17:32:01 2510 1997-02-12 17:32:01 2511 1997-02-13 17:32:01 2512 1997-02-14 17:32:01 2513 1997-02-15 17:32:01 2514 1997-02-16 17:32:01 2515 0097-02-16 17:32:01 BC 2516 0097-02-16 17:32:01 2517 0597-02-16 17:32:01 2518 1097-02-16 17:32:01 2519 1697-02-16 17:32:01 2520 1797-02-16 17:32:01 2521 1897-02-16 17:32:01 2522 1997-02-16 17:32:01 2523 2097-02-16 17:32:01 2524 1996-02-28 17:32:01 2525 1996-02-29 17:32:01 2526 1996-03-01 17:32:01 2527 1996-12-30 17:32:01 2528 1996-12-31 17:32:01 2529 1997-01-01 17:32:01 2530 1997-02-28 17:32:01 2531 1997-03-01 17:32:01 2532 1997-12-30 17:32:01 2533 1997-12-31 17:32:01 2534 1999-12-31 17:32:01 2535 2000-01-01 17:32:01 2536 2000-12-31 17:32:01 2537 2001-01-01 17:32:01 2538 1957-06-13 00:00:00 2539(66 rows) 2540 2541SET DateStyle TO 'European,SQL'; 2542SHOW DateStyle; 2543 DateStyle 2544----------- 2545 SQL, DMY 2546(1 row) 2547 2548SELECT d1 AS european_sql FROM TIMESTAMP_TBL; 2549 european_sql 2550------------------------ 2551 -infinity 2552 infinity 2553 01/01/1970 00:00:00 2554 10/02/1997 17:32:01 2555 10/02/1997 17:32:01 2556 10/02/1997 17:32:02 2557 10/02/1997 17:32:01.4 2558 10/02/1997 17:32:01.5 2559 10/02/1997 17:32:01.6 2560 02/01/1997 00:00:00 2561 02/01/1997 03:04:05 2562 10/02/1997 17:32:01 2563 10/02/1997 17:32:01 2564 10/02/1997 17:32:01 2565 10/02/1997 17:32:01 2566 10/06/1997 17:32:01 2567 22/09/2001 18:19:20 2568 15/03/2000 08:14:01 2569 15/03/2000 13:14:02 2570 15/03/2000 12:14:03 2571 15/03/2000 03:14:04 2572 15/03/2000 02:14:05 2573 10/02/1997 17:32:01 2574 10/02/1997 17:32:01 2575 10/02/1997 17:32:00 2576 10/02/1997 17:32:01 2577 10/02/1997 17:32:01 2578 10/02/1997 17:32:01 2579 10/02/1997 17:32:01 2580 10/02/1997 17:32:01 2581 10/02/1997 17:32:01 2582 10/02/1997 17:32:01 2583 10/02/1997 17:32:01 2584 10/02/1997 17:32:01 2585 10/06/1997 18:32:01 2586 10/02/1997 17:32:01 2587 11/02/1997 17:32:01 2588 12/02/1997 17:32:01 2589 13/02/1997 17:32:01 2590 14/02/1997 17:32:01 2591 15/02/1997 17:32:01 2592 16/02/1997 17:32:01 2593 16/02/0097 17:32:01 BC 2594 16/02/0097 17:32:01 2595 16/02/0597 17:32:01 2596 16/02/1097 17:32:01 2597 16/02/1697 17:32:01 2598 16/02/1797 17:32:01 2599 16/02/1897 17:32:01 2600 16/02/1997 17:32:01 2601 16/02/2097 17:32:01 2602 28/02/1996 17:32:01 2603 29/02/1996 17:32:01 2604 01/03/1996 17:32:01 2605 30/12/1996 17:32:01 2606 31/12/1996 17:32:01 2607 01/01/1997 17:32:01 2608 28/02/1997 17:32:01 2609 01/03/1997 17:32:01 2610 30/12/1997 17:32:01 2611 31/12/1997 17:32:01 2612 31/12/1999 17:32:01 2613 01/01/2000 17:32:01 2614 31/12/2000 17:32:01 2615 01/01/2001 17:32:01 2616 13/06/1957 00:00:00 2617(66 rows) 2618 2619RESET DateStyle; 2620-- 2621-- to_timestamp() 2622-- 2623SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); 2624 to_timestamp 2625------------------------------ 2626 Sat Feb 16 08:14:30 0097 PST 2627(1 row) 2628 2629SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); 2630 to_timestamp 2631------------------------------ 2632 Sat Feb 16 08:14:30 0097 PST 2633(1 row) 2634 2635SELECT to_timestamp('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS'); 2636 to_timestamp 2637------------------------------ 2638 Fri Mar 18 23:38:15 2011 PDT 2639(1 row) 2640 2641SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD'); 2642 to_timestamp 2643------------------------------ 2644 Sat Jan 12 00:00:00 1985 PST 2645(1 row) 2646 2647SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD'); 2648 to_timestamp 2649------------------------------ 2650 Sat Jan 12 00:00:00 1985 PST 2651(1 row) 2652 2653SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD'); 2654 to_timestamp 2655------------------------------ 2656 Sat Jan 12 00:00:00 1985 PST 2657(1 row) 2658 2659SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', 2660 '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD'); 2661 to_timestamp 2662------------------------------ 2663 Sun May 16 00:00:00 1976 PDT 2664(1 row) 2665 2666SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); 2667 to_timestamp 2668------------------------------ 2669 Sat Aug 21 00:00:00 1582 PST 2670(1 row) 2671 2672SELECT to_timestamp('15 "text between quote marks" 98 54 45', 2673 E'HH24 "\\"text between quote marks\\"" YY MI SS'); 2674 to_timestamp 2675------------------------------ 2676 Thu Jan 01 15:54:45 1998 PST 2677(1 row) 2678 2679SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY'); 2680 to_timestamp 2681------------------------------ 2682 Fri May 12 14:45:48 2000 PDT 2683(1 row) 2684 2685SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); 2686 to_timestamp 2687------------------------------ 2688 Sun Jan 09 00:00:00 2000 PST 2689(1 row) 2690 2691SELECT to_timestamp('97/Feb/16', 'YYMonDD'); 2692ERROR: invalid value "/Feb/16" for "Mon" 2693DETAIL: The given value did not match any of the allowed values for this field. 2694SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD'); 2695 to_timestamp 2696------------------------------ 2697 Sun Feb 16 00:00:00 1997 PST 2698(1 row) 2699 2700SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD'); 2701 to_timestamp 2702------------------------------ 2703 Sun Feb 16 00:00:00 1997 PST 2704(1 row) 2705 2706SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD'); 2707 to_timestamp 2708------------------------------ 2709 Sun Feb 16 00:00:00 1997 PST 2710(1 row) 2711 2712SELECT to_timestamp('19971116', 'YYYYMMDD'); 2713 to_timestamp 2714------------------------------ 2715 Sun Nov 16 00:00:00 1997 PST 2716(1 row) 2717 2718SELECT to_timestamp('20000-1116', 'YYYY-MMDD'); 2719 to_timestamp 2720------------------------------- 2721 Thu Nov 16 00:00:00 20000 PST 2722(1 row) 2723 2724SELECT to_timestamp('1997 AD 11 16', 'YYYY BC MM DD'); 2725 to_timestamp 2726------------------------------ 2727 Sun Nov 16 00:00:00 1997 PST 2728(1 row) 2729 2730SELECT to_timestamp('1997 BC 11 16', 'YYYY BC MM DD'); 2731 to_timestamp 2732--------------------------------- 2733 Tue Nov 16 00:00:00 1997 PST BC 2734(1 row) 2735 2736SELECT to_timestamp('1997 A.D. 11 16', 'YYYY B.C. MM DD'); 2737 to_timestamp 2738------------------------------ 2739 Sun Nov 16 00:00:00 1997 PST 2740(1 row) 2741 2742SELECT to_timestamp('1997 B.C. 11 16', 'YYYY B.C. MM DD'); 2743 to_timestamp 2744--------------------------------- 2745 Tue Nov 16 00:00:00 1997 PST BC 2746(1 row) 2747 2748SELECT to_timestamp('9-1116', 'Y-MMDD'); 2749 to_timestamp 2750------------------------------ 2751 Mon Nov 16 00:00:00 2009 PST 2752(1 row) 2753 2754SELECT to_timestamp('95-1116', 'YY-MMDD'); 2755 to_timestamp 2756------------------------------ 2757 Thu Nov 16 00:00:00 1995 PST 2758(1 row) 2759 2760SELECT to_timestamp('995-1116', 'YYY-MMDD'); 2761 to_timestamp 2762------------------------------ 2763 Thu Nov 16 00:00:00 1995 PST 2764(1 row) 2765 2766SELECT to_timestamp('2005426', 'YYYYWWD'); 2767 to_timestamp 2768------------------------------ 2769 Sat Oct 15 00:00:00 2005 PDT 2770(1 row) 2771 2772SELECT to_timestamp('2005300', 'YYYYDDD'); 2773 to_timestamp 2774------------------------------ 2775 Thu Oct 27 00:00:00 2005 PDT 2776(1 row) 2777 2778SELECT to_timestamp('2005527', 'IYYYIWID'); 2779 to_timestamp 2780------------------------------ 2781 Sun Jan 01 00:00:00 2006 PST 2782(1 row) 2783 2784SELECT to_timestamp('005527', 'IYYIWID'); 2785 to_timestamp 2786------------------------------ 2787 Sun Jan 01 00:00:00 2006 PST 2788(1 row) 2789 2790SELECT to_timestamp('05527', 'IYIWID'); 2791 to_timestamp 2792------------------------------ 2793 Sun Jan 01 00:00:00 2006 PST 2794(1 row) 2795 2796SELECT to_timestamp('5527', 'IIWID'); 2797 to_timestamp 2798------------------------------ 2799 Sun Jan 01 00:00:00 2006 PST 2800(1 row) 2801 2802SELECT to_timestamp('2005364', 'IYYYIDDD'); 2803 to_timestamp 2804------------------------------ 2805 Sun Jan 01 00:00:00 2006 PST 2806(1 row) 2807 2808SELECT to_timestamp('20050302', 'YYYYMMDD'); 2809 to_timestamp 2810------------------------------ 2811 Wed Mar 02 00:00:00 2005 PST 2812(1 row) 2813 2814SELECT to_timestamp('2005 03 02', 'YYYYMMDD'); 2815 to_timestamp 2816------------------------------ 2817 Wed Mar 02 00:00:00 2005 PST 2818(1 row) 2819 2820SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD'); 2821 to_timestamp 2822------------------------------ 2823 Wed Mar 02 00:00:00 2005 PST 2824(1 row) 2825 2826SELECT to_timestamp(' 20050302', 'YYYYMMDD'); 2827 to_timestamp 2828------------------------------ 2829 Wed Mar 02 00:00:00 2005 PST 2830(1 row) 2831 2832SELECT to_timestamp('2011-12-18 11:38 AM', 'YYYY-MM-DD HH12:MI PM'); 2833 to_timestamp 2834------------------------------ 2835 Sun Dec 18 11:38:00 2011 PST 2836(1 row) 2837 2838SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM'); 2839 to_timestamp 2840------------------------------ 2841 Sun Dec 18 23:38:00 2011 PST 2842(1 row) 2843 2844SELECT to_timestamp('2011-12-18 11:38 A.M.', 'YYYY-MM-DD HH12:MI P.M.'); 2845 to_timestamp 2846------------------------------ 2847 Sun Dec 18 11:38:00 2011 PST 2848(1 row) 2849 2850SELECT to_timestamp('2011-12-18 11:38 P.M.', 'YYYY-MM-DD HH12:MI P.M.'); 2851 to_timestamp 2852------------------------------ 2853 Sun Dec 18 23:38:00 2011 PST 2854(1 row) 2855 2856SELECT to_timestamp('2011-12-18 11:38 +05', 'YYYY-MM-DD HH12:MI TZH'); 2857 to_timestamp 2858------------------------------ 2859 Sat Dec 17 22:38:00 2011 PST 2860(1 row) 2861 2862SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZH'); 2863 to_timestamp 2864------------------------------ 2865 Sun Dec 18 08:38:00 2011 PST 2866(1 row) 2867 2868SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 2869 to_timestamp 2870------------------------------ 2871 Sat Dec 17 22:18:00 2011 PST 2872(1 row) 2873 2874SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 2875 to_timestamp 2876------------------------------ 2877 Sun Dec 18 08:58:00 2011 PST 2878(1 row) 2879 2880SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); 2881 to_timestamp 2882------------------------------ 2883 Sun Dec 18 03:18:00 2011 PST 2884(1 row) 2885 2886SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ'); -- NYI 2887ERROR: formatting field "TZ" is only supported in to_char 2888SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS'); 2889 to_timestamp 2890---------------------------------- 2891 Fri Nov 02 12:34:56.025 2018 PDT 2892(1 row) 2893 2894SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2895 i | to_timestamp 2896---+------------------------------ 2897 1 | Fri Nov 02 12:34:56 2018 PDT 2898 2 | Fri Nov 02 12:34:56 2018 PDT 2899 3 | Fri Nov 02 12:34:56 2018 PDT 2900 4 | Fri Nov 02 12:34:56 2018 PDT 2901 5 | Fri Nov 02 12:34:56 2018 PDT 2902 6 | Fri Nov 02 12:34:56 2018 PDT 2903(6 rows) 2904 2905SELECT i, to_timestamp('2018-11-02 12:34:56.1', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2906 i | to_timestamp 2907---+-------------------------------- 2908 1 | Fri Nov 02 12:34:56.1 2018 PDT 2909 2 | Fri Nov 02 12:34:56.1 2018 PDT 2910 3 | Fri Nov 02 12:34:56.1 2018 PDT 2911 4 | Fri Nov 02 12:34:56.1 2018 PDT 2912 5 | Fri Nov 02 12:34:56.1 2018 PDT 2913 6 | Fri Nov 02 12:34:56.1 2018 PDT 2914(6 rows) 2915 2916SELECT i, to_timestamp('2018-11-02 12:34:56.12', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2917 i | to_timestamp 2918---+--------------------------------- 2919 1 | Fri Nov 02 12:34:56.1 2018 PDT 2920 2 | Fri Nov 02 12:34:56.12 2018 PDT 2921 3 | Fri Nov 02 12:34:56.12 2018 PDT 2922 4 | Fri Nov 02 12:34:56.12 2018 PDT 2923 5 | Fri Nov 02 12:34:56.12 2018 PDT 2924 6 | Fri Nov 02 12:34:56.12 2018 PDT 2925(6 rows) 2926 2927SELECT i, to_timestamp('2018-11-02 12:34:56.123', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2928 i | to_timestamp 2929---+---------------------------------- 2930 1 | Fri Nov 02 12:34:56.1 2018 PDT 2931 2 | Fri Nov 02 12:34:56.12 2018 PDT 2932 3 | Fri Nov 02 12:34:56.123 2018 PDT 2933 4 | Fri Nov 02 12:34:56.123 2018 PDT 2934 5 | Fri Nov 02 12:34:56.123 2018 PDT 2935 6 | Fri Nov 02 12:34:56.123 2018 PDT 2936(6 rows) 2937 2938SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2939 i | to_timestamp 2940---+----------------------------------- 2941 1 | Fri Nov 02 12:34:56.1 2018 PDT 2942 2 | Fri Nov 02 12:34:56.12 2018 PDT 2943 3 | Fri Nov 02 12:34:56.123 2018 PDT 2944 4 | Fri Nov 02 12:34:56.1234 2018 PDT 2945 5 | Fri Nov 02 12:34:56.1234 2018 PDT 2946 6 | Fri Nov 02 12:34:56.1234 2018 PDT 2947(6 rows) 2948 2949SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2950 i | to_timestamp 2951---+------------------------------------ 2952 1 | Fri Nov 02 12:34:56.1 2018 PDT 2953 2 | Fri Nov 02 12:34:56.12 2018 PDT 2954 3 | Fri Nov 02 12:34:56.123 2018 PDT 2955 4 | Fri Nov 02 12:34:56.1235 2018 PDT 2956 5 | Fri Nov 02 12:34:56.12345 2018 PDT 2957 6 | Fri Nov 02 12:34:56.12345 2018 PDT 2958(6 rows) 2959 2960SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2961 i | to_timestamp 2962---+------------------------------------- 2963 1 | Fri Nov 02 12:34:56.1 2018 PDT 2964 2 | Fri Nov 02 12:34:56.12 2018 PDT 2965 3 | Fri Nov 02 12:34:56.123 2018 PDT 2966 4 | Fri Nov 02 12:34:56.1235 2018 PDT 2967 5 | Fri Nov 02 12:34:56.12346 2018 PDT 2968 6 | Fri Nov 02 12:34:56.123456 2018 PDT 2969(6 rows) 2970 2971SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; 2972ERROR: date/time field value out of range: "2018-11-02 12:34:56.123456789" 2973SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored 2974 to_date 2975------------ 2976 04-01-1902 2977(1 row) 2978 2979SELECT to_date('3 4 21 01', 'W MM CC YY'); 2980 to_date 2981------------ 2982 04-15-2001 2983(1 row) 2984 2985SELECT to_date('2458872', 'J'); 2986 to_date 2987------------ 2988 01-23-2020 2989(1 row) 2990 2991-- 2992-- Check handling of BC dates 2993-- 2994SELECT to_date('44-02-01 BC','YYYY-MM-DD BC'); 2995 to_date 2996--------------- 2997 02-01-0044 BC 2998(1 row) 2999 3000SELECT to_date('-44-02-01','YYYY-MM-DD'); 3001 to_date 3002--------------- 3003 02-01-0044 BC 3004(1 row) 3005 3006SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC'); 3007 to_date 3008------------ 3009 02-01-0044 3010(1 row) 3011 3012SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 3013 to_timestamp 3014--------------------------------- 3015 Fri Feb 01 11:12:13 0044 PST BC 3016(1 row) 3017 3018SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS'); 3019 to_timestamp 3020--------------------------------- 3021 Fri Feb 01 11:12:13 0044 PST BC 3022(1 row) 3023 3024SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 3025 to_timestamp 3026------------------------------ 3027 Mon Feb 01 11:12:13 0044 PST 3028(1 row) 3029 3030-- 3031-- Check handling of multiple spaces in format and/or input 3032-- 3033SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3034 to_timestamp 3035------------------------------ 3036 Sun Dec 18 23:38:15 2011 PST 3037(1 row) 3038 3039SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3040 to_timestamp 3041------------------------------ 3042 Sun Dec 18 23:38:15 2011 PST 3043(1 row) 3044 3045SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3046 to_timestamp 3047------------------------------ 3048 Sun Dec 18 23:38:15 2011 PST 3049(1 row) 3050 3051SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3052 to_timestamp 3053------------------------------ 3054 Sun Dec 18 23:38:15 2011 PST 3055(1 row) 3056 3057SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3058 to_timestamp 3059------------------------------ 3060 Sun Dec 18 23:38:15 2011 PST 3061(1 row) 3062 3063SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 3064 to_timestamp 3065------------------------------ 3066 Sun Dec 18 23:38:15 2011 PST 3067(1 row) 3068 3069SELECT to_timestamp('2000+ JUN', 'YYYY/MON'); 3070 to_timestamp 3071------------------------------ 3072 Thu Jun 01 00:00:00 2000 PDT 3073(1 row) 3074 3075SELECT to_timestamp(' 2000 +JUN', 'YYYY/MON'); 3076 to_timestamp 3077------------------------------ 3078 Thu Jun 01 00:00:00 2000 PDT 3079(1 row) 3080 3081SELECT to_timestamp(' 2000 +JUN', 'YYYY//MON'); 3082 to_timestamp 3083------------------------------ 3084 Thu Jun 01 00:00:00 2000 PDT 3085(1 row) 3086 3087SELECT to_timestamp('2000 +JUN', 'YYYY//MON'); 3088 to_timestamp 3089------------------------------ 3090 Thu Jun 01 00:00:00 2000 PDT 3091(1 row) 3092 3093SELECT to_timestamp('2000 + JUN', 'YYYY MON'); 3094 to_timestamp 3095------------------------------ 3096 Thu Jun 01 00:00:00 2000 PDT 3097(1 row) 3098 3099SELECT to_timestamp('2000 ++ JUN', 'YYYY MON'); 3100 to_timestamp 3101------------------------------ 3102 Thu Jun 01 00:00:00 2000 PDT 3103(1 row) 3104 3105SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 3106ERROR: invalid value "+" for "MON" 3107DETAIL: The given value did not match any of the allowed values for this field. 3108SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 3109 to_timestamp 3110------------------------------ 3111 Thu Jun 01 00:00:00 2000 PDT 3112(1 row) 3113 3114SELECT to_timestamp('2000 -10', 'YYYY TZH'); 3115 to_timestamp 3116------------------------------ 3117 Sat Jan 01 02:00:00 2000 PST 3118(1 row) 3119 3120SELECT to_timestamp('2000 -10', 'YYYY TZH'); 3121 to_timestamp 3122------------------------------ 3123 Fri Dec 31 06:00:00 1999 PST 3124(1 row) 3125 3126SELECT to_date('2011 12 18', 'YYYY MM DD'); 3127 to_date 3128------------ 3129 12-18-2011 3130(1 row) 3131 3132SELECT to_date('2011 12 18', 'YYYY MM DD'); 3133 to_date 3134------------ 3135 12-18-2011 3136(1 row) 3137 3138SELECT to_date('2011 12 18', 'YYYY MM DD'); 3139 to_date 3140------------ 3141 12-18-2011 3142(1 row) 3143 3144SELECT to_date('2011 12 18', 'YYYY MM DD'); 3145 to_date 3146------------ 3147 12-18-2011 3148(1 row) 3149 3150SELECT to_date('2011 12 18', 'YYYY MM DD'); 3151 to_date 3152------------ 3153 12-18-2011 3154(1 row) 3155 3156SELECT to_date('2011 12 18', 'YYYY MM DD'); 3157 to_date 3158------------ 3159 12-18-2011 3160(1 row) 3161 3162SELECT to_date('2011 12 18', 'YYYYxMMxDD'); 3163 to_date 3164------------ 3165 12-18-2011 3166(1 row) 3167 3168SELECT to_date('2011x 12x 18', 'YYYYxMMxDD'); 3169 to_date 3170------------ 3171 12-18-2011 3172(1 row) 3173 3174SELECT to_date('2011 x12 x18', 'YYYYxMMxDD'); 3175ERROR: invalid value "x1" for "MM" 3176DETAIL: Value must be an integer. 3177-- 3178-- Check errors for some incorrect usages of to_timestamp() and to_date() 3179-- 3180-- Mixture of date conventions (ISO week and Gregorian): 3181SELECT to_timestamp('2005527', 'YYYYIWID'); 3182ERROR: invalid combination of date conventions 3183HINT: Do not mix Gregorian and ISO week date conventions in a formatting template. 3184-- Insufficient characters in the source string: 3185SELECT to_timestamp('19971', 'YYYYMMDD'); 3186ERROR: source string too short for "MM" formatting field 3187DETAIL: Field requires 2 characters, but only 1 remain. 3188HINT: If your source string is not fixed-width, try using the "FM" modifier. 3189-- Insufficient digit characters for a single node: 3190SELECT to_timestamp('19971)24', 'YYYYMMDD'); 3191ERROR: invalid value "1)" for "MM" 3192DETAIL: Field requires 2 characters, but only 1 could be parsed. 3193HINT: If your source string is not fixed-width, try using the "FM" modifier. 3194-- We don't accept full-length day or month names if short form is specified: 3195SELECT to_timestamp('Friday 1-January-1999', 'DY DD MON YYYY'); 3196ERROR: invalid value "da" for "DD" 3197DETAIL: Value must be an integer. 3198SELECT to_timestamp('Fri 1-January-1999', 'DY DD MON YYYY'); 3199ERROR: invalid value "uary" for "YYYY" 3200DETAIL: Value must be an integer. 3201SELECT to_timestamp('Fri 1-Jan-1999', 'DY DD MON YYYY'); -- ok 3202 to_timestamp 3203------------------------------ 3204 Fri Jan 01 00:00:00 1999 PST 3205(1 row) 3206 3207-- Value clobbering: 3208SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD'); 3209ERROR: conflicting values for "Mon" field in formatting string 3210DETAIL: This value contradicts a previous setting for the same field type. 3211-- Non-numeric input: 3212SELECT to_timestamp('199711xy', 'YYYYMMDD'); 3213ERROR: invalid value "xy" for "DD" 3214DETAIL: Value must be an integer. 3215-- Input that doesn't fit in an int: 3216SELECT to_timestamp('10000000000', 'FMYYYY'); 3217ERROR: value for "YYYY" in source string is out of range 3218DETAIL: Value must be in the range -2147483648 to 2147483647. 3219-- Out-of-range and not-quite-out-of-range fields: 3220SELECT to_timestamp('2016-06-13 25:00:00', 'YYYY-MM-DD HH24:MI:SS'); 3221ERROR: date/time field value out of range: "2016-06-13 25:00:00" 3222SELECT to_timestamp('2016-06-13 15:60:00', 'YYYY-MM-DD HH24:MI:SS'); 3223ERROR: date/time field value out of range: "2016-06-13 15:60:00" 3224SELECT to_timestamp('2016-06-13 15:50:60', 'YYYY-MM-DD HH24:MI:SS'); 3225ERROR: date/time field value out of range: "2016-06-13 15:50:60" 3226SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 3227 to_timestamp 3228------------------------------ 3229 Mon Jun 13 15:50:55 2016 PDT 3230(1 row) 3231 3232SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH:MI:SS'); 3233ERROR: hour "15" is invalid for the 12-hour clock 3234HINT: Use the 24-hour clock, or give an hour between 1 and 12. 3235SELECT to_timestamp('2016-13-01 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 3236ERROR: date/time field value out of range: "2016-13-01 15:50:55" 3237SELECT to_timestamp('2016-02-30 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 3238ERROR: date/time field value out of range: "2016-02-30 15:50:55" 3239SELECT to_timestamp('2016-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 3240 to_timestamp 3241------------------------------ 3242 Mon Feb 29 15:50:55 2016 PST 3243(1 row) 3244 3245SELECT to_timestamp('2015-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 3246ERROR: date/time field value out of range: "2015-02-29 15:50:55" 3247SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSS'); -- ok 3248 to_timestamp 3249------------------------------ 3250 Wed Feb 11 23:53:20 2015 PST 3251(1 row) 3252 3253SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS'); 3254ERROR: date/time field value out of range: "2015-02-11 86400" 3255SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSSS'); -- ok 3256 to_timestamp 3257------------------------------ 3258 Wed Feb 11 23:53:20 2015 PST 3259(1 row) 3260 3261SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSSS'); 3262ERROR: date/time field value out of range: "2015-02-11 86400" 3263SELECT to_date('2016-13-10', 'YYYY-MM-DD'); 3264ERROR: date/time field value out of range: "2016-13-10" 3265SELECT to_date('2016-02-30', 'YYYY-MM-DD'); 3266ERROR: date/time field value out of range: "2016-02-30" 3267SELECT to_date('2016-02-29', 'YYYY-MM-DD'); -- ok 3268 to_date 3269------------ 3270 02-29-2016 3271(1 row) 3272 3273SELECT to_date('2015-02-29', 'YYYY-MM-DD'); 3274ERROR: date/time field value out of range: "2015-02-29" 3275SELECT to_date('2015 365', 'YYYY DDD'); -- ok 3276 to_date 3277------------ 3278 12-31-2015 3279(1 row) 3280 3281SELECT to_date('2015 366', 'YYYY DDD'); 3282ERROR: date/time field value out of range: "2015 366" 3283SELECT to_date('2016 365', 'YYYY DDD'); -- ok 3284 to_date 3285------------ 3286 12-30-2016 3287(1 row) 3288 3289SELECT to_date('2016 366', 'YYYY DDD'); -- ok 3290 to_date 3291------------ 3292 12-31-2016 3293(1 row) 3294 3295SELECT to_date('2016 367', 'YYYY DDD'); 3296ERROR: date/time field value out of range: "2016 367" 3297SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be 3298 to_date 3299--------------- 3300 02-01-0001 BC 3301(1 row) 3302 3303-- 3304-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572) 3305-- 3306SET TIME ZONE 'America/New_York'; 3307SET TIME ZONE '-1.5'; 3308SHOW TIME ZONE; 3309 TimeZone 3310---------------- 3311 <-01:30>+01:30 3312(1 row) 3313 3314SELECT '2012-12-12 12:00'::timestamptz; 3315 timestamptz 3316--------------------------------- 3317 Wed Dec 12 12:00:00 2012 -01:30 3318(1 row) 3319 3320SELECT '2012-12-12 12:00 America/New_York'::timestamptz; 3321 timestamptz 3322--------------------------------- 3323 Wed Dec 12 15:30:00 2012 -01:30 3324(1 row) 3325 3326SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); 3327 to_char 3328---------------------------- 3329 2012-12-12 12:00:00 -01:30 3330(1 row) 3331 3332SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS'); 3333 to_char 3334------------------ 3335 2012-12-12 43200 3336(1 row) 3337 3338SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS'); 3339 to_char 3340------------------ 3341 2012-12-12 43200 3342(1 row) 3343 3344RESET TIME ZONE; 3345