Date/Time Types

Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API

Data Cloud SQL supports this set of SQL date and time types.

NameStorage SizeDescriptionRangeResolution
timestamp [without time zone]8 bytesboth date and time (no time zone)4713 BC - 294276 AD1 microsecond
timestamp with time zone8 bytesboth date and time, with time zone4713 BC - 294276 AD1 microsecond
date4 bytesdate (no time of day)4713 BC - 5874897 AD1 day
time8 bytestime of day (no date and no time zone)00:00:00 - 24:00:001 microsecond
interval16 bytestime interval-178000000 years - 178000000 years1 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:

ExampleDescription
1999-01-08January 8 in any mode (recommended format)
January 8, 1999Unambiguous in any date_style input mode
1/8/1999January 8 in MDY mode or August 1 in DMY mode
1/18/1999January 18 in MDY mode or rejected in other modes
01/02/03January 2, 2003 in MDY mode, February 1, 2003 in DMY mode, or February 3, 2001 in YMD mode
1999-Jan-08January 8 in any mode
Jan-08-1999January 8 in any mode
08-Jan-1999January 8 in any mode
99-Jan-08January 8 in YMD mode or rejected in other modes
08-Jan-99January 8, or error in YMD mode
Jan-08-99January 8, or error in YMD mode
1999.008Year and day of year
J2451187Julian date
January 8, 99 BCYear 99 BC

The time-of-day type is time, which doesn’t include time zone information. Input type examples:

ExampleDescription
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
04:05 AMsame as 04:05; AM doesn’t affect value
04:05 PMsame as 16:05; input hour must be <= 12
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 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.

ExampleDescription
PSTAbbreviation (for Pacific Standard Time)
America/New_YorkFull time zone name
PST8PDTPOSIX-style time zone specification
-8:00ISO-8601 offset for PST
-800ISO-8601 offset for PST
-8ISO-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:

AbbreviationMeaning
YYears
MMonths (in the date part)
WWeeks
DDays
HHours
MMinutes (in the time part)
SSeconds

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:

ExampleDescription
1-2SQL standard format: 1 year 2 months
3 4:05:06SQL standard format: 3 days 4 hours 5 minutes 6 seconds
P1Y2M3DT4H5M6SISO 8601 “format with designators”: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds
P0001-02-03T04:05:06ISO 8601 "alternative format": 1 year 2 months 3 days 4 hours 5 minutes 6 seconds
1 year 2 hoursVerbose 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 SpecificationYear-Month IntervalDay-Time IntervalMixed Interval
sql_standard1-23 4:05:06-1-2 +3 -4:05:06
iso_8601P1Y2MP3DT4H5M6SP-1Y-2M3DT-4H-5M-6S