Using numeric operators with datetime values

The following examples show the ESQL that you can code to manipulate datetime values with numeric operators.

About this task

Adding an interval to a datetime value

The simplest operation that you can perform is to add an interval to, or subtract an interval from, a datetime value. For example, you could write the following expressions:

DATE '2000-03-29' + INTERVAL '1' MONTH
TIMESTAMP '1999-12-31 23:59:59' + INTERVAL '1' SECOND

The following example shows how to calculate a retirement date by adding the retirement age to the birth date.

DECLARE retAge CHARACTER '65';
DECLARE birthDate DATE DATE '1953-06-01';

SET OutputRoot.XML.Test.retirementDate = birthDate + CAST(retAge AS INTERVAL YEAR);

The repetition of the word DATE in the above example is intentional. The first occurrence of DATE specifies the data type of the declared variable, birthDate. The second occurrence initializes the same variable with the constant character string that is enclosed in single quotation marks as a DATE.

Adding or subtracting two intervals

You can combine two interval values by using addition or subtraction. The two interval values must be of compatible types. It is not valid to add a year-month interval to a day-second interval as shown in the following example:

INTERVAL '1-06' YEAR TO MONTH + INTERVAL '20' DAY

The interval qualifier of the resultant interval is sufficient to encompass all the fields that are present in the two operand intervals. For example:

INTERVAL '2 01' DAY TO HOUR + INTERVAL '123:59' MINUTE TO SECOND

results in an interval with qualifier DAY TO SECOND, because both day and second fields are present in at least one of the operand values.

Subtracting two datetime values

You can subtract two datetime values to return an interval. You must include an interval qualifier in the expression to indicate what precision the result should be returned in. For example:

(CURRENT_DATE - DATE '1776-07-04') DAY

returns the number of days since the 4th July 1776, whereas:

(CURRENT_TIME - TIME '00:00:00') MINUTE TO SECOND

returns the age of the day in minutes and seconds.

Scaling intervals

You can multiply or divide an interval value by an integer factor:

INTERVAL '2:30' MINUTE TO SECOND / 4