# Data types
Each value that AWS Clean Rooms Spark SQL stores or retrieves has a data type with a fixed set of associated properties. Data types are declared when tables are created. A data type constrains the set of values that a column or argument can contain.
The following table lists the data types that you can use in AWS Clean Rooms Spark SQL.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/s_Supported_data_types.html)
**Note**
The ARRAY, STRUCT, and MAP nested data types are currently only enabled for the custom analysis rule. For more information, see [Nested type](s_Nested-data-type.md).
## Multibyte characters
The VARCHAR data type supports UTF-8 multibyte characters up to a maximum of four bytes. Five-byte or longer characters are not supported. To calculate the size of a VARCHAR column that contains multibyte characters, multiply the number of characters by the number of bytes per character. For example, if a string has four Chinese characters, and each character is three bytes long, then you will need a VARCHAR(12) column to store the string.
The VARCHAR data type doesn't support the following invalid UTF-8 codepoints:
`0xD800 – 0xDFFF` (Byte sequences: `ED A0 80` – `ED BF BF`)
The CHAR data type doesn't support multibyte characters.
# Numeric types
Numeric data types include integers, decimals, and floating-point numbers.
**Topics**
+ [Integer types](Numeric_types-integer-types.md)
+ [DECIMAL or NUMERIC type](Numeric_types-decimal-or-numeric-type.md)
+ [Floating-point types](Numeric_types-floating-point-types.md)
+ [Computations with numeric values](Numeric_computations.md)
# Integer types
Use the following data types to store whole numbers of various ranges. You can't store values outside of the allowed range for each type.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_types-integer-types.html)
# DECIMAL or NUMERIC type
Use the DECIMAL or NUMERIC data type to store values with a *user-defined precision*. The DECIMAL and NUMERIC keywords are interchangeable. In this document, *decimal* is the preferred term for this data type. The term *numeric* is used generically to refer to integer, decimal, and floating-point data types.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_types-decimal-or-numeric-type.html)
Define a DECIMAL column in a table by specifying a *precision* and *scale*:
```
decimal(precision, scale)
```
*precision*
The total number of significant digits in the whole value: the number of digits on both sides of the decimal point. For example, the number `48.2891` has a precision of 6 and a scale of 4. The default precision, if not specified, is 18. The maximum precision is 38.
If the number of digits to the left of the decimal point in an input value exceeds the precision of the column minus its scale, the value can't be copied into the column (or inserted or updated). This rule applies to any value that falls outside the range of the column definition. For example, the allowed range of values for a `numeric(5,2)` column is `-999.99` to `999.99`.
*scale*
The number of decimal digits in the fractional part of the value, to the right of the decimal point. Integers have a scale of zero. In a column specification, the scale value must be less than or equal to the precision value. The default scale, if not specified, is 0. The maximum scale is 37.
If the scale of an input value that is loaded into a table is greater than the scale of the column, the value is rounded to the specified scale. For example, the PRICEPAID column in the SALES table is a DECIMAL(8,2) column. If a DECIMAL(8,4) value is inserted into the PRICEPAID column, the value is rounded to a scale of 2.
```
insert into sales
values (0, 8, 1, 1, 2000, 14, 5, 4323.8951, 11.00, null);
select pricepaid, salesid from sales where salesid=0;
pricepaid | salesid
-----------+---------
4323.90 | 0
(1 row)
```
However, results of explicit casts of values selected from tables are not rounded.
**Note**
The maximum positive value that you can insert into a DECIMAL(19,0) column is `9223372036854775807` (263 -1). The maximum negative value is `-9223372036854775807`. For example, an attempt to insert the value `9999999999999999999` (19 nines) will cause an overflow error. Regardless of the placement of the decimal point, the largest string that AWS Clean Rooms can represent as a DECIMAL number is `9223372036854775807`. For example, the largest value that you can load into a DECIMAL(19,18) column is `9.223372036854775807`.
These rules are because of the following:
DECIMAL values with 19 or fewer significant digits of precision are stored internally as 8-byte integers.
DECIMAL values with 20 to 38 significant digits of precision are stored as 16-byte integers.
## Notes about using 128-bit DECIMAL or NUMERIC columns
Do not arbitrarily assign maximum precision to DECIMAL columns unless you are certain that your application requires that precision. 128-bit values use twice as much disk space as 64-bit values and can slow down query execution time.
# Floating-point types
Use the REAL and DOUBLE PRECISION data types to store numeric values with *variable precision*. These types are *inexact* types, meaning that some values are stored as approximations, such that storing and returning a specific value may result in slight discrepancies. If you require exact storage and calculations (such as for monetary amounts), use the DECIMAL data type.
REAL represents the single-precision floating point format, according to the IEEE Standard 754 for Floating-Point Arithmetic. It has a precision of about 6 digits, and a range of around 1E-37 to 1E\$137. You can also specify this data type as FLOAT4.
DOUBLE PRECISION represents the double-precision floating point format, according to the IEEE Standard 754 for Binary Floating-Point Arithmetic. It has a precision of about 15 digits, and a range of around 1E-307 to 1E\$1308. You can also specify this data type as FLOAT or FLOAT8.
# Computations with numeric values
In AWS Clean Rooms, *computation* refers to binary mathematical operations: addition, subtraction, multiplication, and division. This section describes the expected return types for these operations, as well as the specific formula that is applied to determine precision and scale when DECIMAL data types are involved.
When numeric values are computed during query processing, you might encounter cases where the computation is impossible and the query returns a numeric overflow error. You might also encounter cases where the scale of computed values varies or is unexpected. For some operations, you can use explicit casting (type promotion) or AWS Clean Rooms configuration parameters to work around these problems.
For information about the results of similar computations with SQL functions, see [AWS Clean Rooms Spark SQL functions](sql-functions-topic-spark.md).
## Return types for computations
Given the set of numeric data types supported in AWS Clean Rooms, the following table shows the expected return types for addition, subtraction, multiplication, and division operations. The first column on the left side of the table represents the first operand in the calculation, and the top row represents the second operand.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_computations.html)
## Precision and scale of computed DECIMAL results
The following table summarizes the rules for computing resulting precision and scale when mathematical operations return DECIMAL results. In this table, `p1` and `s1` represent the precision and scale of the first operand in a calculation. `p2` and `s2` represent the precision and scale of the second operand. (Regardless of these calculations, the maximum result precision is 38, and the maximum result scale is 38.)
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Numeric_computations.html)
For example, the PRICEPAID and COMMISSION columns in the SALES table are both DECIMAL(8,2) columns. If you divide PRICEPAID by COMMISSION (or vice versa), the formula is applied as follows:
```
Precision = 8-2 + 2 + max(4,2+8-2+1)
= 6 + 2 + 9 = 17
Scale = max(4,2+8-2+1) = 9
Result = DECIMAL(17,9)
```
The following calculation is the general rule for computing the resulting precision and scale for operations performed on DECIMAL values with set operators such as UNION, INTERSECT, and EXCEPT or functions such as COALESCE and DECODE:
```
Scale = max(s1,s2)
Precision = min(max(p1-s1,p2-s2)+scale,19)
```
For example, a DEC1 table with one DECIMAL(7,2) column is joined with a DEC2 table with one DECIMAL(15,3) column to create a DEC3 table. The schema of DEC3 shows that the column becomes a NUMERIC(15,3) column.
```
select * from dec1 union select * from dec2;
```
In the above example, the formula is applied as follows:
```
Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15
Scale = max(2,3) = 3
Result = DECIMAL(15,3)
```
## Notes on division operations
For division operations, divide-by-zero conditions return errors.
The scale limit of 100 is applied after the precision and scale are calculated. If the calculated result scale is greater than 100, division results are scaled as follows:
+ Precision = ` precision - (scale - max_scale)`
+ Scale = ` max_scale `
If the calculated precision is greater than the maximum precision (38), the precision is reduced to 38, and the scale becomes the result of: `max(38 + scale - precision), min(4, 100))`
## Overflow conditions
Overflow is checked for all numeric computations. DECIMAL data with a precision of 19 or less is stored as 64-bit integers. DECIMAL data with a precision that is greater than 19 is stored as 128-bit integers. The maximum precision for all DECIMAL values is 38, and the maximum scale is 37. Overflow errors occur when a value exceeds these limits, which apply to both intermediate and final result sets:
+ Explicit casting results in runtime overflow errors when specific data values don't fit the requested precision or scale specified by the cast function. For example, you can't cast all values from the PRICEPAID column in the SALES table (a DECIMAL(8,2) column) and return a DECIMAL(7,3) result:
```
select pricepaid::decimal(7,3) from sales;
ERROR: Numeric data overflow (result precision)
```
This error occurs because *some* of the larger values in the PRICEPAID column can't be cast.
+ Multiplication operations produce results in which the result scale is the sum of the scale of each operand. If both operands have a scale of 4, for example, the result scale is 8, leaving only 10 digits for the left side of the decimal point. Therefore, it is relatively easy to run into overflow conditions when multiplying two large numbers that both have significant scale.
## Numeric calculations with INTEGER and DECIMAL types
When one of the operands in a calculation has an INTEGER data type and the other operand is DECIMAL, the INTEGER operand is implicitly cast as a DECIMAL.
+ SMALLINT or SHORT is cast as DECIMAL(5,0)
+ INTEGER is cast as DECIMAL(10,0)
+ BIGINT or LONG is cast as DECIMAL(19,0)
For example, if you multiply SALES.COMMISSION, a DECIMAL(8,2) column, and SALES.QTYSOLD, a SMALLINT column, this calculation is cast as:
```
DECIMAL(8,2) * DECIMAL(5,0)
```
# Character types
Character data types include CHAR (character) and VARCHAR (character varying).
**Topics**
+ [CHAR or CHARACTER](Character_types-char-or-character.md)
+ [VARCHAR or CHARACTER VARYING](Character_types-varchar-or-character-varying.md)
+ [Significance of trailing blanks](#Character_types-significance-of-trailing-blanks)
# CHAR or CHARACTER
Use a CHAR or CHARACTER column to store fixed-length strings. These strings are padded with blanks, so a CHAR(10) column always occupies 10 bytes of storage.
```
char(10)
```
A CHAR column without a length specification results in a CHAR(1) column.
CHAR and VARCHAR data types are defined in terms of bytes, not characters. A CHAR column can only contain single-byte characters, so a CHAR(10) column can contain a string with a maximum length of 10 bytes.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Character_types-char-or-character.html)
# VARCHAR or CHARACTER VARYING
Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.
```
varchar(120)
```
VARCHAR data types are defined in terms of bytes, not characters. A VARCHAR can contain multibyte characters, up to a maximum of four bytes per character. For example, a VARCHAR(12) column can contain 12 single-byte characters, 6 two-byte characters, 4 three-byte characters, or 3 four-byte characters.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/Character_types-varchar-or-character-varying.html)
## Significance of trailing blanks
Both CHAR and VARCHAR data types store strings up to *n* bytes in length. An attempt to store a longer string into a column of these types results in an error. However, if the extra characters are all spaces (blanks), the string is truncated to the maximum length. If the string is shorter than the maximum length, CHAR values are padded with blanks, but VARCHAR values store the string without blanks.
Trailing blanks in CHAR values are always semantically insignificant. They are disregarded when you compare two CHAR values, not included in LENGTH calculations, and removed when you convert a CHAR value to another string type.
Trailing spaces in VARCHAR and CHAR values are treated as semantically insignificant when values are compared.
Length calculations return the length of VARCHAR character strings with trailing spaces included in the length. Trailing blanks are not counted in the length for fixed-length character strings.
# 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
```
# Boolean type
Use the BOOLEAN data type to store true and false values in a single-byte column. The following table describes the three possible states for a Boolean value and the literal values that result in that state. Regardless of the input string, a Boolean column stores and outputs "t" for true and "f" for false.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/s_Boolean_type.html)
You can use an IS comparison to check a Boolean value only as a predicate in the WHERE clause. You can't use the IS comparison with a Boolean value in the SELECT list.
## Examples
You can use a BOOLEAN column to store an "Active/Inactive" state for each customer in a CUSTOMER table.
```
select * from customer;
custid | active_flag
-------+--------------
100 | t
```
In this example, the following query selects users from the USERS table who like sports but do not like theatre:
```
select firstname, lastname, likesports, liketheatre
from users
where likesports is true and liketheatre is false
order by userid limit 10;
firstname | lastname | likesports | liketheatre
----------+------------+------------+-------------
Alejandro | Rosalez | t | f
Akua | Mansa | t | f
Arnav | Desai | t | f
Carlos | Salazar | t | f
Diego | Ramirez | t | f
Efua | Owusu | t | f
John | Stiles | t | f
Jorge | Souza | t | f
Kwaku | Mensah | t | f
Kwesi | Manu | t | f
(10 rows)
```
The following example selects users from the USERS table for whom is it unknown whether they like rock music.
```
select firstname, lastname, likerock
from users
where likerock is unknown
order by userid limit 10;
firstname | lastname | likerock
----------+----------+----------
Alejandro | Rosalez |
Carlos | Salazar |
Diego | Ramirez |
John | Stiles |
Kwaku | Mensah |
Martha | Rivera |
Mateo | Jackson |
Paulo | Santos |
Richard | Roe |
Saanvi | Sarkar |
(10 rows)
```
The following example returns an error because it uses an IS comparison in the SELECT list.
```
select firstname, lastname, likerock is true as "check"
from users
order by userid limit 10;
[Amazon](500310) Invalid operation: Not implemented
```
The following example succeeds because it uses an equal comparison ( = ) in the SELECT list instead of the IS comparison.
```
select firstname, lastname, likerock = true as "check"
from users
order by userid limit 10;
firstname | lastname | check
----------+-----------+------
Alejandro | Rosalez |
Carlos | Salazar |
Diego | Ramirez | true
John | Stiles |
Kwaku | Mensah | true
Martha | Rivera | true
Mateo | Jackson |
Paulo | Santos | false
Richard | Roe |
Saanvi | Sarkar |
```
# Boolean literals
The following rules are for working with Boolean literals that are supported by AWS Clean Rooms Spark SQL.
Use a Boolean literal to specify a Boolean value, such as `TRUE` or `FALSE`.
## Syntax
```
TRUE | FALSE
```
## Example
The following example shows a column with a specified value of `TRUE` .
```
SELECT TRUE AS col;
+----+
| col|
+----+
|true|
+----+
```
# Binary type
Use the BINARY data type to store and manage fixed-length, uninterpreted binary data, providing efficient storage and comparison capabilities for specific use cases.
The BINARY data type stores a fixed number of bytes, regardless of the actual length of the data being stored. The maximum length is typically 255 bytes.
BINARY is used to store raw, uninterpreted binary data, such as images, documents, or other types of files. The data is stored exactly as it is provided, without any character encoding or interpretation. Binary data stored in BINARY columns is compared and sorted byte-by-byte, based on the actual binary values, rather than any character encoding or collation rules.
The following example query shows the binary representation of the string `"abc"`. Each character in the string is represented by its ASCII code in hexadecimal format: "a" is 0x61, "b" is 0x62, and "c" is 0x63. When combined, these hexadecimal values form the binary representation `"616263"`.
```
SELECT 'abc'::binary;
binary
---------
616263
```
# Nested type
AWS Clean Rooms supports queries involving data with nested data types, specifically the AWS Glue STRUCT, ARRAY, and MAP column types. Only the custom analysis rule supports nested data types.
Notably, nested data types don't conform to the rigid, tabular structure of the relational data model of SQL databases.
Nested data types contains tags that reference distinct entities within the data. They can contain complex values such as arrays, nested structures, and other complex structures that are associated with serialization formats, such as JSON. Nested data types support up to 1 MB of data for an individual nested data type field or object.
**Topics**
+ [ARRAY type](array_type.md)
+ [MAP type](map_type.md)
+ [STRUCT type](struct_type.md)
+ [Examples of nested data types](s_nested-data-type-examples.md)
# ARRAY type
Use the ARRAY type to represent values comprising a sequence of elements with the type of `elementType`.
```
array(elementType, containsNull)
```
Use `containsNull` to indicate if elements in an ARRAY type can have `null` values.
# MAP type
Use the MAP type to represent values comprising a set of key-value pairs.
```
map(keyType, valueType, valueContainsNull)
```
`keyType`: the data type of keys
`valueType`: the data type of values
Keys aren't allowed to have `null` values. Use `valueContainsNull` to indicate if values of a MAP type value can have `null` values.
# STRUCT type
Use the STRUCT type to represent values with the structure described by a sequence of StructFields (fields).
```
struct(name, dataType, nullable)
```
StructField(name, dataType, nullable): Represents a field in a StructType.
`dataType`: the data type a field
`name`: the name of a field
Use `nullable` to indicate if values of these fields can have `null` values.
# Examples of nested data types
For the `struct` type, there are two attribute names: `given`, and `family`, each corresponding to a `varchar` value.
For the `array` type, the array is specified as a list of `varchar`.
The `array>` type refers to a list of elements with `struct` type.
The `map` data type behaves like an `array` of `structs`, where the attribute name for each element in the array is denoted by `key` and it maps to a `value`.
**Example**
For example, the `map` type is treated as `array>`, where `key` and `value` refer to the attributes of the map in the underlying data.
For information about how AWS Clean Rooms enables navigation into arrays and structures, see [Navigation](query-nested-data.md#navigation).
For information about how AWS Clean Rooms enables iteration over arrays by navigating the array using the FROM clause of a query, see [Unnesting queries](query-nested-data.md#unnesting-queries).
# Type compatibility and conversion
The following topics describe how type conversion rules and data type compatibility work in AWS Clean Rooms Spark SQL.
**Topics**
+ [Compatibility](#s_Type_conversion-compatibility)
+ [General compatibility and conversion rules](#Type_conversion-general-compatibility-and-conversion-rules)
+ [Implicit conversion types](#implicit-conversion-types-spark)
## Compatibility
Data type matching and matching of literal values and constants to data types occurs during various database operations, including the following:
+ Data manipulation language (DML) operations on tables
+ UNION, INTERSECT, and EXCEPT queries
+ CASE expressions
+ Evaluation of predicates, such as LIKE and IN
+ Evaluation of SQL functions that do comparisons or extractions of data
+ Comparisons with mathematical operators
The results of these operations depend on type conversion rules and data type compatibility. *Compatibility* implies that a one-to-one matching of a certain value and a certain data type is not always required. Because some data types are *compatible*, an implicit conversion, or *coercion*, is possible. For more information, see [Implicit conversion types](#implicit-conversion-types-spark). When data types are incompatible, you can sometimes convert a value from one data type to another by using an explicit conversion function.
## General compatibility and conversion rules
Note the following compatibility and conversion rules:
+ In general, data types that fall into the same type category (such as different numeric data types) are compatible and can be implicitly converted.
For example, with implicit conversion you can insert a decimal value into an integer column. The decimal is rounded to produce a whole number. Or you can extract a numeric value, such as `2008`, from a date and insert that value into an integer column.
+ Numeric data types enforce overflow conditions that occur when you attempt to insert out-of-range values. For example, a decimal value with a precision of 5 does not fit into a decimal column that was defined with a precision of 4. An integer or the whole part of a decimal is never truncated. However, the fractional part of a decimal can be rounded up or down, as appropriate. However, results of explicit casts of values selected from tables are not rounded.
+ Different types of character strings are compatible. VARCHAR column strings containing single-byte data and CHAR column strings are comparable and implicitly convertible. VARCHAR strings that contain multibyte data are not comparable. Also, you can convert a character string to a date, time, timestamp, or numeric value if the string is an appropriate literal value. Any leading or trailing spaces are ignored. Conversely, you can convert a date, time, timestamp, or numeric value to a fixed-length or variable-length character string.
**Note**
A character string that you want to cast to a numeric type must contain a character representation of a number. For example, you can cast the strings `'1.0'` or `'5.9'` to decimal values, but you can't cast the string `'ABC'` to any numeric type.
+ If you compare DECIMAL values with character strings, AWS Clean Rooms attempts to convert the character string to a DECIMAL value. When comparing all other numeric values with character strings, the numeric values are converted to character strings. To enforce the opposite conversion (for example, converting character strings to integers, or converting DECIMAL values to character strings), use an explicit function, such as [CAST function](CAST_function.md).
+ To convert 64-bit DECIMAL or NUMERIC values to a higher precision, you must use an explicit conversion function such as the CAST or CONVERT functions.
## Implicit conversion types
There are two types of implicit conversions:
+ Implicit conversions in assignments, such as setting values in INSERT or UPDATE commands
+ Implicit conversions in expressions, such as performing comparisons in the WHERE clause
The following table lists the data types that can be converted implicitly in assignments or expressions. You can also use an explicit conversion function to perform these conversions.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/s_Type_conversion.html)
**Note**
Implicit conversions between DATE, TIME, TIMESTAMP\$1LTZ, TIMESTAMP\$1NTZ, or character strings use the current session time zone.
The VARBYTE data type can't be implicitly converted to any other data type. For more information, see [CAST function](CAST_function.md).