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;
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);
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.
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;
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;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40