# Datetime types
Datetime data types include DATE, TIME, TIMESTAMP\$1LTZ, and TIMESTAMP\$1NTZ.
**Topics**
+ [DATE](Datetime_types-date.md)
+ [TIMESTAMP\$1LTZ](Datetime_types-timestamp_LTZ.md)
+ [TIMESTAMP\$1NTZ](Datetime_types-TIMESTAMP_NTZ.md)
+ [Examples with datetime types](Examples_with_datetime_types.md)
+ [Date, time, and timestamp literals](Date_and_time_literals.md)
+ [Interval literals](Interval_literals.md)
+ [Interval data types and literals](interval_data_types_spark.md)
# DATE
Use the DATE data type to store simple calendar dates without timestamps.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Datetime_types-date.html)
# TIMESTAMP\$1LTZ
Use the TIMESTAMP\$1LTZ data type to store complete timestamp values that include the date, the time of day, and the local time zone.
TIMESTAMP represents values comprising values of fields `year`, `month`, `day`, `hour`, `minute`, and `second`, with the session local timezone. The `timestamp` value represents an absolute point in time.
TIMESTAMP in Spark is a user-specified alias associated with one of the TIMESTAMP\$1LTZ and TIMESTAMP\$1NTZ variations. You can set the default timestamp type as TIMESTAMP\$1LTZ (default value) or TIMESTAMP\$1NTZ via the configuration `spark.sql.timestampType`.
# TIMESTAMP\$1NTZ
Use the TIMESTAMP\$1NTZ data type to store complete timestamp values that include the date, the time of day, without the local time zone.
TIMESTAMP represents values comprising values of fields `year`, `month`, `day`, `hour`, `minute`, and `second`. All operations are performed without taking any time zone into account.
TIMESTAMP in Spark is a user-specified alias associated with one of the TIMESTAMP\$1LTZ and TIMESTAMP\$1NTZ variations. You can set the default timestamp type as TIMESTAMP\$1LTZ (default value) or TIMESTAMP\$1NTZ via the configuration `spark.sql.timestampType`.
# Examples with datetime types
The following examples show you how to work with datetime types that are supported by AWS Clean Rooms.
## Date examples
The following examples insert dates that have different formats and display the output.
```
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.
```
select * from timetable order by 1;
start_time | end_time
------------------------
19:11:19 | 20:41:19+00
19:11:19 | 20:41:19+00
```
# Date, time, and timestamp literals
Following are rules for working with date, time, and timestamp literals that are supported by AWS Clean Rooms Spark SQL.
## Dates
The following table shows input dates that are valid examples of literal date values that you can load into AWS Clean Rooms 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/clean-rooms/latest/sql-reference/Date_and_time_literals.html)
## Times
The following table shows input times that are valid examples of literal time values that you can load into AWS Clean Rooms tables.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Date_and_time_literals.html)
## Special datetime values
The following table shows special values that 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/clean-rooms/latest/sql-reference/Date_and_time_literals.html)
The following examples show how `now` and `today` work with the DATE\$1ADD function.
```
select date_add('today', 1);
date_add
---------------------
2009-11-17 00:00:00
(1 row)
select date_add('now', 1);
date_add
----------------------------
2009-11-17 10:45:32.021394
(1 row)
```
# Interval literals
Following are rules for working with interval literals that are supported by AWS Clean Rooms Spark SQL.
Use an interval literal to identify specific periods of time, such as `12 hours` or `6 weeks`. You can use these interval literals in conditions and calculations that involve datetime expressions.
**Note**
You can't use the INTERVAL data type for columns in AWS Clean Rooms tables.
An interval 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`).
## Examples
The following examples show a series of calculations with different interval values.
The following example 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 example 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 example 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 example 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 example 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 example 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 example 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
```
# 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
```