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

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 no overload for 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 no overload for 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 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 '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 no overload for 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 no overload for interval - timestamp with time zone
SELECT INTERVAL '2' HOUR - TIMESTAMPTZ '2000-01-01 00:00:00-04'

query error no overload for timestamp with time zone \* interval
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' * INTERVAL '2' HOUR

query error no overload for timestamp with time zone / interval
SELECT TIMESTAMPTZ '2000-01-01 00:00:00-04' / INTERVAL '2' HOUR

query error no overload for 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 no overload for 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

query error calls to mz_logical_timestamp in in static or write queries are not supported
CREATE VIEW timeview AS SELECT mz_logical_timestamp()

query error now cannot be used in static queries
CREATE VIEW timeview AS SELECT now()

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 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 RRR
SELECT 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')
----
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 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 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

# TODO(benesch): this should return an error, not NULL.
query T
SELECT to_timestamp('inf'::double)
----
NULL

# TODO(benesch): this should return an error, not NULL.
query T
SELECT to_timestamp('nan'::double)
----
NULL

# 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 year -2 months +2 days +19:54:53.3

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 year -2 months +2 days +19:54:53.3

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