Materialize/test/sqllogictest/cockroach/datetime.slt

# 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