dates-times.slt

# 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.

mode cockroach

statement ok
CREATE TABLE dateish (
    a DATE
)

statement ok
INSERT INTO dateish VALUES (DATE '2000-01-01'), (DATE '2019-12-31')

query T rowsort
SELECT a FROM dateish
----
2000-01-01
2019-12-31

query T
SELECT max(a) FROM dateish
----
2019-12-31

query T
SELECT min(a) FROM dateish
----
2000-01-01

statement ok
CREATE TABLE timestampish (
    b timestamp
)

statement ok
INSERT INTO timestampish
VALUES
    (TIMESTAMP '1969-06-01 10:10:10.410'),
    (TIMESTAMP '1997-02-03 11:12:59.9'),
    (TIMESTAMP '2020-01-01 1:2:3.789')

query T rowsort
SELECT b FROM timestampish
----
1969-06-01 10:10:10.41
1997-02-03 11:12:59.9
2020-01-01 01:02:03.789

query T
SELECT max(b) FROM timestampish
----
2020-01-01 01:02:03.789

query T
SELECT min(b) FROM timestampish
----
1969-06-01 10:10:10.41

query T
SELECT INTERVAL '1' MONTH
----
1 month

query T
SELECT INTERVAL '1' YEAR
----
1 year

query T
SELECT INTERVAL '1-3' YEAR TO MONTH
----
1 year 3 months

query T
SELECT INTERVAL '1' MINUTE
----
00:01:00

query T
SELECT INTERVAL '1 MIN'
----
00:01:00

query T
SELECT INTERVAL '1 MINS'
----
00:01:00

query T
SELECT INTERVAL '1 SECS'
----
00:00:01

query T
SELECT INTERVAL '1 SEC'
----
00:00:01

statement ok
CREATE TABLE iv_ish (
    b interval
)

statement ok
INSERT INTO iv_ish VALUES (INTERVAL '1' YEAR), (INTERVAL '3' HOUR), (INTERVAL '4d 2h' DAY TO HOUR)

query T rowsort
SELECT * FROM iv_ish
----
4 days 02:00:00
1 year
03:00:00

# Date-time literals

query T
SELECT DATE '2000-01-01'
----
2000-01-01

query T
SELECT DATE '2000 01-01'
----
2000-01-01

query T
SELECT DATE '2000 01 01'
----
2000-01-01

query T
SELECT DATE '20000101'
----
2000-01-01

statement error
SELECT DATE '2000-01 01'

query T
SELECT TIMESTAMP '2000-01-01 01:02:03'
----
2000-01-01 01:02:03

query T
SELECT TIMESTAMP '2000 01-01 01:02:03'
----
2000-01-01 01:02:03

query T
SELECT TIMESTAMP '2000 01 01 01:02:03'
----
2000-01-01 01:02:03

query T
SELECT TIMESTAMP '20010101 01:02:03';
----
2001-01-01 01:02:03

statement error
SELECT TIMESTAMP '2000-01 01 01:02:03'

# Date arithmetic with month intervals.

query T
SELECT DATE '2000-01-01' + INTERVAL '1' MONTH
----
2000-02-01 00:00:00

# Test that DATE + INTERVAL addition works in both orders.
query T
SELECT INTERVAL '1' MONTH + DATE '2000-01-01'
----
2000-02-01 00:00:00

query T
SELECT DATE '2000-01-01' + INTERVAL '1' YEAR
----
2001-01-01 00:00:00

query T
SELECT DATE '2000-01-01' + INTERVAL '-1' MONTH
----
1999-12-01 00:00:00

query T
SELECT DATE '2000-01-01' - INTERVAL '1' MONTH
----
1999-12-01 00:00:00

query T
SELECT DATE '2000-01-01' - INTERVAL '-1' MONTH
----
2000-02-01 00:00:00

query T
SELECT DATE '2000-01-01' - INTERVAL '1' YEAR
----
1999-01-01 00:00:00

query error db error: ERROR: operator does not exist: interval \- date
SELECT INTERVAL '1' YEAR - DATE '2000-01-01'

query T
SELECT DATE '2000-01-01' + TIME '01:02:03'
----
2000-01-01 01:02:03

# Date arithmetic with self. Should behave as interval.
query T
SELECT DATE '2019-02-03' - DATE '2019-01-01';
----
33

# Time arithmetic with intervals.

query T
SELECT TIME '01:02:03' + INTERVAL '04:05:06'
----
05:07:09

query T
SELECT TIME '04:05:06' - INTERVAL '01:02:03'
----
03:03:03

query T
SELECT TIME '01:02:03' - INTERVAL '04:05:06'
----
20:56:57

