www.mimer.com/developer

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;

Result:

GUEST_LNAME	DAYS
DATE	1
JOHANSEN	2
HANSEN	1
ALVE	2
KRISTOFFERSEN	1
HOLMER	4
...	...
ZETTERBERG	3
HANSSON	6

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;

Result:

ROOMNO

SKY101

SKY201

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.

Extracting Values

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';

Result:

MONTH

JULY

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.

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';

Result:

DAY

SUNDAY

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se