Materialize/test/sqllogictest/interval.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

query T colnames
SELECT INTERVAL '1';
----
interval
00:00:01

## SQL Standard
query T
SELECT INTERVAL '1:';
----
01:00:00

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

query T
SELECT INTERVAL '1 2:';
----
1 day 02:00:00

# Parse Y-M H:M.
query T
SELECT INTERVAL '1-2 3:4';
----
1 year 2 months 03:04:00

# Parse Y-M D H:M.
query T
SELECT INTERVAL '1-2 3 4:5';
----
1 year 2 months 3 days 04:05:00

# Parse Y- D H:
query T
SELECT INTERVAL '1- 2 3:';
----
1 year 2 days 03:00:00

# Allow parts in any order
query T
SELECT INTERVAL '1:2:3.4 5-6';
----
5 years 6 months 01:02:03.4

# Implicit SECOND for ambiguous part.
query T
SELECT INTERVAL '1-2 3';
----
1 year 2 months 00:00:03

# Explicitly set ambiguous part.
query T
SELECT INTERVAL '1-2 3' HOUR;
----
1 year 2 months 03:00:00

# Disallow components to be set twice.
statement error invalid input syntax for type interval: YEAR field set twice: "1 year 2 years"
SELECT INTERVAL '1 year 2 years'

statement error invalid input syntax for type interval: YEAR or MONTH field set twice: "1-2 3-4"
SELECT INTERVAL '1-2 3-4'

statement error invalid input syntax for type interval: YEAR field set twice: "1-2 3 year"
SELECT INTERVAL '1-2 3 year'

statement error invalid input syntax for type interval: MONTH field set twice: "1-2 3"
SELECT INTERVAL '1-2 3' MONTH;

# 5 would be parsed as second, but the H:M:S.NS
# group was already set by 3:4/
statement error invalid input syntax for type interval: SECOND field set twice: "1-2 3:4 5"
SELECT INTERVAL '1-2 3:4 5';

# Treat trailing TimeUnit as terminating range.
query T
SELECT INTERVAL '1-2 3 4:5:6.7' YEAR;
----
1 year

query T
SELECT INTERVAL '1-2 3 4:5:6.7' MONTH;
----
1 year 2 months

query T
SELECT INTERVAL '1-2 3 4:5:6.7' DAY;
----
1 year 2 months 3 days

query T
SELECT INTERVAL '1-2 3 4:5:6.7' HOUR;
----
1 year 2 months 3 days 04:00:00

query T
SELECT INTERVAL '1-2 3 4:5:6.7' MINUTE;
----
1 year 2 months 3 days 04:05:00

query T
SELECT INTERVAL '1-2 3 4:5:6.7' SECOND;
----
1 year 2 months 3 days 04:05:06.7

# Treat trailing DateTimeFields as range of components to process.
query T
SELECT INTERVAL '1-2 3 4:5:6.7' MONTH TO MINUTE;
----
2 months 3 days 04:05:00

query T
SELECT INTERVAL '1-2 3 4:5:6.7' DAY TO HOUR;
----
3 days 04:00:00

query T
SELECT INTERVAL '12:34';
----
12:34:00

query T
SELECT INTERVAL '12:34' HOUR;
----
12:00:00

query T
SELECT INTERVAL '12:34' MINUTE;
----
12:34:00

query T
SELECT INTERVAL '12:34' SECOND;
----
12:34:00

query T
SELECT INTERVAL '12:34' HOUR TO MINUTE;
----
12:34:00

query T
SELECT INTERVAL '12:34' HOUR TO SECOND;
----
12:34:00

query T
SELECT INTERVAL '12:34' MINUTE TO SECOND;
----
00:12:34

query T
SELECT INTERVAL '12:34:56';
----
12:34:56

query T
SELECT INTERVAL '12:34:56' HOUR;
----
12:00:00

query T
SELECT INTERVAL '12:34:56' MINUTE;
----
12:34:00

query T
SELECT INTERVAL '12:34:56' SECOND;
----
12:34:56

query T
SELECT INTERVAL '12:34:56' HOUR TO MINUTE;
----
12:34:00

query T
SELECT INTERVAL '12:34:56' HOUR TO SECOND;
----
12:34:56

query T
SELECT INTERVAL '12:34:56' MINUTE TO SECOND;
----
00:34:56

query T
SELECT INTERVAL '12:34' DAY TO SECOND;
----
12:34:00

query T
SELECT INTERVAL '12:34' MONTH TO SECOND;
----
12:34:00

# Disallow ranges where trailing element is greater than lead.
statement error
SELECT INTERVAL '1-2 3 4:5:6.7' MINUTE TO MONTH;

# Disallow ranges where elements are explicitly equal.
statement error
SELECT INTERVAL '1-2 3 4:5:6.7' SECOND TO SECOND;

# Disambiguate component (DAY), and treat as range.
query T
SELECT INTERVAL '1:2:3.4 5-6 7' DAY;
----
5 years 6 months 7 days

# Disambiguate component (YEAR), but disallow because
# the group it's in has been closed.
statement error invalid input syntax for type interval: YEAR field set twice: "1:2:3.4 5-6 7"
SELECT INTERVAL '1:2:3.4 5-6 7' YEAR;

# Negative components
query T
SELECT INTERVAL '-1-2 -3 -4:5:6.7';
----
-1 years -2 months -3 days -04:05:06.7

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

# Allow skipping components.
query T
SELECT INTERVAL '1-2 3 4::.5';
----
1 year 2 months 3 days 04:00:00.5

# Mix negatives and missing components.
query T
SELECT INTERVAL '-1-2 -3 4::.5';
----
-1 years -2 months -3 days +04:00:00.5

query T
SELECT INTERVAL '-1-2 -3 -4::.5';
----
-1 years -2 months -3 days -04:00:00.5

# Parse - and infer second value.
query T
SELECT INTERVAL '-::1.27';
----
-00:00:01.27

# Trim all leading colons.
query T
SELECT INTERVAL ':::::1.27';
----
00:00:01.27

query T
SELECT INTERVAL ':::1- ::2 ::::3:';
----
1 year 2 days 03:00:00

# Only trim leading colons if they are not preceded by any
# other characters.
statement error
SELECT INTERVAL '-:::::1.27';

# Disallow multiple ambiguous parts; the second part is ambiguous
# because the leading colons are trimmed.
statement error
SELECT INTERVAL '-1 ::.27';

# Negative time total with negative nanoseconds.
query T
SELECT INTERVAL '-1 -:.27';
----
-1 days -00:00:00.27

# Positive total time with positive nanoseconds.
query T
SELECT INTERVAL '1 0:.27';
----
1 day 00:00:00.27

# Zero value
query T
SELECT INTERVAL '0-0 0 0:0:0.0';
----
00:00:00

# Oversized components in SQL standard-style variables
statement error invalid input syntax for type interval: MONTH must be \[-12, 12\], got 13: "100-13"
SELECT INTERVAL '100-13';

statement error invalid input syntax for type interval: MINUTE must be \[-59, 59\], got 61: "100-11 366 250:61"
SELECT INTERVAL '100-11 366 250:61';

statement error invalid input syntax for type interval: SECOND must be \[-60, 60\], got 61: "100-11 366 250:59:61"
SELECT INTERVAL '100-11 366 250:59:61';

# Invalid syntax
statement error invalid input syntax for type interval: have unprocessed tokens .500000000
SELECT INTERVAL '1:2:3.4.5';

statement error
SELECT INTERVAL '1+2:3.4';

statement error unknown units x
SELECT INTERVAL '1x2:3.4';

## PostgreSQL format

query T
SELECT INTERVAL '1 year 2 month 3 day 4 hour 5 minute 6.7 second';
----
1 year 2 months 3 days 04:05:06.7

# Plural TimeUnits.
query T
SELECT INTERVAL '1 years 2 months 3 days 4 hours 5 minutes 6.7 seconds';
----
1 year 2 months 3 days 04:05:06.7

# Shorthand.
query T
SELECT INTERVAL '1y 2mon 3d 4h 5m 6.7s';
----
1 year 2 months 3 days 04:05:06.7

# Commutative expressions.
query T
SELECT INTERVAL '6.7 seconds 5 minutes 3 days 4 hours 1 year 2 month';
----
1 year 2 months 3 days 04:05:06.7

# Allow mix of positive and negative components.
query T
SELECT INTERVAL '-6.7 seconds 5 minutes -3 days 4 hours -1 year 2 month';
----
-10 months -3 days +04:04:53.3

# Allow fractional components.
query T
SELECT INTERVAL '1y 2.3mon 4.5d';
----
1 year 2 months 13 days 12:00:00

query T
SELECT INTERVAL '-6.7 seconds 5.6 minutes -3.4 days 4.5 hours -1.2 year 2.3 month';
----
-1 years +6 days -05:00:30.7

# Positive total time with negative nanoseconds.
query T
SELECT INTERVAL '1 day -0.27 seconds';
----
1 day -00:00:00.27

# Negative total time with positive nanoseconds.
query T
SELECT INTERVAL '-1 day 0.27 seconds';
----
-1 days +00:00:00.27

# Fractional year expands down to months.
query T
SELECT INTERVAL '10.333 years';
----
10 years 3 months

# Fractional part with ambiguity and range.
query T
SELECT INTERVAL '10.333' years;
----
10 years

# Fractional month, includes nanoseconds.
query T
SELECT INTERVAL '1.5555555 month';
----
1 month 16 days 15:59:59.856