query T
SELECT TIME '04:05:06' + INTERVAL '-01:02:03'
----
03:03:03

query T
SELECT TIME '01:02:03' - INTERVAL '-04:05:06'
----
05:07:09

# Time arithmetic with self. Should behave as interval

query T
SELECT TIME '04:05:06' - TIME '01:02:03'
----
03:03:03

query T
SELECT TIME '01:02:03' - TIME '04:05:06'
----
-03:03:03

# Timestamp arithmetic with month intervals. Should behave the same as DATE.

query T
SELECT TIMESTAMP '2000-01-01 00:00:00' + INTERVAL '1' YEAR
----
2001-01-01 00:00:00

query T
SELECT TIMESTAMP '2000-01-01 00:00:00' - INTERVAL '1' YEAR
----
1999-01-01 00:00:00

query error db error: ERROR: operator does not exist: interval \- timestamp
SELECT INTERVAL '1' YEAR - TIMESTAMP '2000-01-01 00:00:00'

# Date arithmetic with duration intervals.

query T
SELECT DATE '2000-01-01' + INTERVAL '7' DAY
----
2000-01-08 00:00:00

# Test that DATE + INTERVAL addition works in both orders.
query T
SELECT INTERVAL '7' DAY + DATE '2000-01-01'
----
2000-01-08 00:00:00

query T
SELECT INTERVAL '2 YRS 5 DAYS'
----
2 years 5 days

query T
SELECT INTERVAL '2 YR 5 DAYS'
----
2 years 5 days

query T
SELECT DATE '2000-01-01' + INTERVAL '7 5:4:3.2' DAY TO SECOND
----
2000-01-08 05:04:03.2

query T
SELECT DATE '2000-01-01' + INTERVAL '4' HOUR
----
2000-01-01 04:00:00

query T
SELECT DATE '2000-01-01' + INTERVAL '4 HR'
----
2000-01-01 04:00:00

query T
SELECT DATE '2000-01-01' + INTERVAL '3' MINUTE
----
2000-01-01 00:03:00

query T
SELECT DATE '2000-01-01' + INTERVAL '22' SECOND
----
2000-01-01 00:00:22

query T
SELECT DATE '2000-01-01' + INTERVAL '22.0044' SECOND
----
2000-01-01 00:00:22.0044

query T
SELECT DATE '2000-01-01' - INTERVAL '22' DAY
----
1999-12-10 00:00:00

query T
SELECT DATE '2000-01-01' - INTERVAL '22' SECOND
----
1999-12-31 23:59:38

# Timestamp arithmetic with duration intervals.

query T
SELECT TIMESTAMP '2000-01-01 00:00:00' + INTERVAL '7' HOUR
----
2000-01-01 07:00:00

# date and time comparisons after interval math

query B
SELECT DATE '2000-01-01' < DATE '1999-01-01' + INTERVAL '2' YEAR
----
true

query B
SELECT DATE '2000-01-01' > DATE '1999-01-01' + INTERVAL '2' YEAR
----
false

query B
SELECT DATE '2000-01-01' <= DATE '1999-01-01' + INTERVAL '2' YEAR
----
true

query B
SELECT DATE '2000-01-01' >= DATE '1999-01-01' + INTERVAL '2' YEAR
----
false

query T
SELECT DATE '2001-01-01' + INTERVAL '3' YEAR
----
2004-01-01 00:00:00

# Check Comparisons

query T
SELECT * FROM dateish WHERE a <= DATE '1999-01-01' + INTERVAL '2' YEAR
----
2000-01-01

query T
SELECT * FROM dateish WHERE a < DATE '1999-01-01' + INTERVAL '2' YEAR
----
2000-01-01

query T
SELECT * FROM dateish WHERE a <= DATE '1999-12-31' + INTERVAL '2' DAY
----
2000-01-01

query T
SELECT * FROM dateish WHERE a = DATE '1999-12-31' + INTERVAL '1' DAY
----
2000-01-01


# same as above, but inverted

query T
SELECT * FROM dateish WHERE a >= DATE '1999-01-01' + INTERVAL '2' YEAR
----
2019-12-31

query T
SELECT * FROM dateish WHERE a > DATE '1999-01-01' + INTERVAL '2' YEAR
----
2019-12-31

query T
SELECT * FROM dateish WHERE a >= DATE '1999-12-31' + INTERVAL '2' DAY
----
2019-12-31

query T
SELECT * FROM dateish WHERE a != DATE '1999-12-31' + INTERVAL '1' DAY
----
2019-12-31

query T rowsort
SELECT * FROM dateish WHERE a != DATE '1999-12-31' + INTERVAL '2' DAY
----
2000-01-01
2019-12-31

# Comparisons with timestamps

