github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/time (about) 1 # Note that the odd '0000-01-01 hh:mi:ss +0000 UTC' result format is an 2 # artifact of how pq displays TIMEs. 3 4 query T 5 SELECT '12:00:00':::TIME; 6 ---- 7 0000-01-01 12:00:00 +0000 UTC 8 9 query T 10 SELECT '12:00:00.456':::TIME; 11 ---- 12 0000-01-01 12:00:00.456 +0000 UTC 13 14 query T 15 SELECT '00:00:00':::TIME; 16 ---- 17 0000-01-01 00:00:00 +0000 UTC 18 19 query T 20 SELECT '23:59:59.999999':::TIME; 21 ---- 22 0000-01-01 23:59:59.999999 +0000 UTC 23 24 query T 25 select ('24:00'::TIME)::STRING 26 ---- 27 24:00:00 28 29 query T 30 SELECT ('24:00:00'::TIME)::STRING 31 ---- 32 24:00:00 33 34 statement error could not parse 35 SELECT '124:00'::TIME; 36 37 statement error could not parse 38 SELECT '24:00:01'::TIME; 39 40 statement error could not parse 41 SELECT '24:00:00.001'::TIME; 42 43 44 # Timezone should be ignored. 45 query T 46 SELECT '12:00:00-08:00':::TIME; 47 ---- 48 0000-01-01 12:00:00 +0000 UTC 49 50 query T 51 SELECT TIME '12:00:00'; 52 ---- 53 0000-01-01 12:00:00 +0000 UTC 54 55 # Casting 56 57 query T 58 SELECT '12:00:00'::TIME; 59 ---- 60 0000-01-01 12:00:00 +0000 UTC 61 62 query T 63 select '12:00:00':::STRING::TIME; 64 ---- 65 0000-01-01 12:00:00 +0000 UTC 66 67 query T 68 SELECT '12:00:00' COLLATE de::TIME; 69 ---- 70 0000-01-01 12:00:00 +0000 UTC 71 72 query T 73 SELECT '2017-01-01 12:00:00':::TIMESTAMP::TIME; 74 ---- 75 0000-01-01 12:00:00 +0000 UTC 76 77 query T 78 SELECT '2017-01-01 12:00:00-05':::TIMESTAMPTZ::TIME; 79 ---- 80 0000-01-01 17:00:00 +0000 UTC 81 82 query T 83 SELECT '12h':::INTERVAL::TIME; 84 ---- 85 0000-01-01 12:00:00 +0000 UTC 86 87 query T 88 SELECT '12:00:00':::TIME::INTERVAL; 89 ---- 90 12:00:00 91 92 query T 93 SELECT '12:00:00':::TIME::STRING; 94 ---- 95 12:00:00 96 97 # Comparison 98 99 query B 100 SELECT '12:00:00':::TIME = '12:00:00':::TIME 101 ---- 102 true 103 104 query B 105 SELECT '12:00:00':::TIME = '12:00:00.000000':::TIME 106 ---- 107 true 108 109 query B 110 SELECT '12:00:00':::TIME = '12:00:00.000001':::TIME 111 ---- 112 false 113 114 query B 115 SELECT '12:00:00':::TIME < '12:00:00.000001':::TIME 116 ---- 117 true 118 119 query B 120 SELECT '12:00:00':::TIME < '12:00:00':::TIME 121 ---- 122 false 123 124 query B 125 SELECT '12:00:00':::TIME < '11:59:59.999999':::TIME 126 ---- 127 false 128 129 query B 130 SELECT '12:00:00':::TIME > '11:59:59.999999':::TIME 131 ---- 132 true 133 134 query B 135 SELECT '12:00:00':::TIME > '12:00:00':::TIME 136 ---- 137 false 138 139 query B 140 SELECT '12:00:00':::TIME > '12:00:00.000001':::TIME 141 ---- 142 false 143 144 query B 145 SELECT '12:00:00':::TIME <= '12:00:00':::TIME 146 ---- 147 true 148 149 query B 150 SELECT '12:00:00':::TIME >= '12:00:00':::TIME 151 ---- 152 true 153 154 query B 155 SELECT '12:00:00':::TIME IN ('12:00:00'); 156 ---- 157 true 158 159 query B 160 SELECT '12:00:00':::TIME IN ('00:00:00'); 161 ---- 162 false 163 164 # Arithmetic 165 166 query T 167 SELECT '12:00:00':::TIME + '1s':::INTERVAL 168 ---- 169 0000-01-01 12:00:01 +0000 UTC 170 171 query T 172 SELECT '23:59:59':::TIME + '1s':::INTERVAL 173 ---- 174 0000-01-01 00:00:00 +0000 UTC 175 176 query T 177 SELECT '12:00:00':::TIME + '1d':::INTERVAL 178 ---- 179 0000-01-01 12:00:00 +0000 UTC 180 181 query T 182 SELECT '1s':::INTERVAL + '12:00:00':::TIME 183 ---- 184 0000-01-01 12:00:01 +0000 UTC 185 186 query T 187 SELECT '12:00:00':::TIME - '1s':::INTERVAL 188 ---- 189 0000-01-01 11:59:59 +0000 UTC 190 191 query T 192 SELECT '00:00:00':::TIME - '1s':::INTERVAL 193 ---- 194 0000-01-01 23:59:59 +0000 UTC 195 196 query T 197 SELECT '12:00:00':::TIME - '1d':::INTERVAL 198 ---- 199 0000-01-01 12:00:00 +0000 UTC 200 201 query T 202 SELECT '12:00:00':::TIME - '11:59:59':::TIME 203 ---- 204 00:00:01 205 206 query T 207 SELECT '11:59:59':::TIME - '12:00:00':::TIME 208 ---- 209 -00:00:01 210 211 query T 212 SELECT '2017-01-01':::DATE + '12:00:00':::TIME 213 ---- 214 2017-01-01 12:00:00 +0000 +0000 215 216 query T 217 SELECT '12:00:00':::TIME + '2017-01-01':::DATE 218 ---- 219 2017-01-01 12:00:00 +0000 +0000 220 221 query T 222 SELECT '2017-01-01':::DATE - '12:00:00':::TIME 223 ---- 224 2016-12-31 12:00:00 +0000 +0000 225 226 # Storage 227 228 statement ok 229 CREATE TABLE times (t time PRIMARY KEY) 230 231 statement ok 232 INSERT INTO times VALUES 233 ('00:00:00'), 234 ('00:00:00.000001'), 235 ('11:59:59.999999'), 236 ('12:00:00'), 237 ('12:00:00.000001'), 238 ('23:59:59.999999') 239 240 query T 241 SELECT * FROM times ORDER BY t 242 ---- 243 0000-01-01 00:00:00 +0000 UTC 244 0000-01-01 00:00:00.000001 +0000 UTC 245 0000-01-01 11:59:59.999999 +0000 UTC 246 0000-01-01 12:00:00 +0000 UTC 247 0000-01-01 12:00:00.000001 +0000 UTC 248 0000-01-01 23:59:59.999999 +0000 UTC 249 250 statement ok 251 CREATE TABLE arrays (times TIME[]) 252 253 statement ok 254 INSERT INTO arrays VALUES 255 (ARRAY[]), 256 (ARRAY['00:00:00']), 257 (ARRAY['00:00:00', '12:00:00.000001']), 258 ('{13:00:00}'::TIME[]) 259 260 query T rowsort 261 SELECT * FROM arrays 262 ---- 263 {} 264 {00:00:00} 265 {00:00:00,12:00:00.000001} 266 {13:00:00} 267 268 # Built-ins 269 270 query T 271 SELECT date_trunc('hour', time '12:01:02.345678') 272 ---- 273 12:00:00 274 275 query T 276 SELECT date_trunc('minute', time '12:01:02.345678') 277 ---- 278 12:01:00 279 280 query T 281 SELECT date_trunc('second', time '12:01:02.345678') 282 ---- 283 12:01:02 284 285 query T 286 SELECT date_trunc('millisecond', time '12:01:02.345678') 287 ---- 288 12:01:02.345 289 290 query T 291 SELECT date_trunc('microsecond', time '12:01:02.345678') 292 ---- 293 12:01:02.345678 294 295 query error pgcode 22023 date_trunc\(\): unsupported timespan: day 296 SELECT date_trunc('day', time '12:01:02.345') 297 298 query R 299 SELECT extract(hour from time '12:01:02.345678') 300 ---- 301 12 302 303 query R 304 SELECT extract(minute from time '12:01:02.345678') 305 ---- 306 1 307 308 query R 309 SELECT extract(second from time '12:01:02.345678') 310 ---- 311 2.345678 312 313 query R 314 SELECT extract(millisecond from time '12:01:02.345678') 315 ---- 316 2345.678 317 318 query R 319 SELECT extract(microsecond from time '12:01:02.345678') 320 ---- 321 2.345678e+06 322 323 query R 324 SELECT extract(epoch from time '12:00:00') 325 ---- 326 43200 327 328 query error pgcode 22023 extract\(\): unsupported timespan: day 329 SELECT extract(day from time '12:00:00') 330 331 query R 332 SELECT extract('microsecond' from time '12:01:02.345678') 333 ---- 334 2.345678e+06 335 336 query R 337 SELECT extract('EPOCH' from time '12:00:00') 338 ---- 339 43200 340 341 query error pgcode 22023 extract\(\): unsupported timespan: day 342 SELECT extract('day' from time '12:00:00') 343 344 query error pgcode 22023 extract\(\): unsupported timespan: day 345 SELECT extract('DAY' from time '12:00:00') 346 347 subtest precision_tests 348 349 query error precision 7 out of range 350 select '1:00:00.001':::TIME(7) 351 352 statement ok 353 CREATE TABLE time_precision_test ( 354 id integer PRIMARY KEY, 355 t TIME(5) 356 ) 357 358 statement ok 359 INSERT INTO time_precision_test VALUES 360 (1,'12:00:00.123456+03:00'), 361 (2,'12:00:00.12345+03:00'), 362 (3,'12:00:00.1234+03:00'), 363 (4,'12:00:00.123+03:00'), 364 (5,'12:00:00.12+03:00'), 365 (6,'12:00:00.1+03:00'), 366 (7,'12:00:00+03:00') 367 368 query IT 369 SELECT * FROM time_precision_test ORDER BY id ASC 370 ---- 371 1 0000-01-01 12:00:00.12346 +0000 UTC 372 2 0000-01-01 12:00:00.12345 +0000 UTC 373 3 0000-01-01 12:00:00.1234 +0000 UTC 374 4 0000-01-01 12:00:00.123 +0000 UTC 375 5 0000-01-01 12:00:00.12 +0000 UTC 376 6 0000-01-01 12:00:00.1 +0000 UTC 377 7 0000-01-01 12:00:00 +0000 UTC 378 379 query TT 380 select column_name, data_type FROM [SHOW COLUMNS FROM time_precision_test] ORDER BY column_name 381 ---- 382 id INT8 383 t TIME(5) 384 385 statement ok 386 ALTER TABLE time_precision_test ALTER COLUMN t TYPE time(6) 387 388 statement ok 389 INSERT INTO time_precision_test VALUES 390 (100,'12:00:00.123456+03:00') 391 392 query IT 393 SELECT * FROM time_precision_test ORDER BY id ASC 394 ---- 395 1 0000-01-01 12:00:00.12346 +0000 UTC 396 2 0000-01-01 12:00:00.12345 +0000 UTC 397 3 0000-01-01 12:00:00.1234 +0000 UTC 398 4 0000-01-01 12:00:00.123 +0000 UTC 399 5 0000-01-01 12:00:00.12 +0000 UTC 400 6 0000-01-01 12:00:00.1 +0000 UTC 401 7 0000-01-01 12:00:00 +0000 UTC 402 100 0000-01-01 12:00:00.123456 +0000 UTC 403 404 query TT 405 select column_name, data_type FROM [SHOW COLUMNS FROM time_precision_test] ORDER BY column_name 406 ---- 407 id INT8 408 t TIME(6) 409 410 subtest localtime_test 411 412 query B 413 select localtime(3) - localtime <= '1ms'::interval 414 ---- 415 true 416 417 query TTTT 418 select pg_typeof(localtime), pg_typeof(current_time), pg_typeof(localtime(3)), pg_typeof(current_time(3)) 419 ---- 420 time without time zone time with time zone time without time zone time with time zone 421 422 subtest regression_42749 423 424 # cast to string to prove it is 24:00 425 query T 426 SELECT '0000-01-01 24:00:00'::time::string 427 ---- 428 24:00:00 429 430 query T 431 SELECT '2001-01-01 01:24:00'::time 432 ---- 433 0000-01-01 01:24:00 +0000 UTC 434 435 subtest current_time_tests 436 437 statement ok 438 CREATE TABLE current_time_test ( 439 id INTEGER PRIMARY KEY, 440 a TIME(3) DEFAULT CURRENT_TIME, 441 b TIME DEFAULT CURRENT_TIME 442 ) 443 444 statement ok 445 INSERT INTO current_time_test (id) VALUES (1) 446 447 statement ok 448 INSERT INTO current_time_test (id, a, b) VALUES 449 (2, current_time, current_time), 450 (3, current_time, current_time(3)), 451 (4, localtime, localtime(3)) 452 453 query I 454 SELECT id FROM current_time_test WHERE 455 ('1970-01-01 ' || b::string)::timestamp - 456 ('1970-01-01 ' || a::string)::timestamp 457 > '1ms'::interval ORDER BY id ASC 458 ---- 459 460 # test that current_time is correct in different timezones. 461 statement ok 462 set time zone +3 463 464 statement ok 465 create table current_time_tzset_test (id integer, a time, b time) 466 467 statement ok 468 insert into current_time_tzset_test (id, a) values (1, current_time), (2, localtime) 469 470 statement ok 471 set time zone 0 472 473 statement ok 474 update current_time_tzset_test set b = current_time where id = 1 475 476 statement ok 477 update current_time_tzset_test set b = localtime where id = 2 478 479 # a was written at an interval 3 hours ahead, and should persist that way. 480 # make sure they're roughly 3 hours apart. 481 # note time can overflow and result in negative duration, 482 # so test both 3 hour and -21 hour cases. 483 query I 484 select id from current_time_tzset_test WHERE 485 ((a - b) BETWEEN interval '2hr 59m' and interval '3h') OR 486 ((a - b) BETWEEN interval '-21hr -1m' and interval '-21hr') 487 ORDER BY id ASC 488 ---- 489 1 490 2 491 492 # Check default types and expressions get truncated on insert / update. 493 subtest regression_44774 494 495 statement ok 496 CREATE TABLE regression_44774 ( 497 a time(3) DEFAULT '12:13:14.123456' 498 ) 499 500 statement ok 501 INSERT INTO regression_44774 VALUES (default), ('19:21:57.261286') 502 503 query T 504 SELECT a FROM regression_44774 ORDER BY a 505 ---- 506 0000-01-01 12:13:14.123 +0000 UTC 507 0000-01-01 19:21:57.261 +0000 UTC 508 509 statement ok 510 UPDATE regression_44774 511 SET a = '13:14:15.123456'::time + '1 sec'::interval 512 WHERE 1 = 1 513 514 query T 515 SELECT a FROM regression_44774 ORDER BY a 516 ---- 517 0000-01-01 13:14:16.123 +0000 UTC 518 0000-01-01 13:14:16.123 +0000 UTC 519 520 statement ok 521 DROP TABLE regression_44774 522 523 subtest regression_46973 524 525 statement ok 526 CREATE TABLE regression_46973 (a TIME UNIQUE) 527 528 statement ok 529 INSERT INTO regression_46973 VALUES ('23:59:59.999999'), ('24:00') 530 531 query T 532 SELECT * FROM regression_46973 WHERE a != '23:59:59.999999' 533 ---- 534 0000-01-02 00:00:00 +0000 UTC 535 536 query T 537 SELECT * FROM regression_46973 WHERE a != '24:00' 538 ---- 539 0000-01-01 23:59:59.999999 +0000 UTC 540 541 statement ok 542 DROP TABLE regression_46973