# Fractional day, includes nanoseconds.
query T
SELECT INTERVAL '1.5555555 day';
----
1 day 13:19:59.9952

# Fractional minute, includes nanoseconds
query T
SELECT INTERVAL '1.5555555 minute';
----
00:01:33.33333

# Print leading 0s in nanoseconds
query T
SELECT INTERVAL '.001 second';
----
00:00:00.001

# Carry over nanoseconds into seconds.
query T
SELECT INTERVAL '.33333 minute .1 second';
----
00:00:20.0998

# PostgreSQL-style without spaces.
query T
SELECT INTERVAL '1year 2months 3.4days';
----
1 year 2 months 3 days 09:36:00

# Zero values.
query T
SELECT INTERVAL '0 year 0 month 0 day 0 hour 0 minute 0.0 second';
----
00:00:00

# Fails any non-DateTimeField string.
statement error unknown units foo
SELECT INTERVAL '0 foo';

## Misc.
# Mix SQL standard- and PostgreSQL-style.
query T
SELECT INTERVAL '1-2 3:4 5 day';
----
1 year 2 months 5 days 03:04:00

# Parse + signs.
query T
SELECT INTERVAL '+1 year +2 days +3:4:5.6';
----
1 year 2 days 03:04:05.6

# Differentiate between trailing DateTimeField name and
# PostgreSQL TimeUnit.
query T
SELECT INTERVAL '01:02:03' MINUTE;
----
01:02:00

query T
SELECT INTERVAL '01:02:03minute';
----
01:02:03

query T
SELECT INTERVAL '01:02:03minute hour day year';
----
01:02:03

# Use larger numbers.
query T
SELECT INTERVAL '-13-10 15 -16::.27';
----
-13 years -10 months +15 days -16:00:00.27

query T
SELECT INTERVAL '-13-10 -15 -16::.27';
----
-13 years -10 months -15 days -16:00:00.27

# Parsing output as input.
query T
SELECT INTERVAL '-13 years -10 months -15 days -16:00:00.27';
----
-13 years -10 months -15 days -16:00:00.27

query T
SELECT INTERVAL '-13 years -10 months +14 days +07:59:59.73';
----
-13 years -10 months +14 days 07:59:59.73

# Commutative mixed expressions.
query T
SELECT INTERVAL '5 day 3:4 1-2';
----
1 year 2 months 5 days 03:04:00

query T
SELECT INTERVAL '1-2 3:4 5 day';
----
1 year 2 months 5 days 03:04:00

# Mix style allowed, but cannot assigning to closed group.
statement error invalid input syntax for type interval: SECOND field set twice: "1-2 3:4 5 second"
SELECT INTERVAL '1-2 3:4 5 second';

# Commutativity means this is also not allowed.
statement error invalid input syntax for type interval: HOUR, MINUTE, SECOND field set twice: "1-2 5 second 3:4"
SELECT INTERVAL '1-2 5 second 3:4';

# Fractional month in addition to other fields.
query T
SELECT INTERVAL '1.5555 month 2 3:4:5.6';
----
1 month 18 days 19:01:41.6

# Fractional month with terminating range. Truncation should
# only happen after computation.
query T
SELECT INTERVAL '1.5555 month 2 3:4:5.6' HOUR;
----
1 month 18 days 19:00:00

# Fractional month with leading and terminating range.
# Again, truncation should only happen after
# computation.
query T
SELECT INTERVAL '1.5555 month 2 3:4:5.6' DAY TO HOUR;
----
18 days 19:00:00

# Only allow disambiguation if ambiguous element
# is final element of interval string.
statement error
SELECT INTERVAL '1 2-3 4:5' DAY

## Nanosecond precision

# Default nanosecond precision of 6, rounded.
query T
SELECT INTERVAL '1.23456789' SECOND
----
00:00:01.234568

# Allow specific nanosecond precision, which rounds.
query T
SELECT INTERVAL '1.23456789' SECOND(5);
----
00:00:01.23457

query T
SELECT INTERVAL '-1.23456789' SECOND(5);
----
-00:00:01.23457

# Ensure doesn't round when rounded digit is < 5.
query T
SELECT INTERVAL '1.23456789' SECOND(2);
----
00:00:01.23

query T
SELECT INTERVAL '-1.23456789' SECOND(2);
----
-00:00:01.23

# Postgres rounds micros to 6 digits every time a value is added before applying given precision
query T
SELECT INTERVAL '1.2345649' SECOND(5);
----
00:00:01.23457

query T
SELECT INTERVAL '-1.2345649' SECOND(5);
----
-00:00:01.23457

# Ensure overflow from rounding is handled properly
query error interval field value out of range
SELECT INTERVAL '2562047788:00:54.775807' SECOND(2);

query error interval field value out of range
SELECT INTERVAL '-2562047788:00:54.775808' SECOND(2);