statement ok
CREATE TABLE timestamp_compares (
    c timestamp
)

statement ok
INSERT INTO timestamp_compares
VALUES
    (TIMESTAMP '1999-12-31 01:01:01'),
    (TIMESTAMP '2009-01-01 09:09:09.9')

query T
SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2009-01-01 09:09:09.9'
----
2009-01-01 09:09:09.9

query T
SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '1' DAY
----
2009-01-01 09:09:09.9

query T
SELECT c FROM timestamp_compares WHERE c = TIMESTAMP '2008-12-01 09:09:09.9' + INTERVAL '1' MONTH
----
2009-01-01 09:09:09.9

query T rowsort
SELECT c FROM timestamp_compares WHERE c < TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '2' MONTH
----
1999-12-31 01:01:01
2009-01-01 09:09:09.9

# some inverses

query T
SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2009-01-01 09:09:09.9'
----
1999-12-31 01:01:01

query T
SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '1' DAY
----
1999-12-31 01:01:01

query T
SELECT c FROM timestamp_compares WHERE c != TIMESTAMP '2008-12-01 09:09:09.9' + INTERVAL '1' MONTH
----
1999-12-31 01:01:01

query T rowsort
SELECT c FROM timestamp_compares WHERE c > TIMESTAMP '2008-12-31 09:09:09.9' + INTERVAL '2' MONTH
----

statement ok
CREATE TABLE timestampwithtzish (
    t timestamp
)

statement ok
INSERT INTO timestampwithtzish
VALUES
    (TIMESTAMP WITH TIME ZONE '1999-12-31 11:11:01+04:00'),
    (TIMESTAMP WITH TIME ZONE '1999-12-31 16:16:01+02:30'),
    (TIMESTAMP WITH TIME ZONE '1999-12-31 20:00:00-12'),
    (TIMESTAMP WITH TIME ZONE '1999-12-31 18:00:00+12')

query T rowsort
SELECT * FROM timestampwithtzish
----
1999-12-31 06:00:00
1999-12-31 07:11:01
1999-12-31 13:46:01
2000-01-01 08:00:00

statement ok
CREATE TABLE timestamptzish (
    t timestamptz
)

statement ok
INSERT INTO timestamptzish
VALUES
    (TIMESTAMPTZ '1999-12-31 11:11:01+04:00'),
    (TIMESTAMPTZ '1999-12-31 16:16:01+02:30'),
    (TIMESTAMPTZ '1999-12-31 20:00:00-12'),
    (TIMESTAMPTZ '1999-12-31 18:00:00+12')

query T rowsort
SELECT * FROM timestamptzish
----
1999-12-31 06:00:00+00
1999-12-31 07:11:01+00
1999-12-31 13:46:01+00
2000-01-01 08:00:00+00

query T
SELECT max(t) FROM timestamptzish
----
2000-01-01 08:00:00+00

query T
SELECT min(t) FROM timestamptzish
----
1999-12-31 06:00:00+00

# Timestamptz arithmetic with month intervals. Should behave the same as DATE and timestamp

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-6' + INTERVAL '1' YEAR
----
2001-01-01 06:00:00+00

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-7' - INTERVAL '1' YEAR
----
1999-01-01 07:00:00+00

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-6' + INTERVAL '3' MONTH
----
2000-04-01 06:00:00+00

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-7' - INTERVAL '4' MONTH
----
1999-09-01 07:00:00+00

query error db error: ERROR: operator does not exist: interval \- timestamp with time zone
SELECT INTERVAL '1' YEAR - TIMESTAMPTZ '2000-01-01 00:00:00-4:00'

# Timestamptz arithmetic with duration intervals.

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + INTERVAL '7' HOUR
----
2000-01-01 11:00:00+00

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + INTERVAL '3' MINUTE
----
2000-01-01 04:03:00+00

query T
SELECT INTERVAL '6' HOUR + TIMESTAMPTZ '2000-01-01 00:00:00-04'
----
2000-01-01 10:00:00+00

query T
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' - INTERVAL '2' HOUR
----
2000-01-01 02:00:00+00

query error db error: ERROR: operator does not exist: interval \- timestamp with time zone
SELECT INTERVAL '2' HOUR - TIMESTAMPTZ '2000-01-01 00:00:00-04'

query error db error: ERROR: operator does not exist: timestamp with time zone \* interval
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' * INTERVAL '2' HOUR

query error db error: ERROR: operator does not exist: timestamp with time zone / interval
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' / INTERVAL '2' HOUR

query error db error: ERROR: operator does not exist: timestamp with time zone \+ timestamp with time zone
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + TIMESTAMPTZ '1999-01-01 00:00:00z'

