# Temporal <literal>s

This page is part of the book *SQL-99 Complete, Really*, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

### Contents

A temporal <literal> is any temporal value in one of two categories: datetimes and intervals.

## Datetime <literal>s

A datetime <literal> is either a <date literal>, a <time literal> or a <timestamp literal>. Datetime <literal>s must obey the familiar rules for dates and times, i.e., those of the Gregorian calendar and the 24-hour clock.

### <date literal>

A <date literal> represents a date in the Gregorian calendar. The required syntax for a <date literal> is as follows:

<date literal> ::= DATE 'yyyy-mm-dd'

`yyyy`

is 4 digits (ranging from 1 to 9999) representing a
`YEAR`

, `mm`

is 2 digits (ranging from 1 to
12) representing a `MONTH`

in the specified year and
`dd`

is 2 digits (ranging from 1 to 31, depending on the
month) representing a `DAY`

of the specified month. For
example, this <date literal> represents "July 15, 1997":

DATE '1997-07-15'

The valid range of dates is from `DATE '0001-01-01'`

(January
1, 1 AD) to `DATE '9999-12-31'`

(December 31, 9999 AD).

A <date literal>'s <data type> is `DATE`

.

### <time literal>

A <time literal> represents a time of day. The required syntax for a <time literal> is as follows:

<time literal> ::= TIME 'hh:mm:ss[.[nnnnnn]][ <time zone interval> ]'

`hh`

is 2 digits (ranging from 0 to 23) representing an
`HOUR`

on a 24 hour clock, `mm`

is 2 digits
(ranging from 0 to 59) representing a `MINUTE`

within the
specified hour and `ss`

is 2 digits (ranging from 0 to 61)
representing a `SECOND`

within the specified minute (SQL
allows for the addition of up to 2 "leap" seconds in a valid time). For
example, this <time literal> represents "1:35:16 PM":

TIME '13:35:16'

The optional `.nnnnnn`

, if specified, is a period followed by
an unsigned integer and represents a fraction of a second within the specified
second: this is the time value's *fractional seconds precision*. The minimum
fractional seconds precision and the default fractional seconds precision are
both zero. For example, these three <time literal>s all represent "1:35:16
PM":

TIME '13:35:16' TIME '13:35:16.' TIME '13:35:16.00'

This <time literal> represents "1:35:16 and one-hundredth of a second PM":

TIME '13:35:16.01'

[NON-PORTABLE] The valid range of times must include, at a minimum, all times
from `TIME '00:00:00'`

to
`TIME '23:59:61.999999'`

but is non-standard because the SQL
Standard requires implementors to define the maximum fractional seconds
precision for time values.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
<time literal>s to have a fractional seconds precision up to 6 digits
long. This allows you to deal with times ranging from whole seconds to
millionths of a second.

