Guru: The Deception of Fractional Labeled Durations

August 9, 2021 Ted Holt

Hey, Ted:

We measure certain processes in tenths of an hour. For whatever reason, we cannot make date/time arithmetic work properly when dealing with this data. There must be something that we don’t understand — what we’re doing seems simple and straightforward. Can you help?

— One Confused Reader

What this reader wants to do makes perfect sense. He wants to take a value like 8:00 AM, add a fractional number of hours to it, let’s say 1.1, and come up with 9:06 AM. Let’s try an example.

declare global temporary table StampData
 ( Stamp   timestamp );
    
insert into session.StampData values
  ('2021-02-28-23.15.30.123456');

update session.StampData as s
   set s.Stamp = s.Stamp + 1.1 hours; 

Db2 executes the UPDATE without complaint, that is, there are no error messages and no warnings. What’s the new value of STAMP?

select * from session.StampData;  

Stamp
==========================
2021-03-01 00:15:30.123456

It’s not what we expected, is it? Why isn’t it 2021-03-01-00:21:30.123456?

The answer is that, except for seconds, Db2 ignores the fractional portion of labeled durations. Furthermore, Db2 doesn’t bother to tell you that it has ignored those fractions.

Let’s try something. Let’s add both whole and fractional labeled durations to a timestamp.

select s.Stamp,
       s.Stamp + 1 year + 1 month + 1 day + 
                 1 hour + 1 minute + 1 second + 
                 1 microsecond,
       s.Stamp + 1.5 years + 1.5 months + 1.5 days + 
                 1.5 hours + 1.5 minutes + 1.5 seconds + 
                 1 microsecond
  from session.StampData as s;

Here are the three values, folded one per line for ease of comparison.

2021-02-28 23:15:30.123456	
2022-03-30 00:16:31.123457	
2022-03-30 00:16:31.623457

Except for seconds, adding 1.5 is no different from adding 1.

The solution is to work in smaller units of time. For fractional days, work in hours. For fractional hours, work in minutes or seconds. Adding 1.1 hours to a timestamp means multiplying 1.1 by 60 and adding 66 minutes.

Here’s the same UPDATE.

update session.StampData as s
   set s.Stamp = s.Stamp + 66 minutes;  

or

update session.StampData as s
   set s.Stamp = s.Stamp + 3960 seconds;  

Either way, you get the desired result.

Stamp
==========================
2021-03-01 00:21:30.123456

You’ll find you must do the same sort of thing when calculating durations. Use the TIMESTAMPDIFF function to find the elapsed time in a smaller unit, then divide to get the larger unit as a fraction. For example:

declare global temporary table JobData
  ( StartTime timestamp, StopTime timestamp);

insert into session.JobData values
('2021-08-09-08.00.00', '2021-08-09-10.18.00');

How many hours did the machining operation take to run?

select StopTime, StartTime, 
       timestampdiff (8, cast (StopTime - StartTime as char(22)))
          as Elapsed
  from JobData;

The literal 8 tells TIMESTAMPDIFF to calculate elapsed time in minutes. The answer we get is 2, which is wrong.

Instead try either of the following, both of which yield the correct answer — 2.3.

select StopTime, StartTime, 
       (timestampdiff (4, cast (StopTime - StartTime as char(22)))
          / 60.0) as Elapsed
  from JobData;
  
select StopTime, StartTime, 
       dec(timestampdiff (2, cast (StopTime - StartTime as char(22)))
          / 3600.0, 5,4 ) as Elapsed 
  from JobData;

I don’t know why we can’t use fractions in labeled durations. Graeme Birchall, author of the Db2 SQL Cookbook, says that the reason is that fractions don’t make sense when speaking of years and months, as the number of days in a year or month varies. Maybe so, but I don’t know why that precludes the use of fractional labeled durations for days, hours, and minutes. It doesn’t preclude the use of fractional labeled durations for seconds. But no one asked my opinion.

RELATED STORIES

Db2 SQL Cookbook

TIMESTAMPDIFF