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, all dates are mutually comparable and mutually assignable, all times are mutually comparable and mutually assignable, all timestamps are mutually comparable and mutually assignable, all year-month intervals are mutually comparable and mutually assignable and all day-time intervals are mutually comparable and mutually assignable. 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:

CAST ( AS )
    ::= scalar_expression
    ::=  | 

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 a CAST result of 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 result equals the fixed length of the target, then the source is CAST to that result. If the length of the result is shorter than the fixed length of the target, then the source is CAST to that result, padded on the right with however many spaces is required 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 "data 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 result is less than or equals the maximum length of the target, then the source is CAST to that result. 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 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".
• [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.

CAST (INTERVAL AS temporal)

• 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, TIMESTAMP 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 natural rules for dates and times according to the Gregorian calendar.

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:

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". However, SQL won't allow <interval literal>s like:

INTERVAL '02:74' HOUR TO MINUTE

because, according to the SQL Standard, interval fields must follow "the natural rules for intervals" — and these 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 

   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".

1. 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.
2. [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".

1. 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.
2. [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:

Scalar Operations

SQL provides nine scalar operations 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 five the niladic datetime functions), the <extract expression> and the <interval absolute value function>. We'll discuss all but the first two here. Look for the rest in other chapters; for now, just remember that they 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.

<extract expression>

The required syntax for an <extract expression> is:

 ::=
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 an 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:

 ::=
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 four set functions that operate on datetime values: COUNT, MAX, MIN and GROUPING. SQL also provides six 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 Chapter 33 "Searching with Groups".

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 Chapter 29 "Simple Search Conditions".

<overlaps predicate>

The required syntax for an <overlaps predicate> is:

 ::=
(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 argument. Each pair of arguments represents a period, as either "start to end" or "start and interval". The possible argument combinations are thus:

• (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_startfirst_start AND 
  (second_startsecond_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.