[Obscure Rule] A <time literal> may include a <time zone interval> to specify the value's time zone offset. A <time literal> without a <time zone interval> represents a time in the SQL-session's current default time zone, that is, it represents a local time. A <time literal> that includes a <time zone interval> represents a time in the specified time zone. For example, this <time literal> represents "12:35 and 16.5 seconds AM" with a time zone offset of 3 hours and 15 minutes (UTC '09:20:16.5):

TIME '12:35:16.5+03:15'

This <time literal> represents the local time "12:35 and 16.5 seconds AM":

TIME '12:35:16.5'

A <time literal> without a <time zone interval> has a <data type> of
`TIME(fractional seconds precision)`

, though it is compatible
with the `TIME and TIME WITH TIME ZONE`

<data type>s. For
example, this <literal>:

TIME '13:35:16'

has a <data type> of `TIME`

and this <literal>:

TIME '13:35:16.01'

has a <data type> of `TIME(2)`

.

[Obscure Rule] A <time literal> with a <time zone interval> has a <data type>
of `TIME(fractional seconds precision) WITH TIME ZONE`

, though
it is compatible with the `TIME`

and
`TIME WITH TIME ZONE`

<data type>s. For example, this
<literal>:

TIME '13:35:16.5+10:30'

has a <data type> of `TIME(1) WITH TIME ZONE`

.

If you want to restrict your code to Core SQL, don't add a fractional seconds precision or a <time zone interval> to your time values.

### <timestamp literal>

A <timestamp literal> represents a time of a given day. The required syntax for a <timestamp literal> is as follows:

<timestamp literal> ::= TIMESTAMP 'date value <space> time value'

that is:

TIMESTAMP 'yyyy-mm-dd hh:mm:ss[.[nnnnnn]][ <time zone interval> ]'

As with dates, `yyyy`

is 4 digits representing a
`YEAR`

, `mm`

is 2 digits representing a
`MONTH`

in the specified year, `dd`

is 2
digits representing a DAY of the specified month and, as with times,
`hh`

is 2 digits representing an `HOUR`

on
within the specified day, `mm`

is 2 digits representing a
`MINUTE`

within the specified hour, `ss`

is 2
digits representing a `SECOND`

within the specified minute and
the optional `.nnnnnn`

represents a fraction of a second
within the specified second. For example, this <timestamp literal> represents
"1:35:16 PM on July 15, 1997":

TIMESTAMP '1997-07-15 13:35:16'

This <timestamp literal> represents "1:35:16 and one-hundredth of a second PM on July 15, 1997":

TIMESTAMP '1997-07-15 13:35:16.01'

[NON-PORTABLE] The valid range of timestamps must include, at a minimum, all
timestamps from `TIMESTAMP '0001-01-01 00:00:00'`

but is non-standard
because the SQL Standard requires implementors to define the maximum fractional
seconds precision for timestamp values.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
<timestamp literal>s to have a fractional seconds precision up to 6
digits long. This allows you to deal with timestamps whose time values
range from whole seconds to millionths of a second.`<<fixed>>`

to
`<<fixed>>`

TIMESTAMP '9999-12-31 23:59:61.999999'

[Obscure Rule] A <timestamp literal> may include a <time zone interval>. As
with times, a <timestamp literal> without a <time zone interval> represents a
local timestamp, while a <timestamp literal> that includes a <time zone
interval> represents a timestamp in the specified time zone. A <timestamp
literal> without a <time zone interval> has a <data type> of
`TIMESTAMP(fractional seconds precision)`

, though it is
compatible with the `TIMESTAMP`

and
`TIMESTAMP WITH TIME ZONE`

<data type>s. For
example, this <literal>:

TIMESTAMP '1997-07-15 13:35:16'

has a <data type> of `TIMESTAMP`

and this <literal>:

TIMESTAMP '1997-07-15 13:35:16.01'

has a <data type> of `TIMESTAMP(2)`

.

[Obscure Rule] A <timestamp literal> with a <time zone interval> has a <data
type> of
`TIMESTAMP(fractional seconds precision) WITH TIME ZONE`

,
though it is compatible with the `TIMESTAMP`

and
`TIMESTAMP WITH TIME ZONE`

<data type>s. For example, this
<literal>:

TIMESTAMP '1997-07-15 13:35:16.5+10:30'

has a <data type> of `TIMESTAMP(1) WITH TIME ZONE`

.

If you want to restrict your code to Core SQL, don't add a fractional seconds precision greater than 6 digits or a <time zone interval> to your timestamp values.

## Interval <literal>s

An <interval literal> is either a <year-month interval literal> or a <day-time interval literal>. The type of interval is determined by the <interval qualifier> that is part of the <interval literal>.

### <interval qualifier>:

An <interval qualifier> defines the type (or precision) of an interval. The required syntax for an <interval qualifier> is:

<interval qualifier> ::= start_datetime [ TO end_datetime ] start_datetime ::= YEAR [ (leading precision) ] | MONTH [ (leading precision) ] | DAY [ (leading precision) ] | HOUR [ (leading precision) ] | MINUTE [ (leading precision) ] | SECOND [ (leading precision [ ,fractional seconds precision ]) ] end_datetime ::= YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [ (fractional seconds precision) ])

