Summing time

[jj_dahlheimer]

So I have a bunch of start and end timestamps, I need to get the difference, sum them up, and display them. I got close with.

time('00:00:00') + timestampdiff(2,char(pcend-pcstart)) seconds

But when you go over 24 hours it resets back to zero. So I just need a way to display it as either hhh:mm or dd:hh:mm format.

[tomliotta]

A problem for developers is that the provided DATE and TIME formats will verify values to ensure that they are valid. (That is, %bifs and declared variables that use the formats will do the validations.) E.g., a TIME format cannot hold values such as "44:31:12" (44 and a half hours) or "321:22:44" (more than 321 hours) since those "times" don't exist. If you tried to put those into a database TIME column, an error would be returned. Such validation is a fundamental reason they even exist for us to use.

Further, there are no provided formats for values that might be described as "DATE_DURATION" or "TIME_DURATION". So, developers must use their own custom code. There is no standard format for durations. Some might want "hhh:mm" and others want "dd:hh:mm", etc., and a single developer will likely want different formats for different purposes. Any combination is possible, and there are no real rules for validation unless you write them yourself for each specific case.

However, you can create one or more procedures and use them over and over in the future. You'll possibly want to pass in a total number of either minutes or seconds and calculate resulting minutes/hours/days by factoring out 60 secs/min, 3600 secs/hour, 60 mins/hour, 86400 secs/day, etc., to reach whatever final numbers of secs/mins/hours/days that you want to show, and then string values together for the format to return to a caller. Perhaps create two, three or more sub-procs in a *SRVPGM to accept different inputs or return different results.

So, you'd take each "duration" and add to a total_seconds or total_minutes variable. After all have been added in, pass it into the code that breaks the total into the units that you want (secs, mins, hrs, etc.) and create your output string. Not sure exactly how you'll do that since your two example formats have different breakdowns.

[jj_dahlheimer]

I think I got it, just needed the weekend to really think about it. Friday brain was not working. Not sure I really like it but I think it works. Gives me an hours:minutes which should do the trick

select trim(char(totaltime/3600)) ||':'||             
digits(cast(mod(totaltime,3600)/60 as decimal(2)))    
from (values (86399),(86400),(86401),(140000),(172800)
) as x(totaltime)

[Scott Klement]

As Tom points out, the TIME data type is meant to show a time of day not a duration. So it could show 14:00 for "14 O'Clock", but not 26 (there is no 26 O'Clock).

My solution to this was to always calculate the duration as a number of seconds. Then write a routine (a UDF, for example) that formats it for a human. It's, obviously, very simple math for a program to convert from a number of seconds to a number of days, hours minutes and seconds... so write a UDF that takes seconds as input and outputs a string showing the days, hours minutes and seconds. I always use RPG for this, but I don't see any reason why SQL wouldn't work.