ISO 8601 Date, Time, and Duration Support

International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates, times, and durations. Oracle Database supports the most common ISO 8601 formats as proper Oracle SQL date, time, and interval (duration) values. The formats that are supported are essentially those that are numeric-only, language-neutral, and unambiguous.

(Simple Oracle Document Access (SODA) does not support durations.)

Oracle Database Syntax for ISO Dates and Times

This is the syntax that Oracle Database supports for ISO dates and times:

Date (only): YYYY-MM-DD
Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(*|-)hh:mm]

where:

YYYY specifies the year, as four decimal digits.
MM specifies the month, as two decimal digits, 00 to 12.
DD specifies the day, as two decimal digits, 00 to 31.
hh specifies the hour, as two decimal digits, 00 to 23.
mm specifies the minutes, as two decimal digits, 00 to 59.
ss[.s[s[s[s[s]]]]] specifies the seconds, as two decimal digits, 00 to 59, optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
Z specifies UTC time (time zone 0). (It can also be specified by +00:00, but not by –00:00.)
(*|-)hh:mm specifies the time-zone as difference from UTC. (One of + or – is required.)

For a time value, the time-zone part is optional. If it is absent then UTC time is assumed.

No other ISO 8601 date-time syntax is supported. In particular:

Negative dates (dates prior to year 1 BCE), which begin with a hyphen (e.g. –2018–10–26T21:32:52), are not supported.
Hyphen and colon separators are required: so-called “basic” format, YYYYMMDDThhmmss, is not supported.
Ordinal dates (year plus day of year, calendar week plus day number) are not supported.
Using more than four digits for the year is not supported.

Supported dates and times include the following:

2018–10–26T21:32:52
2018-10-26T21:32:52+02:00
2018-10-26T19:32:52Z
2018-10-26T19:32:52+00:00
2018-10-26T21:32:52.12679

Unsupported dates and times include the following:

2018-10-26T21:32 (if a time is specified then all of its parts must be present)
2018-10-26T25:32:52+02:00 (the hours part, 25, is out of range)
18-10-26T21:32 (the year is not specified fully)

Oracle Database Syntax for ISO Durations

Note: Oracle Database supports ISO durations, but Simple Oracle Document Access (SODA) does not support them.

There are two supported Oracle Database syntaxes for ISO durations, the ds_iso_format specified for SQL function to_dsinterval and the ym_iso_format specified for SQL function to_yminterval. (to_dsinterval returns an instance of SQL type INTERVAL DAY TO SECOND, and to_yminterval returns an instance of type INTERVAL YEAR TO MONTH.)

These formats are used for data types daysecondInterval and yearmonthInterval, respectively, which Oracle has added to the JSON language.

ds_iso_format:

PdDThHmMsS, where d, h, m, and s are digit sequences for the number of days, hours, minutes, and seconds, respectively. For example: "P0DT06H23M34S".

s can also be an integer-part digit sequence followed by a decimal point and a fractional-part digit sequence. For example: P1DT6H23M3.141593S.

Any sequence whose value would be zero is omitted, along with its designator. For example: "PT3M3.141593S". However, if all sequences would have zero values then the syntax is "P0D".
ym_iso_format

PyYmM, where y is a digit sequence for the number of years and m is a digit sequence for the number of months. For example: "P7Y8M".

If the number of years or months is zero then it and its designator are omitted. Examples: "P7Y", "P8M". However, if there are zero years and zero months then the syntax is "P0Y".

See Also:

Related Topics

Item-Method Clause (Reference)