Chapter 8 – Temporal values

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.

On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.

For more information specific to CrateDB, check out the CrateDB Reference documentation.

In SQL, a temporal value is either a datetime (i.e.: a date, a clock time or a timestamp) or an interval (i.e.: a span of time). They consist of a contiguous subset of one or more of the datetime fields (in their order of significance): YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR and TIMEZONE_MINUTE. A temporal value may be a <literal>, the value of a parameter or a host language variable or the result of any expression or argument (including a possibly qualified <Column name>) that evaluates to a date, a time, a timestamp or an interval. Temporal values must obey the familiar rules for dates and times, i.e., those of the Gregorian Calendarand the 24-hour clock.

Datetimes are either dates, times or timestamps. Dates are stored in the DATE <data type>, times are stored in either the TIME or the TIME WITH TIME ZONE <data type>s and timestamps are stored in either the TIMESTAMP or the TIMESTAMP WITH TIME ZONE <data type>s.

Intervals are either year-month intervals (spans of time involving years and/or months) or day-time intervals (spans of time involving days and/or hours and/or minutes and/or seconds and/or fractions of a second); they include a qualifier which specifies which of the datetime fields are represented in the interval. All interval values are signed. Intervals are stored in the INTERVAL <data type>.

Table of Contents

Some Preliminaries

Before we talk about datetimes and intervals, there is some necessary background to go through. We provide most of this information mainly for reference purposes, though. If you decide not to read it thoroughly right now, you’ll still understand most of what follows. However, we will be referring to these concepts throughout this chapter.

[Obscure Rule] applies for the rest of this section.

The Gregorian Calendar

The SQL Standard says that all dates must be valid “according to the Gregorian calendar”. Most people know the main rules:

• Thirty days hath September, April, June and November, all the rest have thirty-one, except (the rhyme scheme starts to fail here) February which has 28 days, or 29 in a leap year.

• A leap year occurs every four years.

These first two rules are the rules of the Julian Calendar. Pope Gregory XIII added this exception in 1582:

• A leap year does not occur at the end of a century, except every 400 years. (That is: 1700, 1800, 1900, 2100, 2200 and 2300 are not leap years.)

In a majority of practical situations it’s only necessary to know what the Gregorian calendar is and that SQL enforces it. A minority of practical cases, however, involve historians or astronomers. For these cases, let’s clear out the wrong ideas about the calendar rules and about the Julian-to-Gregorian transition.

The Julian calendar took effect starting January 1, 45 BC. (It is a nice coincidence that January 1 was the year-start in 45 BC and in our time; we ignore the variations that happened between then and now.) There was some confusion and fiddling until 4 AD (which was not a leap year) but after that, the first two rules held firmly: a leap year every 4 years, with the average “Julian year” being 365.25000 days long. (We now consider that the correct period for a year is 365.24220 days, decreasing by about 0.5 seconds per century. The difference, 000.00780 days, is statable as 3 days every 400 years.)

The result of this, though, meant that by the 1500s, the spring equinox was on March 11th. This violated Church teaching, particularly the finding of the 4th-century Council of Nicaea, which made clear that the spring equinox is March 21st. To solve the problem, Pope Gregory XIII had to do two things: shift the calendar forward 10 days and change the rules, so that there would no longer be an overestimate of 3 days every 400 years. He therefore decreed that the day after Thursday, October 4th, 1582 should be Friday, October 15th, 1582. The decree took effect immediately in the Papal States and Iberia, after a short delay in France, by 1700 in most German Protestant states and Scandinavia (though Sweden went back and forth). England held out until 1752 (by which time the discrepancy was 11 days, from September 3rd to September 14th). Japan went Gregorian in 1873, with the proviso that Year #1 is based on the Emperor’s reign rather than Christ’s birth. China changed in 1911, Russia in 1918 and Greece in 1923. Even among Moslem countries, the only non-fully-gregorianized significant holdout, there has been a breakaway: Turkey, in 1927.

As we said earlier, the switch to the Gregorian calendar doesn’t affect most of us – but it has caused some problems for groups like historians and astronomers who use SQL.

The first problem is that SQL allows dates like DATE '1582-10-14' – even though, according to Pope Gregory, there was no such date. Also, any Gregorian date before October 3rd, 1582 is what the Oxford Concise Dictionary calls “prolepis: … representation of thing as existing before it actually does or did so”. (When Americans observed Washington’s birthday on February 22nd, they were proleptic: he was born on February 11 OS, where the initials “OS” stand for “Old Style” i.e. Julian.) Going the other way, into the future, there will certainly have to be more tweaking, since the Pope’s rules do not remove all drift.

The second problem is that, although it serves many purposes well, the Gregorian calendar is inevitably non-decimal. It would be simpler to begin with a fixed moment far in the past – noon on January 1st, 4713 BC for example – and count how many million days have elapsed since then, with no regard for higher units or any calendar rules. This is the system of “Julian days”. Since a Julian day is expressible as a DECIMAL, there is no need for a separate data type for such values. Some ephemerides tables use Julian days, so if your project involves astronomy look for a DBMS that can convert a Julian day to a (proleptic Gregorian) DATE. Standard SQL DBMSs can’t because they may only allow for dates starting with 0001 AD.

Leap Seconds

The earth’s revolutions are getting shorter: it goes round the sun about 0.5 seconds faster than it did in 1900. We point this out for the sake of people who define “1 year” as “1 earth revolution period” – that kind of year is getting shorter, but the other kind, the “civil year”, isn’t. Here’s why.

The earth’s rotations are getting longer: it turns on its axis about 0.04 seconds slower than it did in 1900. A bit of the slowdown is due to tidal friction but mostly we’re looking at an irregular and unpredictable fluctuation – indeed, for all we know, the rotation may get faster in future. We point this out for the sake of people who define “1 day” as “the average period between two sunrises” which is closely linked to the earth’s rotation period. You can keep that definition, but you should see that such a shifty period cannot be the standard in a precise measurement system.

The resonance of a cesium-133 atom is getting neither shorter nor longer. Its electrons change spin (relative to the nucleus) at a constant frequency. So the International System of Units bases its definition of a “second” on a cesium clock, thus: “the duration of 9,192,631,770 periods of the radiation corresponding to [the shift between parallel and anti-parallel electron spin] of the caesium-133 atom”. The official second is this atomic second, and since 1972 we have defined a day as 60*60*24 atomic seconds.

At one instant, the standard day was the same as the day-derived-from-rotation. But since the latter fluctuates, the two figures won’t stay in synch. Yet we must synch them, else the number of days in a year would change fractionally with each revolution. The solution is: when the atomic-second time gains/loses relative to the from-rotation time, add/drop 1 or 2 seconds in the last second of the last day of a month. In practice it has always been necessary to add, and the change has always been on June 30th or December 31st. Since we are adding to the year, the term leap second is good by analogy.

The day-derived-from-rotation time is known as “Universal Time 1” (UT1); corrected for polar wobble, it is used for celestial navigation. The day-based-purely-on-atomic-clock time is known as “International Atomic Time” (TAI); it represents the consensus of several cesium clocks as monitored by a standards bureau in France. The atomic-but-synched-with-UT1-by-leap-seconds time is “Co-ordinated Universal Time” (UTC). It is this last time – UTC – which matters for time signals, for SQL and for us. Do not confuse UTC with the old standard “Greenwich Mean Time” (GMT) – GMT was a variant of UT1 that used a different method to correct for fluctuations. Beware of two prevalent but false opinions: that years are not getting longer (they are), or that UTC is a renaming of GMT (it is not). The distinctions are tiny, but any program which uses leap seconds or fractional seconds is getting into magnitudes which are smaller than those tiny distinctions.

Knowing what leap seconds are, we can move at last to their use in SQL:

• First, the Standard requires a DBMS to extend the range of seconds-field values to “less than 62” (rather than “less than 60”) and thus account for up to 2 positive leap seconds. (There is a GOTCHA here: leap seconds should always be for the last minute of a day, as in TIME '23:59:60', but the Standard allows erroneous values like TIME '12:34:60'.)

• Second, because of leap seconds, it isn’t possible to tell whether TIME '23:59:58' is two seconds before midnight, one second before midnight (leap seconds can be negative, though it has never happened) or as much as four seconds before midnight – the information is simply not present in the syntax of a TIME expression, nor derivable from any Table. Thus, arithmetic with carrying has uncertainty. Not surprisingly, the SQL Standard states that any expressions which involve leap-seconded TIMEs will show implementation-defined results.

Time Zones

In 330 BC, a lunar eclipse was seen at Arbela around 3 AM and in Carthage around midnight. The ancient Greeks knew how eclipses worked so this was one proof that the earth is round (their other proofs were that the sun gets higher in the sky as we travel south and that we can still see ships’ masts after their hulls disappear below the horizon). In our terms, Aristotle and Company were seeing that our anchor point of midnight – the halfway point between dusk and dawn – must change with longitude.

Nowadays, we mark the world off into time zones, with one time zone equal to about 15 degrees of longitude. Time zones are political divisions that allow us to use the convention that all locations in a time zone have the same time, known as local time. Thus, although times and timestamps are supposed to represent an absolute time of day (times) and an absolute time of a specific day (timestamps), they can have ambiguous meanings when an SQL-environment spans multiple time zones. The SQL Standard tries to cater both to users who have only local dealings and thus care only about local time, and to users who operate across time zones. It does this by providing a <time zone interval> option for time and timestamp values: a value without a <time zone interval> (e.g., a TIME or TIMESTAMP <data type>) may represent local time or UTC time, while a value with a <time zone interval> (e.g.: a TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE <data type>) always represents the UTC time. Unless your SQL-environment spans multiple time zones and you have a need for “real time” database operations, the entire matter of time zones probably won’t concern you. In that case, be sure to define your time and timestamp fields with the TIME and TIMESTAMP <data type>s; don’t use the TIME WITH TIME ZONE or the ``TIMESTAMP WITH TIME ZONE <data type>s at all. If, however, “real time” operations are vital, you may want to define time and timestamp fields with the TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE <data type>s.

<time zone interval>

The required syntax for a <time zone interval> is as follows.

<time zone interval> ::=
{+ | -} HH:MM

A <time zone interval> specifies a time or timestamp value’s time zone offset from UTC. It has a <data type> of INTERVAL HOUR TO MINUTE.

HH is 2 digits (ranging from 0 to 13) representing the number of hours (called TIMEZONE_HOURs) in the time zone offset and MM is 2 digits (ranging from 0 to 59) representing the number of additional minutes (called TIMEZONE_MINUTEs) in the time zone offset. For example, this represents a <time zone interval> of 3 hours:

+3:00

A <time zone interval>’s mandatory sign – either “+” (plus) or “-” (minus) – indicates whether the time zone offset is added to, or subtracted from, the UTC time to obtain the local time. The valid range of <time zone interval>s is from -12:59 to +13:00. Any operation that attempts to specify a <time zone interval> that is not within this range will fail: your DBMS will return the SQLSTATE error 22009 "data exception-invalid time zone displacement value".

A time or timestamp value that doesn’t include 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 or timestamp value that does include a <time zone interval> represents a time in the specified time zone.

If you want to restrict your code to Core SQL, don’t use <time zone interval>s.

Time Zone Example

Time zones start at zero longitude (the Prime Meridian), which goes through Greenwich, Britain. The time zones West Of Greenwich (“Wogs”) are behind UTC because the earth rotates from west to east. Therefore, when it’s 12:00 UTC it’s only 8:30 AM in Newfoundland, and even earlier as we go west from there. The time zones East Of Greenwich (“Eogs”) are ahead of UTC, so when it’s 12:00 UTC it’s already 5:30 PM in Dehli, India, and even later as we go east from there. Consider this timeline:

+8:00	+5:00	+0:00	-2:00	-5:30
Vancouver	Detroit	Greenwich	Moscow	Dehli

The numbers on the timeline indicate the time zones’ offsets from UTC, in hours and minutes. (The math is somewhat counterintuitive, since the SQL Standard requires you to subtract the offset from the local time to calculate UTC.) A time zone’s offset from UTC is its <time zone interval>.

As an example, consider an SQL-environment with three installations: one in Vancouver, Canada (with a default time zone offset of +8:00), one in London, England (with a default time zone offset of +0:00) and one in Delhi, India (with a default time zone offset of -5:30). All three installations have access to this Table:

CREATE TABLE Time_Examples (
   Time_Local TIMESTAMP,
   Time_With_Time_Zone TIMESTAMP WITH TIME ZONE);

A user at the London installation adds this row to the Table:

INSERT INTO Time_Examples (Time_Local, Time_With_Time_Zone)
VALUES (TIMESTAMP '1995-07-15 07:30', TIMESTAMP '1995-07-15 07:30');

Now, to a user at the Vancouver installation, this moment in time is equivalent to a local timestamp of '1995-07-14 23:30' (Vancouver time is 8 hours earlier than London time) and to a user at the Delhi installation, the same moment in time is equivalent to a local timestamp of '1995-07-15 13:00' (Delhi time is 5.5 hours after London time). So, despite the fact that “1995-07-15 07:30”, “1995-07-14 23:30” and “1995-07-15 13:00” look like three different values, in this case they all, in fact, represent the same absolute moment in time. If each user now does a SELECT on the Table, this is the result they’ll see:

TIME_LOCAL	TIME_WITH_TIME_ZONE
1995-07-15 07:30:00	1995-07-14 23:30:30-8:00  -- in Vancouver
1995-07-15 07:30:00	1995-07-15 07:30:00+0:00  -- in London
1995-07-15 07:30:00	1995-07-15 13:00:00+5:30  -- in Dehli

Note that the value in the TIME_LOCAL Column stays the same regardless of the installation: a time or timestamp without a <time zone interval> always means “local time” unless the application requires it to take on a time zone offset. The value in the TIME_WITH_TIME_ZONE Column, however, changes with the installation – this is because the <timestamp literal> was forced to take on the default time zone offset at each installation. In this example, the UTC time is equal to the London local time of '1995-07-15 07:30' – i.e.: when the London user selects from the Table, the display shows:

'1995-07-15 07:30+00:00'

to show that the local time is the same as the UTC time; that is, it must be offset by 0 hours and 0 minutes to a UTC time of:

'1995-07-15 07:30'

When the Vancouver user does the same SELECT, however, the display shows:

'1995-07-14 23:30-08:00'

to show that the local time is 8 hours less than the UTC time; that is, it must be offset by 8 hours and 0 minutes to a UTC time of:

'1995-07-15 07:30'

And when the Delhi user does the same SELECT, the display shows:

'1995-07-15 13:00+05:30'

to show that the local time is 5.5 hours greater than the UTC time; that is, it must be offset -5 hours and 30 minutes to a UTC time of:

'1995-07-15 07:30'

Time Zone Offset Arithmetic

Earlier we said that time zone offset arithmetic is somewhat counterintuitive – here’s a more detailed explanation. Recall that a time zone offset is the difference between local time and UTC time – say, for example, 4 hours. Then,

• In the case of a time zone that is 4 hours earlier than UTC (e.g.: 12:00 local is 16:00 UTC), the time zone offset is -04:00 (i.e.: local time is 4 hours less than UTC time).

• In the case of a time zone that is 4 hours later than UTC (e.g.: 16:00 UTC is 20:00 local), the time zone offset is +04:00 (i.e.: local time is 4 hours plus UTC time).

The rule is: to get the UTC value, subtract the time zone offset from the time or timestamp. Thus, a local time of '12:00-04:00' evaluates to UTC 16:00 (add the 4 hours, you’re subtracting a negative) and a local time of '20:00+04:00' evaluates to UTC 16:00 (subtract the 4 hours).

Temporal <literal>s

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 dor 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'

The following <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, vyyy 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. 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' to 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 as follows.

<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 either be MONTH or it 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 hours may range from zero to 11, hours within days may range from zero to 23, minutes within years 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). This 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 99.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)

Examples of <interval qualifier>s
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

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 as follows.

<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 + – 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 as follows.

<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 MONTHv. 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. 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.)

The following <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.

Temporal <data type>s

A temporal <data type> is either a datetime <data type> or an interval <data type>.

Datetime <data type>s

A datetime <data type> is defined by a descriptor that contains two pieces of information:

1. The <data type>’s name: either DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP or TIMESTAMP WITH TIME ZONE.

2. The <data type>s fractional seconds precision (for TIME, TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE types).

DATE

The required syntax for a DATE <data type> specification is as follows.

DATE <data type> ::=
DATE

DATE combines the datetime fields YEAR, MONTH and DAY; it defines a set of correctly formed values that represent any valid Gregorian calendar date between '0001-01-01' and '9999-12-31' (i.e.: between January 1, 1 AD and December 31, 9999 AD). It has a length of 10 positions.

DATE expects dates to have the following form:

yyyy-mm-dd

e.g., this date represents July 15, 1994:

1994-07-15

Any operation that attempts to make a DATE <data type> contain a YEAR value that is either less than 1 or greater than 9999 will fail: the DBMS will return the SQLSTATE error 22007 "data exception-invalid datetime format".

Here is an example of DATE:

CREATE TABLE date_table_1 (
   start_date DATE);

INSERT INTO date_table_1 (start_date)
VALUES (DATE '1996-01-01');

TIME

The required syntax for a TIME <data type> specification is as follows.

TIME <data type> ::=
TIME [ (fractional seconds precision) ] [ WITHOUT TIME ZONE ]

TIME (or TIME WITHOUT TIME ZONE) combines the datetime fields HOUR, MINUTE and SECOND; it defines a set of correctly formed values that represent any valid time of day (based on a 24 hour clock) between '00:00:00' and (at a minimum) '23:59:61.999999'. (The SQL Standard requires DBMSs to allow for the addition of up to 2 “leap” seconds in a valid time.) It has a length of at least 8 positions.

The optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECOND datetime field. The minimum fractional seconds precision and the default fractional seconds precision are both zero. For example, these two <data type> specifications both define a set of times with a fractional seconds precision of zero digits:

TIME
-- would contain values like 13:30:22

TIME(0)
-- would also contain values like 13:30:22

This <data type> specification defines a set of times with a fractional seconds precision of two digits, i.e.: of one-hundredth of a second:

TIME(2)  -- would contain values like 13:30:22.05

[NON-PORTABLE] The maximum fractional seconds precision for TIME (a) may not be less than 6 digits and (b) must be equal to the maximum allowed for the TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE <data type>s but is non-standard because the SQL Standard requires implementors to define TIME’s maximum fractional seconds precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the fractional seconds precision of TIME to range from 0 to 6 digits.

TIME expects times to have the following form:

hh:mm:ss[.nnnnnn]

e.g., these two times both represent half past one, plus 22 seconds, PM:

13:30:22
13:30:22.00

and this time represents half past one, plus 22 and one-tenth seconds, PM:

13:30:22.10

The actual length of a TIME depends on the fractional seconds precision. These two <data type> specifications have a length of 8 positions:

TIME
TIME(0)

The following <data type> specification has a length of 10 positions:

TIME(1) -- 8 plus decimal point plus 1 digit in fractional seconds precision

This <data type> specification has a length of 15 positions:

TIME(6)

[Obscure Rule] TIME has a time zone offset equal to the current default time zone offset of the SQL-session – it represents a local time.

Here is an example of TIME:

CREATE TABLE time_table_1 (
   start_time_1 TIME,
   start_time_2 TIME(2));

INSERT INTO time_table_1 (start_time_1, start_time_2)
VALUES (TIME '14:14:14', TIME '14:14:14.00');

INSERT INTO time_table_1 (start_time_1, start_time_2)
VALUES (TIME '15:15:15.', TIME '15:15:15.10');

INSERT INTO time_table_1 (start_time_1, start_time_2)
VALUES (TIME '16:16:16.00', TIME '16:16:16.05');

If you want to restrict your code to Core SQL, don’t define your TIME <data type>s with a fractional seconds precision and don’t add the optional noise words WITHOUT TIME ZONE – use only TIME, never TIME(x) WITHOUT TIME ZONE.

TIME WITH TIME ZONE

[Obscure Rule] applies for this entire section.

The required syntax for a TIME WITH TIME ZONE <data type> specification is as follows.

TIME WITH TIME ZONE <data type> ::=
TIME [ (fractional seconds precision) ] WITH TIME ZONE

TIME WITH TIME ZONE combines the datetime fields HOUR, MINUTE, SECOND, TIMEZONE_HOUR and TIMEZONE_MINUTE; it defines a set of correctly formed values that represent any valid time of day (based on a 24 hour clock) between '00:00:00' and (at a minimum) '23:59:61.999999' with a time zone offset that must be between '-12:59' and '+13:00'. (The SQL Standard requires DBMSs to allow for the addition of up to 2 “leap” seconds in a valid time.) TIME WITH TIME ZONE has a length of at least 14 positions.

As with TIME, the optional fractional seconds precision for TIME WITH TIME ZONE specifies the number of digits following the decimal point in the SECOND datetime field. The minimum fractional seconds precision and the default fractional seconds precision are both zero.

[NON-PORTABLE] The maximum fractional seconds precision for TIME WITH TIME ZONE (a) may not be less than 6 digits and (b) must be equal to the maximum allowed for the TIME, TIMESTAMP and vTIMESTAMP WITH TIME ZONE`` <data type>s but is non-standard because the SQL Standard requires implementors to define TIME WITH TIME ZONE’s maximum fractional seconds precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the fractional seconds precision of TIME WITH TIME ZONE to range from 0 to 6 digits.

TIME WITH TIME ZONE expects times to have the following form:

hh:mm:ss[.nnnnnn ][{+|-}HH:MM ]

e.g., the following time represents “half past one, plus 22 seconds, PM” with a time zone offset of 2 and a half hours:

13:30:22+02:30

The actual length of a TIME WITH TIME ZONE depends on the fractional seconds precision. These two <data type> specifications have a length of 14 positions:

TIME WITH TIME ZONE
TIME(0) WITH TIME ZONE

This <data type> specification has a length of 16 positions:

TIME(1) WITH TIME ZONE -- 14 plus decimal point plus 1 digit in fractional
                          seconds precision

This <data type> specification has a length of 21 positions:

TIME(6) WITH TIME ZONE

[Obscure Rule] TIME WITH TIME ZONE has a time zone offset equal to the <time zone interval> specified for a given time value: it represents a time in the given time zone. If the <time zone interval> is omitted from a given time value, TIME WITH TIME ZONE has a time zone offset equal to the default time zone offset of the SQL-session: it represents a local time. The default time zone offset is the <time zone interval> specified in the most recent SET TIME ZONE statement issued during the SQL-session. If you haven’t issued a SET TIME ZONE statement, the default time zone offset is your DBMS’s initial default time zone offset.

[NON-PORTABLE] The default time zone offset is non-standard because the SQL Standard requires implementors to define the initial default time zone offset for an SQL-session. [OCELOT Implementation] The OCELOT DBMS that comes with this book sets the SQL-session’s initial default time zone offset to INTERVAL +'00:00' HOUR TO MINUTE – this represents UTC.

Here is an example of TIME WITH TIME ZONE:

CREATE TABLE time_table_2 (
   start_time_1 TIME WITH TIME ZONE,
   start_time_2 TIME(2) WITH TIME ZONE);

INSERT INTO time_table_2 (start_time_1, start_time_2)
VALUES (TIME '14:14:14+03:00', TIME '14:14:14.00+03:00');

INSERT INTO time_table_2 (start_time_1, start_time_2)
VALUES (TIME '15:15:15.-03:00', TIME '15:15:15.10-03:00');

INSERT INTO time_table_2 (start_time_1, start_time_2)
VALUES (TIME '16:16:16.00+03:30', TIME '16:16:16.05+03:30');

If you want to restrict your code to Core SQL, don’t use TIME WITH TIME ZONE <data type>s.

TIMESTAMP

The required syntax for a TIMESTAMP <data type> specification is as follows.

TIMESTAMP <data type> ::=
TIMESTAMP [ (fractional seconds precision) ][ WITHOUT TIME ZONE ]

TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE) combines the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND – it defines a set of correctly formed values that represent any valid Gregorian calendar date between '0001-01-01' and '9999-12-31' (i.e., between January 1, 1 AD and December 31, 9999 AD) combined with any valid time of day (based on a 24 hour clock) between '00:00:00' and (at a minimum) '23:59:61.999999'. (The SQL Standard requires DBMSs to allow for the addition of up to 2 “leap” seconds in a valid time.) TIMESTAMP has a length of at least 19 positions.

The optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECOND datetime field. The minimum fractional seconds precision is zero. The default fractional seconds precision is 6. For example, this <data type> specification defines a set of timestamps with a fractional seconds precision of zero digits:

TIMESTAMP(0)
-- would contain values like '1994-07-15 13:30:22'

These two <data type> specifications both define a set of timestamps with a fractional seconds precision of 6 digits, i.e.: of one-millionth of a second:

TIMESTAMP
-- would contain values like '1994-07-15 13:30:22.999999'

TIMESTAMP(6)
-- would also contain values like '1994-07-15 13:30:22.999999'

[NON-PORTABLE] The maximum fractional seconds precision for TIMESTAMP (a) may not be less than 6 digits and (b) must be equal to the maximum allowed for the TIME, TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE <data type>s but is non-standard because the SQL Standard requires implementors to define TIMESTAMP’s maximum fractional seconds precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the fractional seconds precision of TIMESTAMP to range from 0 to 6 digits.

Any operation that attempts to make a TIMESTAMP <data type> contain a YEAR value that is either less than 1 or greater than 9999 will fail: the DBMS will return the SQLSTATE error 22007 "data exception-invalid datetime format".

TIMESTAMP expects timestamps to have the following form:

yyyy-mm-dd hh:mm:ss[.nnnnnn]

e.g., these two timestamps both represent “half past one, plus 22 seconds, PM on July 15, 1994:

1994-07-15 13:30:22
1994-07-15 13:30:22.00

and this timestamp represents “half past one, plus 22 and one-tenth seconds, PM on July 15, 1994:

1994-07-15 13:30:22.10

Note the mandatory space between the date portion and the time portion of the timestamps.

The actual length of a TIMESTAMP depends on the fractional seconds precision. This <data type> specification has a length of 19 positions:

TIMESTAMP(0)

This <data type> specification has a length of 21 positions:

TIMESTAMP(1) -- 19 plus decimal point plus 1 digit in fractional seconds
                precision

The following two <data type> specifications both have a length of 26 positions:

TIMESTAMP
TIMESTAMP(6)

[Obscure Rule] TIMESTAMP has a time zone offset equal to the current default time zone offset of the SQL-session: it represents a local timestamp.

Here is an example of TIMESTAMP:

CREATE TABLE timestamp_table_1 (
   start_timestamp_1 TIMESTAMP,
   start_timestamp_2 TIMESTAMP(2));

INSERT INTO timestamp_table_1 (start_timestamp_1, start_timestamp_2)
VALUES (
   TIMESTAMP '1997-04-01 14:14:14.999999',
   TIMESTAMP '1994-07-15 15:15:15.15');

If you want to restrict your code to Core SQL, don’t define your TIMESTAMP <data type>s with a fractional seconds precision other than 0 or 6 and don’t add the optional noise words WITHOUT TIME ZONE: use only TIMESTAMP, TIMESTAMP(0) or TIMESTAMP(6), never TIMESTAMP(x) WITHOUT TIME ZONE.

Tip

Consider using a TIMESTAMP to store time-of-day values if you plan on doing time arithmetic: TIMESTAMP '1000-01-01 13:45:00' instead of TIME '13:45:00'. Although this wastes space on a meaningless date value, your time arithmetic will be more meaningful, since any “carries” or “borrows” will show up in the results.

TIMESTAMP WITH TIME ZONE

[Obscure Rule] applies for this entire section.

The required syntax for a TIMESTAMP WITH TIME ZONE <data type> specification is as follows.

TIMESTAMP WITH TIME ZONE <data type> ::=
TIMESTAMP [ (fractional seconds precision) ] WITH TIME ZONE

TIMESTAMP WITH TIME ZONE combines the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR and TIMEZONE_MINUTE; it defines a set of correctly formed values that represent any valid Gregorian calendar date between '0001-01-01' and '9999-12-31' (i.e.: between January 1, 1 AD and December 31, 9999 AD) combined with any valid time of day (based on a 24 hour clock) between '00:00:00' and (at a minimum) '23:59:61.999999' with a time zone offset that must be between '-12:59' and '+13:00'. (The SQL Standard requires DBMSs to allow for the addition of up to 2 “leap” seconds in a valid time.) TIMESTAMP WITH TIME ZONE has a length of at least 25 positions.

As with TIMESTAMP, the optional fractional seconds precision for TIMESTAMP WITH TIME ZONE specifies the number of digits following the decimal point in the SECOND datetime field. The minimum fractional seconds precision is zero. The default fractional seconds precision is 6.

[NON-PORTABLE] The maximum fractional seconds precision for TIMESTAMP WITH TIME ZONE (a) may not be less than 6 digits and (b) must be equal to the maximum allowed for the TIME, TIME WITH TIME ZONE and TIMESTAMP <data type>s but is non-standard because the SQL Standard requires implementors to define TIMESTAMP WITH TIME ZONE’s maximum fractional seconds precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the fractional seconds precision of TIMESTAMP WITH TIME ZONE to range from 0 to 6 digits.

Any operation that attempts to make a TIMESTAMP WITH TIME ZONE <data type> contain a YEAR value that is either less than 1 or greater than 9999 will fail: the DBMS will return the SQLSTATE error 22007 "data exception-invalid datetime format".

TIMESTAMP WITH TIME ZONE expects timestamps to have the following form:

yyyy-mm-dd hh:mm:ss[.nnnnnn ][{+|-}HH:MM ]

e.g., the following timestamps all represent “half past one, plus 22 seconds, PM on July 15, 1994” with a time zone offset of 2 and a half hours:

1994-07-15 13:30:22+02:30
1994-07-15 13:30:22.+02:30
1994-07-15 13:30:22.00+02:30

The actual length of a TIMESTAMP WITH TIME ZONE depends on the fractional seconds precision. This <data type> specification has a length of 25 positions:

TIMESTAMP(0) WITH TIME ZONE

This <data type> specification has a length of 27 positions:

TIMESTAMP(1) WITH TIME ZONE -- 25 plus decimal point plus 1 digit in
                               fractional seconds precision

These two <data type> specifications both have a length of 32 positions:

TIMESTAMP WITH TIME ZONE
TIMESTAMP(6) WITH TIME ZONE

[Obscure Rule] TIMESTAMP WITH TIME ZONE has a time zone offset equal to the <time zone interval> specified for a given timestamp value: it represents a timestamp in the given time zone. If the <time zone interval> is omitted from a given timestamp value, TIMESTAMP WITH TIME ZONE has a time zone offset equal to the default time zone offset of the SQL-session: it represents a local timestamp. The default time zone offset is the <time zone interval> specified in the most recent SET TIME ZONE statement issued during the SQL-session. If you haven’t issued a SET TIME ZONE statement, the default time zone offset is your DBMS’s initial default time zone offset.

[NON-PORTABLE] The default time zone offset is non-standard because the SQL Standard requires implementors to define the initial default time zone offset for an SQL-session. [OCELOT Implementation] The OCELOT DBMS that comes with this book sets the SQL-session’s initial default time zone offset to INTERVAL +'00:00' HOUR TO MINUTE – this represents UTC.

Here is an example of TIMESTAMP WITH TIME ZONE:

CREATE TABLE timestamp_table_2 (
   start_timestamp_1 TIMESTAMP WITH TIME ZONE,
   start_timestamp_2 TIMESTAMP(2) WITH TIME ZONE);

INSERT INTO timestamp_table_2 (start_timestamp_1, start_timestamp_2)
VALUES (
   TIMESTAMP '1997-04-01 14:14:14.999999-03:00',
   TIMESTAMP '1994-07-15 14:14:14.35+02:15');

If you want to restrict your code to Core SQL, don’t use TIMESTAMP WITH TIME ZONE <data type>s.

Interval <data type>s

An interval <data type> is defined by a descriptor that contains two pieces of information:

1. The <data type>’s name – INTERVAL.

2. The <data type>s <interval qualifier>, which specifies the type of interval and the precision of the interval’s set of valid values.

INTERVAL

The required syntax for an INTERVAL <data type> specification is as follows.

INTERVAL <data type> ::=
INTERVAL <interval qualifier>

INTERVAL is a span of time; it defines a set of correctly formed values that represent any span of time compatible with the <interval qualifier>. It combines the datetime fields YEAR and/or MONTH if it is a year-month interval. It combines the datetime fields DAY and/or HOUR and/or MINUTE and/or SECOND if it is a day-time interval. INTERVAL has a length of at least 1 positions.

A year-month INTERVAL combines one or more of the datetime fields YEAR and MONTH in the <interval qualifier>. The possible definitions are thus:

INTERVAL YEAR [ (leading precision) ]
INTERVAL MONTH [ (leading precision) ]
INTERVAL YEAR [ (leading precision) ] TO MONTH

The leading precision, if specified, is as described in “<interval qualifier>”. The values of the start_datetime field are constrained only by the leading precision of that field. The month value in INTERVAL YEAR TO MONTH represents an additional number of months (within years) and can thus range only from 0 to 11.

INTERVAL YEAR expects intervals to have the following form:

'y[...]'

e.g., '20' represents a span of 20 years. INTERVAL YEAR has a length of “leading precision” SQL_TEXT characters. For example, this <data type> specification has a length of 4 positions:

INTERVAL YEAR(4)

INTERVAL MONTH expects intervals to have the following form:

'm[...]'

e.g., '15' represents a span of 15 months. INTERVAL MONTH has a length of “leading precision” SQL_TEXT characters. For example, this <data type> specification has a length of 2 positions:

INTERVAL MONTH

(The default precision is 2 digits.)

INTERVAL YEAR TO MONTH expects intervals to have the following form:

'y[...]-mm'

e.g., '20-03' represents a span of 20 years plus 3 months. INTERVAL YEAR TO MONTH has a length of “leading precision” plus 3 positions. For example, this <data type> specification has a length of 5 positions:

INTERVAL YEAR TO MONTH

A day-time INTERVAL combines one or more of the datetime fields DAY, HOUR, MINUTE and SECOND in the <interval qualifier>. The possible definitions are thus:

INTERVAL DAY [ (leading precision) ]
INTERVAL HOUR [ (leading precision) ]
INTERVAL MINUTE [ (leading precision) ]
INTERVAL SECOND [ (leading precision [ ,fractional seconds precision ]) ]

INTERVAL DAY [ (leading precision) ] TO HOUR
INTERVAL DAY [ (leading precision) ] TO MINUTE
INTERVAL DAY [ (leading precision) ] TO SECOND [ (fractional seconds
                precision) ]

INTERVAL HOUR [ (leading precision) ] TO MINUTE
INTERVAL HOUR [ (leading precision) ] TO SECOND [ (fractional seconds
                 precision) ]
INTERVAL MINUTE [ (leading precision) ] TO SECOND [ (fractional seconds
                   precision) ]

The leading precision, if specified, is as described in “<interval qualifier>”. The values of the start_datetime field are constrained only by the leading precision of that field. The hour value in INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE and INTERVAL DAY TO SECOND represents an additional number of hours (within days) and can thus range only from 0 to 23. The minute value in INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR TO MINUTE and INTERVAL HOUR TO SECOND represents an additional number of minutes (within hours) and can thus range only from 0 to 59. The seconds value in INTERVAL DAY TO SECOND, INTERVAL HOUR TO SECOND and INTERVAL MINUTE TO SECOND represents an additional number of seconds and fractions of a second (within minutes) and can thus range only from 0 to 59.9n (where “.9n” represents the number of digits defined for the fractional seconds precision). The fractional seconds precision, if specified, is as described in “<interval qualifier>.

INTERVAL DAY expects intervals to have the following form:

'd[...]'

e.g., '1' represents a span of 1 day. INTERVAL DAY has a length of “leading precision” SQL_TEXT characters. For example, this <data type> specification has a length of 2 positions:

INTERVAL DAY

(The default precision is 2 digits.)

INTERVAL HOUR expects intervals to have the following form:

'h[...]'

e.g., '15' represents a span of 15 hours. INTERVAL HOUR has a length of “leading precision” SQL_TEXT characters. For example, this <data type> specification has a length of 2 positions:

INTERVAL HOUR

INTERVAL MINUTE expects intervals to have the following form:

'm[...]'

e.g., '75' represents a span of 75 minutes. INTERVAL MINUTE has a length of “leading precision” positions. For example, this <data type> specification has a length of 2 positions:

INTERVAL MINUTE

INTERVAL SECOND expects intervals to have the following form:

's[...[.n...]]'

e.g., '1' represents a span of 1 second, '20' and '20.0' both represent a span of 20 seconds and '20.5' represents a span of 20.5 seconds. INTERVAL SECOND has a length of “leading precision” plus “fractional seconds precision” SQL_TEXT characters. For example, this <data type> specification has a length of 2 positions:

INTERVAL SECOND(0)

These two <data type> specifications both have a length of 9 positions:

INTERVAL SECOND
INTERVAL SECOND(6)

(The default fractional seconds precision is 6 digits. A fractional seconds precision greater than zero includes one position for the decimal point.)

INTERVAL DAY TO HOUR expects intervals to have the following form:

'd[...] h[...]'

e.g., '1 1' represents a span of 1 day plus 1 hour and '20 10' represents a span of 20 days plus 10 hours. (Note the mandatory space between the days portion and the hours portion of the interval.) INTERVAL DAY TO HOUR has a length of “leading precision” plus 3 SQL_TEXT characters. For example, this <data type> specification has a length of 5 positions:

INTERVAL DAY TO HOUR

INTERVAL DAY TO MINUTE expects intervals to have the following form:

'd[...] h[...]:m[...]'

e.g., '1 1:1' represents a span of 1 day, 1 hour plus 1 minute and '20 10:15' represents a span of 20 days, 10 hours plus 15 minutes. (Note the mandatory colon between the hours portion and the minutes portion of the interval.) INTERVAL DAY TO MINUTE has a length of “leading precision” plus 6 SQL_TEXT characters. For example, this <data type> specification has a length of 8 positions:

INTERVAL DAY TO MINUTE

INTERVAL DAY TO SECOND expects intervals to have the following form:

'd[...] h[...]:m[...]:s[...[.n...]]'

e.g., '1 1:1:1' and '01 01:01:01.00' both represent a span of 1 day, 1 hour, 1 minute plus 1 second and '20 10:15:20.5' represents a span of 20 days, 10 hours, 15 minutes plus 20.5 seconds. (Note the mandatory colon between the minutes portion and the seconds portion of the interval.) INTERVAL DAY TO SECOND has a length of “leading precision” plus “fractional seconds precision” plus 9 positions. For example, this <data type> specification has a length of 11 positions:

INTERVAL DAY TO SECOND(0)

These two <data type> specifications both have a length of 18 positions:

INTERVAL DAY TO SECOND
INTERVAL DAY TO SECOND(6)

INTERVAL HOUR TO MINUTE expects intervals to have the following form:

'h[...]:m[...]'

e.g., '10:15' represents a span of 10 hours plus 15 minutes. INTERVAL HOUR TO MINUTE has a length of “leading precision” plus 3 positions. For example, this <data type> specification has a length of 5 positions:

INTERVAL HOUR TO MINUTE

INTERVAL HOUR TO SECOND expects intervals to have the following form:

'h[...]:m[...]:s[...[.n...]]'

e.g., '10:15:20.5' represents a span of 10 hours, 15 minutes plus 20.5 seconds. INTERVAL HOUR TO SECOND has a length of “leading precision” plus “fractional seconds precision” plus 6 positions. For example, this <data type> specification has a length of 8 positions:

INTERVAL HOUR TO SECOND(0)

These two <data type> specifications both have a length of 15 positions:

INTERVAL HOUR TO SECOND
INTERVAL HOUR TO SECOND(6)

INTERVAL MINUTE TO SECOND expects intervals to have the following form:

'm[...]:s[...[.n...]]'

e.g., '15:20.5' represents a span of 15 minutes plus 20.5 seconds and '14:15' represents a span of 14 minutes plus 15 seconds. INTERVAL MINUTE TO SECOND has a length of “leading precision” plus “fractional seconds precision” plus 3 positions. For example, this <data type> specification has a length of 5 positions:

INTERVAL MINUTE TO SECOND(0)

These two <data type> specifications both have a length of 12 positions:

INTERVAL MINUTE TO SECOND
INTERVAL MINUTE TO SECOND(6)

Here is an example of INTERVAL:

CREATE interval_table (
   interval_column_1 INTERVAL YEAR(3) TO MONTH,
   interval_column_2 INTERVAL DAY TO MINUTE,
   interval_column_3 INTERVAL MINUTE TO SECOND(4));

INSERT INTO interval_table (
   interval_column_1,
   interval_column_2,
   interval_column_3)
VALUES (
   INTERVAL '150-01' YEAR TO MONTH,
   INTERVAL '-36 22:30' DAY TO MINUTE,
   INTERVAL -'15:22.0001' MINUTE TO SECOND(4));

If you want to restrict your code to Core SQL, don’t use the INTERVAL <data type>.

Now that we’ve described SQL’s datetime <data type>s, let’s look at some example SQL statements that put them to use.

These SQL statements make a Table with a date Column, insert a row, then search for any date after January 2nd, 2000.

CREATE TABLE Date_Examples (
   occurrence_date DATE);

INSERT INTO Date_Examples (occurrence_date)
VALUES (DATE '2001-02-29');

SELECT occurrence_date
FROM   Date_Examples
WHERE  occurrence_date > DATE '2000-01-02';

These SQL statements make a Table with two time-of-day Columns, insert a row, then search for any time before 8:30 PM.

CREATE TABLE Time_Examples (
   occurrence_time TIME,
   occurrence_time_zone TIME WITH TIME ZONE);

INSERT INTO Time_Examples (occurrence_time, occurrence_time_zone)
VALUES (TIME '12:00:00', TIME '12:00:00+3:00');

SELECT occurrence_time, occurrence_time_zone
FROM   Time_Examples
WHERE  occurrence_time < TIME '20:30:00';

These SQL statements make a Table with two timestamp Columns, insert a row, then search for any timestamp equal to January 2nd, 2000 at 1 second past midnight.

CREATE TABLE Timestamp_Examples (
   occurrence_timestamp TIMESTAMP,
   occurrence_timestamp_zone TIMESTAMP WITH TIME ZONE);

INSERT INTO Timestamp_Examples (
   occurrence_timestamp,
   occurrence_timestamp_zone)
VALUES (
   TIMESTAMP '2001-02-29 16:00:00',
   TIMESTAMP '2001-02-29 16:00:00+0:00');

SELECT occurrence_timestamp, occurrence_timestamp_zone
FROM   Timestamp_Examples
WHERE  occurrence_timestamp_zone = TIMESTAMP '2000-01-02 00:00:01';

These SQL statements make a Table with two year-month interval Columns, insert a row, then search for any interval that is less than or equal to 37 months.

CREATE TABLE YInterval_Examples (
   occurrence_interval_1 INTERVAL YEAR,
   occurrence_interval_2 INTERVAL YEAR TO MONTH);

INSERT INTO YInterval_Examples (
   occurrence_interval_1,
   occurrence_interval_2)
VALUES (
   INTERVAL '3' YEAR,
   INTERVAL '02-10' YEAR TO MONTH');

SELECT occurrence_interval_1, occurrence_interval_2
FROM   YInterval_Examples
WHERE  occurrence_interval_1 <= INTERVAL '37' MONTH;

These SQL statements make a Table with two day-time interval Columns, insert two rows, then search for any interval that doesn’t equal 30 seconds.

CREATE TABLE DInterval_Examples (
   occurrence_interval_1 INTERVAL SECOND,
   occurrence_interval_2 INTERVAL SECOND(2,4));

INSERT INTO DInterval_Examples (
   occurrence_interval_1,
   occurrence_interval_2)
VALUES (
   INTERVAL '25.000005' SECOND,
   INTERVAL '25.0001' SECOND');

INSERT INTO DInterval_Examples (
   occurrence_interval_1,
   occurrence_interval_2)
VALUES (
   INTERVAL '22' SECOND,
   INTERVAL '22' SECOND');

SELECT occurrence_interval_1, occurrence_interval_2
FROM   DInterval_Examples
WHERE  occurrence_interval_1 <> INTERVAL '30' SECOND;

Temporal Operations

A temporal value is only compatible with, and comparable to, a matching temporal value; that is, only temporal values of the same type, that also consist of matching datetime fields, are mutually comparable and mutually assignable. Thus, (a) dates are comparable and assignable only to dates, (b) times are comparable and assignable only to times, (c) timestramps are comparable and assignable only to timestamps, (d) year-month intervals are comparable and assignable only to year-month intervals, and (e) day-time intervals are comparable and assinable only. day-time intervals. Temporal values may not be directly compared with, or directly assigned to, non-compatible datetimes or intervals or to any other <data type> class, though implicit type conversions can occur in expressions, SELECTs, INSERTs, DELETEs and UPDATEs. Explicit temporal type conversions can be forced with the CAST operator.

CAST

In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. The required syntax for the CAST operator is as follows.

CAST (<cast operand> AS <cast target>)
   <cast operand> ::= scalar_expression
   <cast target> ::= <Domain name> | <data type>

The CAST operator converts values of a source <data type> into values of a target <data type>, where each <data type> is an SQL pre-defined <data type> (data conversions between UDTs are done with a user-defined cast). The source <data type>, or <cast operand>, can be any expression that evaluates to a single value. The target <data type>, or <cast target>, is either an SQL predefined <data type> specification or the name of a Domain whose defined <data type> is the SQL predefined <data type> that you want to convert the value of “scalar_expression” into. (If you use CAST (... AS <Domain name>), your current <AuthorizationID> must have the USAGE Privilege on that Domain.)

It isn’t, of course, possible to convert the values of every <data type> into the values of every other <data type>. For temporal values, the rules are:

• CAST (NULL AS <data type>) and CAST (temporal_source_is_a_null_value AS <data type>) both result in NULL.

• You can CAST a date source to these targets: fixed length character string, variable length character string, CLOB, NCLOB, date and timestamp. You can also CAST a date source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

• You can CAST a time source to these targets: fixed length character string, variable length character string, CLOB, NCLOB, time and timestamp. You can also CAST a time source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

• You can CAST a timestamp source to these targets: fixed length character string, variable length character string, CLOB, NCLOB, date, time and timestamp. You can also CAST a timestamp source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

• You can CAST a year-month interval source to these targets: fixed length character string, variable length character string, CLOB, NCLOB and year-month interval. You can CAST a day-time interval source to these targets: fixed length character string, variable length character string, CLOB, NCLOB and day-time interval. You can also CAST an interval source to an exact numeric target, provided the source contains only one datetime field – that is, you can CAST an INTERVAL YEAR to an integer or an INTERVAL MONTH to an integer, but you can’t CAST an INTERVAL YEAR TO MONTH to an integer. You can CAST an interval source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

  When you CAST any temporal value to a fixed length character string, variable length character string, CLOB or NCLOB target, your DBMS converts the source value to the shortest possible character string that can express the source value (for example, CAST (DATE '1994-07-15' AS CHAR(10)) results in the character string '1994-07-15').

• For fixed length character string targets, if the length of the converted source value equals the fixed length of the target, then the result is the converted source c value, padded on the right with however many spaces are needed to make the lengths the same. If the length of the result is longer than the fixed length of the target, the CAST will fail; your DBMS will return the SQLSTATE error 22001 "dtata exception string data, right truncation." And if the result contains any characters that don´t belong to the target´s Character set, the CAST will also fail; your DBMS will return the SQLSTATE error 22018 "data exception-invalid character value for cast."

• For variable length character string, CLOB, or NCLOB targets, if the length of the converted source value is less than or equals the maximum length of the target, then the resut is the converted source value. If the length of the result is longer than the maximum length of the target, the CAST will fail; your DBMS will return the SQLSTATE error 22001 "data exeption-string data, right truncation." And if the result contains any characters that don´t belong to the target´s Character set, the CAST will also fail; your DBMS will return the SQLSTATE error 22018 "data exception-invalid character value for cast."

• [Obscure Rule] The result of a CAST to a character string target has the COERCIBLE coercibility attribute; its Collation is the default Collation for the target’s Character set.

When you CAST any temporal value to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine’s argument. The CAST result is the value returned by the user defined cast.

CAST (DATE AS temporal)

• When you CAST a date to a date target, the result is the source date.

• When you CAST a date to a timestamp target, the result is a timestamp whose date portion is the same as the source date and whose time portion is zero (that is, CAST (DATE '1994-07-15' AS TIMESTAMP) results in TIMESTAMP '1994-07-15 00:00:00.000000').

CAST (TIME AS temporal)

• When you CAST a time to a time target or a time with time zone to a time with time zone target, the result is the source time.

• When you CAST a time to a time with time zone target, the result is the source time converted to UTC.

• When you CAST a time with time zone to a time target, the result is the source time converted to the local time.

• When you CAST a time to a timestamp target or a time with time zone to a timestamp with time zone target, the result is a timestamp whose date portion is the value of CURRENT_DATE and whose time portion is the same as the source time (that is, CAST (TIME '10:10:10.01' AS TIMESTAMP) results in TIMESTAMP '1994-07-15 10:10:10.010000' if today’s date is July 15, 1994).

• When you CAST a time to a timestamp with time zone target, the result is a timestamp whose date portion is the value of CURRENT_DATE and whose time portion is the same as the source time converted to UTC.

• When you CAST a time with time zone to a timestamp target, the result is a timestamp whose date portion is the value of CURRENT_DATE and whose time portion is the same as the source time converted to the local time.

CAST (TIMESTAMP AS temporal)

• When you CAST a timestamp to a date target, the result is the date portion of the timestamp. For example, CAST (TIMESTAMP '1994-07-15 10:10:10:010000' AS DATE) results in DATE '1994-07-15'. When you CAST a timestamp with time zone to a date target, the result is the date portion of the timestamp, adjusted by the time zone offset if required.

• When you CAST a timestamp to a time target or a timestamp with time zone to a time with time zone target, the result is the time portion of the timestamp. For example, CAST (TIMESTAMP '1994-07-15 10:10:10:010000+02:30') results in TIME '10:10:10:010000+02:30'.

• When you CAST a timestamp to a time with time zone target, the result is the time portion of the timestamp converted to UTC.

• When you CAST a timestamp with time zone to a time target, the result is the time portion of the timestamp converted to the local time.

• When you CAST a timestamp to a timestamp target or a timestamp with time zone to a timestamp with time zone target, the result is the source timestamp.

• When you CAST a timestamp to a timestamp with time zone target, the result is the source timestamp, with its time portion converted to UTC.

• When you CAST a timestamp with time zone to a timestamp target, the result is the source timestamp, with its time portion converted to the local time.

When you CAST any interval to a numeric target or temporal target, the rules are as follows:

• When you CAST an interval to an exact numeric target, your interval has to be for one datetime field only. The result of the CAST is the numeric value of that datetime field. For example, CAST ('100' INTERVAL YEAR(3) AS SMALLINT) results in a SMALLINT value of 100. (Note: if the numeric value of your interval can’t be represented as a target value without losing any leading significant digits, the CAST will fail: your DBMS will return the SQLSTATE error 22003 "data exception-numeric value out of range".

• When you CAST a year-month interval to a year-month interval target or a day-time interval to a day-time interval target, if both source and target have the same <interval qualifier> then the result of the CAST is the source interval.

• When you CAST a year-month interval to a year-month interval target or a day-time interval to a day-time interval target, if the source and target have different <interval qualifier>s, then the result of the CAST is the source interval converted to its equivalent in units of the target interval. For example, CAST ('3' INTERVAL YEAR TO INTERVAL MONTH) results in INTERVAL '36' MONTH and CAST ('62' INTERVAL MINUTE AS INTERVAL HOUR TO MINUTE) results in INTERVAL '01:02' HOUR TO MINUTE. (Note: if the CAST would result in the loss of precision of the most significant datetime field of the converted source value, the CAST will fail: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

If you want to restrict your code to Core SQL, don’t use <Domain name> as a CAST target – CAST only to a <data type>.

Assignment

In SQL, temporal values must be compatible to be assigned to one another – that is, the source and the target must either (a) both be dates, (b) both be times (with or without time zone), (c) both be timestamps (with or without time zone), (d) both be year-month intervals or (e) both be day-time intervals.

[Obscure Rule] Since only SQL accepts null values, if your source is NULL and your target is not an SQL-data target, then your target’s value is not changed. Instead, your DBMS will set the target’s indicator parameter to -1, to indicate that an assignment of the null value was attempted. If your target doesn’t have an indicator parameter, the assignment will fail: your DBMS will return the SQLSTATE error 22002 "data exception-null value, no indicator parameter". Going the other way, there are two ways to assign a null value to an SQL-data target. Within SQL, you can use the <keyword> NULL in an INSERT or an UPDATE statement to indicate that the target should be set to NULL; that is, if your source is NULL, your DBMS will set your target to NULL. Outside of SQL, if your source has an indicator parameter that is set to -1, your DBMS will set your target to NULL (regardless of the value of the source). An indicator parameter with a value less than -1 will cause an error: your DBMS will return the SQLSTATE error 22010 "data exception-invalid indicator parameter value". We’ll talk more about indicator parameters in our chapters on SQL binding styles.

Datetime Assignment

When you assign a datetime to a datetime target, your DBMS checks whether the source is a valid value for the target’s <data type> (or if a valid value can be obtained from the source by rounding). If so, then the target is set to that value. If neither of these are true, the assignment will fail: your DBMS will return the SQLSTATE error 22008 "data exception-datetime field overflow".

DATE assignment is straightforward, since all dates have the same form.

[Obscure Rule] TIME, TIME WITH TIME ZONE``, TIMESTAMPv and ``TIMESTAMP WITH TIME ZONE assignment is somewhat more complicated, due to the possibility that only one of the source and target may include a <time zone interval>. If this is the case, your DBMS will effectively replace the source value with the result obtained by:

CAST (source TO target)

This means that if you’re assigning a datetime without time zone source value to a datetime WITH TIME ZONE target, your DBMS will (a) assume the source is a local time value, (b) subtract the default SQL-session time zone offset from the source to convert to the source’s UTC equivalent and then (c) assign the UTC result, with resulting time zone offset, to the target. If you’re assigning a datetime WITH TIME ZONE source value to a datetime without time zone target, your DBMS will (a) assume the source is a UTC time value, (b) add the source’s time zone offset to the source to convert to the source’s local time equivalent and then (c) assign the local time result, without a time zone offset, to the target.

Interval Assignment

When you assign an interval to an interval target, your DBMS checks whether the source is a valid value for the target’s <data type> (or if a valid value can be obtained from the source by rounding or truncation). If so, then the target is set to that value. If neither of these are true, the assignment will fail: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

[NON-PORTABLE] If your source value is not a valid value for your interval target’s <data type>, then the value assigned to the target is non-standard because the SQL Standard requires implementors to define whether the DBMS will round or will truncate the source to obtain a valid value. [OCELOT Implementation] The OCELOT DBMS that comes with this book truncates the interval source to obtain a valid value for the target.

Assignment of year-month intervals with other year-month intervals, or of day-time intervals with other day-time intervals, is straightforward, providing both target and source have the same <interval qualifier>. That is, for example, if both year-month intervals are INTERVAL YEAR, or both are INTERVAL MONTH, or both are INTERVAL YEAR TO MONTH, assignment is straightforward, since all intervals with the same <interval qualifier> have the same form.

If, however, the <interval qualifier>s of the source and target do not match exactly, then your DBMS will effectively convert both to the same precision before the operation is carried out. The conversion is done either by a simple mathematical process or by extending one of the intervals at its most significant and/or at its least significant end, with an appropriate datetime field set (initially) to zero. Thus, for example:

• If you assign INTERVAL '3' YEAR to an INTERVAL YEAR TO MONTH target, your DBMS will extend the source at its least significant end by attaching a zero MONTH field. The source effectively becomes INTERVAL '3-00' YEAR TO MONTH, and assignment becomes straightforward.

• If you assign INTERVAL '13' MONTH to an INTERVAL YEAR TO MONTH target, your DBMS will extend the source at its most significant end by attaching a zero YEAR field. The source effectively becomes INTERVAL '0-13' YEAR TO MONTH. Since a MONTH field may not be more than 11 months in a year-month interval, the source is further adjusted to INTERVAL '1-01' YEAR TO MONTH (1 year and 1 month equals 13 months), and assignment becomes straightforward.

• If you assign INTERVAL '3' YEAR to an INTERVAL MONTH target, your DBMS converts the source to an INTERVAL MONTH value by multiplying the year value by 12. The source effectively becomes INTERVAL '36' MONTH, and assignment becomes straightforward.

• If you assign INTERVAL '3-01' YEAR TO MONTH to an INTERVAL MONTH target, your DBMS converts the source to an INTERVAL MONTH value by multiplying the year value by 12, and adding the number of months to the result. The source effectively becomes INTERVAL '37' MONTH, and assignment becomes straightforward.

• If you assign INTERVAL '24' MONTH to an INTERVAL YEAR target, your DBMS converts the source to an INTERVAL YEAR value by dividing the month value by 12. The source effectively becomes INTERVAL '2' YEAR, and assignment becomes straightforward. If, however, the source’s month value is not evenly divisible by 12 (e.g.: a source of INTERVAL '37' MONTH being assigned to an INTERVAL YEAR target), the assignment will fail so that no information is lost: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

• If you assign INTERVAL '2-00' YEAR TO MONTH to an INTERVAL YEAR target, your DBMS converts the source to an INTERVAL YEAR value by assigning the source’s year value to the target, that is, the source effectively becomes INTERVAL '2' YEAR, and assignment becomes straightforward. If, however, the source’s month value is not equal to zero (e.g.: a source of INTERVAL '2-05' YEAR TO MONTH being assigned to an INTERVAL YEAR target), the assignment will fail so that no information is lost: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

• The same considerations apply for assignments of day-time intervals that don’t have the same <interval qualifier>.

Comparison

SQL provides the usual scalar comparison operators – = and <> and < and <= and > and >= – to perform operations on temporal values. All of them will be familiar; there are equivalent operators in other computer languages. If any of the comparands are NULL, the result of the operation is UNKNOWN. For example:

DATE '1997-07-15' = DATE '1997-08-01'

returns FALSE.

'DATE '1997-07-15' = (result is NULL}

returns UNKNOWN.

SQL also provides three quantifiers – ALL, SOME, ANY – which you can use along with a comparison operator to compare a value with the collection of values returned by a <table subquery>. Place the quantifier after the comparison operator, immediately before the <table subquery>. For example:

SELECT date_column
FROM   Table_1
WHERE  date_column < ALL (
   SELECT date_column
   FROM   Table_2);

ALL returns TRUE either (a) if the collection is an empty set (i.e.: if it contains zero rows) or (b) if the comparison operator returns TRUE for every value in the collection. ALL returns FALSE if the comparison operator returns FALSE for at least one value in the collection.

SOME and ANY are synonyms. They return TRUE if the comparison operator returns TRUE for at least one value in the collection. They return FALSE either (a) if the collection is an empty set or (b) if the comparison operator returns FALSE for every value in the collection. (The search condition = ANY (collection) is equivalent to IN (collection).)

Temporal values must be compatible to be compared with one another – that is, the source and the target must either (a) both be dates, (b) both be times (with or without time zone), (c) both be timestamps (with or without time zone), (d) both be year-month intervals or (e) both be day-time intervals. The results of temporal comparisons are governed by the familiar rules dor dates and times, i.e., those of the Gregorian calendar and the 24-hour-clock.

Datetime Comparison

[Obscure Rule] When you compare two datetime values, the result is determined according to the interval obtained when your comparands are subtracted from one another. If you’re comparing times or timestamps with different <time zone interval>s, your DBMS will ignore the value of the time zone offset for the comparison.

Interval Comparison

[Obscure Rule] When you compare two interval values, your DBMS will effectively convert both comparands to the same precision before the operation is carried out. The conversion is done either by a simple mathematical process or by extending one (or both) of the comparands at the most significant and/or at the least significant end, with an appropriate datetime field set (initially) to zero, just as is done with interval assignments. For example, for this comparison:

INTERVAL '2-05' YEAR TO MONTH = INTERVAL '3' YEAR

both comparands are first converted to INTERVAL MONTH, making the actual comparison:

INTERVAL '29' MONTH = INTERVAL '36' MONTH

The result, of course, is FALSE.

Other Operations

With SQL, you have several other operations that you can perform on temporal values to get a temporal result.

Arithmetic

SQL provides the usual scalar arithmetic operators – + and - and * and / – to perform operations on temporal values. All of them will be familiar; there are equivalent operators in other computer languages. Arithmetic operations on temporal values are governed by the natural rules for dates and times and yield valid datetimes or intervals according to the Gregorian calendar. If any of the operands are NULL, the result of the operation is also NULL.

SQL doesn’t allow you to do arithmetic on every possible combination of datetime and interval operands. Here are the valid possibilities, and the <data type> of the result:

Date + Interval and Interval + Date both yield Date

Date - Interval yields Date

Date - Date yields Interval

Time + Interval and Interval + Time both yield Time

Time - Interval yields Time

Timestamp + Interval and Interval + Timestamp both yield Timestamp

Timestamp - Interval yields Timestam

year-month Interval + year-month Interval yields year-month Interval

day-time Interval + day-time Interval yields day-time Interval

year-month Interval - year-month Interval yields year-month Interval

day-time Interval - day-time Interval yields day-time Interval

Time - Time yields Interval

Timestamp - Timestamp yields Interval

Interval * Number and Number * Interval both yield Interval

Interval / Number yields Interval

In each of these cases, the operands can be any argument that evaluates to the specified <data type>.

The rules for temporal arithmetic can be explained with this analogy. When you subtract the INTEGER value 123456 from 123557, you get another INTEGER value: -101. So, when you subtract TIME '12:34:56' from TIME '12:35:57', should you get the TIME value: '-00:01:01'? Well, no – there’s no such thing as a negative time-of-day so SQL’s TIME <data type> can’t hold this value.

Regardless, some people are of the opinion that it looks right to represent the result as <negative> zero hours : zero minutes : 1 second. After all, the result is still a time, although it is reasonable to distinguish “time as an elapsed duration” from “time as a moment in the time scale”.

Other people don’t believe that the “negative time value” looks correct. They feel that (time minus time) should result in an INTEGER – the number of elapsed seconds, 61. While there are still several DBMSs which follow this line, they aren’t SQL DBMSs – the SQL Standard states that operations like (datetime minus datetime) results in an INTERVAL, which can be signed.

Our analogy would make us expect “date intervals” along these lines:

 1994-03-02            1994-01-31
-1994-01-31           +0000-01-02
 ----------            ----------
 0000-01-02            1994-03-02

but SQL considers these calculations to be illegal because year-month intervals are not compatible with day-time intervals. That is, in SQL temporal arithmetic, you cannot carry from the days field to the months field, nor borrow from the months field to the days field. There is a way to get around what we call “The Day-Month Arithmetic Barrier” – but first we’ll look at the interval combinations that are encouraged by the Standard.

As stated earlier, the year-month intervals are compatible with each other, so this is legal:

INTERVAL '0000' YEAR + INTERVAL '00' MONTH

The result is INTERVAL '0000-00' YEAR TO MONTH.

The day-time intervals are also compatible with each other, so this is legal:

INTERVAL '00:00' HOUR TO MINUTE +
INTERVAL '00:00' MINUTE TO SECOND

The result is INTERVAL '00:00:00' HOUR TO SECOND.

Since year-month intervals and day-time intervals are no compatible, this is illegal:

INTERVAL '00' MONTH + INTERVAL '01' DAY

(From this it is apparent that the Standard’s words "INTERVAL <data type>" are misleading. For all practical purposes we really have two <data types> that are not compatible with one other.)

The 1998 movie Titanic was billed as a “2 hour 74 minute” movie. This is legitimate if there is no law that says “when number of minutes is greater than or equal to 60, carry into the hours column”. Similarly, SQL allows <interval literal>s like:

INTERVAL '02:74' HOUR TO MINUTE

because, according to the SQL Standart, interval fields must follow “the natural rules for intervals” – and these rules are (a) there are no more than 60 seconds in a minute, (b) there are no more than 60 minutes in an hour, (c) there are no more than 24 hours in a day, and (d) there are no more than 12 months in a year. This is not to say, though, that the result of temporal arithmetic operations should look odd – as with assignment and comparison, your DBMS will normalize the result to maintain the integrity of its datetime <data type>. For year-month intervals, it carries: (if month>=12 carry to year). For the day-time intervals, it also carries: (if second>=60 carry to minute), (if minute>=60 carry to hour), (if hour>=24 carry to day). Because the result is normalized, this expression:

INTERVAL '02:74' HOUR TO MINUTE + INTERVAL '00:00' HOUR TO MINUTE

yields:

INTERVAL '03:14' HOUR TO MINUTE

Here, then, is the syntax allowed for temporal expressions:

datetime expression ::=
datetime value [ AT {LOCAL | TIME ZONE <time zone interval} ] |
interval expression + datetime value [ AT {LOCAL | TIME ZONE <time zone interval} ] |
datetime value [ AT {LOCAL | TIME ZONE <time zone interval} ] + interval term |
datetime value [ AT {LOCAL | TIME ZONE <time zone interval} ] - interval term

interval expression ::=
interval term |
interval expression + interval term |
interval expression - interval term |
(datetime expression - datetime value [ AT {LOCAL | TIME ZONE <time zone interval} ]) <interval qualifier>

   interval term ::=
   [ + | - ] interval value |
   [ + | - ] interval value * number |
   [ + | - ] interval value / number |
   number * [ + | - ] interval value

Datetime expressions may only contain values of the same type. A datetime expression involving dates evaluates to a date. A datetime expression involving times evaluates to a time. A datetime expression involving timestamps evaluates to a timestamp. The optional AT LOCAL or AT TIME ZONE clause is valid only for datetime values that evaluate to times or to timestamps. The first case – e.g.: TIME '10:15:00' AT LOCAL – means you want the time value to be adjusted to the current default time zone offset for the SQL-session; this is the default situation. The second case, – e.g.: TIMESTAMP '1994-07-15 14:00:00' AT TIME ZONE INTERVAL '-04:00' HOUR TO MINUTE – means you want the timestamp value to be adjusted to the time zone offset you’ve specified. The result <data type> is TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, as applicable. If <time zone interval> is NULL, the result of the operation is also NULL.

Interval expressions may only contain values of the same type. An interval expression involving year-month intervals evaluates to a year-month interval. An interval expression involving day-time intervals evaluates to a day-time interval.

All temporal arithmetic depends on the concept of the interval: a span of time expressed in calendar or clock units (as appropriate). Intervals may only be used with datetime and/or interval expressions that involve at least one compatible datetime field. For example, this is a legal expression:

start_date + INTERVAL '2' MONTH

because a date and the specified interval have the MONTH field in common. This is not a legal expression:

start_date + (INTERVAL '2' MONTH + INTERVAL '1' DAY)

because the interval expression inside the parentheses would have to be evaluated first, and the two intervals have no datetime fields in common.

These rules apply for date arithmetic:

1. If one operand evaluates to a date, the other operand must evaluate to a date, an INTERVAL YEAR, an INTERVAL MONTH, an INTERVAL YEAR TO MONTH or an INTERVAL DAY.

2. You can’t add two dates. You can only add a date and an interval.

3. You can subtract a date from a date and you can subtract an interval from a date. You can’t subtract a date from an interval.

4. Date expressions are evaluated according to the rules for valid Gregorian calendar dates. If the result is an invalid date, the expression will fail; your DBMS will return the SQLSTATE error 22008 "data exception-datetime field overflow".

5. Remember that if your interval operand is a year-month interval, there is no carry from the date operand’s DAY field. Thus while this expression:

   DATE '1997-07-31' + INTERVAL '1' MONTH
   

   returns`` DATE ‘1997-08-31’`` as expected, the result of this expression:

   DATE '1997-10-31' + INTERVAL '1' MONTH
   

   is an error. There is no DAY field carry, so the result evaluates to DATE '1997-11-31' – an invalid date.

These rules apply for time arithmetic:

1. If one operand evaluates to a time, the other operand must evaluate to a time, an INTERVAL DAY, an INTERVAL HOUR, an INTERVAL MINUTE, an INTERVAL SECOND, an INTERVAL DAY TO HOUR, an INTERVAL DAY TO MINUTE, an INTERVAL DAY TO SECOND, an INTERVAL HOUR TO MINUTE, an INTERVAL HOUR TO SECOND or an INTERVAL MINUTE TO SECOND.

2. You can’t add two times. You can only add a time and an interval.

3. You can subtract a time from a time and you can subtract an interval from a time. You can’t subtract a time from an interval.

4. Time expressions are evaluated modulo 24 – that is:

   TIME '19:00:00' + INTERVAL '9' HOUR
   

   returns TIME '04:00:00'. If the result is an invalid time, the expression will fail: your DBMS will return the SQLSTATE error 22008 "data exception-datetime field overflow".

5. The result of an operation between operands containing a SECONDs value has a fractional seconds precision that is the greater of the operands’ fractional seconds precisions.

6. [Obscure Rule] Arithmetic operations involving a time and an interval preserve the time operand’s <time zone interval>. If your operand is a time without time zone, then the current default time zone offset is assumed.

These rules apply for timestamp arithmetic:

1. If one operand evaluates to a timestamp, the other operand must evaluate to a timestamp, an INTERVAL YEAR, an INTERVAL MONTH, an INTERVAL YEAR TO MONTH, an INTERVAL DAY, an INTERVAL HOUR, an INTERVAL MINUTE, an INTERVAL SECOND, an INTERVAL DAY TO HOUR, an INTERVAL DAY TO MINUTE, an INTERVAL DAY TO SECOND, an INTERVAL HOUR TO MINUTE, an INTERVAL HOUR TO SECOND or an INTERVAL MINUTE TO SECOND.

2. You can’t add two timestamps. You can only add a timestamp and an interval.

3. You can subtract a timestamp from a timestamp and you can subtract an interval from a timestamp. You can’t subtract a timestamp from an interval.

4. Timestamp expressions are evaluated according to the rules for valid Gregorian calendar dates. This means that, unlike time expressions, timestamp expressions are not evaluated modulo 24 because HOURs will carry to/from DAYs. Thus, the result of this expression:

   TIMESTAMP '1997-07-15 19:00:00' + INTERVAL '9' HOUR
   

   is TIMESTAMP '1997-07-16 04:00:00'. If the result of a timestamp expression is an invalid timestamp, the expression will fail: your DBMS will return the SQLSTATE error 22008 "data exception-datetime field overflow".

5. The result of an operation between operands containing a SECONDs value has a fractional seconds precision that is the greater of the operands’ fractional seconds precisions.

6. [Obscure Rule] Arithmetic operations involving a timestamp and an interval preserve the timestamp operand’s <time zone interval>. If your operand is a timestamp without time zone, then the current default time zone offset is assumed.

These additional rules apply for INTERVAL arithmetic:

1. If one operand evaluates to a year-month interval, the other operand must evaluate to a year-month interval, a date or a timestamp. If one operand evaluates to a day-time interval, the other operand must evaluate to a day- time interval, a date, a time or a timestamp.

2. You can add two intervals of the same type.

3. You can subtract two intervals of the same type.

4. You can multiply an interval with a number, or a number with an interval.

5. You can divide an interval by a number. You can’t divide a number by an interval.

6. The result of an operation between interval operands containing a SECONDs value has a fractional seconds precision that is the greater of the operands’ fractional seconds precisions.

7. Interval expressions that result in invalid intervals will fail: your DBMS will return the SQLSTATE error 22015 "data exception-interval field overflow".

If you want to restrict your code to Core SQL, don’t add or subtract datetime expressions, don’t add the optional AT LOCAL/AT TIME ZONE clause to any time or timestamp value and don’t use interval expressions at all.

Coming back to the problem of subtracting two dates, we can see that the expression:

DATE '1994-03-02' - DATE '1994-01-31'

is impossible on the face of it, because it would yield a nonexistent year-month-day interval. The converse is also true – the expression:

DATE '1994-01-31' + INTERVAL '0000-01-02' YEAR TO DAY

will return a syntax error. All, however, is not lost. When subtracting these dates, you can force the result with the syntax (datetime expression - datetime value) <interval qualifier>, where the result is determined by the least significant datetime field in <interval qualifier>. For example, if you want to know the difference between the two dates in years, use:

(DATE '1994-03-02' - DATE '1994-01-31') YEAR

which results in INTERVAL '00' YEAR. (The least significant datetime field in the interval is YEAR, and 1994-1994 is zero.) If you want to know the difference between the two dates in months, use:

(DATE '1994-03-02' - DATE '1994-01-31') MONTH

which results in INTERVAL '02' MONTH. (Note that this is not the “intuitive” answer one might expect! The least significant field in the interval is MONTH, and ((1994*12 months)+ 3 months)-((1994*12 months)+ 1 month) is two, so even though we can see that the difference between the dates is not a full two months, the correct SQL result is two.) If you want to know the difference between the two dates in years and months, use:

(DATE '1994-03-02' - DATE '1994-01-31') YEAR TO MONTH

which results in INTERVAL '00-01' YEAR TO MONTH. If you want to know the difference between the two dates in days, use:

(DATE '1994-03-02' - DATE '1994-01-31') DAY

which results in INTERVAL '30' DAY. (The least significant field in the interval is DAY, and (61 days - 31 days) is 30.)

A runaway serf must hide in a town for a year and a day to gain freedom. If he runs away on March 12 1346, when can he party? SQL doesn’t allow this expression:

DATE '1346-03-12' + (INTERVAL '1' YEAR + INTERVAL '1' DAY)

since the two interval types can’t combine. But they each go well with a date, so:

(DATE '1346-03-12' + INTERVAL '1' YEAR) + INTERVAL '1' DAY

yields DATE '1347-03-13'. (The parentheses here are optional, because calculation is left-to-right.)

Errors

The three common arithmetic exception conditions are as follows.

SQLSTATE 22007	– data exception - invalid datetime format e.g.: returned for this result: DATE '1994-02-30'
SQLSTATE 22008	– data exception - datetime field overflow e.g.: returned for this expression: DATE '9999-01-01' + INTERVAL '1-00' YEAR TO MONTH
SQLSTATE 22015	– data exception - interval field overflow e.g.: returned for this result: INTERVAL '999-11' YEAR TO MONTH (too many digits in leading field)
SQLSTATE 22009	– data exception - invalid time zone displacement valuen e.g.: returned for this result: TIME '02:00:00+14:00'

Scalar Operations

SQL provides nine scalar functions that return a temporal value: the <case expression>, the <cast specification>, the current date value function, the current time value function, the current timestamp value function, the current local time value function and the current local timestamp value function (we’ll call these last five the niladic datetime functions), the <extract expression> and the <interval absolute value function>. We’ll discuss all but the <case expression> and the <cast specification> in Chapter 29 “Simple Search Conditions”. For now, just remember that CASE can evaluate to a temporal value and can therefore be used anywhere in SQL that a temporal value could be used.

Niladic Datetime Functions

The required syntax for a niladic datetime function is:

niladic datetime function ::=
CURRENT_DATE |
CURRENT_TIME [ (fractional seconds precision) ] |
CURRENT_TIMESTAMP [ (fractional seconds precision) ] |
LOCALTIME [ (fractional seconds precision) |
LOCALTIMESTAMP [ (fractional seconds precision) ]

CURRENT_DATE is a niladic datetime function with a result <data type> of DATE. It returns “today”: that is, the current date. Here is an example of CURRENT_DATE:

. . . WHERE date_column = CURRENT_DATE

CURRENT_TIME is a niladic datetime function with a result <data type> of TIME WITH TIME ZONE. It returns “now”: that is, the current time, with a time zone offset equal to the SQL-session default time zone offset. The default time zone offset is the <time zone interval> specified in the most recent SET TIME ZONE statement issued during the SQL-session. If you haven’t issued a SET TIME ZONE statement, the default time zone offset is your DBMS’s initial default time zone offset.

NON-PORTABLE] The default time zone offset is non-standard because the SQL Standard requires implementors to define the initial default time zone offset for an SQL-session. [OCELOT Implementation] The OCELOT DBMS that comes with this book sets the SQL-session’s initial default time zone offset to INTERVAL +'00:00' HOUR TO MINUTE – this represents UTC.

Here is an example of CURRENT_TIME:

. . . WHERE time_column <> CURRENT_TIME

As with the TIME WITH TIME ZONE <data type>, the optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECONDs field of CURRENT_TIME’s result.

CURRENT_TIMESTAMP is a niladic datetime function with a result <data type> of TIMESTAMP WITH TIME ZONE. It returns “now”: that is, the current time “today”, with a time zone offset equal to the SQL-session default time zone offset. As with the TIMESTAMP WITH TIME ZONE <data type>, the optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECONDs field of CURRENT_TIMESTAMP’s result. Here is an example of CURRENT_TIMESTAMP:

. . . WHERE timestamp_column > CURRENT_TIMESTAMP

LOCALTIME is a niladic datetime function with a result <data type> of TIME. It returns “now-here”: that is, the current local time, with no time zone offset. As with the TIME <data type>, the optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECONDs field of LOCALTIME’s result. The result of LOCALTIME is obtained by casting CURRENT_TIME’s result – that is:

LOCALTIME = CAST (CURRENT_TIME AS TIME)

or, if fractional seconds precision is specified:

LOCALTIME(precision) = CAST (CURRENT_TIME(precision) AS TIME(precision))

Here is an example of LOCALTIME:

. . . WHERE time_column < LOCALTIME

LOCALTIMESTAMP is a niladic datetime function with a result <data type> of TIMESTAMP. It returns “now-here”: that is, the current local time “today”, with no time zone offset. As with the TIMESTAMP <data type>, the optional fractional seconds precision, if specified, is an unsigned integer that specifies the number of digits following the decimal point in the SECONDs field of LOCALTIMESTAMP’s result. The result of LOCALTIMESTAMP is obtained by casting CURRENT_TIMESTAMP’s result – that is:

LOCALTIMESTAMP = CAST (CURRENT_TIMESTAMP AS TIMESTAMP)

or, if fractional seconds precision is specified:

LOCALTIMESTAMP(precision) = CAST (CURRENT_TIMESTAMP(precision) AS TIMESTAMP(precision))

Here is an example of LOCALTIMESTAMP:

. . . WHERE timestamp_column >= LOCALTIMESTAMP

All niladic datetime functions in a SQL statement are effectively evaluated at the same time; that is, all references to CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME or LOCALTIMESTAMP in a single SQL statement will return their respective values based on a single clock reading. CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP will therefore always return the same date, and CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME and LOCALTIMESTAMP will always return the same effective time, when used within the same SQL statement.

[NON-PORTABLE] The timing of the clock reading for the evaluation of these functions is non-standard because the SQL Standard requires implementors to define when the clock is read. The choices are to read the clock at the beginning of a transaction, at the end of a transaction or somewhere in- between. [OCELOT Implementation] The OCELOT DBMS that comes with this book reads the clock immediately prior to performing any operations based on a niladic datetime function.

If you want to restrict your code to Core SQL, don’t use CURRENT_TIME or CURRENT_TIMESTAMP, don’t specify a fractional seconds precision for LOCALTIME and don’t specify a fractional seconds precision for LOCALTIMESTAMP other than zero or 6.

Note

The CURRENT_TIME and ``CURRENT_TIMESTAMP functions differ in SQL-92 and SQL3. In SQL -92, CURRENT_TIME and CURRENT_TIMESTAMP return the current local time and their <data types>s are TIME and TIMESTAMP (without time zone). In SQL3, the functions that return the current local time are LOCALTIME and LOCALTIMESTAMP, CURRENT_TIME and CURRENT_TIMESTAMP now return values with a <data type> of TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE (that is, with a time zone offset) and are not part of Core SQL.

<extract expression>

The required syntax for an <extract expression> is as follows.

<extract expression> ::=
EXTRACT(datetime_field FROM temporal_argument)

EXTRACT operates on an argument that evaluates to a date, a time, a timestamp or an interval. It extracts the numeric value of datetime_field from temporal_argument and returns it as a exact numeric value. If the argument is NULL, EXTRACT returns NULL.

The datetime_field may be any one of: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR or TIMEZONE_MINUTE. If datetime_field is TIMEZONE_HOUR or TIMEZONE_MINUTE, temporal_argument must evaluate to a TIME WITH TIME ZONE`` value or a TIMESTAMP WITH TIME ZONE value.

For any datetime_field other than SECOND, EXTRACT returns an integer. For a datetime_field of SECOND, EXTRACT returns a decimal number. For example:

EXTRACT (MINUTE FROM INTERVAL '-05:01:22.01' HOUR TO SECOND)

returns the integer -1 (when “temporal_argument” is a negative interval, the result will be a negative number).

EXTRACT (SECOND FROM INTERVAL '-05:01:22.01' HOUR TO SECOND)

returns the decimal number -22.01.

[NON-PORTABLE] The precision of EXTRACT’s result is non-standard because the SQL Standard requires implementors to define the result’s precision and (if applicable) the result’s scale. (The scale defined must be at least large enough to accept the full size of the argument’s fractional seconds precision.) [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of EXTRACT an INTEGER <data type> for all datetime_fields other than SECOND. It gives the result of EXTRACT a DECIMAL(8,2) <data type> for a datetime_field of SECOND.

Here is a SQL statement which extracts the YEAR field from a timestamp:

SELECT EXTRACT(YEAR FROM occurrence_timestamp)
FROM   Timestamp_Examples;

The result is the integer 2001.

If you want to restrict your code to Core SQL, don’t use EXTRACT.

<interval absolute value function>

The required syntax for an <interval absolute value function> is as follows.

<interval absolute value function> ::=
ABS (interval_argument)

ABS operates on an argument that evaluates to an interval. It strips a negative sign (if it’s present) from the argument and returns a non-negative interval whose <data type> is the same as the argument’s <data type>, e.g.: ABS (INTERVAL '-05' YEAR) returns INTERVAL '5' YEAR, ABS (INTERVAL'05' YEAR) returns ``INTERVAL '05' YEAR, and ``ABS (INTERVAL '00' YEAR) returns ``INTERVAL '00' YEAR. If the argument is NULL, ABS returns NULL.

[Obscure Rule] ABS can also operate on a number. We’ve ignored this option for now – look for it in our chapter on numbers.

If you want to restrict your code to Core SQL, don’t use ABS with an interval argument.

Set functions

SQL provides five set functions that operate on datetime values: COUNT, MAX, MIN and GROUPING. SQL also provides seven set functions that operate on intervals: COUNT, MAX, MIN, SUM, AVG and GROUPING. Since none of these operate exclusively with temporal argument, we won’t discuss them here; look for them in our chapter on set functions.

Predicates

In addition to the comparison operators, SQL provides nine other predicates that operate on temporal values: the <overlaps predicate>, the <between predicate>, the <in predicate>, the <null predicate>, the <exists predicate>, the <unique predicate>, the <match predicate>, the <quantified predicate> and the <distinct predicate>. Each will return a boolean value: either TRUE, FALSE or UNKNOWN. Only the first predicate operates strictly on temporal values; we’ll discuss it here. Look for the rest in our chapter on search conditions.

<overlaps predicate>

The required syntax for an <overlaps predicate> is:

<overlaps predicate> ::=
(datetime_argument_1, temporal_argument_1)
OVERLAPS
(datetime_argument_2, temporal_argument_2)

OVERLAPS is a predicate that operates on two operands that evaluate to a period of time. It compares either a pair of datetimes, or a datetime and an interval, to test whether the two periods overlap in time. It returns TRUE if they do, FALSE if they don’t and UNKNOWN if the result can’t be determined because of NULL arguments.

Each OVERLAPS operand is a parenthesized pair of temporal arguments separated by a comma. (This is a special case of a <row value expression>). The first argument in each operand must evaluate either to a date, a time or a timestamp. The second argument in each operand must either (a) evaluate to the same datetime <data type> as the first argument or (b) evaluate to an interval that contains only the same datetime fields as the first part. Each operand represents a chronological span of time, as either “start to end” or “start and interval”. The possible argument combinations as follows.

• (date,date) OVERLAPS (date,date)

• (date,date) OVERLAPS (date,interval of years or months or days)

• (date,interval of years or months or days) OVERLAPS (date,date)

• (date,interval of years or months or days) OVERLAPS (date,interval of years or months or days)

• (time,time) OVERLAPS (time,time)

• (time,time) OVERLAPS (time,interval of hours or minutes or seconds)

• (time,interval of hours or minutes or seconds) OVERLAPS (time,time)

• (time,interval of hours or minutes or seconds) OVERLAPS (time,interval of hours or minutes or seconds)

• (timestamp,timestamp) OVERLAPS (timestamp,timestamp)

• (timestamp,timestamp) OVERLAPS (timestamp,interval of years or months or days or hours or minutes or seconds)

• (timestamp,interval of years or months or days or hours or minutes or seconds) OVERLAPS (timestamp,timestamp)

• (timestamp,interval of years or months or days or hours or minutes or seconds) OVERLAPS (timestamp,interval of years or months or days or hours or minutes or seconds)

Here is an example of a search condition using OVERLAPS:

(DATE '1994-01-01',DATE '1994-05-01') OVERLAPS
(DATE '1993-07-01',DATE '1994-03-01')

The example is asking whether the two temporal periods overlap as in this diagram:

                   January 1 1994                      May 1 1994
                   **********************************************
                   ^               ^
July 1 1993                        March 1 1994
***********************************************

The diagram shows us that there is an overlap: the search condition result is TRUE. In this example, both OVERLAPS operands are “start to end” argument pairs: they’re both of the same <data type>. Here is an equivalent example, using “start and interval” argument pairs instead:

(DATE '1994-01-01',INTERVAL '05' MONTH) OVERLAPS
(DATE '1993-07-01',INTERVAL '08' MONTH)

(The INTERVAL argument must be compatible with the datetime <data type>, so that the operation “datetime + interval” will be possible. This is how OVERLAPS determines the “end” argument.)

OVERLAPS is really a comparison operation, whose result is determined by this equivalent search condition (the OVERLAPS datetime_argument_1 is first_start, temporal_argument_1 is first_end, datetime_argument_2 is second_start and temporal_argument_2 is second_end):

(first_start>second_start AND
   (first_start<second_end OR first_end<second_end))
OR
(second_start>first_start AND
   (second_start<first_end OR second_end<first_end))
OR
(first_start=second_start AND
   (first_end<>second_end OR first_end=second_end))

If the second argument of a pair is smaller than the first (i.e.: if the end point is earlier in time than the start point) or if the first argument of a pair is NULL, OVERLAPS switches them around. For example, if the search condition contains:

(DATE '1994-01-01',DATE '1993-05-01') OVERLAPS
(DATE '1993-07-01',DATE '1994-03-01')

the expression your DBMS will actually evaluate is:

(DATE '1993-05-01',DATE '1994-01-01') OVERLAPS
(DATE '1993-07-01',DATE '1994-03-01')

which evaluates to TRUE: the periods overlap. If the search condition contains:

(NULL,DATE '1994-05-01') OVERLAPS
(DATE '1993-07-01',DATE '1994-03-01')

the expression your DBMS will actually evaluate is:

(DATE '1994-05-01',NULL) OVERLAPS
(DATE '1993-07-01',DATE '1994-03-01')

which evaluates to UNKNOWN. However, this search condition evaluates to TRUE, despite the NULL argument:

(DATE '1994-07-01',INTERVAL '06' MONTH') OVERLAPS
(DATE '1994-08-01',NULL)

If you want to restrict your code to Core SQL, don’t use the OVERLAPS predicate.

Dialects

The “typical” SQL DBMS supports date, time and timestamp data types but interval (as a separate data type) is not common yet. The majority of SQL DBMSs can’t handle time zones, can’t handle fractional seconds and can’t handle leap seconds. For example, the Oracle DATE data type is typically a timestamp (i.e.: it includes both a date portion and a time portion, despite the name) with no fractional seconds, formatted as DD-MON-YY (e.g.: 06-JAN-97). Valid dates fall into the range January 1 4712 B.C. to December 31 4712 A.D. Intervals are expressed only as integers, representing number of days. The Oracle SYSDATE function returns the current date and the current time.

ODBC has several datetime functions; most are replaceable with standard SQL. Here are the different names to expect.

Standard	ODBC
CURRENT_DATE	CURDATE
CURRENT_TIME	CURTIME
CURRENT_TIMESTAMP	NOW
EXTRACT(MONTH ...	MONTH
(EXTRACT(MONTH...)/4	QUARTER
EXTRACT(DAY ...	DAYOFMONTH
not supported	DAYOFWEEK
not supported	DAYOFYEAR
EXTRACT(HOUR ...	HOUR
EXTRACT(MINUTE ...	MINUTE
EXTRACT(SECOND ...	SECOND

The SQL Library

Before we finish discussing temporal values, it’s time to add something to our SQL library. To be worthy of addition to the SQL library, a routine must (a) be good clean SQL, (b) be callable from C and Delphi, (c) be actually useful in C and Delphi because it does something that those languages can’t and (d) have nothing at all do with “databases” – it should be available for use just like any general function library.

Our addition to the SQL library for this chapter will check dates for SQL validity. Here it is.

/* proleptic test -- test whether the DBMS uses a proleptic calendar
   Pass:   Nothing
   Return: 0 DBMS uses standard SQL with proleptic Gregorian calendar
           1 DBMS uses standard SQL with corrected Gregorian calendar
           2 DBMS has deviant date calculator
           3 DBMS does not understand standard SQL syntax */
int proleptic_test (void *)
{
  int x;
  VALUES(DATE '1999-12-31');
  SQLBindCol
  SQLFetch(&x);
  if (sqlcode==100 no data) return (3);
  if (x==...) return (0);
  if (x==...) return (1);
  return (2); }

/* date_valid_test -- test whether a date is valid
   Pass:   A string containing a date in the format yyyy-mm-dd
   Return: 0 date is valid
           <0     date is not valid
           >0     date is valid but a warning was set */
int date_valid_test (char *szdate)
{
  char      tmp[128];
  strcpy(tmp,"VALUES (DATE '");
  strcat(tmp,szdate);
  strcat(tmp,"');");
  return (SQLExecDirect(-1,tmp)); }