working with Duration.....

[gbshahaq]

I have an import source that gives me lots of fields containing duration in format hh:mm:ss

This is a comma delimited txt file with headings and imports just fine, but I (thought) that i had to import the duration fields as text types....

Now, what can i do with that? I need to calculate (sum, average) on these fields, but can't. It seems a bit daunting (and stupid) to try and split these into separate hour, min and sec fields and use TimeSerial to put them back together. Do i have other choices?

It is not too late to import as a different format but here is a couple of lines of the source file:

Login ID,Date,ACD Calls,Avg ACD Time,Avg ACW Time,Extn In Calls,Avg Extn In Time,Extn Out Calls,Avg Extn Out Time,ACD Time,ACW Time,Agent Ring Time,Other Time,AUX Time,Avail Time,Staffed Time,PC Agent Occupancy w ACW,PC Agent Occupancy wo ACW
OrderCalls,01/01/2005,0,0,0,0,0,0,0,:00:00,:00:00,:00:00,:00:00,24:00:00,:00:00,24:00:00,0,0

as you can see, leading zeroes are dropped.....

please help!

 

[The_Doc_Man]

Your problem is that durations are not what you think they are when you use a date field. Either on input OR as an intermediary computation.

Date fields are actually DAY fields with fractions of days. A given date/time field is really the days and fractions of a day since the reference date for the system. The result is stored as a DOUBLE (real, floating-point, scientific number; whatever term you like). The difference between two such fields is the days and fractions between the referenced dates/times. In units of DAYS and fractions.

The problem is that the built-in date interpretation routines do NOT support "delta-time" formats. They treat all dates as "absolute-time" formats. Therefore, if you take the difference of two such fields and the difference is less than 24 hours, displaying that field looks like it is sometime after midnight of the system reference date.

The way to convert these differences is, I'm sad to say, to NOT keep them in date fields. Keep them as DOUBLE (or SINGLE if you wish) fields in units of hours. Then write a couple of VBA formatting routines to convert these fields to hh:mm format. It shouldn't be that hard to do so.

To convert date differences directly to hours, you can subtract the two dates (in the appropriate order) and then multiply the difference by 24. That becomes hours and fractions thereof. After that, you can accumulate the elapsed times normally. The last trick is to reverse the formatting of the sum/average so you can get hours, minutes, and seconds elapsed.

 

[gbshahaq]

thanks for the reply Doc Man but i'm not sure it helps.

i'm pretty much stuck with the import data that's given to me - lots of calculated fields output by an ancient program as hh:mm:ss in a text file.
Is the answer to import this as a double? I get import errors when trying this.....

it would be tedious and inconceivable to alter the data before import. Any other ideas?

 

[gbshahaq]

i actually have this sorted now.....

I have altered the import source to output in seconds...so i can import as Long Int....

 

[The_Doc_Man]

Actually, that's not a bad solution. OK, to some purists, it is horrible and should never be done. But then again, very few folks here would call me a purist. (I won't tell what some folks call me. It isn't "tater salad", though.)

If you can't solve the problem, change it to something you CAN solve. Which is exactly what you did.

For what it is worth, this kind of time management is no better in other systems. OpenVMS time is just as ugly as Windows time when dealing with the raw binary data. Delta-time computations are a bear in most systems I know because the system support libraries usually don't have very good "tools" for working with these data types directly. That's why more than once I have had to "roll my own" on some projects. I have learned more or less the hard way how to decide when it is time to get out the old toolsmithing tools. I wish there was a good rule I could share with you, but it is a "seat of the pants" type decision.

The other way to do this would have been to import the data as raw times but convert them to other units and store the conversions in separate fields (perhaps after-the-fact). DateDiff with units of minutes or seconds might have done part of the trick for you. This would, of course, represent a different way of changing the problem to something you CAN solve.