# 0.00000001 min is     0.0000006 seconds
# 0.0006 millisecond is 0.0000006 seconds
# 3.5 microseconds is   0.0000035 seconds
# They each separately get rounded to 6 digits and added together
query T
SELECT INTERVAL '0.00000001 min 0.0006 millisecond 3.5 microsecond';
----
00:00:00.000006

query T
SELECT INTERVAL '0.00000001 min 0.0006 millisecond 3.5 microsecond' SECOND(5);
----
00:00:00.00001

query T
SELECT INTERVAL '-1.555555555 years 2.555555555 months -3.555555555 days 4.555555555 hours -5.555555555 minutes 6.555555555 seconds';
----
-1 years -4 months +13 days 07:07:53.220829


# Allow precision as second element in range.
query T
SELECT INTERVAL '1:2:31.23456789' MINUTE TO SECOND(2);
----
00:02:31.23

# Do not allow more than 9 places of precision
query T
SELECT INTERVAL '1.999999999999999999 days'
----
1 day 23:59:59.999914

# Do not allow more than 9 places of precision,
# and properly convert TimeStrToken::Num() to
# nanos.
query T
SELECT INTERVAL '1.999999999999999999days'
----
1 day 23:59:59.999914

# Precision must be (0, 6)
statement error SECOND precision must be \(0, 6\), have SECOND\(7\)
SELECT INTERVAL '1 day 2-3 4' SECOND(7);

# Only allow precision notation for trailing SECOND .
statement error Expected end of statement, found SECOND
SELECT INTERVAL '2-3 3:4' SECOND(1) to SECOND

statement error Expected end of statement, found left parenthesis
SELECT INTERVAL '2-3 3:4' DAY(1)

# Only allow positive integer nanosecond precision
statement error Expected end of statement, found string literal
SELECT INTERVAL '1 day 2-3 4' SECOND(-1);

# Arbitrary punctuation delimiters
query T
SELECT interval '02-01!1~01:02:03';
----
2 years 1 month 1 day 01:02:03

query T
SELECT interval '1! hour';
----
01:00:00

statement error
SELECT interval '02!01!1~01:02:03';

## Math

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

query T
SELECT - INTERVAL '-1 year -2 months -3 days -04:05:06.7';
----
1 year 2 months 3 days 04:05:06.7

query T
SELECT - INTERVAL '1 year 2 months -3 days -04:05:06.7';
----
-1 years -2 months +3 days 04:05:06.7

query T
SELECT - INTERVAL '-1 year -2 months 3 days 04:05:06.7';
----
1 year 2 months -3 days -04:05:06.7

# Add, sub intervals
query T
SELECT INTERVAL '1-2 3 4:5:6.7' + INTERVAL '1-2 3 4:5:6.7';
----
2 years 4 months 6 days 08:10:13.4

query T
SELECT INTERVAL '1-2 3 4:5:6.7' - INTERVAL '7-6 5 4:3:2.1';
----
-6 years -4 months -2 days +00:02:04.6

query T
SELECT INTERVAL '1-2 3 4:5:6.7' + - INTERVAL '7-6 5 4:3:2.1';
----
-6 years -4 months -2 days +00:02:04.6

query T
SELECT INTERVAL '1-2 3 4:5:6.7' + INTERVAL '-7-6 -5 -4:3:2.1';
----
-6 years -4 months -2 days +00:02:04.6

query T
SELECT INTERVAL '1-2 3 4:5:6.7' - INTERVAL '-7-6 -5 -4:3:2.1';
----
8 years 8 months 8 days 08:08:08.8

query T
SELECT INTERVAL '1-2 3 4:5:6.7' - INTERVAL '1-2 3 4:5:6.7'
----
00:00:00

query T
SELECT INTERVAL '1-2 3 4:5:6.7' + INTERVAL '-1-2 -3 -4:5:6.7'
----
00:00:00

query T
SELECT INTERVAL '-1-2 -3 -4:5:6.7' + INTERVAL '1-2 3 4:5:6.7'
----
00:00:00

# Div, mul intervals
query TT
SELECT INTERVAL '1' MONTH * 0.5, 0.5 * INTERVAL '1' MONTH
----
15␠days  15␠days

query T
SELECT INTERVAL '1' MONTH / 30
----
1 day

query T
SELECT INTERVAL '1' YEAR * 13/12
----
1 year 1 month

query error "10 years \* 10000000000" interval out of range
SELECT INTERVAL '10 years' *  9999999999.999999999;

query error "10 years / 0.000000000000000001" interval out of range
SELECT INTERVAL '10 years' / 0.000000000000000001;

# In an idiosyncratic fashion when dividing/multiplying an interval,
# PostgreSQL considers a year 360 days. But when extracting an epoch,
# it's considered 365.25 days instead.
query T
SELECT INTERVAL '1' YEAR / 360
----
1 day

