This topic provides some examples of the ESQL that you can code to
manipulate datetime values with numeric operators.
- Adding an interval to a datetime value
- The simplest operation 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
- Adding or subtracting two intervals
- Two interval values can be combined 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 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 of the fields 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
- Two datetime values can be subtracted to return an interval. In order
to do this an interval qualifier must be given 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
- An interval value can be multiplied by or divided by an integer factor:
INTERVAL '2:30' MINUTE TO SECOND / 4