Extracting hours, minutes or seconds from the difference of two datetime values

Problem(Abstract)

This article shows several methods of producing values in minutes from the difference of two datetime values. These examples can also be applied to produce hours and seconds.

Resolving the problem

Q. How can I determine the number of hours, minutes or seconds difference between two datetime values?


A. This can be accomplished with SQL using the following methods:

The following SQL script uses a temp table to calculate the number of minutes difference between col1 and col2. This method works with any version of IBM® Informix® Dynamic Server (IDS):

create temp table tmp_dt_tab 
( 
  col1 datetime year to second, 
  col2 datetime year to second, 
  col3 interval minute(9) to minute 
) with no log;

insert into tmp_dt_tab values (CURRENT YEAR TO SECOND, "2001-09-11 12:00:00", NULL);

update tmp_dt_tab set col3 = (col1 - col2);

The following SQL script uses an interval to implicitly cast into minutes the difference between the current datetime and noon on 9/11/2001. This method works with any version of IDS:

select interval(0) minute(9) to minute + 
       (current year to minute - "2001-09-11 12:00") 
  from systables 
 where tabid = 99;

The following SQL script explicitly casts into minutes the difference between the current datetime and noon on 9/11/2001. This method requires IDS version 9.x or 10.x:

select (current year to minute - 
        "2001-09-11 12:00")::interval minute(9) to minute 
  from systables 
 where tabid = 99;

The following SQL script explicitly casts into minutes the difference between the current datetime and noon on 9/11/2001. This method requires IDS version 9.x or 10.x:

select cast ((current year to minute - "2001-09-11 12:00") as 
              interval minute(9) to minute) 
  from systables 
 where tabid = 99;

http://www-01.ibm.com/support/docview.wss?uid=swg21216971