query T
SELECT INTERVAL '1' YEAR * 0.9999999
----
11 months 29 days 23:59:56.8896

query T
SELECT INTERVAL '-1-2 -3 -4:5:6.7' * 999
----
-1165 years -6 months -2997 days -4081:06:33.3

# Division by zero
statement error division by zero
SELECT INTERVAL '1' YEAR / 0

## Largest values

query T
SELECT INTERVAL '2147483647 days 2562047788 hours 54.775807 seconds'
----
2147483647 days 2562047788:00:54.775807

query T
SELECT INTERVAL '-2147483648 days -2562047788 hours -54.775808 seconds'
----
-2147483648 days -2562047788:00:54.775808

query T
SELECT INTERVAL '2147483647 days 2562047787 hours 59 minutes 59.999999 seconds'
----
2147483647 days 2562047787:59:59.999999

query T
SELECT INTERVAL '-2147483648 days -2562047786 hours -59 minutes -59.999999 seconds'
----
-2147483648 days -2562047786:59:59.999999

query T
SELECT INTERVAL '-2147483648 days -2562047787 hours -59 minutes -59.999999 seconds'
----
-2147483648 days -2562047787:59:59.999999

query T
SELECT INTERVAL '2147483647 months 2147483647 days 9223372036854775807 us'
----
178956970 years 7 months 2147483647 days 2562047788:00:54.775807

query T
SELECT INTERVAL '-2147483648 months -2147483648 days -9223372036854775808 us'
----
-178956970 years -8 months -2147483648 days -2562047788:00:54.775808

statement error Overflows maximum microseconds;
SELECT INTERVAL '2147483647 days 2562047789 hours';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-2147483648 days -2562047789 hours'

statement error Overflows maximum microseconds;
SELECT INTERVAL '-2147483648 days -2562047788 hours -60 min'

statement error Overflows maximum microseconds;
SELECT INTERVAL '-2147483648 days -2562047788 hours -59 min -60 sec'

statement error "2147483647 days 2562047788:00:54 \+ 00:00:01" interval out of range
SELECT INTERVAL '2147483647 days 2562047788 hours 54 seconds' + INTERVAL '1'

statement error "-2147483648 days -2562047788:00:54 - 00:00:01" interval out of range
SELECT INTERVAL '-2147483648 days -2562047788 hours -54 seconds' - INTERVAL '1'

statement error "178956970 years 7 months / 0.99" interval out of range
SELECT INTERVAL '2147483647' MONTH / 0.99

statement error "-178956970 years -7 months \* 1.01" interval out of range
SELECT INTERVAL '-2147483647' MONTH * 1.01

# Largest number of cumulative nanoseconds
query T
SELECT INTERVAL '0.999999999 months 0.999999999 days 0.999999999 hours 0.999999999 minutes 0.999999999 seconds';
----
29 days 49:01:00.997318

# Largest and smallest numbers of cumulative microseconds
query T
SELECT INTERVAL '2562047788.01521550194 hours';
----
2562047788:00:54.775804

query T
SELECT INTERVAL '2562047788.015215502 hours';
----
2562047788:00:54.775807

query T
SELECT INTERVAL '-2562047788.01521550222 hours';
----
-2562047788:00:54.775807

query T
SELECT INTERVAL '-2562047788.0152155023 hours';
----
-2562047788:00:54.775807

query T
SELECT INTERVAL '153722867280.912930117 minutes';
----
2562047788:00:54.775807

query T
SELECT INTERVAL '-153722867280.912930133 minutes';
----
-2562047788:00:54.775808

query T
SELECT INTERVAL '9223372036854.775807 seconds';
----
2562047788:00:54.775807

query T
SELECT INTERVAL '-9223372036854.775808 seconds';
----
-2562047788:00:54.775808

query T
SELECT INTERVAL '9223372036854775.807 milliseconds';
----
2562047788:00:54.775807

query T
SELECT INTERVAL '-9223372036854775.808 milliseconds';
----
-2562047788:00:54.775808

query T
SELECT INTERVAL '9223372036854775807 microseconds'
----
2562047788:00:54.775807

query T
SELECT INTERVAL '-9223372036854775808 microseconds'
----
-2562047788:00:54.775808

## Overflows

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 centuries';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 centuries';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 decades';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 decades';

statement error Overflows maximum months;
SELECT INTERVAL '2147483648 years';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483649 years';

statement error Overflows maximum months;
SELECT INTERVAL '768614336404564651 year';

statement error Overflows maximum months;
SELECT INTERVAL '768614336404564650.7 year';

statement error Overflows maximum months;
SELECT INTERVAL '1 decade 2147483647 years';

statement error Overflows maximum months;
SELECT INTERVAL '1 century 2147483647 years';

statement error Overflows maximum months;
SELECT INTERVAL '1 millennium 2147483647 years';

