github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/datetime (about) 1 statement ok 2 CREATE TABLE t ( 3 a TIMESTAMP PRIMARY KEY, 4 b DATE, 5 c INTERVAL, 6 UNIQUE (b), 7 UNIQUE (c), 8 FAMILY (a), 9 FAMILY (b), 10 FAMILY (c) 11 ) 12 13 statement ok 14 INSERT INTO t VALUES 15 ('2015-08-30 03:34:45.34567', '2015-08-30', '34h2s'), 16 ('2015-08-25 04:45:45.53453', '2015-08-25', '2h45m2s234ms'), 17 ('2015-08-29 23:10:09.98763', '2015-08-29', '234h45m2s234ms') 18 19 # Spot-check date math. 20 query T 21 SELECT b + '6 month' from t order by a desc 22 ---- 23 2016-02-29 00:00:00 +0000 +0000 24 2016-02-29 00:00:00 +0000 +0000 25 2016-02-25 00:00:00 +0000 +0000 26 27 query TTT 28 SELECT * FROM t WHERE a = '2015-08-25 04:45:45.53453+01:00'::timestamp 29 ---- 30 2015-08-25 04:45:45.53453 +0000 +0000 2015-08-25 00:00:00 +0000 +0000 02:45:02.234 31 32 # insert duplicate value with different time zone offset 33 statement error duplicate key value \(a\)=\('2015-08-30 03:34:45\.34567\+00:00'\) violates unique constraint "primary" 34 INSERT INTO t VALUES 35 ('2015-08-30 03:34:45.34567-07:00', '2015-08-31', '35h2s') 36 37 # Check that time/date/interval representations outside of the index are okay. 38 statement ok 39 CREATE TABLE u ( 40 a BIGINT PRIMARY KEY, 41 b TIMESTAMP, 42 c TIMESTAMPTZ, 43 d DATE, 44 e INTERVAL 45 ) 46 47 statement ok 48 INSERT INTO u VALUES 49 (123, '2015-08-30 03:34:45.34567', '2015-08-30 03:34:45.34567', '2015-08-30', '34h2s'), 50 (234, '2015-08-25 04:45:45.53453-02:00', '2015-08-25 04:45:45.53453-02:00', '2015-08-25', '2h45m2s234ms') 51 52 statement ok 53 SET TIME ZONE -5 54 55 query TTT 56 SELECT DATE '2000-01-01', DATE '2000-12-31', DATE '1993-05-16' 57 ---- 58 2000-01-01 00:00:00 +0000 +0000 2000-12-31 00:00:00 +0000 +0000 1993-05-16 00:00:00 +0000 +0000 59 60 statement ok 61 INSERT INTO u VALUES 62 (345, '2015-08-29 23:10:09.98763', '2015-08-29 23:10:09.98763', '2015-08-29', '234h45m2s234ms'), 63 (456, '2015-08-29 23:10:09.98763 UTC', '2015-08-29 23:10:09.98763 UTC', '2015-08-29', '234h45m2s234ms') 64 65 query ITTTT 66 SELECT * FROM u ORDER BY a 67 ---- 68 123 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-29 22:34:45.34567 -0500 -0500 2015-08-30 00:00:00 +0000 +0000 34:00:02 69 234 2015-08-25 04:45:45.53453 +0000 +0000 2015-08-25 01:45:45.53453 -0500 -0500 2015-08-25 00:00:00 +0000 +0000 02:45:02.234 70 345 2015-08-29 23:10:09.98763 +0000 +0000 2015-08-29 23:10:09.98763 -0500 -0500 2015-08-29 00:00:00 +0000 +0000 234:45:02.234 71 456 2015-08-29 23:10:09.98763 +0000 +0000 2015-08-29 18:10:09.98763 -0500 -0500 2015-08-29 00:00:00 +0000 +0000 234:45:02.234 72 73 statement ok 74 SET TIME ZONE UTC 75 76 query ITTTT 77 SELECT * FROM u ORDER BY a 78 ---- 79 123 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-30 03:34:45.34567 +0000 UTC 2015-08-30 00:00:00 +0000 +0000 34:00:02 80 234 2015-08-25 04:45:45.53453 +0000 +0000 2015-08-25 06:45:45.53453 +0000 UTC 2015-08-25 00:00:00 +0000 +0000 02:45:02.234 81 345 2015-08-29 23:10:09.98763 +0000 +0000 2015-08-30 04:10:09.98763 +0000 UTC 2015-08-29 00:00:00 +0000 +0000 234:45:02.234 82 456 2015-08-29 23:10:09.98763 +0000 +0000 2015-08-29 23:10:09.98763 +0000 UTC 2015-08-29 00:00:00 +0000 +0000 234:45:02.234 83 84 statement ok 85 SET TIME ZONE -5 86 87 query TTTT 88 SELECT max(b), max(c), max(d), max(e) FROM u 89 ---- 90 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-29 23:10:09.98763 -0500 -0500 2015-08-30 00:00:00 +0000 +0000 234:45:02.234 91 92 query TTTT 93 SELECT min(b), min(c), min(d), min(e) FROM u 94 ---- 95 2015-08-25 04:45:45.53453 +0000 +0000 2015-08-25 01:45:45.53453 -0500 -0500 2015-08-25 00:00:00 +0000 +0000 02:45:02.234 96 97 query BB 98 SELECT now() < now() + '1m'::interval, now() <= now() + '1m'::interval 99 ---- 100 true true 101 102 query BB 103 SELECT now() + '1m'::interval > now(), now() + '1m'::interval >= now() 104 ---- 105 true true 106 107 # Date sentinel values. 108 109 query TTT 110 SELECT 'epoch'::date, 'infinity'::date, '-infinity'::date 111 ---- 112 1970-01-01 00:00:00 +0000 +0000 infinity -infinity 113 114 # Date edge cases. 115 116 statement error year value 0 is out of range 117 SELECT '0000-01-01'::date 118 119 query TTTTT 120 SELECT '4714-11-24 BC'::date, '5874897-12-31'::date, '2000-01-01'::date, '0001-01-01'::date, '0001-12-13 BC'::date 121 ---- 122 -4713-11-24 00:00:00 +0000 +0000 5874897-12-31 00:00:00 +0000 +0000 2000-01-01 00:00:00 +0000 +0000 0001-01-01 00:00:00 +0000 +0000 0000-12-13 00:00:00 +0000 +0000 123 124 # Also test as strings because lib/pq marshals the previous results to 125 # time.Times, which don't stringify the same. 126 query TTTTT 127 SELECT '4714-11-24 BC'::date::string, '5874897-12-31'::date::string, '2000-01-01'::date::string, '0001-01-01'::date::string, '0001-12-13 BC'::date::string 128 ---- 129 4714-11-24 BC 5874897-12-31 2000-01-01 0001-01-01 0001-12-13 BC 130 131 statement error date is out of range 132 SELECT '4714-11-24 BC'::date - 1 133 134 statement error date is out of range 135 SELECT '5874897-12-31'::date + 1 136 137 query TT 138 SELECT ('4714-11-24 BC'::date + 1)::string, ('5874897-12-31'::date - 1)::string 139 ---- 140 4714-11-25 BC 5874897-12-30 141 142 query TTTT 143 SELECT 'infinity'::date + 1, 'infinity'::date - 1, '-infinity'::date + 1, '-infinity'::date - 1 144 ---- 145 infinity infinity -infinity -infinity 146 147 statement error cannot subtract infinite dates 148 SELECT 'infinity'::date - 'infinity'::date 149 150 query I 151 SELECT '5874897-12-31'::date - '4714-11-24 BC'::date 152 ---- 153 2147483493 154 155 # TIMESTAMP/DATE builtins. 156 157 query T 158 SELECT age('2001-04-10 22:06:45', '1957-06-13') 159 ---- 160 384190:06:45 161 162 query B 163 SELECT age('1957-06-13') - age(now(), '1957-06-13') = interval '0s' 164 ---- 165 true 166 167 query B 168 SELECT now() - timestamp '2015-06-13' > interval '100h' 169 ---- 170 true 171 172 query TT 173 SELECT now()::timestamp - now(), now() - now()::timestamp 174 ---- 175 00:00:00 00:00:00 176 177 query BB 178 SELECT now() = now()::timestamp, now()::timestamp = now() 179 ---- 180 true true 181 182 query BB 183 SELECT now()::timestamp < now(), now() < now()::timestamp 184 ---- 185 false false 186 187 query BB 188 SELECT now()::timestamp <= now(), now() <= now()::timestamp 189 ---- 190 true true 191 192 query B 193 SELECT current_date - current_date() = 0 194 ---- 195 true 196 197 query B 198 SELECT now() - current_timestamp() = interval '0s' 199 ---- 200 true 201 202 query B 203 SELECT now() - current_timestamp = interval '0s' 204 ---- 205 true 206 207 query B 208 SELECT now() - statement_timestamp() < interval '10s' 209 ---- 210 true 211 212 query B 213 SELECT clock_timestamp() - statement_timestamp() < interval '10s' 214 ---- 215 true 216 217 query B 218 SELECT now() - transaction_timestamp() = interval '0s' 219 ---- 220 true 221 222 statement ok 223 BEGIN TRANSACTION 224 225 statement ok 226 CREATE TABLE kv ( 227 k CHAR PRIMARY KEY, 228 v TIMESTAMPTZ 229 ) 230 231 statement ok 232 INSERT INTO kv (k,v) VALUES ('a', transaction_timestamp()) 233 234 query T 235 SELECT k FROM kv 236 ---- 237 a 238 239 query T 240 SELECT k FROM kv where v = transaction_timestamp() 241 ---- 242 a 243 244 statement ok 245 COMMIT TRANSACTION 246 247 # Changing timezones changes the output of current_date(). 248 249 statement ok 250 RESET TIME ZONE 251 252 query BBB 253 SELECT 254 d = tz, d = t, d = n 255 FROM 256 ( 257 SELECT 258 current_date()::DATE AS d, 259 current_date()::TIMESTAMPTZ::DATE AS tz, 260 current_date()::TIMESTAMP::DATE AS t, 261 now():::DATE AS n 262 ) 263 ---- 264 true true true 265 266 query B 267 SELECT now() - current_date()::timestamptz < interval '24h10s' 268 ---- 269 true 270 271 statement ok 272 SET TIME ZONE 48 273 274 query B 275 SELECT now() - current_date()::timestamptz < interval '24h10s' 276 ---- 277 true 278 279 query BBB 280 SELECT 281 d = tz, d = t, d = n 282 FROM 283 ( 284 SELECT 285 current_date()::DATE AS d, 286 current_date()::TIMESTAMPTZ::DATE AS tz, 287 current_date()::TIMESTAMP::DATE AS t, 288 now():::DATE AS n 289 ) 290 ---- 291 true true true 292 293 statement ok 294 RESET TIME ZONE 295 296 # Check that the current_timestamp, now and transaction_timestamp are the same. 297 # Test that the transaction_timestamp can differ from the statement_timestamp. 298 # Check that the transaction_timestamp changes with each transaction. 299 # We use, SELECT * FROM kv, to insert delays of more than a microsecond. 300 statement ok 301 BEGIN; 302 INSERT INTO kv (k,v) VALUES ('b', transaction_timestamp()); 303 SELECT * FROM kv; 304 INSERT INTO kv (k,v) VALUES ('c', transaction_timestamp()); 305 SELECT * FROM kv; 306 INSERT INTO kv (k,v) VALUES ('d', current_timestamp()); 307 SELECT * FROM kv; 308 INSERT INTO kv (k,v) VALUES ('e', current_timestamp()); 309 SELECT * FROM kv; 310 INSERT INTO kv (k,v) VALUES ('f', now()); 311 SELECT * FROM kv; 312 INSERT INTO kv (k,v) VALUES ('g', now()); 313 SELECT * FROM kv; 314 INSERT INTO kv (k,v) VALUES ('h', statement_timestamp()); 315 SELECT * FROM kv; 316 COMMIT; 317 SELECT * FROM kv; 318 BEGIN; 319 SELECT * FROM KV; 320 INSERT INTO kv (k,v) VALUES ('i', transaction_timestamp()); 321 COMMIT 322 323 query I 324 SELECT count(DISTINCT (v)) FROM kv 325 ---- 326 4 327 328 # Test that transaction_timestamp() is consistent in transaction 329 # spanning multiple batches of statements. 330 statement ok 331 DELETE FROM kv 332 333 statement ok 334 BEGIN; 335 INSERT INTO kv (k,v) VALUES ('a', transaction_timestamp()); 336 SELECT * FROM kv 337 338 statement ok 339 INSERT INTO kv (k,v) VALUES ('b', transaction_timestamp()); 340 SELECT * FROM kv; 341 COMMIT 342 343 statement ok 344 BEGIN; 345 SELECT * FROM KV; 346 INSERT INTO kv (k,v) VALUES ('c', transaction_timestamp()); 347 COMMIT 348 349 query I 350 SELECT count(DISTINCT (v)) FROM kv 351 ---- 352 2 353 354 statement ok 355 DROP TABLE kv 356 357 statement ok 358 CREATE TABLE kv ( 359 k INT PRIMARY KEY, 360 v DECIMAL 361 ) 362 363 # Test that cluster_logical_timestamp() is consistent in transactions 364 # spanning multiple batches of statements. 365 statement ok 366 BEGIN; 367 INSERT INTO kv (k,v) VALUES (1, cluster_logical_timestamp()); 368 SELECT * FROM kv 369 370 statement ok 371 INSERT INTO kv (k,v) VALUES (2, cluster_logical_timestamp()); 372 SELECT * FROM kv; 373 COMMIT 374 375 statement ok 376 BEGIN; 377 SELECT * FROM kv; 378 INSERT INTO kv (k,v) VALUES (3, cluster_logical_timestamp()); 379 COMMIT 380 381 query I 382 SELECT count(DISTINCT (v)) FROM kv 383 ---- 384 2 385 386 statement ok 387 DELETE FROM kv 388 389 statement ok 390 CREATE TABLE m (mints DECIMAL) 391 392 statement ok 393 INSERT INTO m VALUES (cluster_logical_timestamp()) 394 395 # Test that cluster_logical_timestamp() is monotonic in transaction order 396 statement ok 397 INSERT INTO kv (k,v) VALUES (1, cluster_logical_timestamp()-(select mints from m)); 398 SELECT * FROM kv 399 400 statement ok 401 INSERT INTO kv (k,v) VALUES (2, cluster_logical_timestamp()-(select mints from m)); 402 SELECT * FROM kv 403 404 statement ok 405 INSERT INTO kv (k,v) VALUES (3, cluster_logical_timestamp()-(select mints from m)); 406 SELECT * FROM kv 407 408 statement ok 409 INSERT INTO kv (k,v) VALUES (4, cluster_logical_timestamp()-(select mints from m)); 410 SELECT * FROM kv 411 412 statement ok 413 INSERT INTO kv (k,v) VALUES (5, cluster_logical_timestamp()-(select mints from m)); 414 SELECT * FROM kv 415 416 statement ok 417 INSERT INTO kv (k,v) VALUES (6, cluster_logical_timestamp()-(select mints from m)); 418 SELECT * FROM kv 419 420 query I 421 SELECT k FROM kv ORDER BY v 422 ---- 423 1 424 2 425 3 426 4 427 5 428 6 429 430 statement ok 431 SET TIME ZONE UTC 432 433 statement ok 434 CREATE TABLE ex ( 435 k BIGINT PRIMARY KEY, 436 element STRING, 437 input TIMESTAMPTZ, 438 extract_result FLOAT, 439 date_trunc_result TIMESTAMPTZ 440 ) 441 442 statement ok 443 INSERT INTO ex VALUES 444 (1, 'year', '2001-04-10 12:04:59', 2001, '2001-01-01 00:00:00'), 445 (2, 'year', '2016-02-10 19:46:33.306157519', 2016, '2016-01-01 00:00:00'), 446 (3, 'years', '2016-02-10 19:46:33.306157519', 2016, '2016-01-01 00:00:00'), 447 (4, 'quarter', '2001-04-10 12:04:59', 2, '2001-04-01 00:00:00'), 448 (5, 'quarter', '2016-02-10 19:46:33.306157519', 1, '2016-01-01 00:00:00'), 449 (6, 'quarter', '2016-05-10 19:46:33.306157519', 2, '2016-04-01 00:00:00'), 450 (7, 'quarter', '2016-09-09 19:46:33.306157519', 3, '2016-07-01 00:00:00'), 451 (8, 'quarter', '2016-10-10 19:46:33.306157519', 4, '2016-10-01 00:00:00'), 452 (9, 'month', '2001-04-10 12:04:59', 4, '2001-04-01 00:00:00'), 453 (10, 'month', '2016-02-10 19:46:33.306157519', 2, '2016-02-01 00:00:00'), 454 (11, 'months', '2016-02-10 19:46:33.306157519', 2, '2016-02-01 00:00:00'), 455 (12, 'week', '2001-04-10 12:04:59', 15, '2001-04-09 00:00:00'), 456 (13, 'weeks', '2001-01-05 12:04:59', 1, '2001-01-01 00:00:00'), 457 (14, 'day', '2001-04-10 12:04:59', 10, '2001-04-10 00:00:00'), 458 (15, 'day', '2016-02-10 19:46:33.306157519', 10, '2016-02-10 00:00:00'), 459 (16, 'days', '2016-02-10 19:46:33.306157519', 10, '2016-02-10 00:00:00'), 460 (17, 'dayofweek', '2001-04-10 12:04:59', 2, null), 461 (18, 'dow', '2001-04-12 12:04:59', 4, null), 462 (19, 'dayofyear', '2001-04-10 12:04:59', 100, null), 463 (20, 'doy', '2001-04-12 12:04:59', 102, null), 464 (21, 'epoch', '1970-01-02 00:00:01.000001', 86401.000001, null), 465 (22, 'epoch', '1970-01-02 00:00:01.000001-04', 100801.000001, null), 466 (23, 'epoch', '2001-04-10 12:04:59', 986904299, null), 467 (24, 'hour', '2001-04-10 12:04:59', 12, '2001-04-10 12:00:00'), 468 (25, 'hour', '2016-02-10 19:46:33.306157519', 19, '2016-02-10 19:00:00'), 469 (26, 'hour', '2016-02-10 19:46:33.306157519-04', 23, '2016-02-10 19:00:00-04'), 470 (27, 'hours', '2016-02-10 19:46:33.306157519', 19, '2016-02-10 19:00:00'), 471 (28, 'hours', '2016-02-10 19:46:33.306157519-04', 23, '2016-02-10 19:00:00-04'), 472 (29, 'minute', '2001-04-10 12:04:59', 4, '2001-04-10 12:04:00'), 473 (30, 'minute', '2016-02-10 19:46:33.306157519', 46, '2016-02-10 19:46:00'), 474 (31, 'minutes', '2016-02-10 19:46:33.306157519', 46, '2016-02-10 19:46:00'), 475 (32, 'second', '2001-04-10 12:04:59.234', 59.234, '2001-04-10 12:04:59'), 476 (33, 'second', '2016-02-10 19:46:33.306157519', 33.306158, '2016-02-10 19:46:33'), 477 (34, 'seconds', '2016-02-10 19:46:33.306157519', 33.306158, '2016-02-10 19:46:33'), 478 (35, 'millisecond', '2001-04-10 12:04:59.234567', 59234.567, '2001-04-10 12:04:59.234'), 479 (36, 'millisecond', '2016-02-10 19:46:33.306157519', 33306.158, '2016-02-10 19:46:33.306'), 480 (37, 'milliseconds', '2016-02-10 19:46:33.306157519', 33306.158, '2016-02-10 19:46:33.306'), 481 (38, 'microsecond', '2001-04-10 12:04:59.34565423', 59345654, '2001-04-10 12:04:59.345654'), 482 (39, 'microsecond', '2016-02-10 19:46:33.306157519', 33306158, '2016-02-10 19:46:33.306158'), 483 (40, 'microseconds', '2016-02-10 19:46:33.306157519', 33306158, '2016-02-10 19:46:33.306158'), 484 (41, 'isodow', '2001-04-10 12:04:59', 2, null), 485 (42, 'isodow', '2001-04-08 12:04:59', 7, null), 486 (43, 'isoyear', '2007-12-31 12:04:59', 2008, null), 487 (44, 'isoyear', '2008-01-01 12:04:59', 2008, null), 488 (45, 'decade', '2001-04-10 12:04:59', 200, '2000-01-01 00:00:00'), 489 (46, 'decade', '2016-02-10 19:46:33.306157519 BC', -202, '2021-01-01 00:00:00 BC'), 490 (47, 'century', '2016-02-10 19:46:33.306157519', 21, '2001-01-01 00:00:00'), 491 (48, 'century', '0004-02-10 19:46:33.306157519 BC', -1, '0100-01-01 00:00:00 BC'), 492 (49, 'millennium', '2016-02-10 19:46:33.306157519', 3, '2001-01-01 00:00:00'), 493 (50, 'millennium', '1004-02-10 19:46:33.306157519 BC', -2, '2000-01-01 00:00:00 BC'), 494 (51, 'julian', '4714-11-24 BC', 0, null), 495 (52, 'julian', '2016-02-10 19:46:33.306157519', 2457429.823996599, null) 496 497 query IBR 498 SELECT k, extract(element, input::timestamp) = extract_result, extract(element, input::timestamp) FROM ex ORDER BY k 499 ---- 500 1 true 2001 501 2 true 2016 502 3 true 2016 503 4 true 2 504 5 true 1 505 6 true 2 506 7 true 3 507 8 true 4 508 9 true 4 509 10 true 2 510 11 true 2 511 12 true 15 512 13 true 1 513 14 true 10 514 15 true 10 515 16 true 10 516 17 true 2 517 18 true 4 518 19 true 100 519 20 true 102 520 21 true 86401.000001 521 22 true 100801.000001 522 23 true 9.86904299e+08 523 24 true 12 524 25 true 19 525 26 true 23 526 27 true 19 527 28 true 23 528 29 true 4 529 30 true 46 530 31 true 46 531 32 true 59.234 532 33 true 33.306158 533 34 true 33.306158 534 35 true 59234.567 535 36 true 33306.158 536 37 true 33306.158 537 38 true 5.9345654e+07 538 39 true 3.3306158e+07 539 40 true 3.3306158e+07 540 41 true 2 541 42 true 7 542 43 true 2008 543 44 true 2008 544 45 true 200 545 46 true -202 546 47 true 21 547 48 true -1 548 49 true 3 549 50 true -2 550 51 true 0 551 52 true 2.4574298239966e+06 552 553 query error extract\(\): unsupported timespan: nansecond 554 SELECT extract(nansecond from '2001-04-10 12:04:59.34565423'::timestamp) 555 556 query error unknown unit "nanosecond" 557 SELECT INTERVAL '1 nanosecond'; 558 559 query error unknown unit "ns" 560 SELECT INTERVAL '1 ns'; 561 562 query IBR 563 SELECT k, extract(element, input::timestamptz) = extract_result, extract(element, input::timestamptz) FROM ex ORDER BY k 564 ---- 565 1 true 2001 566 2 true 2016 567 3 true 2016 568 4 true 2 569 5 true 1 570 6 true 2 571 7 true 3 572 8 true 4 573 9 true 4 574 10 true 2 575 11 true 2 576 12 true 15 577 13 true 1 578 14 true 10 579 15 true 10 580 16 true 10 581 17 true 2 582 18 true 4 583 19 true 100 584 20 true 102 585 21 true 86401.000001 586 22 true 100801.000001 587 23 true 9.86904299e+08 588 24 true 12 589 25 true 19 590 26 true 23 591 27 true 19 592 28 true 23 593 29 true 4 594 30 true 46 595 31 true 46 596 32 true 59.234 597 33 true 33.306158 598 34 true 33.306158 599 35 true 59234.567 600 36 true 33306.158 601 37 true 33306.158 602 38 true 5.9345654e+07 603 39 true 3.3306158e+07 604 40 true 3.3306158e+07 605 41 true 2 606 42 true 7 607 43 true 2008 608 44 true 2008 609 45 true 200 610 46 true -202 611 47 true 21 612 48 true -1 613 49 true 3 614 50 true -2 615 51 true 0 616 52 true 2.4574298239966e+06 617 618 query error extract\(\): unsupported timespan: nansecond 619 SELECT extract(nansecond from '2001-04-10 12:04:59.34565423'::timestamptz) 620 621 query R 622 SELECT extract(hour from '2016-02-10 19:46:33.306157519-04'::timestamptz) 623 ---- 624 23 625 626 query R 627 SELECT extract(hours from '2016-02-10 19:46:33.306157519-04'::timestamptz) 628 ---- 629 23 630 631 query ITTBT 632 SELECT k, element, input, date_trunc(element, input::timestamp) = date_trunc_result, date_trunc(element, input::timestamp)::string 633 FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k 634 ---- 635 1 year 2001-04-10 12:04:59 +0000 UTC true 2001-01-01 00:00:00+00:00 636 2 year 2016-02-10 19:46:33.306158 +0000 UTC true 2016-01-01 00:00:00+00:00 637 3 years 2016-02-10 19:46:33.306158 +0000 UTC true 2016-01-01 00:00:00+00:00 638 4 quarter 2001-04-10 12:04:59 +0000 UTC true 2001-04-01 00:00:00+00:00 639 5 quarter 2016-02-10 19:46:33.306158 +0000 UTC true 2016-01-01 00:00:00+00:00 640 6 quarter 2016-05-10 19:46:33.306158 +0000 UTC true 2016-04-01 00:00:00+00:00 641 7 quarter 2016-09-09 19:46:33.306158 +0000 UTC true 2016-07-01 00:00:00+00:00 642 8 quarter 2016-10-10 19:46:33.306158 +0000 UTC true 2016-10-01 00:00:00+00:00 643 9 month 2001-04-10 12:04:59 +0000 UTC true 2001-04-01 00:00:00+00:00 644 10 month 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-01 00:00:00+00:00 645 11 months 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-01 00:00:00+00:00 646 12 week 2001-04-10 12:04:59 +0000 UTC true 2001-04-09 00:00:00+00:00 647 13 weeks 2001-01-05 12:04:59 +0000 UTC true 2001-01-01 00:00:00+00:00 648 14 day 2001-04-10 12:04:59 +0000 UTC true 2001-04-10 00:00:00+00:00 649 15 day 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 00:00:00+00:00 650 16 days 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 00:00:00+00:00 651 24 hour 2001-04-10 12:04:59 +0000 UTC true 2001-04-10 12:00:00+00:00 652 25 hour 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:00:00+00:00 653 26 hour 2016-02-10 23:46:33.306158 +0000 UTC true 2016-02-10 23:00:00+00:00 654 27 hours 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:00:00+00:00 655 28 hours 2016-02-10 23:46:33.306158 +0000 UTC true 2016-02-10 23:00:00+00:00 656 29 minute 2001-04-10 12:04:59 +0000 UTC true 2001-04-10 12:04:00+00:00 657 30 minute 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:00+00:00 658 31 minutes 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:00+00:00 659 32 second 2001-04-10 12:04:59.234 +0000 UTC true 2001-04-10 12:04:59+00:00 660 33 second 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33+00:00 661 34 seconds 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33+00:00 662 35 millisecond 2001-04-10 12:04:59.234567 +0000 UTC true 2001-04-10 12:04:59.234+00:00 663 36 millisecond 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33.306+00:00 664 37 milliseconds 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33.306+00:00 665 38 microsecond 2001-04-10 12:04:59.345654 +0000 UTC true 2001-04-10 12:04:59.345654+00:00 666 39 microsecond 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33.306158+00:00 667 40 microseconds 2016-02-10 19:46:33.306158 +0000 UTC true 2016-02-10 19:46:33.306158+00:00 668 45 decade 2001-04-10 12:04:59 +0000 UTC true 2000-01-01 00:00:00+00:00 669 46 decade -2015-02-10 19:46:33.306158 +0000 UTC true -2020-01-01 00:00:00+00:00 670 47 century 2016-02-10 19:46:33.306158 +0000 UTC true 2001-01-01 00:00:00+00:00 671 48 century -0003-02-10 19:46:33.306158 +0000 UTC true -0099-01-01 00:00:00+00:00 672 49 millennium 2016-02-10 19:46:33.306158 +0000 UTC true 2001-01-01 00:00:00+00:00 673 50 millennium -1003-02-10 19:46:33.306158 +0000 UTC true -1999-01-01 00:00:00+00:00 674 675 query IBT 676 SELECT k, date_trunc(element, input::timestamptz) = date_trunc_result, date_trunc(element, input::timestamptz)::string 677 FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k 678 ---- 679 1 true 2001-01-01 00:00:00+00:00 680 2 true 2016-01-01 00:00:00+00:00 681 3 true 2016-01-01 00:00:00+00:00 682 4 true 2001-04-01 00:00:00+00:00 683 5 true 2016-01-01 00:00:00+00:00 684 6 true 2016-04-01 00:00:00+00:00 685 7 true 2016-07-01 00:00:00+00:00 686 8 true 2016-10-01 00:00:00+00:00 687 9 true 2001-04-01 00:00:00+00:00 688 10 true 2016-02-01 00:00:00+00:00 689 11 true 2016-02-01 00:00:00+00:00 690 12 true 2001-04-09 00:00:00+00:00 691 13 true 2001-01-01 00:00:00+00:00 692 14 true 2001-04-10 00:00:00+00:00 693 15 true 2016-02-10 00:00:00+00:00 694 16 true 2016-02-10 00:00:00+00:00 695 24 true 2001-04-10 12:00:00+00:00 696 25 true 2016-02-10 19:00:00+00:00 697 26 true 2016-02-10 23:00:00+00:00 698 27 true 2016-02-10 19:00:00+00:00 699 28 true 2016-02-10 23:00:00+00:00 700 29 true 2001-04-10 12:04:00+00:00 701 30 true 2016-02-10 19:46:00+00:00 702 31 true 2016-02-10 19:46:00+00:00 703 32 true 2001-04-10 12:04:59+00:00 704 33 true 2016-02-10 19:46:33+00:00 705 34 true 2016-02-10 19:46:33+00:00 706 35 true 2001-04-10 12:04:59.234+00:00 707 36 true 2016-02-10 19:46:33.306+00:00 708 37 true 2016-02-10 19:46:33.306+00:00 709 38 true 2001-04-10 12:04:59.345654+00:00 710 39 true 2016-02-10 19:46:33.306158+00:00 711 40 true 2016-02-10 19:46:33.306158+00:00 712 45 true 2000-01-01 00:00:00+00:00 713 46 true -2020-01-01 00:00:00+00:00 714 47 true 2001-01-01 00:00:00+00:00 715 48 true -0099-01-01 00:00:00+00:00 716 49 true 2001-01-01 00:00:00+00:00 717 50 true -1999-01-01 00:00:00+00:00 718 719 query T 720 SELECT date_trunc('millennia', '2000-02-10 19:46:33.306157519-04'::timestamptz)::string 721 ---- 722 1001-01-01 00:00:00+00:00 723 724 query T 725 SELECT date_trunc('centuries', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string 726 ---- 727 2001-01-01 00:00:00+00:00 728 729 query T 730 SELECT date_trunc('decades', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string 731 ---- 732 2010-01-01 00:00:00+00:00 733 734 query T 735 SELECT date_trunc('hour', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string 736 ---- 737 2016-02-10 23:00:00+00:00 738 739 query T 740 SELECT date_trunc('hours', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string 741 ---- 742 2016-02-10 23:00:00+00:00 743 744 query IBT 745 SELECT k, date_trunc(element, input::date) = date_trunc_result::date, date_trunc(element, input::date)::string 746 FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k 747 ---- 748 1 true 2001-01-01 00:00:00+00:00 749 2 true 2016-01-01 00:00:00+00:00 750 3 true 2016-01-01 00:00:00+00:00 751 4 true 2001-04-01 00:00:00+00:00 752 5 true 2016-01-01 00:00:00+00:00 753 6 true 2016-04-01 00:00:00+00:00 754 7 true 2016-07-01 00:00:00+00:00 755 8 true 2016-10-01 00:00:00+00:00 756 9 true 2001-04-01 00:00:00+00:00 757 10 true 2016-02-01 00:00:00+00:00 758 11 true 2016-02-01 00:00:00+00:00 759 12 true 2001-04-09 00:00:00+00:00 760 13 true 2001-01-01 00:00:00+00:00 761 14 true 2001-04-10 00:00:00+00:00 762 15 true 2016-02-10 00:00:00+00:00 763 16 true 2016-02-10 00:00:00+00:00 764 24 true 2001-04-10 00:00:00+00:00 765 25 true 2016-02-10 00:00:00+00:00 766 26 true 2016-02-10 00:00:00+00:00 767 27 true 2016-02-10 00:00:00+00:00 768 28 true 2016-02-10 00:00:00+00:00 769 29 true 2001-04-10 00:00:00+00:00 770 30 true 2016-02-10 00:00:00+00:00 771 31 true 2016-02-10 00:00:00+00:00 772 32 true 2001-04-10 00:00:00+00:00 773 33 true 2016-02-10 00:00:00+00:00 774 34 true 2016-02-10 00:00:00+00:00 775 35 true 2001-04-10 00:00:00+00:00 776 36 true 2016-02-10 00:00:00+00:00 777 37 true 2016-02-10 00:00:00+00:00 778 38 true 2001-04-10 00:00:00+00:00 779 39 true 2016-02-10 00:00:00+00:00 780 40 true 2016-02-10 00:00:00+00:00 781 45 true 2000-01-01 00:00:00+00:00 782 46 true -2020-01-01 00:00:00+00:00 783 47 true 2001-01-01 00:00:00+00:00 784 48 true -0099-01-01 00:00:00+00:00 785 49 true 2001-01-01 00:00:00+00:00 786 50 true -1999-01-01 00:00:00+00:00 787 788 query T 789 SELECT (timestamp '2016-02-10 19:46:33.306157519')::string 790 ---- 791 2016-02-10 19:46:33.306158+00:00 792 793 query T 794 SELECT (timestamptz '2016-02-10 19:46:33.306157519')::string 795 ---- 796 2016-02-10 19:46:33.306158+00:00 797 798 # Test SET TIME ZONE 799 800 # default time zone of UTC 801 query T 802 SELECT '2015-08-25 05:45:45.53453'::timestamp 803 ---- 804 2015-08-25 05:45:45.53453 +0000 +0000 805 806 query T 807 SELECT '2015-08-25 05:45:45.53453'::timestamp 808 ---- 809 2015-08-25 05:45:45.53453 +0000 +0000 810 811 statement ok 812 SET TIME ZONE 'Europe/Rome' 813 814 query error unimplemented: timestamp abbreviations not supported 815 SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false 816 817 statement ok 818 SET TIME ZONE +1 819 820 query error unimplemented: timestamp abbreviations not supported 821 SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false 822 823 query T 824 SELECT '2015-08-25 05:45:45.53453'::timestamp 825 ---- 826 2015-08-25 05:45:45.53453 +0000 +0000 827 828 query T 829 SELECT '2015-08-25 05:45:45.53453'::timestamptz 830 ---- 831 2015-08-25 05:45:45.53453 +0100 +0100 832 833 query T 834 SELECT '2015-08-25 05:45:45-01:00'::timestamp 835 ---- 836 2015-08-25 05:45:45 +0000 +0000 837 838 query T 839 SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz 840 ---- 841 2015-08-25 05:45:45 +0100 +0100 842 843 query T 844 SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp 845 ---- 846 2015-08-25 07:45:45 +0000 +0000 847 848 query T 849 SELECT '2015-08-25 05:45:45-01:00'::timestamptz 850 ---- 851 2015-08-25 07:45:45 +0100 +0100 852 853 # alias test: TIMEZONE instead of TIME ZONE 854 statement ok 855 SET TIMEZONE = +2 856 857 query error unimplemented: timestamp abbreviations not supported 858 SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false 859 860 query T 861 SELECT '2015-08-25 05:45:45.53453'::timestamp 862 ---- 863 2015-08-25 05:45:45.53453 +0000 +0000 864 865 query T 866 SELECT '2015-08-25 05:45:45.53453'::timestamptz 867 ---- 868 2015-08-25 05:45:45.53453 +0200 +0200 869 870 query T 871 SELECT '2015-08-25 05:45:45-01:00'::timestamp 872 ---- 873 2015-08-25 05:45:45 +0000 +0000 874 875 query T 876 SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz 877 ---- 878 2015-08-25 05:45:45 +0200 +0200 879 880 query T 881 SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp 882 ---- 883 2015-08-25 08:45:45 +0000 +0000 884 885 query T 886 SELECT '2015-08-25 05:45:45-01:00'::timestamptz 887 ---- 888 2015-08-25 08:45:45 +0200 +0200 889 890 statement ok 891 SET TIME ZONE -5 892 893 query T 894 SELECT '2015-08-24 23:45:45.53453'::timestamp 895 ---- 896 2015-08-24 23:45:45.53453 +0000 +0000 897 898 query T 899 SELECT '2015-08-24 23:45:45.53453'::timestamptz 900 ---- 901 2015-08-24 23:45:45.53453 -0500 -0500 902 903 query T 904 SELECT '2015-08-24 23:45:45.53453 UTC'::timestamp 905 ---- 906 2015-08-24 23:45:45.53453 +0000 +0000 907 908 query T 909 SELECT '2015-08-24 23:45:45.53453 UTC'::timestamptz 910 ---- 911 2015-08-24 18:45:45.53453 -0500 -0500 912 913 query T 914 SELECT '2015-08-24 23:45:45.53453-02:00'::timestamp 915 ---- 916 2015-08-24 23:45:45.53453 +0000 +0000 917 918 query T 919 SELECT '2015-08-24 23:45:45.53453-02:00'::timestamptz 920 ---- 921 2015-08-24 20:45:45.53453 -0500 -0500 922 923 query T 924 SELECT '2015-08-24 23:45:45.53453-05:00'::timestamptz 925 ---- 926 2015-08-24 23:45:45.53453 -0500 -0500 927 928 query T 929 SELECT '2015-08-24 23:45:45.534 -02:00'::timestamp 930 ---- 931 2015-08-24 23:45:45.534 +0000 +0000 932 933 query T 934 SELECT '2015-08-24 23:45:45.534 -02:00'::timestamptz 935 ---- 936 2015-08-24 20:45:45.534 -0500 -0500 937 938 query T 939 SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz 940 ---- 941 2015-08-25 05:45:45 -0500 -0500 942 943 query T 944 SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp 945 ---- 946 2015-08-25 01:45:45 +0000 +0000 947 948 # using Eastern instead of fixed -5 should handle DST. 949 statement ok 950 SET TIME ZONE 'America/New_York' 951 952 query T 953 SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz 954 ---- 955 2015-08-25 05:45:45 -0400 EDT 956 957 query T 958 SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp 959 ---- 960 2015-08-25 02:45:45 +0000 +0000 961 962 963 statement error cannot find time zone "foobar" 964 SET TIME ZONE 'foobar' 965 966 statement ok 967 SET TIME ZONE default 968 969 query T 970 SELECT '2015-08-24 21:45:45.53453'::timestamptz 971 ---- 972 2015-08-24 21:45:45.53453 +0000 UTC 973 974 statement ok 975 SET TIME ZONE local 976 977 query T 978 SELECT '2015-08-24 21:45:45.53453'::timestamptz 979 ---- 980 2015-08-24 21:45:45.53453 +0000 UTC 981 982 statement ok 983 SET TIME ZONE 'DEFAULT' 984 985 query T 986 SELECT '2015-08-24 21:45:45.53453'::timestamptz 987 ---- 988 2015-08-24 21:45:45.53453 +0000 UTC 989 990 statement ok 991 SET TIME ZONE '' 992 993 query T 994 SELECT '2015-08-24 21:45:45.53453'::timestamptz 995 ---- 996 2015-08-24 21:45:45.53453 +0000 UTC 997 998 999 statement ok 1000 SET TIME ZONE INTERVAL '-7h' 1001 1002 query T 1003 SELECT '2015-08-24 21:45:45.53453'::timestamp 1004 ---- 1005 2015-08-24 21:45:45.53453 +0000 +0000 1006 1007 query T 1008 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1009 ---- 1010 2015-08-24 21:45:45.53453 -0700 -0700 1011 1012 statement ok 1013 SET TIME ZONE -7.5 1014 1015 query T 1016 SELECT '2015-08-24 21:45:45.53453'::timestamp 1017 ---- 1018 2015-08-24 21:45:45.53453 +0000 +0000 1019 1020 query T 1021 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1022 ---- 1023 2015-08-24 21:45:45.53453 -0730 -0730 1024 1025 query T 1026 SELECT '2015-08-24 21:45:45.53453 UTC'::timestamptz 1027 ---- 1028 2015-08-24 14:15:45.53453 -0730 -0730 1029 1030 statement ok 1031 SET TIME ZONE LOCAL 1032 1033 query T 1034 SELECT '2015-08-25 04:45:45.53453'::timestamp 1035 ---- 1036 2015-08-25 04:45:45.53453 +0000 +0000 1037 1038 statement ok 1039 SET TIME ZONE DEFAULT 1040 1041 query T 1042 SELECT '2015-08-25 04:45:45.53453'::timestamp 1043 ---- 1044 2015-08-25 04:45:45.53453 +0000 +0000 1045 1046 # reset for what follows. 1047 statement ok 1048 SET TIME ZONE 'UTC' 1049 1050 # Check that casting from a timestamp to a date and vice versa 1051 # uses the time zone. 1052 query TTTT 1053 SELECT b, b::date, c, c::date FROM u WHERE a = 123 1054 ---- 1055 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-30 00:00:00 +0000 +0000 2015-08-30 03:34:45.34567 +0000 UTC 2015-08-30 00:00:00 +0000 +0000 1056 1057 query T 1058 SELECT d::timestamp FROM u WHERE a = 123 1059 ---- 1060 2015-08-30 00:00:00 +0000 +0000 1061 1062 statement ok 1063 SET TIME ZONE -5 1064 1065 query TTTT 1066 SELECT b, b::date, c, c::date FROM u WHERE a = 123 1067 ---- 1068 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-30 00:00:00 +0000 +0000 2015-08-29 22:34:45.34567 -0500 -0500 2015-08-29 00:00:00 +0000 +0000 1069 1070 query T 1071 SELECT d::timestamp FROM u WHERE a = 123 1072 ---- 1073 2015-08-30 00:00:00 +0000 +0000 1074 1075 statement ok 1076 SET TIME ZONE UTC 1077 1078 # TODO(mjibson): Remove family definition once #41283 is fixed. 1079 statement ok 1080 CREATE TABLE tz ( 1081 a INT PRIMARY KEY, 1082 b TIMESTAMP, 1083 c TIMESTAMPTZ, 1084 d TIMESTAMPTZ, 1085 FAMILY "primary" (a, b, c, d) 1086 ) 1087 1088 query TTBTTTB 1089 SHOW COLUMNS FROM tz 1090 ---- 1091 a INT8 false NULL · {primary} false 1092 b TIMESTAMP true NULL · {} false 1093 c TIMESTAMPTZ true NULL · {} false 1094 d TIMESTAMPTZ true NULL · {} false 1095 1096 statement ok 1097 INSERT INTO tz VALUES 1098 (1, timestamp '2015-08-30 03:34:45', timestamptz '2015-08-30 03:34:45', timestamptz '2015-08-30 03:34:45'), 1099 (2, timestamp '2015-08-30 03:34:45+01:00', timestamptz '2015-08-30 03:34:45+01:00', timestamptz '2015-08-30 03:34:45') 1100 1101 statement ok 1102 SET TIME ZONE -2 1103 1104 query ITT 1105 SELECT a, b, c FROM tz ORDER BY a 1106 ---- 1107 1 2015-08-30 03:34:45 +0000 +0000 2015-08-30 01:34:45 -0200 -0200 1108 2 2015-08-30 03:34:45 +0000 +0000 2015-08-30 00:34:45 -0200 -0200 1109 1110 query TTTT 1111 SELECT b + interval '1m', interval '1m' + b, c + interval '1m', interval '1m' + c FROM tz WHERE a = 1 1112 ---- 1113 2015-08-30 03:35:45 +0000 +0000 2015-08-30 03:35:45 +0000 +0000 2015-08-30 01:35:45 -0200 -0200 2015-08-30 01:35:45 -0200 -0200 1114 1115 query I 1116 SELECT a FROM tz WHERE c = d 1117 ---- 1118 1 1119 1120 query I rowsort 1121 SELECT a FROM tz WHERE c <= d 1122 ---- 1123 1 1124 2 1125 1126 query I 1127 SELECT a FROM tz WHERE c < d 1128 ---- 1129 2 1130 1131 1132 query I rowsort 1133 SELECT a FROM tz WHERE b = c::timestamp 1134 ---- 1135 1136 query I rowsort 1137 SELECT a FROM tz WHERE c = d::timestamp 1138 ---- 1139 1 1140 1141 # reset for what follows. 1142 statement ok 1143 SET TIME ZONE 'UTC' 1144 1145 statement ok 1146 SET TIME ZONE -5 1147 1148 query T 1149 SHOW TIME ZONE 1150 ---- 1151 -5 1152 1153 statement ok 1154 SET TIME ZONE INTERVAL '+04:00' HOUR TO MINUTE 1155 1156 query T 1157 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1158 ---- 1159 2015-08-24 21:45:45.53453 +0400 +0400 1160 1161 statement ok 1162 SET TIME ZONE INTERVAL '-04:00' MINUTE TO SECOND 1163 1164 query T 1165 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1166 ---- 1167 2015-08-24 21:45:45.53453 -0004 -0004 1168 1169 # alias test: TIMEZONE instead of TIME ZONE 1170 statement ok 1171 SET TIMEZONE TO INTERVAL '+05:00' HOUR TO MINUTE 1172 1173 query T 1174 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1175 ---- 1176 2015-08-24 21:45:45.53453 +0500 +0500 1177 1178 statement ok 1179 SET TIMEZONE TO INTERVAL '-05:00' MINUTE TO SECOND 1180 1181 query T 1182 SELECT '2015-08-24 21:45:45.53453'::timestamptz 1183 ---- 1184 2015-08-24 21:45:45.53453 -0005 -0005 1185 1186 statement ok 1187 SET TIME ZONE 0 1188 1189 query T 1190 SHOW TIME ZONE 1191 ---- 1192 0 1193 1194 query T 1195 SELECT DATE '1999-01-01' + INTERVAL '4 minutes' 1196 ---- 1197 1999-01-01 00:04:00 +0000 +0000 1198 1199 query T 1200 SELECT INTERVAL '4 minutes' + DATE '1999-01-01' 1201 ---- 1202 1999-01-01 00:04:00 +0000 +0000 1203 1204 query T 1205 SELECT DATE '1999-01-01' - INTERVAL '4 minutes' 1206 ---- 1207 1998-12-31 23:56:00 +0000 +0000 1208 1209 query B 1210 SELECT DATE '1999-01-02' < TIMESTAMPTZ '1999-01-01' 1211 ---- 1212 false 1213 1214 query B 1215 SELECT DATE '1999-01-02' < TIMESTAMP '1999-01-01' 1216 ---- 1217 false 1218 1219 query B 1220 SELECT DATE '1999-01-02' <= TIMESTAMPTZ '1999-01-01' 1221 ---- 1222 false 1223 1224 query B 1225 SELECT DATE '1999-01-02' <= TIMESTAMP '1999-01-01' 1226 ---- 1227 false 1228 1229 query B 1230 SELECT DATE '1999-01-02' <= TIMESTAMPTZ '1999-01-02' 1231 ---- 1232 true 1233 1234 query B 1235 SELECT DATE '1999-01-02' <= TIMESTAMP '1999-01-02' 1236 ---- 1237 true 1238 1239 query B 1240 SELECT DATE '1999-01-02' > TIMESTAMPTZ '1999-01-01' 1241 ---- 1242 true 1243 1244 query B 1245 SELECT DATE '1999-01-02' > TIMESTAMP '1999-01-01' 1246 ---- 1247 true 1248 1249 query B 1250 SELECT DATE '1999-01-02' >= TIMESTAMPTZ '1999-01-01' 1251 ---- 1252 true 1253 1254 query B 1255 SELECT DATE '1999-01-02' >= TIMESTAMP '1999-01-01' 1256 ---- 1257 true 1258 1259 query B 1260 SELECT DATE '1999-01-02' = TIMESTAMPTZ '1999-01-01' 1261 ---- 1262 false 1263 1264 query B 1265 SELECT DATE '1999-01-01' = TIMESTAMP '1999-01-01' 1266 ---- 1267 true 1268 1269 ## Test parsing of unitless interval constants with field specifiers 1270 query TTTTT 1271 SELECT INTERVAL '5', INTERVAL '5' SECOND, INTERVAL '5' MINUTE TO SECOND, INTERVAL '5' HOUR TO SECOND, INTERVAL '5' DAY TO SECOND; 1272 ---- 1273 00:00:05 00:00:05 00:00:05 00:00:05 00:00:05 1274 1275 query TTT 1276 SELECT INTERVAL '5' MINUTE, INTERVAL '5' HOUR TO MINUTE, INTERVAL '5' DAY TO MINUTE; 1277 ---- 1278 00:05:00 00:05:00 00:05:00 1279 1280 query TT 1281 SELECT INTERVAL '5' HOUR, INTERVAL '5' DAY TO HOUR; 1282 ---- 1283 05:00:00 05:00:00 1284 1285 query T 1286 SELECT INTERVAL '5' DAY; 1287 ---- 1288 5 days 1289 1290 query TT 1291 SELECT INTERVAL '5' MONTH, INTERVAL '5' YEAR TO MONTH; 1292 ---- 1293 5 mons 5 mons 1294 1295 query T 1296 SELECT INTERVAL '5' YEAR 1297 ---- 1298 5 years 1299 1300 ## Test truncation via field specifiers 1301 query TTTT 1302 SELECT INTERVAL '1-2 3 4:5:6' SECOND, INTERVAL '1-2 3 4:5:6' MINUTE TO SECOND, INTERVAL '1-2 3 4:5:6' HOUR TO SECOND, INTERVAL '1-2 3 4:5:6' DAY TO SECOND; 1303 ---- 1304 1 year 2 mons 3 days 04:05:06 1 year 2 mons 3 days 04:05:06 1 year 2 mons 3 days 04:05:06 1 year 2 mons 3 days 04:05:06 1305 1306 query TTT 1307 SELECT INTERVAL '1-2 3 4:5:6' MINUTE, INTERVAL '1-2 3 4:5:6' HOUR TO MINUTE, INTERVAL '1-2 3 4:5:6' DAY TO MINUTE; 1308 ---- 1309 1 year 2 mons 3 days 04:05:00 1 year 2 mons 3 days 04:05:00 1 year 2 mons 3 days 04:05:00 1310 1311 query TT 1312 SELECT INTERVAL '1-2 3 4:5:6' HOUR, INTERVAL '1-2 3 4:5:6' DAY TO HOUR 1313 ---- 1314 1 year 2 mons 3 days 04:00:00 1 year 2 mons 3 days 04:00:00 1315 1316 query T 1317 SELECT INTERVAL '1-2 3 4:5:6' DAY; 1318 ---- 1319 1 year 2 mons 3 days 1320 1321 query TT 1322 SELECT INTERVAL '1-2 3 4:5:6' MONTH, INTERVAL '1-2 3 4:5:6' YEAR TO MONTH; 1323 ---- 1324 1 year 2 mons 1 year 2 mons 1325 1326 query T 1327 SELECT INTERVAL '1-2 3 4:5:6' YEAR 1328 ---- 1329 1 year 1330 1331 1332 # Test regression, #20464. When a built-in returns a datum that does not 1333 # match the function signature, distSQL will panic on table scans. 1334 1335 statement ok 1336 CREATE TABLE topics ( 1337 ts TIMESTAMP, 1338 tstz TIMESTAMPTZ, 1339 "date" DATE 1340 ); 1341 1342 statement ok 1343 INSERT INTO topics VALUES ( 1344 '2017-12-05 04:04:04.913231+00:00', 1345 '2017-12-05 04:04:04.913231+00:00', 1346 '2017-12-05 04:04:04.913231+00:00' 1347 ); 1348 1349 query T 1350 SELECT date_trunc('month', ts) AS date_trunc_month_created_at FROM "topics"; 1351 ---- 1352 2017-12-01 00:00:00 +0000 +0000 1353 1354 query T 1355 SELECT date_trunc('month', tstz) AS date_trunc_month_created_at FROM "topics"; 1356 ---- 1357 2017-12-01 00:00:00 +0000 +0000 1358 1359 query T 1360 SELECT date_trunc('month', "date") AS date_trunc_month_created_at FROM "topics"; 1361 ---- 1362 2017-12-01 00:00:00 +0000 +0000 1363 1364 # Test date_trunc works when timestamp zone changes. 1365 subtest regression_41663 1366 1367 query T 1368 select date_trunc('day', '2011-01-01 22:30:00'::date); 1369 ---- 1370 2011-01-01 00:00:00 +0000 +0000 1371 1372 query T 1373 select date_trunc('day', '2011-01-01 22:30:00+01:00'::timestamptz); 1374 ---- 1375 2011-01-01 00:00:00 +0000 +0000 1376 1377 statement ok 1378 SET TIME ZONE 'Africa/Nairobi' 1379 1380 query T 1381 select date_trunc('day', '2011-01-01 22:30:00'::date) 1382 ---- 1383 2011-01-01 00:00:00 +0300 EAT 1384 1385 query T 1386 select date_trunc('day', '2011-01-02 01:30:00'::timestamp) 1387 ---- 1388 2011-01-02 00:00:00 +0000 +0000 1389 1390 query T 1391 select date_trunc('day', '2011-01-01 22:30:00+01:00'::timestamptz) 1392 ---- 1393 2011-01-02 00:00:00 +0300 EAT 1394 1395 statement ok 1396 SET TIME ZONE -5 1397 1398 query TT 1399 select date_trunc('day', '2011-01-02 01:30:00'::date), pg_typeof(date_trunc('day', '2011-01-02 01:30:00'::date)) 1400 ---- 1401 2011-01-02 00:00:00 -0500 -0500 timestamp with time zone 1402 1403 query TT 1404 select date_trunc('day', '2011-01-02 01:30:00'::timestamp), pg_typeof(date_trunc('day', '2011-01-02 01:30:00'::timestamp)) 1405 ---- 1406 2011-01-02 00:00:00 +0000 +0000 timestamp without time zone 1407 1408 query TT 1409 select date_trunc('day', '2011-01-02 01:30:00+00:00'::timestamptz), pg_typeof(date_trunc('day', '2011-01-02 01:30:00+00:00'::timestamptz)) 1410 ---- 1411 2011-01-01 00:00:00 -0500 -0500 timestamp with time zone 1412 1413 statement ok 1414 SET TIME ZONE 0 1415 1416 # Test casting timestamptz to time works in the presence of time zones. 1417 1418 statement ok 1419 SET TIME ZONE 'UTC' 1420 1421 statement ok 1422 CREATE TABLE django_37 (a TIMESTAMPTZ); INSERT INTO django_37 VALUES ('2018-09-28T12:42:10.234567-05:00'::TIMESTAMPTZ) 1423 1424 query T 1425 SELECT a::TIME FROM django_37 1426 ---- 1427 0000-01-01 17:42:10.234567 +0000 UTC 1428 1429 statement ok 1430 SET TIME ZONE 'America/Chicago' 1431 1432 query T 1433 SELECT a::TIME FROM django_37 1434 ---- 1435 0000-01-01 12:42:10.234567 +0000 UTC 1436 1437 # Test negative years to ensure they can round-trip through the parser. 1438 # Also ensure that we don't trigger any of the "convenience" rules. 1439 # Update: dates now have a much more limited range such that the original 1440 # dates from this issue are no longer possible to express. 1441 subtest regression_35255 1442 1443 statement error date is out of range 1444 SELECT '-56325279622-12-26'::DATE 1445 1446 statement error date is out of range 1447 SELECT '-5632-12-26'::DATE 1448 1449 query T 1450 SELECT '-563-12-26'::DATE 1451 ---- 1452 -0563-12-26 00:00:00 +0000 +0000 1453 1454 query T 1455 SELECT '-56-12-26'::DATE 1456 ---- 1457 -0056-12-26 00:00:00 +0000 +0000 1458 1459 query T 1460 SELECT '-5-12-26'::DATE 1461 ---- 1462 -0005-12-26 00:00:00 +0000 +0000 1463 1464 # Update: dates now have a much more limited range such that the original 1465 # dates from this issue are no longer possible to express. 1466 subtest regression_36146 1467 1468 statement error out of range 1469 WITH 1470 w (c) AS (VALUES (NULL), (NULL)) 1471 SELECT 1472 '1971-03-18'::DATE + 300866802885581286 1473 FROM 1474 w 1475 ORDER BY 1476 c 1477 1478 statement error out of range 1479 SELECT 1480 '1971-03-18'::DATE + 300866802885581286 1481 1482 # Update: dates now have a much more limited range such that the original 1483 # dates from this issue are no longer possible to express. 1484 subtest regression_36557 1485 1486 statement error out of range 1487 SELECT 7133080445639580613::INT8 + '1977-11-03'::DATE 1488 1489 statement error out of range 1490 SELECT '-239852040018-04-28':::DATE 1491 1492 statement error out of range 1493 SELECT(7133080445639580613::INT8 + '1977-11-03'::DATE) = '-239852040018-04-28':::DATE 1494 1495 subtest interval_math 1496 1497 query TTTTTTT 1498 SELECT 1499 i, 1500 i / 2::INT8, 1501 i * 2::INT8, 1502 i / 2::FLOAT8, 1503 i * 2::FLOAT8, 1504 i / .2362::FLOAT8, 1505 i * .2362::FLOAT8 1506 FROM 1507 ( 1508 VALUES 1509 ('1 day'::INTERVAL), 1510 ('1 month'::INTERVAL), 1511 ('1 hour'::INTERVAL), 1512 ('1 month 2 days 4 hours'::INTERVAL) 1513 ) 1514 AS v (i) 1515 ORDER BY 1516 i 1517 ---- 1518 01:00:00 00:30:00 02:00:00 00:30:00 02:00:00 04:14:01.320914 00:14:10.32 1519 1 day 12:00:00 2 days 12:00:00 2 days 4 days 05:36:31.701948 05:40:07.68 1520 1 mon 15 days 2 mons 15 days 2 mons 4 mons 7 days 00:15:51.058425 7 days 02:03:50.4 1521 1 mon 2 days 04:00:00 16 days 02:00:00 2 mons 4 days 08:00:00 16 days 02:00:00 2 mons 4 days 08:00:00 4 mons 15 days 28:24:59.745978 7 days 14:20:47.04 1522 1523 subtest tz_utc_normalization 1524 1525 # This is a special case, pending resolution of #36864. 1526 query T 1527 SET timezone = 'utc'; SHOW timezone 1528 ---- 1529 UTC 1530 1531 subtest regression_42244 1532 1533 statement ok 1534 SET TIME ZONE -5 1535 1536 # Check date is still configured correctly from day. 1537 query R 1538 select extract(day from '2019-01-15'::date) as final 1539 ---- 1540 15 1541 1542 # Check other usages of MakeDTimestampTZFromDate 1543 1544 query TT 1545 select ('2019-01-15'::date + '16:17:18'::time), pg_typeof('2019-01-15'::date + '16:17:18'::time) 1546 ---- 1547 2019-01-15 16:17:18 +0000 +0000 timestamp without time zone 1548 1549 query TT 1550 select ('16:17:18'::time + '2019-01-15'::date), pg_typeof(('16:17:18'::time + '2019-01-15'::date)) 1551 ---- 1552 2019-01-15 16:17:18 +0000 +0000 timestamp without time zone 1553 1554 query TT 1555 select ('2019-01-15'::date + '1 hour'::interval), pg_typeof('2019-01-15'::date + '1 hour'::interval) 1556 ---- 1557 2019-01-15 01:00:00 +0000 +0000 timestamp without time zone 1558 1559 query TT 1560 select ('1 hour'::interval + '2019-01-15'::date), pg_typeof('1 hour'::interval + '2019-01-15'::date) 1561 ---- 1562 2019-01-15 01:00:00 +0000 +0000 timestamp without time zone 1563 1564 query TT 1565 select ('2019-01-15'::date - '16:17:18'::time), pg_typeof('2019-01-15'::date - '16:17:18'::time) 1566 ---- 1567 2019-01-14 07:42:42 +0000 +0000 timestamp without time zone 1568 1569 query TT 1570 select ('2019-01-15'::date - '1 hour'::interval), pg_typeof('2019-01-15'::date - '1 hour'::interval) 1571 ---- 1572 2019-01-14 23:00:00 +0000 +0000 timestamp without time zone 1573 1574 query B 1575 select '2019-01-01'::date > '2019-01-01 00:00:00+00'::timestamptz 1576 ---- 1577 true 1578 1579 query B 1580 select '2019-01-01 00:00:00+00'::timestamptz < '2019-01-01'::date 1581 ---- 1582 true 1583 1584 query B 1585 select '2019-01-01'::date = '2019-01-01 00:00:00'::timestamp 1586 ---- 1587 true 1588 1589 query B 1590 select '2019-01-01 00:00:00'::timestamp = '2019-01-01'::date 1591 ---- 1592 true 1593 1594 query B 1595 select '2019-01-01'::date = '2019-01-01'::date 1596 ---- 1597 true 1598 1599 # Check logic works on a table. 1600 1601 statement ok 1602 SET TIME ZONE 0 1603 1604 statement ok 1605 CREATE TABLE date_test (date_val date, time_val time, interval_val interval) 1606 1607 statement ok 1608 INSERT INTO date_test VALUES ('2019-01-15'::date, '16:17:18'::time, '1 hour'::interval) 1609 1610 statement ok 1611 SET TIME ZONE -5 1612 1613 query TT 1614 select (date_test.date_val + date_test.time_val), pg_typeof(date_test.date_val + date_test.time_val) from date_test 1615 ---- 1616 2019-01-15 16:17:18 +0000 +0000 timestamp without time zone 1617 1618 query TT 1619 select (date_test.time_val + date_test.date_val), pg_typeof((date_test.time_val + date_test.date_val)) from date_test 1620 ---- 1621 2019-01-15 16:17:18 +0000 +0000 timestamp without time zone 1622 1623 query TT 1624 select (date_test.date_val + date_test.interval_val), pg_typeof(date_test.date_val + date_test.interval_val) from date_test 1625 ---- 1626 2019-01-15 01:00:00 +0000 +0000 timestamp without time zone 1627 1628 query TT 1629 select (date_test.interval_val + date_test.date_val), pg_typeof(date_test.interval_val + date_test.date_val) from date_test 1630 ---- 1631 2019-01-15 01:00:00 +0000 +0000 timestamp without time zone 1632 1633 query TT 1634 select (date_test.date_val - date_test.time_val), pg_typeof(date_test.date_val - date_test.time_val) from date_test 1635 ---- 1636 2019-01-14 07:42:42 +0000 +0000 timestamp without time zone 1637 1638 query TT 1639 select (date_test.date_val - date_test.interval_val), pg_typeof(date_test.date_val - date_test.interval_val) from date_test 1640 ---- 1641 2019-01-14 23:00:00 +0000 +0000 timestamp without time zone 1642 1643 query I 1644 select count(1) from date_test where date_test.date_val > '2019-01-15 00:00:00+00'::timestamptz 1645 ---- 1646 1 1647 1648 query I 1649 select count(1) from date_test where '2019-01-15 00:00:00+00'::timestamptz < date_test.date_val 1650 ---- 1651 1 1652 1653 query I 1654 select count(1) from date_test where '2019-01-15 00:00:00'::timestamp = date_test.date_val 1655 ---- 1656 1 1657 1658 query I 1659 select count(1) from date_test where date_test.date_val = '2019-01-15 00:00:00'::timestamp 1660 ---- 1661 1 1662 1663 query I 1664 select count(1) from date_test where date_test.date_val = '2019-01-15'::date 1665 ---- 1666 1 1667 1668 statement ok 1669 SET TIME ZONE +5 1670 1671 query I 1672 select count(1) from date_test where date_test.date_val < '2019-01-15 00:00:00+00'::timestamptz 1673 ---- 1674 1 1675 1676 query I 1677 select count(1) from date_test where '2019-01-15 00:00:00+00'::timestamptz > date_test.date_val 1678 ---- 1679 1 1680 1681 query I 1682 select count(1) from date_test where date_test.date_val = '2019-01-15 00:00:00'::timestamp 1683 ---- 1684 1 1685 1686 query I 1687 select count(1) from date_test where date_test.date_val = '2019-01-15'::date 1688 ---- 1689 1 1690 1691 statement ok 1692 SET TIME ZONE 0