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/).
# Datetime types
**Topics**
+ [
## Storage and ranges
](#r_Datetime_types-storage-and-ranges)
+ [
## DATE
](#r_Datetime_types-date)
+ [
## TIME
](#r_Datetime_types-time)
+ [
## TIMETZ
](#r_Datetime_types-timetz)
+ [
## TIMESTAMP
](#r_Datetime_types-timestamp)
+ [
## TIMESTAMPTZ
](#r_Datetime_types-timestamptz)
+ [
# Examples with datetime types
](r_Examples_with_datetime_types.md)
+ [
# Date, time, and timestamp literals
](r_Date_and_time_literals.md)
+ [
# Interval data types and literals
](r_interval_data_types.md)
Datetime data types include DATE, TIME, TIMETZ, TIMESTAMP, and TIMESTAMPTZ.
## Storage and ranges
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html)
## DATE
Use the DATE data type to store simple calendar dates without timestamps.
## TIME
TIME is an alias of TIME WITHOUT TIME ZONE.
Use the TIME data type to store the time of day.
TIME columns store values with up to a maximum of six digits of precision for fractional seconds.
By default, TIME values are Coordinated Universal Time (UTC) in both user tables and Amazon Redshift system tables.
## TIMETZ
TIMETZ is an alias of TIME WITH TIME ZONE.
Use the TIMETZ data type to store the time of day with a time zone.
TIMETZ columns store values with up to a maximum of six digits of precision for fractional seconds.
By default, TIMETZ values are UTC in both user tables and Amazon Redshift system tables.
## TIMESTAMP
TIMESTAMP is an alias of TIMESTAMP WITHOUT TIME ZONE.
Use the TIMESTAMP data type to store complete timestamp values that include the date and the time of day.
TIMESTAMP columns store values with up to a maximum of six digits of precision for fractional seconds.
If you insert a date into a TIMESTAMP column, or a date with a partial timestamp value, the value is implicitly converted into a full timestamp value. This full timestamp value has default values (00) for missing hours, minutes, and seconds. Time zone values in input strings are ignored.
By default, TIMESTAMP values are UTC in both user tables and Amazon Redshift system tables.
## TIMESTAMPTZ
TIMESTAMPTZ is an alias of TIMESTAMP WITH TIME ZONE.
Use the TIMESTAMPTZ data type to input complete timestamp values that include the date, the time of day, and a time zone. When an input value includes a time zone, Amazon Redshift uses the time zone to convert the value to UTC and stores the UTC value.
To view a list of supported time zone names, run the following command.
```
select pg_timezone_names();
```
To view a list of supported time zone abbreviations, run the following command.
```
select pg_timezone_abbrevs();
```
You can also find current information about time zones in the [IANA Time Zone Database](https://www.iana.org/time-zones).
The following table has examples of time zone formats.
| Format | Example |
| --- | --- |
| dd mon hh:mi:ss yyyy tz | 17 Dec 07:37:16 1997 PST |
| mm/dd/yyyy hh:mi:ss.ss tz | 12/17/1997 07:37:16.00 PST |
| mm/dd/yyyy hh:mi:ss.ss tz | 12/17/1997 07:37:16.00 US/Pacific |
| yyyy-mm-dd hh:mi:ss\$1/-tz | 1997-12-17 07:37:16-08 |
| dd.mm.yyyy hh:mi:ss tz | 17.12.1997 07:37:16.00 PST |
TIMESTAMPTZ columns store values with up to a maximum of six digits of precision for fractional seconds.
If you insert a date into a TIMESTAMPTZ column, or a date with a partial timestamp, the value is implicitly converted into a full timestamp value. This full timestamp value has default values (00) for missing hours, minutes, and seconds.
TIMESTAMPTZ values are UTC in user tables.
# Examples with datetime types
Following, you can find examples for working with datetime types supported by Amazon Redshift.
## Date examples
The following examples insert dates that have different formats and display the output.
```
create table datetable (start_date date, end_date date);
```
```
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values ('Jun 1,2008','20081231');
```
```
select * from datetable order by 1;
start_date | end_date
-----------------------
2008-06-01 | 2008-12-31
2008-06-01 | 2008-12-31
```
If you insert a timestamp value into a DATE column, the time portion is ignored and only the date is loaded.
## Time examples
The following examples insert TIME and TIMETZ values that have different formats and display the output.
```
create table timetable (start_time time, end_time timetz);
```
```
insert into timetable values ('19:11:19','20:41:19 UTC');
insert into timetable values ('191119', '204119 UTC');
```
```
select * from timetable order by 1;
start_time | end_time
------------------------
19:11:19 | 20:41:19+00
19:11:19 | 20:41:19+00
```
## Time stamp examples
If you insert a date into a TIMESTAMP or TIMESTAMPTZ column, the time defaults to midnight. For example, if you insert the literal `20081231`, the stored value is `2008-12-31 00:00:00`.
To change the time zone for the current session, use the [SET](r_SET.md) command to set the [timezone](r_timezone_config.md) configuration parameter.
The following example inserts timestamps that have different formats and display the resulting table.
```
create table tstamp(timeofday timestamp, timeofdaytz timestamptz);
insert into tstamp values('Jun 1,2008 09:59:59', 'Jun 1,2008 09:59:59 EST' );
insert into tstamp values('Dec 31,2008 18:20','Dec 31,2008 18:20');
insert into tstamp values('Jun 1,2008 09:59:59 EST', 'Jun 1,2008 09:59:59');
SELECT * FROM tstamp;
+---------------------+------------------------+
| timeofday | timeofdaytz |
+---------------------+------------------------+
| 2008-06-01 09:59:59 | 2008-06-01 14:59:59+00 |
| 2008-12-31 18:20:00 | 2008-12-31 18:20:00+00 |
| 2008-06-01 09:59:59 | 2008-06-01 09:59:59+00 |
+---------------------+------------------------+
```
# Date, time, and timestamp literals
Following are rules for working with date, time, and timestamp literals supported by Amazon Redshift.
## Dates
The following input dates are all valid examples of literal date values for the DATE data type that you can load into Amazon Redshift tables. The default `MDY DateStyle` mode is assumed to be in effect. This mode means that the month value precedes the day value in strings such as `1999-01-08` and `01/02/00`.
**Note**
A date or timestamp literal must be enclosed in quotation marks when you load it into a table.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Times
The following input times are all valid examples of literal time values for the TIME and TIMETZ data types that you can load into Amazon Redshift tables.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Timestamps
The following input timestamps are all valid examples of literal time values for the TIMESTAMP and TIMESTAMPTZ data types that you can load into Amazon Redshift tables. All of the valid date literals can be combined with the following time literals.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
## Special datetime values
The following special values can be used as datetime literals and as arguments to date functions. They require single quotation marks and are converted to regular timestamp values during query processing.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_Date_and_time_literals.html)
The following examples show how `now` and `today` work with the DATEADD function.
```
select dateadd(day,1,'today');
date_add
---------------------
2009-11-17 00:00:00
(1 row)
select dateadd(day,1,'now');
date_add
----------------------------
2009-11-17 10:45:32.021394
(1 row)
```
# 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
```