statement error Overflows maximum months;
SELECT INTERVAL '-1 decade -2147483648 years';

statement error Overflows maximum months;
SELECT INTERVAL '-1 century -2147483648 years';

statement error Overflows maximum months;
SELECT INTERVAL '-1 millennium -2147483648 years';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 years 1 decade';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 years 1 century';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 years 1 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 years -1 decade';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 years -1 century';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 years -1 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '2147483648 months';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483649 months';

statement error Unable to parse value 9223372036854775808 as a number: number too large to fit in target type
SELECT INTERVAL '9223372036854775808 months';

statement error Overflows maximum months;
SELECT INTERVAL '-9223372036854775808 months';

statement error Overflows maximum months;
SELECT INTERVAL '0.1 millennium 2147483647 months';

statement error Overflows maximum months;
SELECT INTERVAL '0.1 centuries 2147483647 months';

statement error Overflows maximum months;
SELECT INTERVAL '0.1 decades 2147483647 months';

statement error Overflows maximum months;
SELECT INTERVAL '0.1 yrs 2147483647 months';

statement error Overflows maximum months;
SELECT INTERVAL '-0.1 millennium -2147483648 months';

statement error Overflows maximum months;
SELECT INTERVAL '-0.1 centuries -2147483648 months';

statement error Overflows maximum months;
SELECT INTERVAL '-0.1 decades -2147483648 months';

statement error Overflows maximum months;
SELECT INTERVAL '-0.1 yrs -2147483648 months';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 months 0.1 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 months 0.1 centuries';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 months 0.1 decades';

statement error Overflows maximum months;
SELECT INTERVAL '2147483647 months 0.1 yrs';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 months -0.1 millennium';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 months -0.1 centuries';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 months -0.1 decades';

statement error Overflows maximum months;
SELECT INTERVAL '-2147483648 months -0.1 yrs';

statement error Overflows maximum days;
SELECT INTERVAL '2147483648 days';

statement error Overflows maximum days;
SELECT INTERVAL '-2147483649 days';

statement error Overflows maximum days;
SELECT INTERVAL '0.1 months 2147483647 days';

statement error Overflows maximum days;
SELECT INTERVAL '-0.1 months -2147483648 days';

statement error Overflows maximum days;
SELECT INTERVAL '2147483647 days 0.1 months';

statement error Overflows maximum days;
SELECT INTERVAL '-2147483648 days -0.1 months';

statement error Overflows maximum days;
SELECT INTERVAL '106751991167300 days .1 month';

statement error Overflows maximum days;
SELECT INTERVAL '106751991167301 days';

statement error Overflows maximum days;
SELECT INTERVAL '106751991167300.9 days';

statement error Overflows maximum microseconds;
SELECT INTERVAL '2562047789 hours';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-2562047789 hours';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854775807 seconds 1 hour';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854771807 seconds 1.9 hour';

statement error Overflows maximum microseconds;
SELECT INTERVAL '153722867281 minutes';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-153722867281 minutes';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854775807 seconds 1 minute';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854775707 seconds 1.9 minute';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036855 seconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-9223372036855 seconds';

statement error Unable to parse value 9223372036854775808 as a number: number too large to fit in target type
SELECT INTERVAL '9223372036854775808 seconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-9223372036854775808 seconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854777 millisecond';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-9223372036854777 millisecond';

statement error Unable to parse value 9223372036854775808 as a number: number too large to fit in target type
SELECT INTERVAL '9223372036854775808 microsecond';

statement error Unable to parse value 9223372036854775809 as a number: number too large to fit in target type
SELECT INTERVAL '-9223372036854775809 microsecond';

statement error Overflows maximum microseconds;
SELECT INTERVAL '0.01 months 9223372036854775807 microseconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-0.01 months -9223372036854775808 microseconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854775807 microseconds 0.01 months';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-9223372036854775808 microseconds -0.01 months';

statement error Overflows maximum microseconds;
SELECT INTERVAL '0.1 days 9223372036854775807 microseconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-0.1 days -9223372036854775808 microseconds';

statement error Overflows maximum microseconds;
SELECT INTERVAL '9223372036854775807 microseconds 0.1 days';

statement error Overflows maximum microseconds;
SELECT INTERVAL '-9223372036854775808 microseconds -0.1 days';

statement error Overflows maximum microseconds;
SELECT INTERVAL '0.1 2562047788:0:54.775807';

# 0 interval equality
query B
SELECT (interval '-1' day + interval '1' day) = (interval '1' day + interval '-1' day)
----
true

## Millisecond/Microsecond parsing

query T
SELECT INTERVAL '1 milliseconds'
----
00:00:00.001

query T
SELECT INTERVAL '1002 milliseconds'
----
00:00:01.002

query T
SELECT INTERVAL '1002 milliseconds 1 second'
----
00:00:02.002

