Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:

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 only be compared to other long intervals, and short intervals may only be compared to other short intervals, i.e. 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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX