7.27.1 Specification format and rules for datetime operations : Hitachi Advanced Database SQL Reference

(1) Specification format

datetime-operation ::= {value-expression-primary
     |datetime-value-expression + labeled-duration [{*|/}value-expression-primary]
     |datetime-value-expression - labeled-duration [{*|/}value-expression-primary]}

To Page Top

(2) Explanation of specification format

value-expression-primary:: For details about value-expression-primary, see 7.20.1 Specification format and rules for value expressions.
datetime-value-expression:: For details about datetime-value-expression, see 7.20.1 Specification format and rules for value expressions.
labeled-duration:: For details about labeled durations, see 7.28 Labeled duration.

To Page Top

(3) Data types on which datetime operations can be performed

Datetime operations can be performed on DATE, TIME, and TIMESTAMP type data.

Datetime operations can also be performed on character string literals (CHAR or VARCHAR) that follow the format of the predefined input representations of dates, times, or time stamps. If a character string literal is specified, the datetime operation is performed after converting the character string literal to datetime data.

For details about the predefined input representations of dates, times, and time stamps, see 6.3.3 Predefined character-string representations.

To Page Top

(4) Rules

(a) Common rules

When a DATE type operation is performed, the data type of the result of the operation will also be DATE type.
When a TIME type operation is performed, the data type of the result of the operation will also be TIME type.
When a TIMESTAMP type operation is performed, the data type of the result of the operation will also be TIMESTAMP type.
Datetime operations can use a maximum of 500 operators (+ or -). If an operand is a value expression with a column from a viewed table, derived table, or query name, the total number of value expressions after expanding the value expression it is based on cannot exceed 10,000.
On the left side of the operator (+ or -), you cannot specify a value expression that consists of only a dynamic parameter.
The results of the operation are not subject to the NOT NULL constraint (null values are allowed).
If an operand has the null value, the result of the operation will also have the null value.
Datetime operations are subject to the rules in 7.20 Value expression in addition to the rules listed above.

(b) Rules for performing datetime operations on DATE type data

The result of the operation must fall in the range from January 01, 0001 to December 31, 9999.
Dates are calculated with the year or month carried over as necessary. The following is an example.

Example 1
```
DATE'2012-12-31'+2 DAY --> DATE'2013-01-02'
```
Example 2
```
DATE'2013-01-01'-1 DAY --> DATE'2012-12-31'
```
If an operation results in a nonexistent date in a particular year or month (such as 31 in a 30-day month, February 30, or February 29 in a non-leap year), it will be changed to the last day of that month. The following is an example.

Example:
```
DATE'2013-03-31'+1 MONTH --> DATE'2013-04-30'
```
When an operation produces a nonexistent date, it is automatically corrected to the last day of that month. As a consequence, if you add some number of months to a certain date, then subtract the same number of months from the resulting date, it does not necessarily return to the original date. The following is an example:

Example:
```
DATE'2013-03-31'+1 MONTH --> DATE'2013-04-30'
DATE'2013-04-30'-1 MONTH --> DATE'2013-03-30'
```

(c) Rules for performing datetime operations on TIME type data

The result of the operation must fall in the range from 00:00:00.000000000000 to 23:59:59.999999999999.
When operations are performed on data with different fractional seconds precisions, the higher precision is used, and the lower-precision data is padded with zeros. For example, if the data in a TIME type operand has a fractional seconds precision of 0 and the labeled duration is MILLISECONDS, the calculation is performed with the fractional seconds precision of the TIME type data extended to 3.

(d) Rules for performing datetime operations on TIMESTAMP type data

The result of the operation must fall in the range from January 01, 0001 00:00:00.000000000000 to December 31, 9999 23:59:59.999999999999.
The methods for calculating the year, month, and date follow the rules in (b) Rules for performing datetime operations on DATE type data.
When operations are performed on data with different fractional seconds precisions, the higher precision is used, and the lower-precision data is padded with zeros. For example, if the data in a TIMESTAMP type operand has a fractional seconds precision of 0 and the labeled duration is MILLISECONDS, the calculation is performed with the fractional seconds precision of the TIMESTAMP type data extended to 3.

Time stamps are calculated with the day carried over as necessary. This is illustrated in the examples below.

Example 1:

TIMESTAMP'2014-02-01 23:59:59'+1 SECOND --> TIMESTAMP'2014-02-02 00:00:00'

Example 2:

TIMESTAMP'2014-02-02 00:00:00'-1 SECOND --> TIMESTAMP'2014-02-01 23:59:59'

Example 3:

TIMESTAMP'2013-12-31 23:05:06'+2 HOUR --> TIMESTAMP'2014-01-01 01:05:06'

(e) Rules for multiplication and division of labeled durations

When multiplying or dividing a labeled duration, the following labeled durations are equivalent:
- value-expression-1 labeled-duration-qualifier * value-expression-2 → (value-expression-1*value-expression-2) labeled-duration-qualifier
- value-expression-1 labeled-duration-qualifier / value-expression-2 → (value-expression-1/value-expression-2) labeled-duration-qualifier
Examples:
```
C1 DAYS * C2 → (C1*C2) DAYS
(C1+C2) MINUTES / (C3+C4) → ((C1+C2)/(C3+C4)) MINUTES
```
The value-expression-primary that multiplies or divides the labeled duration must be an integer (SMALLINT or INTEGER type).
If a dynamic parameter is specified by itself for the value-expression-primary that multiplies or divides the labeled duration, the assumed data type of the dynamic parameter is INTEGER.
If the result of the value-expression-primary that multiplies or divides the labeled duration is the null value, the result of the labeled duration will be the null value.

To Page Top

(5) Example

Example

Searching the employee table (EMPLIST), retrieve the ID (USERID) and name (NAME) of all employees whose date of hire (ENT-DAY) was at least two years ago.

SELECT "USERID","NAME" FROM "EMPLIST"
    WHERE "ENT-DAY" <= CURRENT_DATE -2 YEARS

The underlined portion indicates the datetime operation, in which the labeled duration is 2 YEARS.

To Page Top