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.
How many days have the guests at hotel LAPONIA stayed?SELECT GUEST_LNAME, (COALESCE(CHECKOUT,CURRENT_DATE)-CHECKIN) DAY(2) AS DAYS FROM BOOK_GUEST WHERE HOTELCODE = 'LAP' AND CHECKIN IS NOT NULL;
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 example above. In that example the precision is actually superfluous as the default precision for day is 2.
The above example uses the COALESCE short form of the CASE expression, a complete description of this can be found in the Mimer SQL Reference Manual.
Which hotel rooms have requested a wake up call within the next hour and a half (assuming the time is 08:35:00)?SELECT ROOMNO FROM WAKE_UP WHERE WAKE_DATE = CURRENT_DATE AND WAKE_TIME BETWEEN LOCALTIME AND LOCALTIME + INTERVAL '01:30' HOUR TO MINUTE;
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.
It is possible to extract part of a datetime value with the EXTRACT function. The function returns a numeric value.
Which month did FREDRIK SELLIN stay at any of the hotels?SELECT CASE EXTRACT (MONTH FROM ARRIVE) 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 AS MONTH FROM BOOK_GUEST WHERE GUEST_FNAME = 'FREDRIK' AND GUEST_LNAME = 'SELLIN';
Another useful function is DAYOFWEEK which returns the day number within a week. MONDAY has the value 1 and SUNDAY has the value 7.
Which day did FREDRIK SELLIN arrive at any of the hotels?SELECT CASE DAYOFWEEK(ARRIVE) 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 AS DAY FROM BOOK_GUEST WHERE GUEST_FNAME = 'FREDRIK' AND GUEST_LNAME = 'SELLIN';
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40