Both `start_datetime`

and `end_datetime`

may
be either: `YEAR`

, `MONTH`

,
`DAY`

, `HOUR`

, `MINUTE`

or
`SECOND`

, providing that `start_datetime`

is
not less significant than `end_datetime`

. If
`start_datetime`

is `YEAR`

, then
`end_datetime`

must either be `YEAR`

,
`MONTH`

or it must be omitted. If
`start_datetime`

is `MONTH`

, then
`end_datetime`

must be omitted. If
`start_datetime`

is `SECOND`

, then
`end_datetime`

must either be `SECOND`

with
a fractional seconds precision less than `start_datetime`

's
fractional seconds precision or it must be omitted.

The optional `start_datetime`

leading precision, if specified,
is an unsigned integer that defines the maximum number of digits allowed in the
`start_datetime`

value. For example, this
`start_datetime`

:

MONTH(1)

means that the month value may range from 0 to 9 months (up to 1 digit). The
minimum `start_datetime`

precision is 1. The default
`start_datetime`

precision is 2. For example, these two
<interval qualifier>s both describe an interval that may contain from 0 to 99
seconds:

SECOND SECOND(2)

[NON-PORTABLE] The maximum `start_datetime`

leading precision
may not be less than 2 digits but is non-standard because the SQL Standard
requires implementors to define an <interval qualifier>'s maximum leading
precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the leading precision of `YEAR`

to range from 1 to 4
digits and allows the leading precision of `MONTH`

,
`DAY`

, `HOUR`

,
`MINUTE`

and `SECOND`

to range from 0
to 2 digits.

An <interval qualifier>'s `start_datetime`

has a precision as
specified. All other datetime fields in the interval, except for
`SECOND`

, have an implied precision of 2 digits. The implied
precision for `SECOND`

is 2 digits before the decimal point
and a number of digits equal to the fractional seconds precision after the
decimal point. In all cases, the non-leading fields are constrained by the
familiar rules for dates and times; months within years may range from zero to
11, hours within days may range from zero to 23, minutes within hours may range
from zero to 59, and seconds within minutes may range from zero to 59.9*n*
(where .9*n* represents the number of digits defined for the fractional
seconds precision).

The optional fractional seconds precision for a
`start_datetime`

or an `end_datetime`

of
`SECOND`

, if specified, is an unsigned integer that defines
the number of digits in the `SECOND`

value's fractional
seconds portion. For example, this `start_datetime`

:

SECOND(2,3)

means that the seconds value may range from 0 to 99.999 seconds (up to 2
digits for the seconds value, followed by up to 3 digits for the fractional
seconds value). The `end_datetime`

:

TO SECOND(3)

means that the seconds value may range from 0 to 99.999 seconds. (Note that
`end_datetime`

may never have an explicit leading precision,
even for `SECOND`

.) The minimum fractional seconds precision
is 0. The default fractional seconds precision is 6. For example, these two
`start_datetime`

s both describe an interval that may contain
from 0 to 99 seconds:

SECOND(2) SECOND(2,0)

These two `start_datetime`

s both describe an interval that may
contain from 0 to 99.999999 seconds:

SECOND SECOND(2,6)

This `end_datetime`

describes an interval that may contain
from 0 to 99 seconds:

TO SECOND(0)

And these two `end_datetime`

s both describe an interval that
may contain from 0 to 59.999999 seconds:

TO SECOND TO SECOND(6)

[NON-PORTABLE] The maximum fractional seconds precision for an <interval
qualifier>'s `start_datetime`

or
`end_datetime`

of `SECOND`

may not be less
than 6 digits but is non-standard because the SQL Standard requires
implementors to define an <interval qualifier>'s maximum fractional seconds
precision.
[OCELOT Implementation] The OCELOT DBMS that comes with this book allows
the fractional seconds precision of `SECOND`

