Date types:
| Data type | Description |
|---|---|
| DATE() | A date. Format: YYYY-MM-DDNote: The supported range is from ‘1000-01-01’ to ‘9999-12-31’ |
| DATETIME() | *A date and time combination. Format: YYYY-MM-DD HH:MI:SSNote: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ |
| TIMESTAMP() | *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SSNote: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC |
| TIME() | A time. Format: HH:MI:SSNote: The supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR() | A year in two-digit or four-digit format.Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069 |
*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.
Ref: http://www.w3schools.com/sql/sql_datatypes.asp
| AWS Documentation » Amazon Redshift » Developer Guide » SQL Reference » Using SQL » Basic Elements » Data Types » Datetime Types |
Datetime Types
Topics
- Storage and Ranges
- DATE
- TIMESTAMP
- TIMESTAMPTZ
- Examples with Datetime Types
- Date and Timestamp Literals
- Interval Literals
Datetime data types include DATE, TIMESTAMP, and TIMESTAMPTZ.
Storage and Ranges
| Name | Storage | Range | Resolution |
|---|---|---|---|
| DATE | 4 bytes | 4713 BC to 294276 AD | 1 day |
| TIMESTAMP | 8 bytes | 4713 BC to 294276 AD | 1 microsecond |
| TIMESTAMPTZ | 8 bytes | 4713 BC to 294276 AD | 1 microsecond |
DATE
Use the DATE data type to store simple calendar dates without time stamps.
TIMESTAMP
TIMESTAMP is an alias of TIMESTAMP WITHOUT TIME ZONE.
Use the TIMESTAMP data type to store complete time stamp values that include the date and the time of day.
TIMESTAMP columns store values with up to a maximum of 6 digits of precision for fractional seconds.
If you insert a date into a TIMESTAMP column, or a date with a partial time stamp value, the value is implicitly converted into a full time stamp value with default values (00) for missing hours, minutes, and seconds. Time zone values in input strings are ignored.
By default, TIMESTAMP values are Coordinated Universal Time (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 time stamp 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 Coordinated Universal Time (UTC) and stores the UTC value.
To view a list of supported time zone names, execute the following command.
select pg_timezone_names();
To view a list of supported time zone abbreviations, execute the following command.
select pg_timezone_abbrevs();
You can also find current information about time zones in the IANA Time Zone Database.
The following table has examples of time zone formats.
| Format | Example |
|---|---|
| day mon hh:mm:ss yyyy tz | 17 Dec 07:37:16 1997 PST |
| mm/dd/yyyy hh:mm:ss.ss tz | 12/17/1997 07:37:16.00 PST |
| mm/dd/yyyy hh:mm:ss.ss tz | 12/17/1997 07:37:16.00 US/Pacific |
| yyyy-mm-dd hh:mm:ss+/-tz | 1997-12-17 07:37:16-08 |
| dd.mm.yyyy hh:mm:ss tz | 12.17.1997 07:37:16.00 PST |
TIMESTAMPTZ columns store values with up to a maximum of 6 digits of precision for fractional seconds.
If you insert a date into a TIMESTAMPTZ column, or a date with a partial time stamp, the value is implicitly converted into a full time stamp value with default values (00) for missing hours, minutes, and seconds.
TIMESTAMPTZ values are UTC in user tables.
DataUntangle 