Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# 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 Amazon Redshift 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](#r_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, Amazon Redshift 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, Amazon Redshift 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, Amazon Redshift 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. For more information, see [interval\$1forbid\$1composite\$1literals](r_interval_forbid_composite_literals.md).
## 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/redshift/latest/dg/r_interval_data_types.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/redshift/latest/dg/r_interval_data_types.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/redshift/latest/dg/r_interval_data_types.html)
## Interval styles
You can use the SQL [SET](r_SET.md) command to change the output display format of your interval values. When you use the interval data type in SQL, cast it to text to see the expected interval style, for example, `YEAR TO MONTH::text`. Available values to SET the `IntervalStyle` value are:
+ `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, Amazon Redshift 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, Amazon Redshift 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](r_interval_data_types.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
```