github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/timetz (about) 1 # Note that the odd '0000-01-01 hh:mi:ss +0000 +0000' result format is an 2 # artifact of how pq displays TIMETZs. 3 # 4 # We sometimes cast timetz to strings here to avoid confusion with lib/pq 5 # printing out timetz values with dates. 6 7 statement ok 8 CREATE TABLE timetz_test (a TIMETZ PRIMARY KEY, b TIMETZ, c INT) 9 10 statement ok 11 INSERT INTO timetz_test VALUES 12 ('11:00:00+0', '12:00:00+1', 1), 13 ('12:00:00+1', '10:00:00-1', 2), 14 ('13:00:00+2', '15:00:00-6', 3), 15 ('14:00:00+3', '24:00:00-1559', 4), 16 ('15:00:00+3', '15:00:00+3', 5) 17 18 # NOTE: lib/pq parses '24:00:00-1559' incorrectly, but the data 19 # structure underneath should still be correct. 20 query B 21 SELECT '24:00:00-1559'::timetz > '23:59:59-1559'::timetz; 22 ---- 23 true 24 25 query T 26 select 27 extract('hour' from '11:12+03:01'::timetz)::string || ':' || 28 extract('minute' from '11:12+03:01'::timetz)::string || 'Z' || 29 extract('timezone_hour' from '11:12+03:01'::timetz)::string || ':' || 30 extract('timezone_minute' from '11:12+03:01'::timetz)::string 31 ---- 32 11:12Z3:1 33 34 query T 35 select 36 extract('hour' from '11:12-03:01'::timetz)::string || ':' || 37 extract('minute' from '11:12-03:01'::timetz)::string || 'Z' || 38 extract('timezone_hour' from '11:12-03:01'::timetz)::string || ':' || 39 extract('timezone_minute' from '11:12-03:01'::timetz)::string 40 ---- 41 11:12Z-3:-1 42 43 query TTI 44 SELECT a::string, b::string, c FROM timetz_test ORDER BY a, c ASC 45 ---- 46 11:00:00+00:00:00 12:00:00+01:00:00 1 47 12:00:00+01:00:00 10:00:00-01:00:00 2 48 13:00:00+02:00:00 15:00:00-06:00:00 3 49 14:00:00+03:00:00 24:00:00-15:59:00 4 50 15:00:00+03:00:00 15:00:00+03:00:00 5 51 52 query TTI 53 SELECT a::string, b::string, c FROM timetz_test ORDER BY a DESC, c ASC 54 ---- 55 15:00:00+03:00:00 15:00:00+03:00:00 5 56 14:00:00+03:00:00 24:00:00-15:59:00 4 57 13:00:00+02:00:00 15:00:00-06:00:00 3 58 12:00:00+01:00:00 10:00:00-01:00:00 2 59 11:00:00+00:00:00 12:00:00+01:00:00 1 60 61 query TTI 62 SELECT a::string, b::string, c FROM timetz_test WHERE a > b ORDER BY c 63 ---- 64 11:00:00+00:00:00 12:00:00+01:00:00 1 65 66 query TTI 67 SELECT a::string, b::string, c FROM timetz_test WHERE a = b ORDER BY c 68 ---- 69 15:00:00+03:00:00 15:00:00+03:00:00 5 70 71 # Test various casts involving different timezones. 72 subtest cast_tests 73 74 statement ok 75 SET TIME ZONE -5 76 77 query T 78 SELECT '11:00+03:00'::timetz::time 79 ---- 80 0000-01-01 11:00:00 +0000 UTC 81 82 # This should take the timezone in the background. 83 query T 84 SELECT '11:00'::time::timetz 85 ---- 86 0000-01-01 11:00:00 -0500 -0500 87 88 # This should observe the time and zone from the timestamp. 89 query T 90 SELECT '2001-01-01 11:00+04:00'::timestamptz::timetz 91 ---- 92 0000-01-01 02:00:00 -0500 -0500 93 94 statement ok 95 SET TIME ZONE UTC 96 97 subtest current_time_tests 98 99 # current_time cannot be subtracted, but can as a timestamp. 100 query B 101 select 102 ('1970-01-01 ' || current_time(3)::string)::timestamp - 103 ('1970-01-01 ' || current_time::string)::timestamp 104 <= '1ms'::interval 105 ---- 106 true 107 108 statement ok 109 CREATE TABLE current_time_test ( 110 id INTEGER PRIMARY KEY, 111 a TIMETZ(3) DEFAULT CURRENT_TIME, 112 b TIMETZ DEFAULT CURRENT_TIME 113 ) 114 115 statement ok 116 INSERT INTO current_time_test (id) VALUES (1) 117 118 statement ok 119 INSERT INTO current_time_test (id, a, b) VALUES 120 (2, current_time, current_time), 121 (3, current_time, current_time(3)) 122 123 query I 124 SELECT id FROM current_time_test WHERE 125 ('1970-01-01 ' || b::string)::timestamp - 126 ('1970-01-01 ' || a::string)::timestamp 127 > '1ms'::interval ORDER BY id ASC 128 ---- 129 130 # switching timezones should make current_time() change timezones too. 131 statement ok 132 set time zone +4 133 134 query B 135 select current_time() + current_timestamp()::date = current_timestamp() 136 ---- 137 true 138 139 statement ok 140 set time zone UTC 141 142 subtest precision_tests 143 144 query error precision 7 out of range 145 select '1:00:00.001':::TIMETZ(7) 146 147 statement ok 148 CREATE TABLE timetz_precision_test ( 149 id integer PRIMARY KEY, 150 t TIMETZ(5) 151 ) 152 153 statement ok 154 INSERT INTO timetz_precision_test VALUES 155 (1,'12:00:00.123456+03:00'), 156 (2,'12:00:00.12345+03:00'), 157 (3,'12:00:00.1234+03:00'), 158 (4,'12:00:00.123+03:00'), 159 (5,'12:00:00.12+03:00'), 160 (6,'12:00:00.1+03:00'), 161 (7,'12:00:00+03:00') 162 163 query IT 164 SELECT * FROM timetz_precision_test ORDER BY id ASC 165 ---- 166 1 0000-01-01 12:00:00.12346 +0300 +0300 167 2 0000-01-01 12:00:00.12345 +0300 +0300 168 3 0000-01-01 12:00:00.1234 +0300 +0300 169 4 0000-01-01 12:00:00.123 +0300 +0300 170 5 0000-01-01 12:00:00.12 +0300 +0300 171 6 0000-01-01 12:00:00.1 +0300 +0300 172 7 0000-01-01 12:00:00 +0300 +0300 173 174 query TT 175 select column_name, data_type FROM [SHOW COLUMNS FROM timetz_precision_test] ORDER BY column_name 176 ---- 177 id INT8 178 t TIMETZ(5) 179 180 statement ok 181 ALTER TABLE timetz_precision_test ALTER COLUMN t TYPE timetz(6) 182 183 statement ok 184 INSERT INTO timetz_precision_test VALUES 185 (100,'12:00:00.123456+03:00') 186 187 query IT 188 SELECT * FROM timetz_precision_test ORDER BY id ASC 189 ---- 190 1 0000-01-01 12:00:00.12346 +0300 +0300 191 2 0000-01-01 12:00:00.12345 +0300 +0300 192 3 0000-01-01 12:00:00.1234 +0300 +0300 193 4 0000-01-01 12:00:00.123 +0300 +0300 194 5 0000-01-01 12:00:00.12 +0300 +0300 195 6 0000-01-01 12:00:00.1 +0300 +0300 196 7 0000-01-01 12:00:00 +0300 +0300 197 100 0000-01-01 12:00:00.123456 +0300 +0300 198 199 query TT 200 select column_name, data_type FROM [SHOW COLUMNS FROM timetz_precision_test] ORDER BY column_name 201 ---- 202 id INT8 203 t TIMETZ(6) 204 205 subtest regression_42749 206 207 # cast to string to prove it is 24:00 208 query T 209 SELECT '0000-01-01 24:00:00'::timetz::string 210 ---- 211 24:00:00+00:00:00 212 213 query T 214 SELECT '2001-01-01 01:24:00+3'::timetz 215 ---- 216 0000-01-01 01:24:00 +0300 +0300 217 218 # Test various evals using different timezones. 219 subtest eval_test 220 221 statement ok 222 SET TIME ZONE -3 223 224 query B 225 SELECT '11:00+5'::timetz = '11:00+5'::timetz 226 ---- 227 true 228 229 query B 230 SELECT '11:00-3'::timetz = '11:00'::time 231 ---- 232 true 233 234 query B 235 SELECT '11:00-2'::timetz < '11:00'::time 236 ---- 237 true 238 239 statement ok 240 SET TIME ZONE UTC 241 242 # These are the old tests that were in the old iteration. Included for completeness. 243 subtest windchan7_tests 244 245 query T 246 SELECT '12:00:00':::TIMETZ; 247 ---- 248 0000-01-01 12:00:00 +0000 UTC 249 250 query T 251 SELECT '12:00:00.456':::TIMETZ; 252 ---- 253 0000-01-01 12:00:00.456 +0000 UTC 254 255 query T 256 SELECT '12:00:00.456789':::TIMETZ; 257 ---- 258 0000-01-01 12:00:00.456789 +0000 UTC 259 260 query T 261 SELECT '12:00:00.456789+00':::TIMETZ; 262 ---- 263 0000-01-01 12:00:00.456789 +0000 UTC 264 265 query T 266 SELECT '12:00:00.456789-07':::TIMETZ; 267 ---- 268 0000-01-01 12:00:00.456789 -0700 -0700 269 270 query T 271 SELECT '23:59:59.999999-10':::TIMETZ; 272 ---- 273 0000-01-01 23:59:59.999999 -1000 -1000 274 275 query T 276 SELECT '24:00:00':::TIMETZ; 277 ---- 278 0000-01-02 00:00:00 +0000 UTC 279 280 query T 281 SELECT TIMETZ '12:00:00-07'; 282 ---- 283 0000-01-01 12:00:00 -0700 -0700 284 285 # Casting 286 287 query T 288 SELECT '12:00:00-07'::TIMETZ; 289 ---- 290 0000-01-01 12:00:00 -0700 -0700 291 292 query T 293 select '12:00:00-07':::STRING::TIMETZ; 294 ---- 295 0000-01-01 12:00:00 -0700 -0700 296 297 query T 298 select '22:00:00-07':::STRING::TIMETZ; 299 ---- 300 0000-01-01 22:00:00 -0700 -0700 301 302 query T 303 SELECT '09:00:00.456-07' COLLATE de::TIMETZ; 304 ---- 305 0000-01-01 09:00:00.456 -0700 -0700 306 307 query T 308 SELECT '2017-01-01 12:00:00-07':::TIMESTAMPTZ::TIMETZ; 309 ---- 310 0000-01-01 19:00:00 +0000 UTC 311 312 query T 313 SELECT '12:00:00-07':::TIME::TIMETZ; 314 ---- 315 0000-01-01 12:00:00 +0000 UTC 316 317 query T 318 select '12:00:00-07:00'::TIMETZ::STRING; 319 ---- 320 12:00:00-07:00:00 321 322 query T 323 select '11:00:00-07:00'::TIMETZ::TIME; 324 ---- 325 0000-01-01 11:00:00 +0000 UTC 326 327 query T 328 select '11:00:00-07:00'::TIMETZ::TIMETZ; 329 ---- 330 0000-01-01 11:00:00 -0700 -0700 331 332 # Comparison 333 334 query B 335 select '12:00:00+00':::TIMETZ = '12:00:00+00':::TIMETZ 336 ---- 337 true 338 339 query B 340 select '12:00:00-06':::TIMETZ = '12:00:00-07':::TIMETZ 341 ---- 342 false 343 344 query B 345 select '12:00:00+00':::TIMETZ >= '12:00:00+00':::TIMETZ 346 ---- 347 true 348 349 query B 350 select '12:00:00+00':::TIMETZ <= '12:00:00+00':::TIMETZ 351 ---- 352 true 353 354 query B 355 SELECT '12:00:00+01:00':::TIMETZ < '11:59:59.999999+00':::TIMETZ 356 ---- 357 true 358 359 query B 360 SELECT '12:00:00+01:00':::TIMETZ < '11:59:59.999999+02':::TIMETZ 361 ---- 362 false 363 364 query B 365 SELECT '12:00:00+01:00':::TIMETZ > '11:59:59.999999+02':::TIMETZ 366 ---- 367 true 368 369 query B 370 SELECT '23:00:01-01:00':::TIMETZ > '00:00:01+00:00':::TIMETZ 371 ---- 372 true 373 374 query B 375 SELECT '23:00:01-06:00':::TIMETZ > '00:00:01-04:00':::TIMETZ 376 ---- 377 true 378 379 query B 380 SELECT '07:00:01-06:00':::TIMETZ > '23:00:01-04:00':::TIMETZ 381 ---- 382 false 383 384 query B 385 SELECT '12:00:00-05':::TIMETZ IN ('12:00:00'); 386 ---- 387 false 388 389 query B 390 SELECT '12:00:00-05':::TIMETZ IN ('12:00:00-05'); 391 ---- 392 true 393 394 query B 395 SELECT '12:00:00-05':::TIMETZ IN ('12:00:00-07'); 396 ---- 397 false 398 399 query B 400 SELECT '12:00:00-05':::TIMETZ IN ('11:00:00-06'); 401 ---- 402 false 403 404 # Arithmetic 405 406 query T 407 SELECT '12:00:00-01':::TIMETZ + '1s':::INTERVAL 408 ---- 409 0000-01-01 12:00:01 -0100 -0100 410 411 query T 412 SELECT '23:59:59+00':::TIMETZ + '1s':::INTERVAL 413 ---- 414 0000-01-01 00:00:00 +0000 UTC 415 416 query T 417 SELECT '23:59:59+00':::TIMETZ + '4m':::INTERVAL 418 ---- 419 0000-01-01 00:03:59 +0000 UTC 420 421 query T 422 SELECT '12:00:00-07':::TIMETZ + '1d':::INTERVAL 423 ---- 424 0000-01-01 12:00:00 -0700 -0700 425 426 query T 427 SELECT '1s':::INTERVAL + '12:00:00+03':::TIMETZ 428 ---- 429 0000-01-01 12:00:01 +0300 +0300 430 431 query T 432 SELECT '12:00:00-07':::TIMETZ - '1s':::INTERVAL 433 ---- 434 0000-01-01 11:59:59 -0700 -0700 435 436 query T 437 SELECT '12:00:00-07':::TIMETZ - '1d':::INTERVAL 438 ---- 439 0000-01-01 12:00:00 -0700 -0700 440 441 query T 442 SELECT '01:00:00-07':::TIMETZ - '9h':::INTERVAL 443 ---- 444 0000-01-01 16:00:00 -0700 -0700 445 446 query T 447 SELECT '2017-01-01':::DATE + '12:00:00-03':::TIMETZ 448 ---- 449 2017-01-01 15:00:00 +0000 UTC 450 451 query T 452 SELECT '12:00:00+03':::TIMETZ + '2017-01-01':::DATE 453 ---- 454 2017-01-01 09:00:00 +0000 UTC 455 456 # Storage 457 458 statement ok 459 CREATE TABLE timetzs (t timetz PRIMARY KEY) 460 461 statement ok 462 INSERT INTO timetzs VALUES 463 ('00:00:00-07'), 464 ('00:00:00.000001+06'), 465 ('11:59:59.999999+10'), 466 ('12:00:00-05'), 467 ('12:00:00.000001-05'), 468 ('23:59:59.999999+00') 469 470 query T 471 SELECT * FROM timetzs ORDER BY t 472 ---- 473 0000-01-01 00:00:00.000001 +0600 +0600 474 0000-01-01 11:59:59.999999 +1000 +1000 475 0000-01-01 00:00:00 -0700 -0700 476 0000-01-01 12:00:00 -0500 -0500 477 0000-01-01 12:00:00.000001 -0500 -0500 478 0000-01-01 23:59:59.999999 +0000 UTC 479 480 statement ok 481 CREATE TABLE tzarrays (timetzs TIMETZ[]) 482 483 statement ok 484 INSERT INTO tzarrays VALUES 485 (ARRAY[]), 486 (ARRAY['00:00:00-07']), 487 (ARRAY['00:00:00-07', '12:00:00.000001-07']), 488 ('{13:00:00-07}'::TIMETZ[]) 489 490 query T rowsort 491 SELECT * FROM tzarrays 492 ---- 493 {} 494 {00:00:00-07:00:00} 495 {00:00:00-07:00:00,12:00:00.000001-07:00:00} 496 {13:00:00-07:00:00} 497 498 # Built-ins 499 500 query R 501 SELECT extract(hour from timetz '12:01:02.345678-07') 502 ---- 503 12 504 505 query R 506 SELECT extract(minute from timetz '12:01:02.345678+03') 507 ---- 508 1 509 510 query R 511 SELECT extract(second from timetz '12:01:02.345678-06') 512 ---- 513 2.345678 514 515 query R 516 SELECT extract(millisecond from timetz '12:01:02.345678+00') 517 ---- 518 2345.678 519 520 query R 521 SELECT extract(microsecond from timetz '12:01:02.345678-05') 522 ---- 523 2.345678e+06 524 525 query R 526 SELECT extract(epoch from timetz '12:00:00+04') 527 ---- 528 28800 529 530 # Adapted from `src/test/regress/expected/timetz.out` in postgres 531 subtest regress_postgres 532 533 statement ok 534 CREATE TABLE TIMETZ_TBL (id serial primary key, f1 time(2) with time zone) 535 536 # Changed PDT/PST/EDT -> zone offsets, as pgdate does not support abbreviations. 537 statement ok 538 INSERT INTO TIMETZ_TBL (f1) VALUES ('00:01-07') 539 540 statement ok 541 INSERT INTO TIMETZ_TBL (f1) VALUES ('01:00-07') 542 543 statement ok 544 INSERT INTO TIMETZ_TBL (f1) VALUES ('02:03-07') 545 546 statement ok 547 INSERT INTO TIMETZ_TBL (f1) VALUES ('07:07-05') 548 549 statement ok 550 INSERT INTO TIMETZ_TBL (f1) VALUES ('08:08-04') 551 552 statement ok 553 INSERT INTO TIMETZ_TBL (f1) VALUES ('11:59-07') 554 555 statement ok 556 INSERT INTO TIMETZ_TBL (f1) VALUES ('12:00-07') 557 558 statement ok 559 INSERT INTO TIMETZ_TBL (f1) VALUES ('12:01-07') 560 561 statement ok 562 INSERT INTO TIMETZ_TBL (f1) VALUES ('23:59-07') 563 564 statement ok 565 INSERT INTO TIMETZ_TBL (f1) VALUES ('11:59:59.99 PM-07') 566 567 statement ok 568 INSERT INTO TIMETZ_TBL (f1) VALUES ('2003-03-07 15:36:39 America/New_York') 569 570 statement ok 571 INSERT INTO TIMETZ_TBL (f1) VALUES ('2003-07-07 15:36:39 America/New_York') 572 573 # pgdate supports this, but postgres does not. 574 # INSERT INTO TIMETZ_TBL (f1) VALUES ('15:36:39 America/New_York') 575 576 # this should fail (timezone not specified without a date) 577 query error could not parse "1970-01-01 15:36:39 m2" as TimeTZ 578 INSERT INTO TIMETZ_TBL (f1) VALUES ('15:36:39 m2') 579 580 # this should fail (dynamic timezone abbreviation without a date) 581 query error could not parse "1970-01-01 15:36:39 MSK m2" as TimeTZ 582 INSERT INTO TIMETZ_TBL (f1) VALUES ('15:36:39 MSK m2') 583 584 query T 585 SELECT f1::string AS "Time TZ" FROM TIMETZ_TBL ORDER BY id 586 ---- 587 00:01:00-07:00:00 588 01:00:00-07:00:00 589 02:03:00-07:00:00 590 07:07:00-05:00:00 591 08:08:00-04:00:00 592 11:59:00-07:00:00 593 12:00:00-07:00:00 594 12:01:00-07:00:00 595 23:59:00-07:00:00 596 23:59:59.99-07:00:00 597 15:36:39-05:00:00 598 15:36:39-04:00:00 599 600 query T 601 SELECT f1::string AS "Three" FROM TIMETZ_TBL WHERE f1 < '05:06:07-07' ORDER BY id 602 ---- 603 00:01:00-07:00:00 604 01:00:00-07:00:00 605 02:03:00-07:00:00 606 607 query T 608 SELECT f1::string AS "Seven" FROM TIMETZ_TBL WHERE f1 > '05:06:07-07' ORDER BY id 609 ---- 610 07:07:00-05:00:00 611 08:08:00-04:00:00 612 11:59:00-07:00:00 613 12:00:00-07:00:00 614 12:01:00-07:00:00 615 23:59:00-07:00:00 616 23:59:59.99-07:00:00 617 15:36:39-05:00:00 618 15:36:39-04:00:00 619 620 query T 621 SELECT f1::string AS "None" FROM TIMETZ_TBL WHERE f1 < '00:00-07' ORDER BY id 622 ---- 623 624 query T 625 SELECT f1::string AS "Ten" FROM TIMETZ_TBL WHERE f1 >= '00:00-07' ORDER BY id 626 ---- 627 00:01:00-07:00:00 628 01:00:00-07:00:00 629 02:03:00-07:00:00 630 07:07:00-05:00:00 631 08:08:00-04:00:00 632 11:59:00-07:00:00 633 12:00:00-07:00:00 634 12:01:00-07:00:00 635 23:59:00-07:00:00 636 23:59:59.99-07:00:00 637 15:36:39-05:00:00 638 15:36:39-04:00:00 639 640 query error pq: unsupported binary operator: <timetz\(2\)> \+ <timetz> 641 SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL ORDER BY id 642 643 # check default types and expressions get truncated on insert / update. 644 subtest regression_44774 645 646 statement ok 647 CREATE TABLE regression_44774 ( 648 a timetz(3) DEFAULT '12:13:14.123456' 649 ) 650 651 statement ok 652 INSERT INTO regression_44774 VALUES (default), ('19:21:57.261286') 653 654 query T 655 SELECT a FROM regression_44774 ORDER BY a 656 ---- 657 0000-01-01 12:13:14.123 +0000 UTC 658 0000-01-01 19:21:57.261 +0000 UTC 659 660 statement ok 661 UPDATE regression_44774 662 SET a = '13:14:15.123456'::timetz + '1 sec'::interval 663 WHERE 1 = 1 664 665 query T 666 SELECT a FROM regression_44774 ORDER BY a 667 ---- 668 0000-01-01 13:14:16.123 +0000 UTC 669 0000-01-01 13:14:16.123 +0000 UTC 670 671 statement ok 672 DROP TABLE regression_44774