The definition for interval_expression can be expressed in three forms:
- interval_term
- interval_expression + interval_term
- (date_time_expression - date_time_term) start TO end
This example uses the second definition.
SELECT (CAST(INTERVAL '125' MONTH AS INTERVAL YEAR(2) TO MONTH)) + INTERVAL '12' YEAR;
The interval_expression is INTERVAL '125' MONTH.
The interval_term is INTERVAL '12' YEAR.
The processing involves the following stages:
- The CAST function converts the interval_expression value of 125 months to 10 years and 5 months.
- The interval_term amount of 12 years is added to the interval_expression amount, giving 22 years and 5 months.
- The result is converted to the appropriate data type, which is INTERVAL YEAR(2) TO MONTH, giving '22-05'.
This example uses the third definition for interval_expression.
You must ensure that the values for date_time_expression and date_time_term are comparable.
SELECT (TIME '23:59:59.99' - CURRENT_TIME(2)) HOUR(2) TO SECOND(2);
The date_time_expression is TIME '23:59:59.99'.
The date_term is the date_time_primary - CURRENT_TIME(2).
The processing involves the following stages:
- Assume that the current system time is 18:35:37.83.
- The HOUR(2) TO SECOND(2) time interval 18:35:37.83 is subtracted from the TIME value 23:59:59.99, giving the result '5:24:22.16'.
Here is another example that uses the third definition for interval_expression to find the difference in minutes between two TIMESTAMP values. First define a table:
CREATE TABLE BillDateTime (start_time TIMESTAMP(0) ,end_time TIMESTAMP(0));
Now, determine the difference in minutes:
SELECT (end_time - start_time) MINUTE(4) FROM BillDateTime;
The processing involves the following stages:
- The start_time TIMESTAMP value is subtracted from the end_time TIMESTAMP value, giving an interval result.
- The MINUTE(4) specifies an interval unit of minutes with a precision of four digits, which allows for a maximum of 9999 minutes, or approximately one week.