to range
from 0 to 6 digits.

[Obscure Rule] Of two `start_datetime`

s that are the same
except for their leading precision, the one with the higher precision is
treated as more significant. Of two `end_datetime`

values with
a <data type> of `SECOND`

that are the same except for their
fractional seconds precision, the one with the larger fractional seconds
precision is treated as more significant. This may become relevant during
assignments, comparisons and type conversions.

This <interval qualifier> means that the `YEAR`

value for the
interval may be any 3 digit number, i.e.: the `YEAR`

value may
range from 0 to 999 years:

YEAR(3)

Other examples of <interval qualifier>s include:

`YEAR` | `YEAR` may range from 0 to 99 |

`YEAR(4) TO MONTH` | `YEAR` may range from 0 to 9999,`MONTH` may range from 0 to 99 |

`SECOND` | `SECOND` may range from 0 to 99 |

`SECOND(1)` | `SECOND` may range from 0 to 9 |

`SECOND(1,3)` | `SECOND` may range from 0.000 to 9.999 |

`HOUR TO SECOND` | `HOUR` may range from 0 to 99,`SECOND` may range from 0.000000 to 99.999999 |

`HOUR TO SECOND(3)` | `HOUR` may range from 0 to 99,`SECOND` may range from 0.000 to 99.999 |

<</fixed>>

If you want to restrict your code to Core SQL, don't use <interval qualifier>s.

### <interval literal>

An <interval literal> represents a span of time and is either a <year-month literal> or a <day-time literal>.

#### <year-month literal>

The required syntax for a <year-month literal> is:

<year-month literal> ::= INTERVAL [ {+ | -} ]'yy' <interval qualifier> | INTERVAL [ {+ | -} ]'[ yy- ] mm' <interval qualifier>

A <year-month literal> includes either `YEAR`

,
`MONTH`

or both. It may not include the datetime fields
`DAY`

, `HOUR`

, `MINUTE`

or
`SECOND`

. Its <data type> is `INTERVAL`

with
a matching <interval qualifier>.

The optional sign specifies whether this is a positive interval or a negative
interval. If you omit the sign, it defaults to + `<<entity>>`

mdash<</fixed>> a
positive interval. A negative <interval literal> can be written in one of two
ways. For example, for the interval "minus (5 years 5 months)", you could write
either:

INTERVAL -'05-05' YEAR TO MONTH

or

INTERVAL '-05-05' YEAR TO MONTH

that is, the minus sign can be either outside or inside the interval string. (In fact it can even be both, e.g.:

-'-05-05' YEAR TO MONTH

which is a double negative and therefore a positive interval: "plus (5 years 5 months)".)

TIP: Use the second form. If you're going to be passing intervals as parameters, get used to the idea that the sign can be part of the string.

`yy`

is 1 or more digits representing a number of
`YEAR`

s and `mm`

is 1 or more digits
representing a number of `MONTH`

s. There are three types of
<year-month literal>s. For ease of reading, the following examples mostly
exclude the use of explicit leading precisions.

This <year-month literal> has a <data type> of `INTERVAL YEAR`

and represents a time span of four years:

INTERVAL '4' YEAR

These two <year-month literal>s have a <data type> of
`INTERVAL MONTH`

and both represent a negative time span of
fifty months:

INTERVAL -'50' MONTH INTERVAL '-50' MONTH

(Note the sign, which may be written outside the single quotes delimiting the month value or within the quotes.)

This <year-month literal> has a <data type> of
`INTERVAL YEAR TO MONTH`

and represents a time span of four
hundred years and 6 months:

INTERVAL '400-03' YEAR(3) TO MONTH

(Note the minus sign between the year value and the month value.)

#### <day-time literal>

The required syntax for a <day-time literal> is:

