# Interval data types and literals
You can use an interval data type to store durations of time in units such as, `seconds`, `minutes`, `hours`, `days`, `months`, and `years`. Interval data types and literals can be used in datetime calculations, such as, adding intervals to dates and timestamps, summing intervals, and subtracting an interval from a date or timestamp. Interval literals can be used as input values to interval data type columns in a table.
## Syntax of interval data type
To specify an interval data type to store a duration of time in years and months:
```
INTERVAL year_to_month_qualifier
```
To specify an interval data type to store a duration in days, hours, minutes, and seconds:
```
INTERVAL day_to_second_qualifier [ (fractional_precision) ]
```
## Syntax of interval literal
To specify an interval literal to define a duration of time in years and months:
```
INTERVAL quoted-string year_to_month_qualifier
```
To specify an interval literal to define a duration in days, hours, minutes, and seconds:
```
INTERVAL quoted-string day_to_second_qualifier [ (fractional_precision) ]
```
## Arguments
*quoted-string*
Specifies a positive or negative numeric value specifying a quantity and the datetime unit as an input string. If the *quoted-string* contains only a numeric, then AWS Clean Rooms determines the units from the *year\$1to\$1month\$1qualifier* or *day\$1to\$1second\$1qualifier*. For example, `'23' MONTH` represents `1 year 11 months`, `'-2' DAY` represents `-2 days 0 hours 0 minutes 0.0 seconds`, `'1-2' MONTH` represents `1 year 2 months`, and `'13 day 1 hour 1 minute 1.123 seconds' SECOND` represents `13 days 1 hour 1 minute 1.123 seconds`. For more information about output formats of an interval, see [Interval styles](#interval_data_types-interval-styles).
*year\$1to\$1month\$1qualifier*
Specifies the range of the interval. If you use a qualifier and create an interval with time units smaller than the qualifier, AWS Clean Rooms truncates and discards the smaller parts of the interval. Valid values for *year\$1to\$1month\$1qualifier* are:
+ `YEAR`
+ `MONTH`
+ `YEAR TO MONTH`
*day\$1to\$1second\$1qualifier*
Specifies the range of the interval. If you use a qualifier and create an interval with time units smaller than the qualifier, AWS Clean Rooms truncates and discards the smaller parts of the interval. Valid values for *day\$1to\$1second\$1qualifier* are:
+ `DAY`
+ `HOUR`
+ `MINUTE`
+ `SECOND`
+ `DAY TO HOUR`
+ `DAY TO MINUTE`
+ `DAY TO SECOND`
+ `HOUR TO MINUTE`
+ `HOUR TO SECOND`
+ `MINUTE TO SECOND`
The output of the INTERVAL literal is truncated to the smallest INTERVAL component specified. For example, when using a MINUTE qualifier, AWS Clean Rooms discards the time units smaller than MINUTE.
```
select INTERVAL '1 day 1 hour 1 minute 1.123 seconds' MINUTE
```
The resulting value is truncated to `'1 day 01:01:00'`.
*fractional\$1precision*
Optional parameter that specifies the number of fractional digits allowed in the interval. The *fractional\$1precision* argument should only be specified if your interval contains SECOND. For example, `SECOND(3)` creates an interval that allows only three fractional digits, such as 1.234 seconds. The maximum number of fractional digits is six.
The session configuration `interval_forbid_composite_literals` determines whether an error is returned when an interval is specified with both YEAR TO MONTH and DAY TO SECOND parts.
## Interval arithmetic
You can use interval values with other datetime values to perform arithmetic operations. The following tables describe the available operations and what data type results from each operation.
**Note**
Operations that can produce both `date` and `timestamp` results do so based on the smallest unit of time involved in the equation. For example, when you add an `interval` to a `date` the result is a `date` if it is a YEAR TO MONTH interval, and a timestamp if it is a DAY TO SECOND interval.
Operations where the first operand is an `interval` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/interval_data_types_spark.html)
Operations where the first operand is a `date` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/interval_data_types_spark.html)
Operations where the first operand is a `timestamp` produce the following results for the given second operand:
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/interval_data_types_spark.html)
## Interval styles
+ `postgres` – follows PostgreSQL style. This is the default.
+ `postgres_verbose` – follows PostgreSQL verbose style.
+ `sql_standard` – follows the SQL standard interval literals style.
The following command sets the interval style to `sql_standard`.
```
SET IntervalStyle to 'sql_standard';
```
**postgres output format**
The following is the output format for `postgres` interval style. Each numeric value can be negative.
```
' [, ...]'
```
```
select INTERVAL '1-2' YEAR TO MONTH::text
varchar
---------------
1 year 2 mons
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
------------------
1 day 02:03:04.5678
```
**postgres\$1verbose output format**
postgres\$1verbose syntax is similar to postgres, but postgres\$1verbose outputs also contain the unit of time.
```
'[@] [, ...] [direction]'
```
```
select INTERVAL '1-2' YEAR TO MONTH::text
varchar
-----------------
@ 1 year 2 mons
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
---------------------------
@ 1 day 2 hours 3 mins 4.56 secs
```
**sql\$1standard output format**
Interval year to month values are formatted as the following. Specifying a negative sign before the interval indicates the interval is a negative value and applies to the entire interval.
```
'[-]yy-mm'
```
Interval day to second values are formatted as the following.
```
'[-]dd hh:mm:ss.ffffff'
```
```
SELECT INTERVAL '1-2' YEAR TO MONTH::text
varchar
-------
1-2
```
```
select INTERVAL '1 2:3:4.5678' DAY TO SECOND::text
varchar
---------------
1 2:03:04.5678
```
## Examples of interval data type
The following examples demonstrate how to use INTERVAL data types with tables.
```
create table sample_intervals (y2m interval month, h2m interval hour to minute);
insert into sample_intervals values (interval '20' month, interval '2 days 1:1:1.123456' day to second);
select y2m::text, h2m::text from sample_intervals;
y2m | h2m
---------------+-----------------
1 year 8 mons | 2 days 01:01:00
```
```
update sample_intervals set y2m = interval '2' year where y2m = interval '1-8' year to month;
select * from sample_intervals;
y2m | h2m
---------+-----------------
2 years | 2 days 01:01:00
```
```
delete from sample_intervals where h2m = interval '2 1:1:0' day to second;
select * from sample_intervals;
y2m | h2m
-----+-----
```
## Examples of interval literals
The following examples are run with interval style set to `postgres`.
The following example demonstrates how to create an INTERVAL literal of 1 year.
```
select INTERVAL '1' YEAR
intervaly2m
---------------
1 years 0 mons
```
If you specify a *quoted-string* that exceeds the qualifier, the remaining units of time are truncated from the interval. In the following example, an interval of 13 months becomes 1 year and 1 month, but the remaining 1 month is left out because of the YEAR qualifier.
```
select INTERVAL '13 months' YEAR
intervaly2m
---------------
1 years 0 mons
```
If you use a qualifier lower than your interval string, leftover units are included.
```
select INTERVAL '13 months' MONTH
intervaly2m
---------------
1 years 1 mons
```
Specifying a precision in your interval truncates the number of fractional digits to the specified precision.
```
select INTERVAL '1.234567' SECOND (3)
intervald2s
--------------------------------
0 days 0 hours 0 mins 1.235 secs
```
If you don't specify a precision, AWS Clean Rooms uses the maximum precision of 6.
```
select INTERVAL '1.23456789' SECOND
intervald2s
-----------------------------------
0 days 0 hours 0 mins 1.234567 secs
```
The following example demonstrates how to create a ranged interval.
```
select INTERVAL '2:2' MINUTE TO SECOND
intervald2s
------------------------------
0 days 0 hours 2 mins 2.0 secs
```
Qualifiers dictate the units that you're specifying. For example, even though the following example uses the same *quoted-string* of '2:2' as the previous example, AWS Clean Rooms recognizes that it uses different units of time because of the qualifier.
```
select INTERVAL '2:2' HOUR TO MINUTE
intervald2s
------------------------------
0 days 2 hours 2 mins 0.0 secs
```
Abbreviations and plurals of each unit are also supported. For example, `5s`, `5 second`, and `5 seconds` are equivalent intervals. Supported units are years, months, hours, minutes, and seconds.
```
select INTERVAL '5s' SECOND
intervald2s
------------------------------
0 days 0 hours 0 mins 5.0 secs
```
```
select INTERVAL '5 HOURS' HOUR
intervald2s
------------------------------
0 days 5 hours 0 mins 0.0 secs
```
```
select INTERVAL '5 h' HOUR
intervald2s
------------------------------
0 days 5 hours 0 mins 0.0 secs
```
# Examples of interval literals without qualifier syntax
**Note**
The following examples demonstrate using an interval literal without a `YEAR TO MONTH` or `DAY TO SECOND` qualifier. For information about using the recommended interval literal with a qualifier, see [Interval data types and literals](interval_data_types_spark.md).
Use an interval literal to identify specific periods of time, such as `12 hours` or `6 months`. You can use these interval literals in conditions and calculations that involve datetime expressions.
An interval literal is expressed as a combination of the INTERVAL keyword with a numeric quantity and a supported date part, for example `INTERVAL '7 days'` or `INTERVAL '59 minutes'`. You can connect several quantities and units to form a more precise interval, for example: `INTERVAL '7 days, 3 hours, 59 minutes'`. Abbreviations and plurals of each unit are also supported; for example: `5 s`, `5 second`, and `5 seconds` are equivalent intervals.
If you don't specify a date part, the interval value represents seconds. You can specify the quantity value as a fraction (for example: `0.5 days`).
The following examples show a series of calculations with different interval values.
The following adds 1 second to the specified date.
```
select caldate + interval '1 second' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 00:00:01
(1 row)
```
The following adds 1 minute to the specified date.
```
select caldate + interval '1 minute' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 00:01:00
(1 row)
```
The following adds 3 hours and 35 minutes to the specified date.
```
select caldate + interval '3 hours, 35 minutes' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 03:35:00
(1 row)
```
The following adds 52 weeks to the specified date.
```
select caldate + interval '52 weeks' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2009-12-30 00:00:00
(1 row)
```
The following adds 1 week, 1 hour, 1 minute, and 1 second to the specified date.
```
select caldate + interval '1w, 1h, 1m, 1s' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2009-01-07 01:01:01
(1 row)
```
The following adds 12 hours (half a day) to the specified date.
```
select caldate + interval '0.5 days' as dateplus from date
where caldate='12-31-2008';
dateplus
---------------------
2008-12-31 12:00:00
(1 row)
```
The following subtracts 4 months from February 15, 2023 and the result is October 15, 2022.
```
select date '2023-02-15' - interval '4 months';
?column?
---------------------
2022-10-15 00:00:00
```
The following subtracts 4 months from March 31, 2023 and the result is November 30, 2022. The calculation considers the number of days in a month.
```
select date '2023-03-31' - interval '4 months';
?column?
---------------------
2022-11-30 00:00:00
```