Datetime Arithmetic and Functions

---

It is possible to use datetime and interval values in expressions to calculate new datetime and interval values.

Valid operations are:

• addition or subtraction between an interval value and a datetime value
• subtracting a datetime from another datetime value
• adding or subtracting two interval values
• multiplying or dividing an interval by a numerical value

The first of these operations yields a datetime value while the others result in an interval value.

Retrieve the EAN, price and the number of days to the release date for any items with a release date in the future:

 SELECT ean_code, price,
        (release_date - CURRENT_DATE) DAY(3) AS days
    FROM items
    WHERE release_date > CURRENT_DATE;

Returns:

EAN_CODE	PRICE	DAYS
7298976754871	13.98	5
7464376662256	15.98	12
9781990789861	13.99	8
9781993789639	6.99	4

When taking the difference between two datetime values it is necessary to specify the type of the resulting interval.

It is also possible to specify the precision of the interval as shown in the above example; the default precision for day is 2.

Retrieve the EAN, price and the release date for any items with a release date in the next 100 hours:

 SELECT ean_code, price, release_date
    FROM items
    WHERE CAST(release_date AS TIMESTAMP)
          BETWEEN LOCALTIMESTAMP
          AND LOCALTIMESTAMP + INTERVAL '100' HOUR(3);

Returns:

EAN_CODE	PRICE	RELEASE_DATE
9781993789639	6.99	2002-03-15

About Intervals

SQL distinguishes between YEAR-MONTH (long) intervals and DAY-TIME (short) intervals.

YEAR-MONTH intervals are: YEAR, MONTH and YEAR TO MONTH.

DAY-TIME intervals are: DAY, HOUR, MINUTE, SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND, DAY TO HOUR, DAY TO MINUTE and DAY TO SECOND.

Long intervals may be compared to other long intervals, and short intervals may be compared to other short intervals, but short and long intervals are not comparable.

Extracting Values

It is possible to extract part of a datetime value with the EXTRACT function. The function returns a numeric value.

Extract the month and year for any items with a release date in the next 4 days:

 SELECT CASE EXTRACT(MONTH FROM release_date)
           WHEN  1 THEN 'January'
           WHEN  2 THEN 'February'
           WHEN  3 THEN 'March'
           WHEN  4 THEN 'April'
           WHEN  5 THEN 'May'
           WHEN  6 THEN 'June'
           WHEN  7 THEN 'July'
           WHEN  8 THEN 'August'
           WHEN  9 THEN 'September'
           WHEN 10 THEN 'October'
           WHEN 11 THEN 'November'
           WHEN 12 THEN 'December'
        END
        || ' ' ||
        CAST(EXTRACT(YEAR FROM release_date) AS CHAR(4))
    FROM items
    WHERE release_date BETWEEN CURRENT_DATE
                       AND CURRENT_DATE + INTERVAL '4' DAY;

Returns:

March 2002

DAYOFWEEK

Another useful function is DAYOFWEEK which returns the day number within a week. Monday has the value 1 and Sunday has the value 7.

Find the release day for any items with a release date in the next 4 days:

 SELECT CASE DAYOFWEEK(release_date)
           WHEN 1 THEN 'Monday'
           WHEN 2 THEN 'Tuesday'
           WHEN 3 THEN 'Wednesday'
           WHEN 4 THEN 'Thursday'
           WHEN 5 THEN 'Friday'
           WHEN 6 THEN 'Saturday'
           WHEN 7 THEN 'Sunday'
        END
    FROM items
    WHERE release_date BETWEEN CURRENT_DATE
                       AND CURRENT_DATE + INTERVAL '4' DAY;

Result:

Friday

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se