<day-time literal> ::= INTERVAL [ {+ | -} ]'dd [ <space>hh [ :mm [ :ss ]]]' <interval qualifier> INTERVAL [ {+ | -} ]'hh [ :mm [ :ss [ .nn ]]]' <interval qualifier> INTERVAL [ {+ | -} ]'mm [ :ss [ .nn ]]' <interval qualifier> INTERVAL [ {+ | -} ]'ss [ .nn ]' <interval qualifier>

A <day-time literal> includes either `DAY`

,
`HOUR`

, `MINUTE`

, `SECOND`

or some contiguous subset of these fields. It may not include the datetime
fields `YEAR`

or `MONTH`

. Its <data type> is
`INTERVAL`

with a matching <interval qualifier>.

The optional sign specifies whether this is a positive interval or a negative interval. If you omit the sign, it defaults to +, a positive interval. A negative <interval literal> can be written with the sign inside or outside the string; see "<year-month literal>".

`dd`

is 1 or more digits representing a number of
`DAY`

s, `hh`

is 1 or more digits
representing a number of `HOUR`

s, `mm`

is 1
or more digits representing a number of `MINUTE`

s,
`ss`

is 1 or more digits representing a number of
`SECOND`

s and `.nn`

is 1 or more digits
representing a number of fractions of a `SECOND`

. There are
ten types of <day-time literal>s. For ease of reading, the following examples
mostly exclude the use of explicit leading precisions and fractional seconds
precisions.

This <day-time literal> has a <data type> of `INTERVAL DAY`

and represents a time span of 94 days:

INTERVAL '94' DAY

This <day-time literal> has a <data type> of `INTERVAL HOUR`

and represents a time span of 35 hours:

INTERVAL '35' HOUR(2)

This <day-time literal> has a <data type> of `INTERVAL MINUTE`

and represents a time span of 20 minutes:

INTERVAL '20' MINUTE

This <day-time literal> has a <data type> of `INTERVAL SECOND`

and represents a time span of 77 seconds (or 77.000000 seconds):

INTERVAL '77' SECOND(0)

This <day-time literal> has a <data type> of `INTERVAL SECOND`

and represents a time span of 142.999 seconds:

INTERVAL '142.999' SECOND(3,3)

This <day-time literal> has a <data type> of
`INTERVAL DAY TO HOUR`

and represents a time span of forty
days and 23 hours:

INTERVAL '40 23' DAY(2) TO HOUR

(Note the space between the day value and the hour value.)

This <day-time literal> has a <data type> of
`INTERVAL DAY TO MINUTE`

and represents a time span of 45
days, 23 hours and 16 minutes:

INTERVAL '45 23:16' DAY TO MINUTE

(Note the colon between the hour value and the minute value.)

This <day-time literal> has a <data type> of
`INTERVAL DAY TO SECOND`

and represents a time span of 45
days, 23 hours, 16 minutes and 15 seconds:

INTERVAL '45 23:16:15' DAY TO SECOND(0)

(Note the colon between the minute value and the second value.)

This <day-time literal> has a <data type> of
`INTERVAL DAY TO SECOND`

and represents a time span of 45
days, 23 hours, 16 minutes and 15.25 seconds:

INTERVAL '45 23:16:15.25' DAY TO SECOND(2)

(Note the decimal point between the second value and the fractional second value.)

This <day-time literal> has a <data type> of
`INTERVAL HOUR TO MINUTE`

and represents a time span of 23
hours and 16 minutes:

INTERVAL '23:16' HOUR TO MINUTE

This <day-time literal> has a <data type> of
`INTERVAL HOUR TO SECOND`

and represents a time span of 23
hours, 16 minutes and 15.25 seconds:

INTERVAL '23:16:15.25' HOUR TO SECOND(2)

This <day-time literal> has a <data type> of
`INTERVAL MINUTE TO SECOND`

and represents a time span of 16
minutes and 15.25 seconds:

INTERVAL '16:15.25' MINUTE TO SECOND(2)

If you want to restrict your code to Core SQL, don't use <interval literal>s.