# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the logic test suite in CockroachDB. The # original file was retrieved on June 10, 2019 from: # # https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/datetime # # The original source code is subject to the terms of the Apache # 2.0 license, a copy of which can be found in the LICENSE file at the # root of this repository. mode cockroach simple conn=mz_system,user=mz_system ALTER SYSTEM SET unsafe_enable_table_keys = true ---- COMPLETE 0 statement ok CREATE TABLE t ( a TIMESTAMP PRIMARY KEY, b DATE, c INTERVAL, UNIQUE (b), UNIQUE (c), FAMILY (a), FAMILY (b), FAMILY (c) ) statement ok INSERT INTO t VALUES ('2015-08-30 03:34:45.34567', '2015-08-30', '34h2s'), ('2015-08-25 04:45:45.53453', '2015-08-25', '2h45m2s234ms'), ('2015-08-29 23:10:09.98763', '2015-08-29', '234h45m2s234ms') # Spot-check date math. query T SELECT b + '6 month' from t order by a desc ---- 2016-02-29 00:00:00 +0000 UTC 2016-02-29 00:00:00 +0000 UTC 2016-02-25 00:00:00 +0000 UTC query TTT SELECT * FROM t WHERE a = '2015-08-25 04:45:45.53453+01:00'::timestamp ---- 2015-08-25 04:45:45.53453 +0000 +0000 2015-08-25 00:00:00 +0000 +0000 02:45:02.234 # insert duplicate value with different time zone offset statement error duplicate key value \(a\)=\('2015-08-30 03:34:45\.34567\+00:00'\) violates unique constraint "primary" INSERT INTO t VALUES ('2015-08-30 03:34:45.34567-07:00', '2015-08-31', '35h2s') # Check that time/date/interval representations outside of the index are okay. statement ok CREATE TABLE u ( a BIGINT PRIMARY KEY, b TIMESTAMP, c TIMESTAMPTZ, d DATE, e INTERVAL ) statement ok INSERT INTO u VALUES (123, '2015-08-30 03:34:45.34567', '2015-08-30 03:34:45.34567', '2015-08-30', '34h2s'), (234, '2015-08-25 04:45:45.53453-02:00', '2015-08-25 04:45:45.53453-02:00', '2015-08-25', '2h45m2s234ms') statement ok SET TIME ZONE -5 query TTT SELECT DATE '2000-01-01', DATE '2000-12-31', DATE '1993-05-16' ---- 2000-01-01 00:00:00 +0000 +0000 2000-12-31 00:00:00 +0000 +0000 1993-05-16 00:00:00 +0000 +0000 statement ok INSERT INTO u VALUES (345, '2015-08-29 23:10:09.98763', '2015-08-29 23:10:09.98763', '2015-08-29', '234h45m2s234ms'), (456, '2015-08-29 23:10:09.98763 UTC', '2015-08-29 23:10:09.98763 UTC', '2015-08-29', '234h45m2s234ms') query ITTTT SELECT * FROM u ORDER BY a ---- 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 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 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 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 statement ok SET TIME ZONE UTC query ITTTT SELECT * FROM u ORDER BY a ---- 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 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 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 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 statement ok SET TIME ZONE -5 query TTTT SELECT max(b), max(c), max(d), max(e) FROM u ---- 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 query TTTT SELECT min(b), min(c), min(d), min(e) FROM u ---- 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 query BB SELECT now() < now() + '1m'::interval, now() <= now() + '1m'::interval ---- true true query BB SELECT now() + '1m'::interval > now(), now() + '1m'::interval >= now() ---- true true # Date sentinel values. query TTT SELECT 'epoch'::date, 'infinity'::date, '-infinity'::date ---- 1970-01-01 00:00:00 +0000 +0000 infinity -infinity # Date edge cases. statement error year value 0 is out of range SELECT '0000-01-01'::date query TTTTT SELECT '4714-11-24 BC'::date, '5874897-12-31'::date, '2000-01-01'::date, '0001-01-01'::date, '0001-12-13 BC'::date ---- -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 # Also test as strings because lib/pq marshals the previous results to # time.Times, which don't stringify the same. query TTTTT 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 ---- 4714-11-24 BC 5874897-12-31 2000-01-01 0001-01-01 0001-12-13 BC statement error date is out of range SELECT '4714-11-24 BC'::date - 1 statement error date is out of range SELECT '5874897-12-31'::date + 1 query TT SELECT ('4714-11-24 BC'::date + 1)::string, ('5874897-12-31'::date - 1)::string ---- 4714-11-25 BC 5874897-12-30 query TTTT SELECT 'infinity'::date + 1, 'infinity'::date - 1, '-infinity'::date + 1, '-infinity'::date - 1 ---- infinity infinity -infinity -infinity statement error cannot subtract infinite dates SELECT 'infinity'::date - 'infinity'::date query I SELECT '5874897-12-31'::date - '4714-11-24 BC'::date ---- 2147483493 # TIMESTAMP/DATE builtins. query T SELECT age('2001-04-10 22:06:45', '1957-06-13') ---- 384190:06:45 query B SELECT age('1957-06-13') - age(now(), '1957-06-13') = interval '0s' ---- true query B SELECT now() - timestamp '2015-06-13' > interval '100h' ---- true query TT SELECT now()::timestamp - now(), now() - now()::timestamp ---- 00:00:00 00:00:00 query BB SELECT now() = now()::timestamp, now()::timestamp = now() ---- true true query BB SELECT now()::timestamp < now(), now() < now()::timestamp ---- false false query BB SELECT now()::timestamp <= now(), now() <= now()::timestamp ---- true true query B SELECT current_date - current_date() = 0 ---- true query B SELECT now() - current_timestamp() = interval '0s' ---- true query B SELECT now() - current_timestamp = interval '0s' ---- true query B SELECT now() - statement_timestamp() < interval '10s' ---- true query B SELECT clock_timestamp() - statement_timestamp() < interval '10s' ---- true query B SELECT now() - transaction_timestamp() = interval '0s' ---- true statement ok BEGIN TRANSACTION statement ok CREATE TABLE kv ( k CHAR PRIMARY KEY, v TIMESTAMPTZ ) statement ok INSERT INTO kv (k,v) VALUES ('a', transaction_timestamp()) query T SELECT k FROM kv ---- a query T SELECT k FROM kv where v = transaction_timestamp() ---- a statement ok COMMIT TRANSACTION # Changing timezones changes the output of current_date(). statement ok RESET TIME ZONE query BBB SELECT d = tz, d = t, d = n FROM ( SELECT current_date()::DATE AS d, current_date()::TIMESTAMPTZ::DATE AS tz, current_date()::TIMESTAMP::DATE AS t, now():::DATE AS n ) ---- true true true query B SELECT now() - current_date()::timestamptz < interval '24h10s' ---- true statement ok SET TIME ZONE 48 query B SELECT now() - current_date()::timestamptz < interval '24h10s' ---- true query BBB SELECT d = tz, d = t, d = n FROM ( SELECT current_date()::DATE AS d, current_date()::TIMESTAMPTZ::DATE AS tz, current_date()::TIMESTAMP::DATE AS t, now():::DATE AS n ) ---- true true true statement ok RESET TIME ZONE # Check that the current_timestamp, now and transaction_timestamp are the same. # Test that the transaction_timestamp can differ from the statement_timestamp. # Check that the transaction_timestamp changes with each transaction. # We use, SELECT * FROM kv, to insert delays of more than a microsecond. statement ok BEGIN; INSERT INTO kv (k,v) VALUES ('b', transaction_timestamp()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('c', transaction_timestamp()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('d', current_timestamp()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('e', current_timestamp()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('f', now()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('g', now()); SELECT * FROM kv; INSERT INTO kv (k,v) VALUES ('h', statement_timestamp()); SELECT * FROM kv; COMMIT; SELECT * FROM kv; BEGIN; SELECT * FROM KV; INSERT INTO kv (k,v) VALUES ('i', transaction_timestamp()); COMMIT query I SELECT count(DISTINCT (v)) FROM kv ---- 4 # Test that transaction_timestamp() is consistent in transaction # spanning multiple batches of statements. statement ok DELETE FROM kv statement ok BEGIN; INSERT INTO kv (k,v) VALUES ('a', transaction_timestamp()); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES ('b', transaction_timestamp()); SELECT * FROM kv; COMMIT statement ok BEGIN; SELECT * FROM KV; INSERT INTO kv (k,v) VALUES ('c', transaction_timestamp()); COMMIT query I SELECT count(DISTINCT (v)) FROM kv ---- 2 statement ok DROP TABLE kv statement ok CREATE TABLE kv ( k INT PRIMARY KEY, v DECIMAL ) # Test that cluster_logical_timestamp() is consistent in transactions # spanning multiple batches of statements. statement ok BEGIN; INSERT INTO kv (k,v) VALUES (1, cluster_logical_timestamp()); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (2, cluster_logical_timestamp()); SELECT * FROM kv; COMMIT statement ok BEGIN; SELECT * FROM kv; INSERT INTO kv (k,v) VALUES (3, cluster_logical_timestamp()); COMMIT query I SELECT count(DISTINCT (v)) FROM kv ---- 2 statement ok DELETE FROM kv statement ok CREATE TABLE m (mints DECIMAL) statement ok INSERT INTO m VALUES (cluster_logical_timestamp()) # Test that cluster_logical_timestamp() is monotonic in transaction order statement ok INSERT INTO kv (k,v) VALUES (1, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (2, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (3, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (4, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (5, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv statement ok INSERT INTO kv (k,v) VALUES (6, cluster_logical_timestamp()-(select mints from m)); SELECT * FROM kv query I SELECT k FROM kv ORDER BY v ---- 1 2 3 4 5 6 statement ok SET TIME ZONE UTC statement ok CREATE TABLE ex ( k BIGINT PRIMARY KEY, element STRING, input TIMESTAMPTZ, extract_result INT, date_trunc_result TIMESTAMPTZ ) statement ok INSERT INTO ex VALUES (1, 'year', '2001-04-10 12:04:59', 2001, '2001-01-01 00:00:00'), (2, 'year', '2016-02-10 19:46:33.306157519', 2016, '2016-01-01 00:00:00'), (3, 'years', '2016-02-10 19:46:33.306157519', 2016, '2016-01-01 00:00:00'), (4, 'quarter', '2001-04-10 12:04:59', 2, '2001-04-01 00:00:00'), (5, 'quarter', '2016-02-10 19:46:33.306157519', 1, '2016-01-01 00:00:00'), (6, 'quarter', '2016-05-10 19:46:33.306157519', 2, '2016-04-01 00:00:00'), (7, 'quarter', '2016-09-09 19:46:33.306157519', 3, '2016-07-01 00:00:00'), (8, 'quarter', '2016-10-10 19:46:33.306157519', 4, '2016-10-01 00:00:00'), (9, 'month', '2001-04-10 12:04:59', 4, '2001-04-01 00:00:00'), (10, 'month', '2016-02-10 19:46:33.306157519', 2, '2016-02-01 00:00:00'), (11, 'months', '2016-02-10 19:46:33.306157519', 2, '2016-02-01 00:00:00'), (12, 'week', '2001-04-10 12:04:59', 15, '2001-04-08 00:00:00'), (13, 'weeks', '2001-01-05 12:04:59', 1, '2000-12-31 00:00:00'), (14, 'day', '2001-04-10 12:04:59', 10, '2001-04-10 00:00:00'), (15, 'day', '2016-02-10 19:46:33.306157519', 10, '2016-02-10 00:00:00'), (16, 'days', '2016-02-10 19:46:33.306157519', 10, '2016-02-10 00:00:00'), (17, 'dayofweek', '2001-04-10 12:04:59', 2, null), (18, 'dow', '2001-04-12 12:04:59', 4, null), (19, 'dayofyear', '2001-04-10 12:04:59', 100, null), (20, 'doy', '2001-04-12 12:04:59', 102, null), (21, 'epoch', '1970-01-02 00:00:01.000001', 86401, null), (22, 'epoch', '1970-01-02 00:00:01.000001-04', 100801, null), (23, 'epoch', '2001-04-10 12:04:59', 986904299, null), (24, 'hour', '2001-04-10 12:04:59', 12, '2001-04-10 12:00:00'), (25, 'hour', '2016-02-10 19:46:33.306157519', 19, '2016-02-10 19:00:00'), (26, 'hour', '2016-02-10 19:46:33.306157519-04', 23, '2016-02-10 19:00:00-04'), (27, 'hours', '2016-02-10 19:46:33.306157519', 19, '2016-02-10 19:00:00'), (28, 'hours', '2016-02-10 19:46:33.306157519-04', 23, '2016-02-10 19:00:00-04'), (29, 'minute', '2001-04-10 12:04:59', 4, '2001-04-10 12:04:00'), (30, 'minute', '2016-02-10 19:46:33.306157519', 46, '2016-02-10 19:46:00'), (31, 'minutes', '2016-02-10 19:46:33.306157519', 46, '2016-02-10 19:46:00'), (32, 'second', '2001-04-10 12:04:59.234', 59, '2001-04-10 12:04:59'), (33, 'second', '2016-02-10 19:46:33.306157519', 33, '2016-02-10 19:46:33'), (34, 'seconds', '2016-02-10 19:46:33.306157519', 33, '2016-02-10 19:46:33'), (35, 'millisecond', '2001-04-10 12:04:59.234567', 234, '2001-04-10 12:04:59.234'), (36, 'millisecond', '2016-02-10 19:46:33.306157519', 306, '2016-02-10 19:46:33.306'), (37, 'milliseconds', '2016-02-10 19:46:33.306157519', 306, '2016-02-10 19:46:33.306'), (38, 'microsecond', '2001-04-10 12:04:59.34565423', 345654, '2001-04-10 12:04:59.345654'), (39, 'microsecond', '2016-02-10 19:46:33.306157519', 306158, '2016-02-10 19:46:33.306158'), (40, 'microseconds', '2016-02-10 19:46:33.306157519', 306158, '2016-02-10 19:46:33.306158') query IBI SELECT k, extract(element, input::timestamp) = extract_result, extract(element, input::timestamp) FROM ex ORDER BY k ---- 1 true 2001 2 true 2016 3 true 2016 4 true 2 5 true 1 6 true 2 7 true 3 8 true 4 9 true 4 10 true 2 11 true 2 12 true 15 13 true 1 14 true 10 15 true 10 16 true 10 17 true 2 18 true 4 19 true 100 20 true 102 21 true 86401 22 true 100801 23 true 986904299 24 true 12 25 true 19 26 true 23 27 true 19 28 true 23 29 true 4 30 true 46 31 true 46 32 true 59 33 true 33 34 true 33 35 true 234 36 true 306 37 true 306 38 true 345654 39 true 306158 40 true 306158 query error extract\(\): unsupported timespan: nansecond SELECT extract(nansecond from '2001-04-10 12:04:59.34565423'::timestamp) query error unknown unit "nanosecond" SELECT INTERVAL '1 nanosecond'; query error unknown unit "ns" SELECT INTERVAL '1 ns'; query IBI SELECT k, extract(element, input::timestamptz) = extract_result, extract(element, input::timestamptz) FROM ex ORDER BY k ---- 1 true 2001 2 true 2016 3 true 2016 4 true 2 5 true 1 6 true 2 7 true 3 8 true 4 9 true 4 10 true 2 11 true 2 12 true 15 13 true 1 14 true 10 15 true 10 16 true 10 17 true 2 18 true 4 19 true 100 20 true 102 21 true 86401 22 true 100801 23 true 986904299 24 true 12 25 true 19 26 true 23 27 true 19 28 true 23 29 true 4 30 true 46 31 true 46 32 true 59 33 true 33 34 true 33 35 true 234 36 true 306 37 true 306 38 true 345654 39 true 306158 40 true 306158 query error extract\(\): unsupported timespan: nansecond SELECT extract(nansecond from '2001-04-10 12:04:59.34565423'::timestamptz) query I SELECT extract(hour from '2016-02-10 19:46:33.306157519-04'::timestamptz) ---- 19 query I SELECT extract(hours from '2016-02-10 19:46:33.306157519-04'::timestamptz) ---- 19 query IBT SELECT k, date_trunc(element, input::timestamp) = date_trunc_result, date_trunc(element, input::timestamp)::string FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k ---- 1 true 2001-01-01 00:00:00+00:00 2 true 2016-01-01 00:00:00+00:00 3 true 2016-01-01 00:00:00+00:00 4 true 2001-04-01 00:00:00+00:00 5 true 2016-01-01 00:00:00+00:00 6 true 2016-04-01 00:00:00+00:00 7 true 2016-07-01 00:00:00+00:00 8 true 2016-10-01 00:00:00+00:00 9 true 2001-04-01 00:00:00+00:00 10 true 2016-02-01 00:00:00+00:00 11 true 2016-02-01 00:00:00+00:00 12 true 2001-04-08 00:00:00+00:00 13 true 2000-12-31 00:00:00+00:00 14 true 2001-04-10 00:00:00+00:00 15 true 2016-02-10 00:00:00+00:00 16 true 2016-02-10 00:00:00+00:00 24 true 2001-04-10 12:00:00+00:00 25 true 2016-02-10 19:00:00+00:00 26 true 2016-02-10 23:00:00+00:00 27 true 2016-02-10 19:00:00+00:00 28 true 2016-02-10 23:00:00+00:00 29 true 2001-04-10 12:04:00+00:00 30 true 2016-02-10 19:46:00+00:00 31 true 2016-02-10 19:46:00+00:00 32 true 2001-04-10 12:04:59+00:00 33 true 2016-02-10 19:46:33+00:00 34 true 2016-02-10 19:46:33+00:00 35 true 2001-04-10 12:04:59.234+00:00 36 true 2016-02-10 19:46:33.306+00:00 37 true 2016-02-10 19:46:33.306+00:00 38 true 2001-04-10 12:04:59.345654+00:00 39 true 2016-02-10 19:46:33.306158+00:00 40 true 2016-02-10 19:46:33.306158+00:00 query IBT SELECT k, date_trunc(element, input::timestamptz) = date_trunc_result, date_trunc(element, input::timestamptz)::string FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k ---- 1 true 2001-01-01 00:00:00+00:00 2 true 2016-01-01 00:00:00+00:00 3 true 2016-01-01 00:00:00+00:00 4 true 2001-04-01 00:00:00+00:00 5 true 2016-01-01 00:00:00+00:00 6 true 2016-04-01 00:00:00+00:00 7 true 2016-07-01 00:00:00+00:00 8 true 2016-10-01 00:00:00+00:00 9 true 2001-04-01 00:00:00+00:00 10 true 2016-02-01 00:00:00+00:00 11 true 2016-02-01 00:00:00+00:00 12 true 2001-04-08 00:00:00+00:00 13 true 2000-12-31 00:00:00+00:00 14 true 2001-04-10 00:00:00+00:00 15 true 2016-02-10 00:00:00+00:00 16 true 2016-02-10 00:00:00+00:00 24 true 2001-04-10 12:00:00+00:00 25 true 2016-02-10 19:00:00+00:00 26 true 2016-02-10 23:00:00+00:00 27 true 2016-02-10 19:00:00+00:00 28 true 2016-02-10 23:00:00+00:00 29 true 2001-04-10 12:04:00+00:00 30 true 2016-02-10 19:46:00+00:00 31 true 2016-02-10 19:46:00+00:00 32 true 2001-04-10 12:04:59+00:00 33 true 2016-02-10 19:46:33+00:00 34 true 2016-02-10 19:46:33+00:00 35 true 2001-04-10 12:04:59.234+00:00 36 true 2016-02-10 19:46:33.306+00:00 37 true 2016-02-10 19:46:33.306+00:00 38 true 2001-04-10 12:04:59.345654+00:00 39 true 2016-02-10 19:46:33.306158+00:00 40 true 2016-02-10 19:46:33.306158+00:00 query T SELECT date_trunc('hour', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string ---- 2016-02-10 19:00:00-04:00 query T SELECT date_trunc('hours', '2016-02-10 19:46:33.306157519-04'::timestamptz)::string ---- 2016-02-10 19:00:00-04:00 query IBT SELECT k, date_trunc(element, input::date) = date_trunc_result::date, date_trunc(element, input::date)::string FROM ex WHERE date_trunc_result IS NOT NULL ORDER BY k ---- 1 true 2001-01-01 00:00:00+00:00 2 true 2016-01-01 00:00:00+00:00 3 true 2016-01-01 00:00:00+00:00 4 true 2001-04-01 00:00:00+00:00 5 true 2016-01-01 00:00:00+00:00 6 true 2016-04-01 00:00:00+00:00 7 true 2016-07-01 00:00:00+00:00 8 true 2016-10-01 00:00:00+00:00 9 true 2001-04-01 00:00:00+00:00 10 true 2016-02-01 00:00:00+00:00 11 true 2016-02-01 00:00:00+00:00 12 true 2001-04-08 00:00:00+00:00 13 true 2000-12-31 00:00:00+00:00 14 true 2001-04-10 00:00:00+00:00 15 true 2016-02-10 00:00:00+00:00 16 true 2016-02-10 00:00:00+00:00 24 true 2001-04-10 00:00:00+00:00 25 true 2016-02-10 00:00:00+00:00 26 true 2016-02-10 00:00:00+00:00 27 true 2016-02-10 00:00:00+00:00 28 true 2016-02-10 00:00:00+00:00 29 true 2001-04-10 00:00:00+00:00 30 true 2016-02-10 00:00:00+00:00 31 true 2016-02-10 00:00:00+00:00 32 true 2001-04-10 00:00:00+00:00 33 true 2016-02-10 00:00:00+00:00 34 true 2016-02-10 00:00:00+00:00 35 true 2001-04-10 00:00:00+00:00 36 true 2016-02-10 00:00:00+00:00 37 true 2016-02-10 00:00:00+00:00 38 true 2001-04-10 00:00:00+00:00 39 true 2016-02-10 00:00:00+00:00 40 true 2016-02-10 00:00:00+00:00 query T SELECT (timestamp '2016-02-10 19:46:33.306157519')::string ---- 2016-02-10 19:46:33.306158+00:00 query T SELECT (timestamptz '2016-02-10 19:46:33.306157519')::string ---- 2016-02-10 19:46:33.306158+00:00 # Test SET TIME ZONE # default time zone of UTC query T SELECT '2015-08-25 05:45:45.53453'::timestamp ---- 2015-08-25 05:45:45.53453 +0000 +0000 query T SELECT '2015-08-25 05:45:45.53453'::timestamp ---- 2015-08-25 05:45:45.53453 +0000 +0000 statement ok SET TIME ZONE 'Europe/Rome' query error pq: unimplemented: timestamp abbreviations not supported SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false statement ok SET TIME ZONE +1 query error pq: unimplemented: timestamp abbreviations not supported SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false query T SELECT '2015-08-25 05:45:45.53453'::timestamp ---- 2015-08-25 05:45:45.53453 +0000 +0000 query T SELECT '2015-08-25 05:45:45.53453'::timestamptz ---- 2015-08-25 05:45:45.53453 +0100 +0100 query T SELECT '2015-08-25 05:45:45-01:00'::timestamp ---- 2015-08-25 05:45:45 +0000 +0000 query T SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz ---- 2015-08-25 05:45:45 +0100 +0100 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp ---- 2015-08-25 07:45:45 +0000 +0000 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz ---- 2015-08-25 07:45:45 +0100 +0100 # alias test: TIMEZONE instead of TIME ZONE statement ok SET TIMEZONE = +2 query error pq: unimplemented: timestamp abbreviations not supported SELECT '2015-08-25 05:45:45.53453 CET'::timestamptz WHERE false query T SELECT '2015-08-25 05:45:45.53453'::timestamp ---- 2015-08-25 05:45:45.53453 +0000 +0000 query T SELECT '2015-08-25 05:45:45.53453'::timestamptz ---- 2015-08-25 05:45:45.53453 +0200 +0200 query T SELECT '2015-08-25 05:45:45-01:00'::timestamp ---- 2015-08-25 05:45:45 +0000 +0000 query T SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz ---- 2015-08-25 05:45:45 +0200 +0200 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp ---- 2015-08-25 08:45:45 +0000 +0000 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz ---- 2015-08-25 08:45:45 +0200 +0200 statement ok SET TIME ZONE -5 query T SELECT '2015-08-24 23:45:45.53453'::timestamp ---- 2015-08-24 23:45:45.53453 +0000 +0000 query T SELECT '2015-08-24 23:45:45.53453'::timestamptz ---- 2015-08-24 23:45:45.53453 -0500 -0500 query T SELECT '2015-08-24 23:45:45.53453 UTC'::timestamp ---- 2015-08-24 23:45:45.53453 +0000 +0000 query T SELECT '2015-08-24 23:45:45.53453 UTC'::timestamptz ---- 2015-08-24 18:45:45.53453 -0500 -0500 query T SELECT '2015-08-24 23:45:45.53453-02:00'::timestamp ---- 2015-08-24 23:45:45.53453 +0000 +0000 query T SELECT '2015-08-24 23:45:45.53453-02:00'::timestamptz ---- 2015-08-24 20:45:45.53453 -0500 -0500 query T SELECT '2015-08-24 23:45:45.53453-05:00'::timestamptz ---- 2015-08-24 23:45:45.53453 -0500 -0500 query T SELECT '2015-08-24 23:45:45.534 -02:00'::timestamp ---- 2015-08-24 23:45:45.534 +0000 +0000 query T SELECT '2015-08-24 23:45:45.534 -02:00'::timestamptz ---- 2015-08-24 20:45:45.534 -0500 -0500 query T SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz ---- 2015-08-25 05:45:45 -0500 -0500 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp ---- 2015-08-25 01:45:45 +0000 +0000 # using Eastern instead of fixed -5 should handle DST. statement ok SET TIME ZONE 'America/New_York' query T SELECT '2015-08-25 05:45:45-01:00'::timestamp::timestamptz ---- 2015-08-25 05:45:45 -0400 -0400 query T SELECT '2015-08-25 05:45:45-01:00'::timestamptz::timestamp ---- 2015-08-25 02:45:45 +0000 +0000 statement error cannot find time zone "foobar" SET TIME ZONE 'foobar' statement ok SET TIME ZONE default query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0000 UTC statement ok SET TIME ZONE local query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0000 UTC statement ok SET TIME ZONE 'DEFAULT' query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0000 UTC statement ok SET TIME ZONE '' query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0000 UTC statement ok SET TIME ZONE INTERVAL '-7h' query T SELECT '2015-08-24 21:45:45.53453'::timestamp ---- 2015-08-24 21:45:45.53453 +0000 +0000 query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 -0700 -0700 statement ok SET TIME ZONE -7.5 query T SELECT '2015-08-24 21:45:45.53453'::timestamp ---- 2015-08-24 21:45:45.53453 +0000 +0000 query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 -0730 -0730 query T SELECT '2015-08-24 21:45:45.53453 UTC'::timestamptz ---- 2015-08-24 14:15:45.53453 -0730 -0730 statement ok SET TIME ZONE LOCAL query T SELECT '2015-08-25 04:45:45.53453'::timestamp ---- 2015-08-25 04:45:45.53453 +0000 +0000 statement ok SET TIME ZONE DEFAULT query T SELECT '2015-08-25 04:45:45.53453'::timestamp ---- 2015-08-25 04:45:45.53453 +0000 +0000 # reset for what follows. statement ok SET TIME ZONE 'UTC' # Check that casting from a timestamp to a date and vice versa # uses the time zone. query TTTT SELECT b, b::date, c, c::date FROM u WHERE a = 123 ---- 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 query T SELECT d::timestamp FROM u WHERE a = 123 ---- 2015-08-30 00:00:00 +0000 +0000 statement ok SET TIME ZONE -5 query TTTT SELECT b, b::date, c, c::date FROM u WHERE a = 123 ---- 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 query T SELECT d::timestamp FROM u WHERE a = 123 ---- 2015-08-30 00:00:00 +0000 +0000 statement ok SET TIME ZONE UTC statement ok CREATE TABLE tz ( a INT PRIMARY KEY, b TIMESTAMP, c TIMESTAMPTZ, d TIMESTAMPTZ ) query TTBTTTB SHOW COLUMNS FROM tz ---- a INT8 false NULL · {primary} false b TIMESTAMP true NULL · {} false c TIMESTAMPTZ true NULL · {} false d TIMESTAMPTZ true NULL · {} false statement ok INSERT INTO tz VALUES (1, timestamp '2015-08-30 03:34:45', timestamptz '2015-08-30 03:34:45', timestamptz '2015-08-30 03:34:45'), (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') statement ok SET TIME ZONE -2 query ITT SELECT a, b, c FROM tz ORDER BY a ---- 1 2015-08-30 03:34:45 +0000 +0000 2015-08-30 01:34:45 -0200 -0200 2 2015-08-30 03:34:45 +0000 +0000 2015-08-30 00:34:45 -0200 -0200 query TTTT SELECT b + interval '1m', interval '1m' + b, c + interval '1m', interval '1m' + c FROM tz WHERE a = 1 ---- 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 query I SELECT a FROM tz WHERE c = d ---- 1 query I rowsort SELECT a FROM tz WHERE c <= d ---- 1 2 query I SELECT a FROM tz WHERE c < d ---- 2 query I rowsort SELECT a FROM tz WHERE b = c::timestamp ---- query I rowsort SELECT a FROM tz WHERE c = d::timestamp ---- 1 # reset for what follows. statement ok SET TIME ZONE 'UTC' statement ok SET TIME ZONE -5 query T SHOW TIME ZONE ---- -5 statement ok SET TIME ZONE INTERVAL '+04:00' HOUR TO MINUTE query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0400 +0400 statement ok SET TIME ZONE INTERVAL '-04:00' MINUTE TO SECOND query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 -0400 -0400 # alias test: TIMEZONE instead of TIME ZONE statement ok SET TIMEZONE TO INTERVAL '+05:00' HOUR TO MINUTE query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 +0500 +0500 statement ok SET TIMEZONE TO INTERVAL '-05:00' MINUTE TO SECOND query T SELECT '2015-08-24 21:45:45.53453'::timestamptz ---- 2015-08-24 21:45:45.53453 -0500 -0500 statement ok SET TIME ZONE 0 query T SHOW TIME ZONE ---- 0 query T SELECT DATE '1999-01-01' + INTERVAL '4 minutes' ---- 1999-01-01 00:04:00 +0000 UTC query T SELECT INTERVAL '4 minutes' + DATE '1999-01-01' ---- 1999-01-01 00:04:00 +0000 UTC query T SELECT DATE '1999-01-01' - INTERVAL '4 minutes' ---- 1998-12-31 23:56:00 +0000 UTC query B SELECT DATE '1999-01-02' < TIMESTAMPTZ '1999-01-01' ---- false query B SELECT DATE '1999-01-02' < TIMESTAMP '1999-01-01' ---- false query B SELECT DATE '1999-01-02' <= TIMESTAMPTZ '1999-01-01' ---- false query B SELECT DATE '1999-01-02' <= TIMESTAMP '1999-01-01' ---- false query B SELECT DATE '1999-01-02' <= TIMESTAMPTZ '1999-01-02' ---- true query B SELECT DATE '1999-01-02' <= TIMESTAMP '1999-01-02' ---- true query B SELECT DATE '1999-01-02' > TIMESTAMPTZ '1999-01-01' ---- true query B SELECT DATE '1999-01-02' > TIMESTAMP '1999-01-01' ---- true query B SELECT DATE '1999-01-02' >= TIMESTAMPTZ '1999-01-01' ---- true query B SELECT DATE '1999-01-02' >= TIMESTAMP '1999-01-01' ---- true query B SELECT DATE '1999-01-02' = TIMESTAMPTZ '1999-01-01' ---- false query B SELECT DATE '1999-01-01' = TIMESTAMP '1999-01-01' ---- true ## Test parsing of unitless interval constants with field specifiers query TTTTT SELECT INTERVAL '5', INTERVAL '5' SECOND, INTERVAL '5' MINUTE TO SECOND, INTERVAL '5' HOUR TO SECOND, INTERVAL '5' DAY TO SECOND; ---- 00:00:05 00:00:05 00:00:05 00:00:05 00:00:05 query TTT SELECT INTERVAL '5' MINUTE, INTERVAL '5' HOUR TO MINUTE, INTERVAL '5' DAY TO MINUTE; ---- 00:05:00 00:05:00 00:05:00 query TT SELECT INTERVAL '5' HOUR, INTERVAL '5' DAY TO HOUR; ---- 05:00:00 05:00:00 query T SELECT INTERVAL '5' DAY; ---- 5 days query TT SELECT INTERVAL '5' MONTH, INTERVAL '5' YEAR TO MONTH; ---- 5 mons 5 mons query T SELECT INTERVAL '5' YEAR ---- 5 years ## Test truncation via field specifiers query TTTT 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; ---- 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 query TTT 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; ---- 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 query TT SELECT INTERVAL '1-2 3 4:5:6' HOUR, INTERVAL '1-2 3 4:5:6' DAY TO HOUR ---- 1 year 2 mons 3 days 04:00:00 1 year 2 mons 3 days 04:00:00 query T SELECT INTERVAL '1-2 3 4:5:6' DAY; ---- 1 year 2 mons 3 days query TT SELECT INTERVAL '1-2 3 4:5:6' MONTH, INTERVAL '1-2 3 4:5:6' YEAR TO MONTH; ---- 1 year 2 mons 1 year 2 mons query T SELECT INTERVAL '1-2 3 4:5:6' YEAR ---- 1 year # Test regression, database-issues#6155. When a built-in returns a datum that does not # match the function signature, distSQL will panic on table scans. statement ok CREATE TABLE topics ( ts TIMESTAMP, tstz TIMESTAMPTZ, "date" DATE ); statement ok INSERT INTO topics VALUES ( '2017-12-05 04:04:04.913231+00:00', '2017-12-05 04:04:04.913231+00:00', '2017-12-05 04:04:04.913231+00:00' ); query T SELECT date_trunc('month', ts) AS date_trunc_month_created_at FROM "topics"; ---- 2017-12-01 00:00:00 +0000 +0000 query T SELECT date_trunc('month', tstz) AS date_trunc_month_created_at FROM "topics"; ---- 2017-12-01 00:00:00 +0000 UTC query T SELECT date_trunc('month', "date") AS date_trunc_month_created_at FROM "topics"; ---- 2017-12-01 00:00:00 +0000 UTC # Test negative years to ensure they can round-trip through the parser. # Also ensure that we don't trigger any of the "convenience" rules. # Update: dates now have a much more limited range such that the original # dates from this issue are no longer possible to express. subtest regression_35255 statement error date is out of range SELECT '-56325279622-12-26'::DATE statement error date is out of range SELECT '-5632-12-26'::DATE query T SELECT '-563-12-26'::DATE ---- -0563-12-26 00:00:00 +0000 +0000 query T SELECT '-56-12-26'::DATE ---- -0056-12-26 00:00:00 +0000 +0000 query T SELECT '-5-12-26'::DATE ---- -0005-12-26 00:00:00 +0000 +0000 # Update: dates now have a much more limited range such that the original # dates from this issue are no longer possible to express. subtest regression_36146 statement error out of range WITH w (c) AS (VALUES (NULL), (NULL)) SELECT '1971-03-18'::DATE + 300866802885581286 FROM w ORDER BY c statement error out of range SELECT '1971-03-18'::DATE + 300866802885581286 # Update: dates now have a much more limited range such that the original # dates from this issue are no longer possible to express. subtest regression_36557 statement error out of range SELECT 7133080445639580613::INT8 + '1977-11-03'::DATE statement error out of range SELECT '-239852040018-04-28':::DATE statement error out of range SELECT(7133080445639580613::INT8 + '1977-11-03'::DATE) = '-239852040018-04-28':::DATE subtest interval_math query TTTTTTT SELECT i, i / 2::INT8, i * 2::INT8, i / 2::FLOAT8, i * 2::FLOAT8, i / .2362::FLOAT8, i * .2362::FLOAT8 FROM ( VALUES ('1 day'::INTERVAL), ('1 month'::INTERVAL), ('1 hour'::INTERVAL), ('1 month 2 days 4 hours'::INTERVAL) ) AS v (i) ORDER BY i ---- 01:00:00 00:30:00 02:00:00 00:30:00 02:00:00 04:14:01.320914 00:14:10.32 1 day 12:00:00 2 days 12:00:00 2 days 4 days 05:36:31.701948 05:40:07.68 1 mon 15 days 2 mons 15 days 2 mons 4 mons 7 days 00:15:51.058425 7 days 02:03:50.4 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