Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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:

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
Mimer SQL User's Manual TOC PREV NEXT INDEX