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

# Ensure doesn't round when rounded digit is < 5.
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

# 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 literal unsigned integer, found operator
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 one of YEAR or MONTH or DAY or HOUR or MINUTE or SECOND or YEARS or MONTHS or DAYS or HOURS or MINUTES or SECONDS, found identifier "milliseconds"
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

# 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/materialize/issues/10566)

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