Find the difference between to Time fields DB2


Whether you have a character field holding time data (e.g. 10:45 PM) or you an actual time field (i.e. 10:45:00) and you want to find out the difference in minutes or hours between the two, you probably already know that you cannot use timestampdiff(), not without some modifications anyway.

In order to successfully extract the time difference, you need to convert your time into a timestamp. If the times we are comparing are in the same day, then you can use the current date. Example:

select timestampdiff(4,char(
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 PM' as time) as varchar(10)))
-
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 AM' as time) as varchar(10)))
)) AS MINUTES
from sysibm.sysdummy1

If you already have a date field with either an actual date only value or a character field with a date formatted YYYY-MM-DD, then you can replace “current date” with that date field value. Provided your field holding “time” properly casts into a time value, you can replace the string values (’10:50 PM’, ’10:50 AM’) with your time fields.

If your comparing the min and max of one or more fields, you can simply change the above to:

select timestampdiff(4,char(
max(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
-
min(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
)) AS MINUTES
from tbl

Comments and questions are welcome.🙂

Leave a comment

Filed under Sharing Stuff

Comments are closed.