Some Informix DATETIME/INTERVAL Tips

Determine the DATE of the first day of the current week.

(SELECT TODAY - (WEEKDAY(TODAY)) UNITS DAY FROM systables WHERE tabid=1)

Informix always treats Sunday as day 0 of the week. The WEEKDAY function returns the number of the day of the week as a value of 0 - 6 so subtracting the weekday from current day (TODAY) returns the DATE value of Sunday of the current week.

Determining HOURS between two DATETIME values.

It is all about the INTERVAL data type and its rather odd syntax.

SELECT mpr.person_id, mpr.cn_name, 
  ((SUM(out_time - in_time))::INTERVAL HOUR(9) TO HOUR) AS hours
FROM service_time_card stc
  INNER JOIN morrisonpersonr mpr ON (mpr.person_id = stc.technician_id)
WHERE mpr.person_id IN (SELECT person_id FROM branch_membership WHERE branch_code = 'TSC')
  AND in_time > (SELECT TODAY - (WEEKDAY(TODAY)) UNITS DAY FROM systables WHERE tabid=1)  
GROUP BY 1,2

The "(9)" part of the expression INTERVAL HOUR(9) TO HOUR is key - it allocates lots of room for hours, otherwise any value of more than a trivial number of hours will cause the clearly correct by not helpful SQL -1265 error: "Overflow occurred on a datetime or interval operation". As, in my case I had a highest value of 6,483 hours I needed at least HOUR(4) TO HOUR to avoid the overflow error. HOUR(9) is the maximum - an expression of HOUR(10) results in an unhelpful generic SQL -201: "A syntax error has occurred.". On the other hand HOURS(9) is 114,155 years and some change, so... it is doubtful that is going to be a problem in most applications.

Converting An INTERVAL To An Integer

I just want to know the number of hours! Seems so simple; but the INTERVAL data type is a frustrating beast, it has no as units operator and it has no useful casting rules [I mean, seriously, just default to seconds!]. You could descend into SPL land, write something to disect and accumulate the interval to hours or minutes... or you can hack it and convert the INTERVAL to a STRING - then cast the string to an INTEGER.

((SUM(out_time - in_time))::INTERVAL HOUR(9) TO HOUR)::VARCHAR(12)::INT AS hours

Revolting! But it works. Make sure your VARCHAR is long enough to contain your maximum value - which will includes a bit of white-space (just for kicks?). This would be so much cleaner if the engine had an INTERVAL->INTEGER casting rule.