query error db error: ERROR: operator does not exist: timestamp with time zone \+ timestamp
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' + TIMESTAMP '1999-01-01 00:00:00'

# Tests with comparison operators and timestamptz

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' < TIMESTAMPTZ '2000-01-01 00:00:00-04'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' > TIMESTAMPTZ '2000-01-01 00:00:00-04'
----
false

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' <= TIMESTAMPTZ '2000-01-01 00:00:00-04'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+01' >= TIMESTAMPTZ '2000-01-01 00:00:00-04'
----
false

query B
SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' = TIMESTAMPTZ '2000-01-01 01:00:00z'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' >= TIMESTAMPTZ '2000-01-01 01:00:00z'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' <= TIMESTAMPTZ '2000-01-01 01:00:00z'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 02:00:00+01' != TIMESTAMPTZ '2000-01-01 01:00:00z'
----
false

# Tests with comparison operators across different time types

query B
SELECT TIMESTAMP '2000-01-01 00:00:00' > DATE '2000-01-01'
----
false

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+4' > DATE '2000-01-01'
----
false

query B
SELECT DATE '2001-01-01' > TIMESTAMPTZ '2000-01-01 00:00:00+4'
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 00:00:00+4' > TIMESTAMP '2000-01-01 01:00:00'
----
false

query B
SELECT TIMESTAMP '2000-01-01 00:00:00' > TIMESTAMPTZ '2000-01-01 01:00:00+04'
----
true

# Timestamptz comparisons after interval math

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' < TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' <= TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' > TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR
----
false

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' >= TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR
----
false

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' + INTERVAL '3' HOUR > TIMESTAMPTZ '2000-01-01 00:01:00z' + INTERVAL '1' HOUR
----
true

query B
SELECT TIMESTAMPTZ '2000-01-01 01:00:00+01' + INTERVAL '1' MINUTE != TIMESTAMPTZ '2000-01-01 00:01:00+01' + INTERVAL '1' HOUR
----
false

# comparisons with timestamptz

query T
SELECT t FROM timestamptzish WHERE t = TIMESTAMPTZ '1999-12-31 13:46:01z'
----
1999-12-31 13:46:01+00

query T
SELECT t FROM timestamptzish WHERE t = TIMESTAMP '1999-12-31 13:46:01'
----
1999-12-31 13:46:01+00

query T
SELECT t FROM timestamptzish WHERE t = TIMESTAMPTZ '1999-12-31 9:46:01-04'
----
1999-12-31 13:46:01+00

query T
SELECT t FROM timestamptzish WHERE t > TIMESTAMPTZ '1999-12-31 9:46:01-04'
----
2000-01-01 08:00:00+00

query T rowsort
SELECT t FROM timestamptzish WHERE t >= TIMESTAMPTZ '1999-12-31 9:46:01-04'
----
1999-12-31 13:46:01+00
2000-01-01 08:00:00+00

query T rowsort
SELECT t FROM timestamptzish WHERE t < TIMESTAMPTZ '1999-12-31 9:46:01-04'
----
1999-12-31 06:00:00+00
1999-12-31 07:11:01+00

query T rowsort
SELECT t FROM timestamptzish WHERE t <= TIMESTAMPTZ '1999-12-31 9:46:01-04'
----
1999-12-31 06:00:00+00
1999-12-31 07:11:01+00
1999-12-31 13:46:01+00

query T rowsort
SELECT t FROM timestamptzish WHERE t > TIMESTAMPTZ '1999-12-31 9:46:01-04' - INTERVAL '12' HOUR
----
1999-12-31 06:00:00+00
1999-12-31 07:11:01+00
1999-12-31 13:46:01+00
2000-01-01 08:00:00+00

# Tests now() and current_timestamp()

query B
SELECT now() > timestamp '2015-06-13 00:00:00'
----
true

query B
SELECT now() + INTERVAL '100' HOUR > now()
----
true

query B
SELECT current_timestamp() > TIMESTAMP '2016-06-13 00:00:00'
----
true

query B
SELECT current_timestamp > TIMESTAMP '2016-06-13 00:00:00'
----
true

statement ok
CREATE VIEW logical_timestamp_view(ts) AS SELECT mz_now()

# Equivalent to running `SELECT mz_now()` directly. If there are
# other objects in the same time domain as the view, they will dictate what is
# returned. Otherwise it defaults to the epoch millisecond timeline.
query T
SELECT ts < 18446744073709551615 FROM logical_timestamp_view
----
true

statement ok
CREATE VIEW now_view AS SELECT now() AS ts

query B
SELECT ts > TIMESTAMP '2016-06-13 00:00:00' FROM now_view
----
true