query T
SELECT INTERVAL '1 second 42 milliseconds'
----
00:00:01.042

query T
SELECT INTERVAL '1.0 second 42 milliseconds'
----
00:00:01.042

query T
SELECT INTERVAL '5 microseconds'
----
00:00:00.000005

query T
SELECT INTERVAL '5000006 microseconds'
----
00:00:05.000006

query T
SELECT INTERVAL '5000006 microseconds 1 second'
----
00:00:06.000006

query T
SELECT INTERVAL '27 second 24 microseconds'
----
00:00:27.000024

query T
SELECT INTERVAL '27.0 second 24 microseconds'
----
00:00:27.000024

query T
SELECT INTERVAL '27.0 second 46 milliseconds 24 microseconds'
----
00:00:27.046024

statement error Cannot set MILLISECONDS or MICROSECONDS field if SECOND field has a fraction component
SELECT INTERVAL '1.5 second 42 milliseconds'

statement error Cannot set MILLISECONDS or MICROSECONDS field if SECOND field has a fraction component
SELECT INTERVAL '1.5 second 43 microseconds'

statement error Cannot set MILLISECONDS or MICROSECONDS field if SECOND field has a fraction component
SELECT INTERVAL '1.5 second 42 milliseconds 43 microseconds'

statement error Expected end of statement, found string literal
SELECT INTERVAL '6 days 1.5 second 42 milliseconds' HOURS to MILLISECONDS

## Millennium/Century/Decade parsing

query T
SELECT INTERVAL '1 millennium'
----
1000 years

query T
SELECT INTERVAL '2 century'
----
200 years

query T
SELECT INTERVAL '3 decade'
----
30 years

query T
SELECT INTERVAL '4 year'
----
4 years

query T
SELECT INTERVAL '1 millenniums 2 centuries 3 decades 4 years'
----
1234 years

query error "-178956970 years -8 months" interval out of range
SELECT -INTERVAL '-2147483648 months';

query error "-2147483648 days" interval out of range
SELECT -INTERVAL '-2147483648 days';

query error "-2562047788:00:54.775808" interval out of range
SELECT -INTERVAL '-9223372036854775808 microseconds';

query error "-1 months - -178956970 years -8 months" interval out of range
SELECT INTERVAL '-1 months' - INTERVAL '-2147483648 months';

query error "-1 days - -2147483648 days" interval out of range
SELECT INTERVAL '-1 day' - INTERVAL '-2147483648 days';

query error "-01:00:00 \+ -2562047788:00:00" interval out of range
SELECT INTERVAL '-1 hours' + INTERVAL '-2562047788 hours';

query error "-2147483648 days -2562047787:59:59.999999" interval out of range
SELECT -INTERVAL '-2147483648 days -2562047787 hours -59 minutes -59.999999 seconds';

# Cast Interval to time
query T
SELECT INTERVAL '-1 hour -2 minutes -3.45 seconds'::time;
----
22:57:56.55

query T
SELECT INTERVAL '1 hour 2 minutes 3.45 seconds'::time;
----
01:02:03.45

query T
SELECT INTERVAL '6 days'::time;
----
00:00:00

query T
SELECT INTERVAL '7 months'::time;
----
00:00:00

query T
SELECT INTERVAL '-86400000001 us'::time;
----
23:59:59.999999

query T
SELECT CAST(interval '-02:03' AS time) AS "21:57:00";
----
21:57:00

query T
SELECT CAST(interval '-9223372022400000000 us' AS time) AS "00:00:00";
----
00:00:00

# Sub microseconds get rounded to microseconds
query T
SELECT INTERVAL '01:00:01.00000009';
----
01:00:01

query T
SELECT INTERVAL '01:00:01.0000009';
----
01:00:01.000001

query T
SELECT INTERVAL '01:00:01.0000004';
----
01:00:01

query T
SELECT INTERVAL '1.4 microseconds';
----
00:00:00.000001

query T
SELECT INTERVAL '1.5 microseconds';
----
00:00:00.000002

query T
SELECT INTERVAL '1.4 microseconds';
----
00:00:00.000001

query T
SELECT INTERVAL '1.5 microseconds';
----
00:00:00.000002

query T
SELECT INTERVAL '0.4 microseconds';
----
00:00:00

# Postgres actually returns 00:00:00 here, which is most likely a bug/rounding error on their part
query T
SELECT INTERVAL '0.5 microseconds';
----
00:00:00.000001

query T
SELECT INTERVAL '0.6 microseconds';
----
00:00:00.000001

