github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/timestamp (about) 1 query T 2 SELECT '2000-05-05 10:00:00+03':::TIMESTAMP 3 ---- 4 2000-05-05 10:00:00 +0000 +0000 5 6 statement ok 7 CREATE TABLE a (a int); INSERT INTO a VALUES(1) 8 9 # Ensure that timestamp serialization doesn't break even if the computation is 10 # distributed: #28110. 11 12 query T 13 SELECT '2000-05-05 10:00:00+03':::TIMESTAMP FROM a 14 ---- 15 2000-05-05 10:00:00 +0000 +0000 16 17 query T 18 select '1-1-18 1:00:00.001-8':::TIMESTAMPTZ 19 ---- 20 2001-01-18 09:00:00.001 +0000 UTC 21 22 # Test timezone() and ... AT TIME ZONE functions. 23 subtest timezone 24 25 statement ok 26 SET TIME ZONE 'PST8PDT' 27 28 query TT 29 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST', timezone('MST', TIMESTAMP '2001-02-16 20:38:40') 30 ---- 31 2001-02-16 19:38:40 -0800 PST 2001-02-16 19:38:40 -0800 PST 32 33 query TT 34 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST', timezone('MST', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05') 35 ---- 36 2001-02-16 18:38:40 +0000 +0000 2001-02-16 18:38:40 +0000 +0000 37 38 query TT 39 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST', timezone('MST', TIMESTAMP '2001-02-16 20:38:40') 40 ---- 41 2001-02-16 19:38:40 -0800 PST 2001-02-16 19:38:40 -0800 PST 42 43 # Test timestamp precisions 44 subtest timestamp_precision 45 46 query error precision 7 out of range 47 select '1-1-18 1:00:00.001':::TIMESTAMP(7) 48 49 query error precision 7 out of range 50 select '1-1-18 1:00:00.001':::TIMESTAMPTZ(7) 51 52 query T 53 select '1-1-18 1:00:00.001':::TIMESTAMP(0) 54 ---- 55 2001-01-18 01:00:00 +0000 +0000 56 57 query T 58 select '1-1-18 1:00:00.001':::TIMESTAMP(6) 59 ---- 60 2001-01-18 01:00:00.001 +0000 +0000 61 62 query T 63 select '1-1-18 1:00:00.001':::TIMESTAMP 64 ---- 65 2001-01-18 01:00:00.001 +0000 +0000 66 67 query T 68 select '1-1-18 1:00:00.001-8':::TIMESTAMPTZ(0) 69 ---- 70 2001-01-18 01:00:00 -0800 PST 71 72 query T 73 select '1-1-18 1:00:00.001-8':::TIMESTAMPTZ(6) 74 ---- 75 2001-01-18 01:00:00.001 -0800 PST 76 77 query B 78 select current_timestamp(3) - current_timestamp <= '1ms'::interval 79 ---- 80 true 81 82 statement ok 83 CREATE TABLE timestamp_test ( 84 id integer PRIMARY KEY, 85 t TIMESTAMP(5), 86 ttz TIMESTAMPTZ(4) 87 ) 88 89 statement ok 90 INSERT INTO timestamp_test VALUES 91 (1, '2001-01-01 12:00:00.123456', '2001-01-01 12:00:00.123456+4'), 92 (2, '2001-01-01 12:00:00.12345', '2001-01-01 12:00:00.12345+4'), 93 (3, '2001-01-01 12:00:00.1234', '2001-01-01 12:00:00.1234+4'), 94 (4, '2001-01-01 12:00:00.123', '2001-01-01 12:00:00.123+4'), 95 (5, '2001-01-01 12:00:00.12', '2001-01-01 12:00:00.12+4'), 96 (6, '2001-01-01 12:00:00.1', '2001-01-01 12:00:00.1+4'), 97 (7, '2001-01-01 12:00:00', '2001-01-01 12:00:00+4') 98 99 query ITT 100 SELECT * FROM timestamp_test ORDER BY id ASC 101 ---- 102 1 2001-01-01 12:00:00.12346 +0000 +0000 2001-01-01 00:00:00.1235 -0800 PST 103 2 2001-01-01 12:00:00.12345 +0000 +0000 2001-01-01 00:00:00.1235 -0800 PST 104 3 2001-01-01 12:00:00.1234 +0000 +0000 2001-01-01 00:00:00.1234 -0800 PST 105 4 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00.123 -0800 PST 106 5 2001-01-01 12:00:00.12 +0000 +0000 2001-01-01 00:00:00.12 -0800 PST 107 6 2001-01-01 12:00:00.1 +0000 +0000 2001-01-01 00:00:00.1 -0800 PST 108 7 2001-01-01 12:00:00 +0000 +0000 2001-01-01 00:00:00 -0800 PST 109 110 query TT 111 select column_name, data_type FROM [SHOW COLUMNS FROM timestamp_test] ORDER BY column_name 112 ---- 113 id INT8 114 t TIMESTAMP(5) 115 ttz TIMESTAMPTZ(4) 116 117 query ITTTT 118 SELECT id, t::timestamp(0), t::timestamp(3), ttz::timestamptz(0), ttz::timestamptz(3) FROM timestamp_test ORDER BY id 119 ---- 120 1 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.124 -0800 PST 121 2 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.124 -0800 PST 122 3 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.123 -0800 PST 123 4 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.123 -0800 PST 124 5 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.12 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.12 -0800 PST 125 6 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00.1 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00.1 -0800 PST 126 7 2001-01-01 12:00:00 +0000 +0000 2001-01-01 12:00:00 +0000 +0000 2001-01-01 00:00:00 -0800 PST 2001-01-01 00:00:00 -0800 PST 127 128 # Altering type to more units of precision should work. 129 statement ok 130 ALTER TABLE timestamp_test ALTER COLUMN t TYPE timestamp 131 132 statement ok 133 ALTER TABLE timestamp_test ALTER COLUMN ttz TYPE timestamptz(5) 134 135 statement ok 136 INSERT INTO timestamp_test VALUES 137 (100, '2001-01-01 12:00:00.123456', '2001-01-01 12:00:00.123456+4') 138 139 query ITT 140 SELECT * FROM timestamp_test ORDER BY id ASC 141 ---- 142 1 2001-01-01 12:00:00.12346 +0000 +0000 2001-01-01 00:00:00.1235 -0800 PST 143 2 2001-01-01 12:00:00.12345 +0000 +0000 2001-01-01 00:00:00.1235 -0800 PST 144 3 2001-01-01 12:00:00.1234 +0000 +0000 2001-01-01 00:00:00.1234 -0800 PST 145 4 2001-01-01 12:00:00.123 +0000 +0000 2001-01-01 00:00:00.123 -0800 PST 146 5 2001-01-01 12:00:00.12 +0000 +0000 2001-01-01 00:00:00.12 -0800 PST 147 6 2001-01-01 12:00:00.1 +0000 +0000 2001-01-01 00:00:00.1 -0800 PST 148 7 2001-01-01 12:00:00 +0000 +0000 2001-01-01 00:00:00 -0800 PST 149 100 2001-01-01 12:00:00.123456 +0000 +0000 2001-01-01 00:00:00.12346 -0800 PST 150 151 query TT 152 select column_name, data_type FROM [SHOW COLUMNS FROM timestamp_test] ORDER BY column_name 153 ---- 154 id INT8 155 t TIMESTAMP 156 ttz TIMESTAMPTZ(5) 157 158 subtest regression_timestamp_comparison 159 160 statement ok 161 SET TIME ZONE -5 162 163 query B 164 SELECT '2001-01-01'::date = '2001-01-01 00:00:00'::timestamp 165 ---- 166 true 167 168 query B 169 SELECT '2001-01-01'::date = '2001-01-01 00:00:00-5'::timestamptz 170 ---- 171 true 172 173 query B 174 SELECT '2001-01-01 00:00:00'::timestamp = '2001-01-01 01:00:00-4'::timestamptz 175 ---- 176 true 177 178 subtest regression_django-cockroachdb_47 179 180 statement ok 181 SET TIME ZONE -3 182 183 query R 184 SELECT extract(hour FROM '2001-01-01 13:00:00+01'::timestamptz) 185 ---- 186 9 187 188 query R 189 SELECT extract(hour FROM '2001-01-01 13:00:00'::timestamp) 190 ---- 191 13 192 193 query R 194 SELECT extract(timezone FROM '2001-01-01 13:00:00+01:15'::timestamptz) 195 ---- 196 -10800 197 198 statement ok 199 SET TIME ZONE +3 200 201 query R 202 SELECT extract(hour FROM '2001-01-01 13:00:00+01'::timestamptz) 203 ---- 204 15 205 206 query R 207 SELECT extract(hour FROM '2001-01-01 13:00:00'::timestamp) 208 ---- 209 13 210 211 query R 212 SELECT extract(timezone FROM '2001-01-01 13:00:00+01:15'::timestamptz) 213 ---- 214 10800 215 216 subtest regression_41776 217 218 statement ok 219 SET TIME ZONE 'GMT+1' 220 221 query T 222 SELECT '2001-01-01 00:00:00'::TIMESTAMP::TIMESTAMPTZ 223 ---- 224 2001-01-01 00:00:00 -0100 -0100 225 226 statement ok 227 SET TIME ZONE '+1:00' 228 229 query T 230 SELECT '2001-01-01 00:00:00'::TIMESTAMP::TIMESTAMPTZ 231 ---- 232 2001-01-01 00:00:00 -0100 -0100 233 234 235 # test that current_timestamp is correct in different timezones. 236 subtest current_timestamp_correct_in_timezone 237 238 statement ok 239 set time zone +3 240 241 statement ok 242 create table current_timestamp_test (a timestamp, b timestamptz) 243 244 statement ok 245 insert into current_timestamp_test values (current_timestamp, current_timestamp) 246 247 statement ok 248 set time zone 0 249 250 # a was written at an interval 3 hours ahead, and should persist that way. 251 # b will remember the timezone, so should be "constant" for comparison's sake. 252 query TT 253 select * from current_timestamp_test WHERE a - interval '3h' <> b 254 ---- 255 256 subtest localtimestamp_test 257 258 query TTTT 259 select pg_typeof(localtimestamp), pg_typeof(current_timestamp), pg_typeof(localtimestamp(3)), pg_typeof(current_timestamp(3)) 260 ---- 261 timestamp without time zone timestamp with time zone timestamp without time zone timestamp with time zone 262 263 query B 264 select localtimestamp(3) - localtimestamp <= '1ms'::interval 265 ---- 266 true 267 268 # When doing daylight savings comparisons, ensure they compare correctly. 269 # Test day before and after DST. 270 subtest regression_django-cockroachdb_120 271 272 statement ok 273 SET TIME ZONE 'America/Chicago' 274 275 query B 276 SELECT '2011-03-13'::date = '2011-03-13'::timestamp 277 ---- 278 true 279 280 query B 281 SELECT '2011-03-13'::date = '2011-03-13'::timestamptz 282 ---- 283 true 284 285 query B 286 SELECT '2011-03-13'::timestamp = '2011-03-13'::timestamptz 287 ---- 288 true 289 290 query B 291 SELECT '2011-03-14'::date = '2011-03-14'::timestamp 292 ---- 293 true 294 295 query B 296 SELECT '2011-03-14'::date = '2011-03-14'::timestamptz 297 ---- 298 true 299 300 query B 301 SELECT '2011-03-14'::timestamp = '2011-03-14'::timestamptz 302 ---- 303 true 304 305 statement ok 306 SET TIME ZONE 0 307 308 # Check default types and expressions get truncated on insert / update. 309 subtest regression_44774 310 311 statement ok 312 CREATE TABLE regression_44774 ( 313 a timestamp(3) DEFAULT '1970-02-03 12:13:14.123456', 314 b timestamptz(3) DEFAULT '1970-02-03 12:13:14.123456' 315 ) 316 317 statement ok 318 INSERT INTO regression_44774 VALUES (default, default), ('2020-02-05 19:21:57.261286', '2020-02-05 19:21:57.261286') 319 320 query TT 321 SELECT a, b FROM regression_44774 ORDER BY a 322 ---- 323 1970-02-03 12:13:14.123 +0000 +0000 1970-02-03 12:13:14.123 +0000 +0000 324 2020-02-05 19:21:57.261 +0000 +0000 2020-02-05 19:21:57.261 +0000 +0000 325 326 statement ok 327 UPDATE regression_44774 328 SET a = '1970-03-04 13:14:15.123456'::timestamp + '1 sec'::interval, b = '1970-03-04 13:14:15.123456'::timestamptz + '1 sec'::interval 329 WHERE 1 = 1 330 331 query TT 332 SELECT a, b FROM regression_44774 ORDER BY a 333 ---- 334 1970-03-04 13:14:16.123 +0000 +0000 1970-03-04 13:14:16.123 +0000 +0000 335 1970-03-04 13:14:16.123 +0000 +0000 1970-03-04 13:14:16.123 +0000 +0000 336 337 statement ok 338 DROP TABLE regression_44774 339 340 # Test for timestamptz math with interval involving DST. 341 subtest regression-cockroachdb/django-cockroachdb_57 342 343 statement ok 344 SET TIME ZONE 'America/Chicago' 345 346 query T 347 WITH a(a) AS ( VALUES 348 ('2010-11-06 23:59:00'::timestamptz + '24 hours'::interval), -- no offset specified 349 ('2010-11-06 23:59:00'::timestamptz + '1 day'::interval), 350 ('2010-11-06 23:59:00'::timestamptz + '1 month'::interval), 351 ('2010-11-07 23:59:00'::timestamptz - '24 hours'::interval), 352 ('2010-11-07 23:59:00'::timestamptz - '1 day'::interval), 353 ('2010-11-07 23:59:00'::timestamptz - '1 month'::interval), 354 ('2010-11-06 23:59:00-05'::timestamptz + '24 hours'::interval), -- offset at time zone 355 ('2010-11-06 23:59:00-05'::timestamptz + '1 day'::interval), 356 ('2010-11-06 23:59:00-05'::timestamptz + '1 month'::interval), 357 ('2010-11-07 23:59:00-06'::timestamptz - '24 hours'::interval), 358 ('2010-11-07 23:59:00-06'::timestamptz - '1 day'::interval), 359 ('2010-11-07 23:59:00-06'::timestamptz - '1 month'::interval), 360 ('2010-11-06 23:59:00-04'::timestamptz + '24 hours'::interval), -- different offset 361 ('2010-11-06 23:59:00-04'::timestamptz + '1 day'::interval), 362 ('2010-11-06 23:59:00-04'::timestamptz + '1 month'::interval), 363 ('2010-11-07 23:59:00-04'::timestamptz - '24 hours'::interval), 364 ('2010-11-07 23:59:00-04'::timestamptz - '1 day'::interval), 365 ('2010-11-07 23:59:00-04'::timestamptz - '1 month'::interval) 366 ) select * from a; 367 ---- 368 2010-11-07 22:59:00 -0600 CST 369 2010-11-07 23:59:00 -0600 CST 370 2010-12-06 23:59:00 -0600 CST 371 2010-11-07 00:59:00 -0500 CDT 372 2010-11-06 23:59:00 -0500 CDT 373 2010-10-07 23:59:00 -0500 CDT 374 2010-11-07 22:59:00 -0600 CST 375 2010-11-07 23:59:00 -0600 CST 376 2010-12-06 23:59:00 -0600 CST 377 2010-11-07 00:59:00 -0500 CDT 378 2010-11-06 23:59:00 -0500 CDT 379 2010-10-07 23:59:00 -0500 CDT 380 2010-11-07 21:59:00 -0600 CST 381 2010-11-07 22:59:00 -0600 CST 382 2010-12-06 22:59:00 -0600 CST 383 2010-11-06 22:59:00 -0500 CDT 384 2010-11-06 21:59:00 -0500 CDT 385 2010-10-07 21:59:00 -0500 CDT 386 387 statement ok 388 CREATE TABLE example (a timestamptz) 389 390 statement ok 391 INSERT INTO example VALUES 392 ('2010-11-06 23:59:00'), 393 ('2010-11-07 23:59:00') 394 395 query TTTTTTTTT 396 SELECT 397 a + '24 hours'::interval, a + '1 day'::interval, a + '1 month'::interval, 398 a - '24 hours'::interval, a - '1 day'::interval, a - '1 month'::interval, 399 a - '2010-11-06 23:59:00'::timestamptz, 400 a - '2010-11-07 23:59:00'::timestamptz, 401 a::string 402 FROM example 403 ORDER BY a 404 ---- 405 2010-11-07 22:59:00 -0600 CST 2010-11-07 23:59:00 -0600 CST 2010-12-06 23:59:00 -0600 CST 2010-11-05 23:59:00 -0500 CDT 2010-11-05 23:59:00 -0500 CDT 2010-10-06 23:59:00 -0500 CDT 00:00:00 -25:00:00 2010-11-06 23:59:00-05:00 406 2010-11-08 23:59:00 -0600 CST 2010-11-08 23:59:00 -0600 CST 2010-12-07 23:59:00 -0600 CST 2010-11-07 00:59:00 -0500 CDT 2010-11-06 23:59:00 -0500 CDT 2010-10-07 23:59:00 -0500 CDT 25:00:00 00:00:00 2010-11-07 23:59:00-06:00 407 408 statement ok 409 DROP TABLE example 410 411 statement ok 412 SET TIME ZONE 0 413 414 subtest regression_46973 415 416 statement ok 417 CREATE TABLE regression_46973(c0 TIMESTAMP UNIQUE, c1 TIMESTAMPTZ UNIQUE) 418 419 statement ok 420 INSERT INTO regression_46973 VALUES ('1970-01-01 00:00:00', '1970-01-01 00:00:00') 421 422 statement error "292277026596-12-04T15:30:08Z" exceeds supported timestamp bounds 423 SELECT * FROM regression_46973 WHERE (-9223372036854775808)::TIMESTAMP!=regression_46973.c0 424 425 statement error "292277026596-12-04T15:30:08Z" exceeds supported timestamp bounds 426 SELECT * FROM regression_46973 WHERE (-9223372036854775808)::TIMESTAMPTZ!=regression_46973.c1 427 428 statement error "294277-01-01T00:00:00Z" exceeds supported timestamp bounds 429 SELECT '294276-12-31 23:59:59.999999'::TIMESTAMP(0) 430 431 statement ok 432 DROP TABLE regression_46973