query T
SELECT (DATE '2000-01-01')::text
----
2000-01-01

query T
SELECT (TIMESTAMP '2000-01-01 00:00:00')::text
----
2000-01-01 00:00:00

query T
SELECT (TIMESTAMPTZ '2000-01-01 00:00:00-6')::text
----
2000-01-01 06:00:00+00

query T
SELECT (INTERVAL '1-3' YEAR TO MONTH)::text
----
1 year 3 months

query RRRRR
SELECT EXTRACT(HOUR from TIME '11:12:42.666'),
    EXTRACT(MINUTE from TIME '11:12:42.666'),
    EXTRACT(SECOND from TIME '11:12:42.666'),
    EXTRACT(MILLISECONDS from TIME '11:12:42.666'),
    EXTRACT(MICROSECONDS from TIME '11:12:42.666')
----
11 12 42.666 42666 42666000

query RRRRR
SELECT date_part('HOUR', TIME '11:12:42.666'),
    date_part('MINUTE', TIME '11:12:42.666'),
    date_part('SECOND', TIME '11:12:42.666'),
    date_part('MILLISECONDS', TIME '11:12:42.666'),
    date_part('MICROSECONDS', TIME '11:12:42.666')
----
11 12 42.666 42666 42666000

query error unit 'millennium' not supported for type time
SELECT EXTRACT(MILLENNIUM from TIME '11:12:42.666')

query error unit 'century' not supported for type time
SELECT EXTRACT(CENTURY from TIME '11:12:42.666')

query error unit 'decade' not supported for type time
SELECT EXTRACT(DECADE from TIME '11:12:42.666')

query error unit 'year' not supported for type time
SELECT EXTRACT(YEAR from TIME '11:12:42.666')

query error unit 'quarter' not supported for type time
SELECT EXTRACT(QUARTER from TIME '11:12:42.666')

query error unit 'month' not supported for type time
SELECT EXTRACT(MONTH from TIME '11:12:42.666')

query error unit 'week' not supported for type time
SELECT EXTRACT(WEEK from TIME '11:12:42.666')

query error unit 'day' not supported for type time
SELECT EXTRACT(DAY from TIME '11:12:42.666')

query error unit 'dow' not supported for type time
SELECT EXTRACT(DOW from TIME '11:12:42.666')

query error unit 'doy' not supported for type time
SELECT EXTRACT(DOY from TIME '11:12:42.666')

query error unit 'isodow' not supported for type time
SELECT EXTRACT(ISODOW from TIME '11:12:42.666')

query error unit 'isodoy' not supported for type time
SELECT EXTRACT(ISODOY from TIME '11:12:42.666')

query error unit 'month' not supported for type time
SELECT date_part('MONTH', TIME '11:12:42.666')

query RR
SELECT EXTRACT(DOW FROM TIMESTAMP '1999-12-26 00:00:00'), EXTRACT(DOW FROM TIMESTAMP '2000-01-01 00:00:00')
----
0 6

query RR
SELECT EXTRACT(ISODOW FROM TIMESTAMP '1999-12-26 00:00:00'), EXTRACT(ISODOW FROM TIMESTAMP '2000-01-01 00:00:00')
----
7 6

