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