# PostgreSQL actually has a bug where when using EXTRACT it truncates this value to 365, but
# when using date_part it does not truncate this value. Therefore our EXTRACT function may differ
# from PostgreSQL.
# EXTRACT: https://github.com/postgres/postgres/blob/c2e8bd27519f47ff56987b30eb34a01969b9a9e8/src/backend/utils/adt/timestamp.c#L5270-L5273
# date_part: https://github.com/postgres/postgres/blob/c2e8bd27519f47ff56987b30eb34a01969b9a9e8/src/backend/utils/adt/timestamp.c#L5301
query T
SELECT EXTRACT(epoch FROM INTERVAL '2147483647 months 2147483647 days 2147483647 hours 59 minutes 59.999999 seconds');
----
5840726022849599.999999

query T
SELECT EXTRACT(epoch FROM INTERVAL '-2147483648 months -2147483648 days -2147483648 hours -59 minutes -59.999999 seconds');
----
-5840726025531599.999999

# Interval comparison is done on a field by field basis, without combining the fields. This is due to a limitation in
# Materialize (See: https://github.com/MaterializeInc/database-issues/issues/3130)

query T
SELECT INTERVAL '1 month' > INTERVAL '9999999 days';
----
true

query T
SELECT INTERVAL '-1 month' < INTERVAL '-9999999 days';
----
true

query T
SELECT INTERVAL '1 month' > INTERVAL '999999999 hours';
----
true

query T
SELECT INTERVAL '-1 month' < INTERVAL '-999999999 hours';
----
true

query T
SELECT INTERVAL '1 day' > INTERVAL '9999999 hours';
----
true

query T
SELECT INTERVAL '-1 day' < INTERVAL '-9999999 hours';
----
true

# Justify tests
query T
SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds');
----
6 months 5 days 04:03:02

query T
SELECT justify_hours(interval '27 hours');
----
1 day 03:00:00

query T
SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds');
----
7 months 6 days 05:04:03

query T
SELECT justify_days(interval '35 days');
----
1 month 5 days

query T
SELECT justify_interval(interval '1 month -1 hour');
----
29 days 23:00:00

query error "2147483647 days 24:00:00" interval out of range
SELECT justify_hours(interval '2147483647 days 24 hrs');

query error "178956970 years 7 months 30 days" interval out of range
SELECT justify_days(interval '2147483647 months 30 days');

query T
SELECT justify_interval(interval '2147483647 days 24 hrs');
----
5965232 years 4 months 8 days

query T
SELECT justify_interval(interval '-2147483648 days -24 hrs');
----
-5965232 years -4 months -9 days

query error "178956970 years 7 months 30 days" interval out of range
SELECT justify_interval(interval '2147483647 months 30 days');

query error "-178956970 years -8 months -30 days" interval out of range
SELECT justify_interval(interval '-2147483648 months -30 days');

query T
SELECT justify_interval(interval '2147483647 months 30 days -24 hrs');
----
178956970 years 7 months 29 days

query T
SELECT justify_interval(interval '-2147483648 months -30 days 24 hrs');
----
-178956970 years -8 months -29 days

query error "178956970 years 7 months -30 days \+1440:00:00" interval out of range
SELECT justify_interval(interval '2147483647 months -30 days 1440 hrs');

query error "-178956970 years -8 months \+30 days -1440:00:00" interval out of range
SELECT justify_interval(interval '-2147483648 months 30 days -1440 hrs');

# TIMESTAMP/DATE builtins.

query T
SELECT age('2001-04-10 22:06:45', '1957-06-13')
----
43 years 9 months 27 days 22:06:45

query T
SELECT age('2017-12-10'::timestamptz, '2017-12-01'::timestamptz)
----
9 days

query T
SELECT age('2017-12-10 04:05:01.555'::timestamp, '2017-12-10 04:05:01.550'::timestamp);
----
00:00:00.005

query T
SELECT age('0001-06-01 08:10:56.555'::timestamp, '9999-12-10 04:05:01.550'::timestamp);
----
-9998 years -6 months -8 days -19:54:04.995

query B
SELECT age('2020-04-05'::date + '03:00'::time, '2020-04-05'::date + '02:00'::time) = '1 hour'::interval;
----
true

query T
SELECT age(to_timestamp(-210833720368), to_timestamp(8200000000000));
----
-266528 years -8 months -17 days -12:06:08

query T
SELECT age(to_timestamp(8200000000000), to_timestamp(-210833720368));
----
266528 years 8 months 17 days 12:06:08

# Aggregation

query T
SELECT MAX(unnest) FROM (SELECT UNNEST(ARRAY[INTERVAL '1-2 3 4:5:6.7' MONTH, INTERVAL '1-2 3 4:5:6.7' YEAR]));
----
1 year 2 months

query T
SELECT MAX(unnest) FROM (SELECT UNNEST(ARRAY[INTERVAL '2' SECOND, INTERVAL '1' SECOND]));
----
00:00:02

query T
SELECT MIN(unnest) FROM (SELECT UNNEST(ARRAY[INTERVAL '2' SECOND, INTERVAL '1' SECOND]));
----
00:00:01