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.

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'<<fixed>> to <<fixed>>TIMESTAMP '9999-12-31 23:59:61.999999' 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.

[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.9n (where .9n 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_datetimes both describe an interval that may contain from 0 to 99 seconds:

SECOND(2)
SECOND(2,0)

These two start_datetimes 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_datetimes 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_datetimes 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:

YEARYEAR may range from 0 to 99
YEAR(4) TO MONTHYEAR may range from 0 to 9999,
MONTH may range from 0 to 99
SECONDSECOND 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 SECONDHOUR 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 YEARs and mm is 1 or more digits representing a number of MONTHs. 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 DAYs, hh is 1 or more digits representing a number of HOURs, mm is 1 or more digits representing a number of MINUTEs, ss is 1 or more digits representing a number of SECONDs 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.

Comments

Comments loading...