query RRRRRRRRRRRRRRRRR
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(MILLENNIUM FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(CENTURY FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(DECADE FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(YEAR FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(QUARTER FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(WEEK FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(MONTH FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(HOUR FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(DAY FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(DOW FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(DOY FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(ISODOW FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(MINUTE FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(SECOND FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(MS FROM TIMESTAMP '2019-11-26 15:56:46.241150'),
    EXTRACT(US FROM TIMESTAMP '2019-11-26 15:56:46.241150')
----
1574783806.24115 3 21 201 2019 4 48 11 15 26 2 330 2 56 46.24115 46241.15 46241150

query RRRRRRRRRRRRRRRRR
SELECT date_part('EPOCH', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('MILLENNIUM', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('CENTURY', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('DECADE', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('YEAR', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('QUARTER', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('WEEK', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('MONTH', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('HOUR', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('DAY', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('DOW', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('DOY', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('ISODOW', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('MINUTE', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('SECOND', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('MS', TIMESTAMP '2019-11-26 15:56:46.241150'),
    date_part('US', TIMESTAMP '2019-11-26 15:56:46.241150')
----
1574783806.24115 3 21 201 2019 4 48 11 15 26 2 330 2 56 46.24115 46241.15 46241150

query RRRRRRRRRRRR
SELECT EXTRACT(QUARTER FROM DATE '2000-01-01'),
    EXTRACT(QUARTER FROM DATE '2000-02-03'),
    EXTRACT(QUARTER FROM DATE '2000-03-05'),
    EXTRACT(QUARTER FROM DATE '2000-04-07'),
    EXTRACT(QUARTER FROM DATE '2000-05-09'),
    EXTRACT(QUARTER FROM DATE '2000-06-11'),
    EXTRACT(QUARTER FROM DATE '2000-07-13'),
    EXTRACT(QUARTER FROM DATE '2000-08-15'),
    EXTRACT(QUARTER FROM DATE '2000-09-17'),
    EXTRACT(QUARTER FROM DATE '2000-10-19'),
    EXTRACT(QUARTER FROM DATE '2000-11-21'),
    EXTRACT(QUARTER FROM DATE '2000-12-24')
----
1 1 1 2 2 2 3 3 3 4 4 4

query RRRRRRRR
SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '2000-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '1999-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '1001-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '1000-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '0001-01-01'),
    EXTRACT(MILLENNIUM FROM DATE '0001-01-01' - INTERVAL '1'SECOND),
    EXTRACT(MILLENNIUM FROM DATE '0001-01-01' - INTERVAL '1000 YEAR 1 SECOND')
----
3 2 2 2 1 1 -1 -2

query RRRRRRRR
SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'),
    EXTRACT(CENTURY FROM DATE '2000-01-01'),
    EXTRACT(CENTURY FROM DATE '1999-01-01'),
    EXTRACT(CENTURY FROM DATE '1001-01-01'),
    EXTRACT(CENTURY FROM DATE '1000-01-01'),
    EXTRACT(CENTURY FROM DATE '0001-01-01'),
    EXTRACT(CENTURY FROM DATE '0001-01-01' - INTERVAL '1'SECOND),
    EXTRACT(CENTURY FROM DATE '0001-01-01' - INTERVAL '100 YEAR 1 SECOND')
----
21 20 20 11 10 1 -1 -2

query RRRRRRRR
SELECT EXTRACT(DECADE FROM DATE '2001-01-01'),
    EXTRACT(DECADE FROM DATE '2000-01-01'),
    EXTRACT(DECADE FROM DATE '1999-01-01'),
    EXTRACT(DECADE FROM DATE '0001-01-01'),
    EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '1'SECOND),
    EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '1 YEAR 1 SECOND'),
    EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '10 YEAR 1 SECOND'),
    EXTRACT(DECADE FROM DATE '0001-01-01' - INTERVAL '11 YEAR 1 SECOND')
----
200 200 199 0 0 -1 -1 -2

query RR
SELECT EXTRACT(WEEK FROM DATE '2000-01-01'), EXTRACT(WEEK FROM DATE '2000-01-08')
----
52 1

query RR
SELECT EXTRACT(DOY FROM DATE '2000-01-01'), EXTRACT(DOY FROM DATE '2000-12-31')
----
1 366

query error unit 'hour' not supported for type date
SELECT EXTRACT(HOUR FROM DATE '2000-12-31')

query error unit 'seconds' not supported for type date
SELECT EXTRACT(SECOND FROM DATE '2000-12-31')

query error unit 'milliseconds' not supported for type date
SELECT EXTRACT(MILLISECOND FROM DATE '2000-12-31')

query error unit 'microseconds' not supported for type date
SELECT EXTRACT(MICROSECOND FROM DATE '2000-12-31')

query RRRR
SELECT date_part('HOUR', DATE '2000-12-31'),
    date_part('SECOND', DATE '2000-12-31'),
    date_part('MILLISECOND', DATE '2000-12-31'),
    date_part('MICROSECOND', DATE '2000-12-31')
----
0 0 0 0

query RR
SELECT EXTRACT(EPOCH from INTERVAL '-1' MINUTE), EXTRACT(MINUTE from INTERVAL '-1' MINUTE)
----
-60 -1

query RR
SELECT EXTRACT(EPOCH from INTERVAL '1' YEAR), EXTRACT(EPOCH from INTERVAL '1' MONTH) * 12
----
31557600 31104000

query RR
SELECT EXTRACT(MILLISECOND from INTERVAL '72.345678'SECOND), EXTRACT(MICROSECOND from INTERVAL '72.345678'SECOND)
----
12345.678 12345678

query RRR
SELECT EXTRACT(DECADE from INTERVAL '39'YEAR),
    EXTRACT(CENTURY from INTERVAL '399'YEAR),
    EXTRACT(MILLENNIUM from INTERVAL '3999'YEAR)
----
3 3 3

query RR
SELECT EXTRACT(MONTH from INTERVAL '-13'MONTH), EXTRACT(MONTH from INTERVAL '15'MONTH)
----
-1 3

query RRRRRRRRRRR
SELECT date_part('EPOCH', INTERVAL '-1' MINUTE),
    date_part('MINUTE', INTERVAL '-1' MINUTE),
    date_part('EPOCH', INTERVAL '1' YEAR),
    date_part('EPOCH', INTERVAL '1' MONTH) * 12,
    date_part('MILLISECOND', INTERVAL '72.345678'SECOND),
    date_part('MICROSECOND', INTERVAL '72.345678'SECOND),
    date_part('DECADE', INTERVAL '39'YEAR),
    date_part('CENTURY', INTERVAL '399'YEAR),
    date_part('MILLENNIUM', INTERVAL '3999'YEAR),
    date_part('MONTH', INTERVAL '-13'MONTH),
    date_part('MONTH', INTERVAL '15'MONTH)
----
-60 -1 31557600 31104000 12345.678 12345678 3 3 3 -1 3

query T
SELECT to_char(TIMESTAMPTZ '1997-02-03 11:12:59.9', 'YYYY-MM-DD HH24:MI:SS.MS TZ')
----
1997-02-03 11:12:59.900 UTC

# Test that fractional milliseconds are not rounded, which matches Postgres's
# behavior.

query T
SELECT to_char(TIMESTAMPTZ '1997-02-03 11:12:59.7777', 'YYYY-MM-DD HH24:MI:SS.MS TZ')
----
1997-02-03 11:12:59.777 UTC

# Test a degenerate pattern that contains no field specifiers.

query T
SELECT to_char(TIMESTAMP '2000-01-01', 'no patterns at all')
----
no patterns at all

query T
SELECT to_timestamp(-1)
----
1969-12-31 23:59:59+00

query T
SELECT to_timestamp(0)
----
1970-01-01 00:00:00+00

query T
SELECT to_timestamp(946684800)
----
2000-01-01 00:00:00+00

query T
SELECT to_timestamp(1262349296.7890123)
----
2010-01-01 12:34:56.789012+00

query T
SELECT to_timestamp(1.999999999)
----
1970-01-01 00:00:02+00

query error timestamp out of range
SELECT to_timestamp('inf'::double)

query error timestamp cannot be NaN
SELECT to_timestamp('nan'::double)

# Negative timestamps are not allowed

statement error
SELECT TIMESTAMP '-2000-01-01 1:2:3';

statement error
SELECT DATE '-2000-01-01';

statement error
SELECT TIMESTAMP '-2000-01 1:2:3';

statement error
SELECT DATE '-2000-01 1:2:3';

# Test string to datetime types

query T
SELECT '2007-02-01'::date
----
2007-02-01

query T
SELECT '-1-2 3 -4:5:6.7'::interval;
----
-1 years -2 months +3 days -04:05:06.7

query T
SELECT '01:23:45'::time;
----
01:23:45

query T
SELECT '2007-02-01 15:04:05'::timestamp;
----
2007-02-01 15:04:05

query T
SELECT '2007-02-01 15:04:05+06'::timestamptz;
----
2007-02-01 09:04:05+00

# Test datetime types to string

query T
SELECT (date '2007-02-01')::text
----
2007-02-01

query T
SELECT (interval '-1-2 3 -4:5:6.7')::text;
----
-1 years -2 months +3 days -04:05:06.7

query T
SELECT (time '01:23:45')::text;
----
01:23:45

query T
SELECT (timestamp '2007-02-01 15:04:05')::text;
----
2007-02-01 15:04:05

query T
SELECT (timestamptz '2007-02-01 15:04:05+06')::text;
----
2007-02-01 09:04:05+00

# Test special date-timme inputs from Postgres

query T
SELECT 'epoch'::timestamp
----
1970-01-01 00:00:00

query T
SELECT 'epoch'::timestamptz
----
1970-01-01 00:00:00+00

query T
SELECT 'epoch'::date
----
1970-01-01

# Test ISO-formatted timestamps

query T
SELECT TIMESTAMP '2007-02-01T15:04:05'
----
2007-02-01 15:04:05

query T
SELECT TIMESTAMPTZ '2007-02-01T15:04:05+00'
----
2007-02-01 15:04:05+00

query T
SELECT TIMESTAMPTZ '20070201 T 15:04:05+00';
----
2007-02-01 15:04:05+00

query T
SELECT TIMESTAMPTZ '20070201T15:04:05+00';
----
2007-02-01 15:04:05+00

query T
SELECT DATE '2007-02-01T15:04:05+00'
----
2007-02-01

query T
SELECT DATE '2007-02-01 T 15:04:05+00'
----
2007-02-01

query T
SELECT DATE '2007-02-01   T  15:04:05+00'
----
2007-02-01

statement error invalid input syntax for type date: unknown units X: "2007-02-01X15:04:05"
SELECT DATE '2007-02-01X15:04:05'

statement error invalid input syntax for type date: unknown units TT: "2007-02-01TT15:04:05"
SELECT DATE '2007-02-01TT15:04:05'

statement error invalid input syntax for type date: Cannot determine format of all parts: "2007-02-01  T  T  15:04:05"
SELECT DATE '2007-02-01  T  T  15:04:05'

statement error invalid input syntax for type date: Invalid timezone string \(T\): 'T' is not a valid timezone. Failed to parse T at token index 0
SELECT DATE '2007-02-01  T '

# Test casting time to interval & vice versa

query T
SELECT time '01:02:03.04'::interval;
----
01:02:03.04

query T
SELECT interval '01:02:03.04'::time;
----
01:02:03.04

query T
SELECT interval '-01:02:03.04'::time;
----
22:57:56.96

query T
SELECT interval '-3 days -2 hours'::time;
----
22:00:00

# Test using date as a column name.
query T
SELECT date FROM (SELECT column1 AS date FROM (VALUES ('2020-01-01')))
----
2020-01-01

# Arbitrary punctuation as delimiter
query T
SELECT '"2020-03-17 ~02:36:56~"'::timestamp;
----
2020-03-17 02:36:56

query T
SELECT '"2020!03-17 #?~T~02:36:56#"'::timestamp;
----
2020-03-17 02:36:56

query error invalid input syntax for type timestamp: have unprocessed tokens 56
select TIMESTAMP '"2020-03-17 ~02:36:~56~"';

# Regression for #6272. These match postgres.
query TTT
select '9::60'::time, '9:59:60'::time, '9::59.999999'::time
----
09:01:00 10:00:00 09:00:59.999999

# TODO: Postgres returns 09:01:00 for this.
query T
select '9::59.999999999'::time
----
09:00:59.999999

# TODO: Postgres supports this as 09:01:00.1.
statement error invalid input syntax for type time: NANOSECOND
select '9::60.1'::time

# BC years are properly formatted
# Using INTERVAL to work around the parser not supporting BC identifiers yet
query T
select '0001-02-24'::date - interval '1 YEAR'
----
0001-02-24 00:00:00 BC

query T
select ('0001-02-24'::date - interval '1 YEAR')::date
----
0001-02-24 BC

query T
select ('0001-01-01'::date - interval '1 DAY')::date
----
0001-12-31 BC

query error interval out of range
select '0001-02-24'::date - interval '-2147483648 MONTHS'

query T
select '0001-02-24 03:04:05'::timestamp - interval '1 YEAR'
----
0001-02-24 03:04:05 BC

query T
select '0001-02-24 03:04:05.6789'::timestamp - interval '1 YEAR'
----
0001-02-24 03:04:05.6789 BC

query T
select '0001-02-24 03:04:05.6789 +00:00'::timestamptz - interval '1 YEAR'
----
0001-02-24 03:04:05.6789+00 BC

query error interval out of range
select '0001-02-24 03:04:05.6789'::timestamp - interval '-2147483648 MONTHS'

query error interval out of range
select '0001-02-24 03:04:05.6789 +00:00'::timestamptz - interval '-2147483648 MONTHS'

# Infinity dates not supported.
query error invalid input syntax for type date
select 'infinity'::date

# We don't support BC parsing or 6 digit dates, so do some jank. Additionally,
# we don't yet support the `date - int` operation and have to use `date -
# interval` instead, which produces a `timestamp`, and thus we aren't able to even
# express the full range of date values.

# Lowest date we could support, but timestamps don't.
# select ('0001-01-01'::date - '4713years 1months 7days')::date

query T
select ('0001-01-01'::date - '1721389days'::interval)::date
----
4714-12-31 BC

query T
select ('0001-01-01'::date + '262142years 11months 30days'::interval)::date
----
262143-12-31

# Out of range for both dates and timestamps, but timestamp triggers first.
query error timestamp out of range
select ('0001-01-01'::date - '4713years 1months 8days')::date

query error timestamp out of range
select ('0001-01-01'::date + '262142years 11months 30days')::date + '1day'

query II
select ('0001-01-01'::date - '1721389days'::interval)::date - ('0001-01-01'::date + '262142years 11months 30days'::interval)::date, ('0001-01-01'::date + '262142years 11months 30days'::interval)::date - ('0001-01-01'::date - '1721389days'::interval)::date
----
-97467152 97467152

query error timestamp out of range
SELECT to_timestamp(9223372036854775808::float8);

query error timestamp out of range
SELECT to_timestamp(-9223372036854775809::float8);

# Negative fractional timestamp

query T
SELECT to_timestamp(-0.1::float8);
----
1969-12-31 23:59:59.9+00