Date/Time Types
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
Data Cloud SQL supports this set of SQL date and time types.
Name | Storage Size | Description | Range | Resolution |
---|---|---|---|---|
timestamp [without time zone] | 8 bytes | both date and time (no time zone) | 4713 BC - 294276 AD | 1 microsecond |
timestamp with time zone | 8 bytes | both date and time, with time zone | 4713 BC - 294276 AD | 1 microsecond |
date | 4 bytes | date (no time of day) | 4713 BC - 5874897 AD | 1 day |
time | 8 bytes | time of day (no date and no time zone) | 00:00:00 - 24:00:00 | 1 microsecond |
interval | 16 bytes | time interval | -178000000 years - 178000000 years | 1 microsecond |
Dates follow the Gregorian calendar, even in years before that calendar was introduced. For operations available on these data types, see Date/Time Functions and Operators.
Date and time input is accepted in almost any format, including ISO 8601 and SQL-compatible. For some formats, the ordering of day, month, and year is ambiguous. You can define the expected ordering of these fields with the date_style
setting. Set it to MDY
for month-day-year interpretation, DMY
for day-month-year interpretation, or YMD
for year-month-day interpretation.
Enclose all date or time literal inputs in single quotes, type 'value'
. For example:
Example date
type inputs:
Example | Description |
---|---|
1999-01-08 | January 8 in any mode (recommended format) |
January 8, 1999 | Unambiguous in any date_style input mode |
1/8/1999 | January 8 in MDY mode or August 1 in DMY mode |
1/18/1999 | January 18 in MDY mode or rejected in other modes |
01/02/03 | January 2, 2003 in MDY mode, February 1, 2003 in DMY mode, or February 3, 2001 in YMD mode |
1999-Jan-08 | January 8 in any mode |
Jan-08-1999 | January 8 in any mode |
08-Jan-1999 | January 8 in any mode |
99-Jan-08 | January 8 in YMD mode or rejected in other modes |
08-Jan-99 | January 8, or error in YMD mode |
Jan-08-99 | January 8, or error in YMD mode |
1999.008 | Year and day of year |
J2451187 | Julian date |
January 8, 99 BC | Year 99 BC |
The time-of-day type is time
, which doesn’t include time zone information. Input type examples:
Example | Description |
---|---|
04:05:06.789 | ISO 8601 |
04:05:06 | ISO 8601 |
04:05 | ISO 8601 |
04:05 AM | same as 04:05; AM doesn’t affect value |
04:05 PM | same as 16:05; input hour must be <= 12 |
04:05:06.789-8 | ISO 8601 |
04:05:06-08:00 | ISO 8601 |
04:05-08:00 | ISO 8601 |
Valid input for the timestamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD
or BC
. For example:
To treat a literal as timestamp with time zone
, provide an explicit type.
timestamp with time zone
is internally stored in Universal Coordinated Time (UTC), traditionally known as Greenwich Mean Time (GMT). An input value that has an explicit time zone specified is converted to UTC by using the appropriate offset for that time zone. If you don’t provide a time zone, the time zone is assumed to be the system’s time zone and converted to UTC by using the time zone offset.
A timestamp with time zone
output value is converted from UTC and displayed as local time in the system’s time zone.
Conversions between timestamp without time zone
and timestamp with time zone
assume that the timestamp without time zone
value is the local time zone.
The output format of the date/time types follows the ISO 8601 format: 1997-12-17 07:37:16-08
.
Data Cloud SQL accepts an uppercase letter T
to separate the date and time, but it outputs a space to separate the date and time.
The formatting function to_char
is a flexible way to format date or time output. See Data Type Formatting Functions.
Data Cloud SQL uses the IANA (Olson) time zone database for information about historical time zone rules. For times in the future, the assumption is that the latest known rules for a given time zone continue to be observed indefinitely.
With Data Cloud SQL you can specify time zones in three different forms.
-
A full-time zone name (
America/New_York
) -
A time zone abbreviation (
PST
) -
POSIX-style time zone specifications of the form
<STD><offset>
or<STD><offset><DST>
(EST5EDT
)
<STD>
is a zone abbreviation.
<offset>
is a numeric offset in hours west from UTC.
<DST>
is an optional daylight savings zone abbreviation, assumed to stand for 1 hour ahead of the given offset .
Abbreviations represent an offset from UTC. Full time zone names can have a local daylight savings time rule and two possible UTC offsets. For example, 2014-06-04 12:00 America/New_York
represents noon local time in New York, which for this date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT
is the same time. But 2014-06-04 12:00 EST
is noon Eastern Standard Time (UTC-5), regardless of daylight savings.
Here are some examples of time zone input.
Example | Description |
---|---|
PST | Abbreviation (for Pacific Standard Time) |
America/New_York | Full time zone name |
PST8PDT | POSIX-style time zone specification |
-8:00 | ISO-8601 offset for PST |
-800 | ISO-8601 offset for PST |
-8 | ISO-8601 offset for PST |
Time zone names and abbreviations are case-insensitive.
Interval values can be written as ISO 8601 time intervals by using either the “format with designators” of the standard’s section 4.4.3.2 or the “alternative format” of section 4.4.3.3. Here’s an example of format with designators.
The string starts with a P
and can include a T
that introduces the time-of-day units. The available unit abbreviations are:
Abbreviation | Meaning |
---|---|
Y | Years |
M | Months (in the date part) |
W | Weeks |
D | Days |
H | Hours |
M | Minutes (in the time part) |
S | Seconds |
Units can be omitted and specified in any order, but units smaller than a day appear after T
. The meaning of M
depends on if it’s before or after T
.
In the alternative format, P [<years>-<months>-<days>] [T <hours>:<minutes>:<seconds>],
the string begins with P
and a T
separates the date and time parts of the interval. The values are given as numbers similar to ISO 8601 dates.
All fields of an interval value must have the same sign. For example, the negative sign in the interval literal '-1 2:03:04'
applies to the days and hour/minute/second parts.
The interval can also be written in this verbose form.
<quantity>
is a signed number. The available values for <unit>
are microsecond
, millisecond
, second
, minute
, hour
, day
, week
, month
, year
, decade
, century
, millennium
, or abbreviations or plurals of them. <direction>
can be ago
or empty.
The amounts of the different quantities and units are added by taking the signs into consideration. The default value for <unit>
is second
, and it can only be omitted for the last <quantity>
in the expression. ago
negates all the fields.
Examples of valid interval
inputs are:
Example | Description |
---|---|
1-2 | SQL standard format: 1 year 2 months |
3 4:05:06 | SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S | ISO 8601 “format with designators”: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P0001-02-03T04:05:06 | ISO 8601 "alternative format": 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
1 year 2 hours | Verbose format: 1 year 2 hours |
Functions justify_days
and justify_hours
are available for adjusting days and hours that overflow their normal ranges. For more information, see Date/Time Functions.
Use the interval_style
setting to set the output format of the interval type to sql_standard
or iso_8601
. The default is the iso_8601
format. Examples of each output style are:
Style Specification | Year-Month Interval | Day-Time Interval | Mixed Interval |
---|---|---|---|
sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |