Interval Time Query

[Dayna]

Hey All!

I have an output that provides the time based on milliseconds, I originally used this to calculate the days/hours for each incident:

interval

(TIMETAKEN / (24 * 60 * 60 * 1000),'DD:hh:mm')

This worked great, but rather than basing it on a 24 hour day, we want to use a 7.5 hour day (working day). When I change 24 to 7.5 my figures get skewed so instead of showing 0.1.30 it gives me 0.4.48.. Any idea what I should do?

Many thanks for your help!

Kind Regards,
Dayna

[Dayna]

Hello All!

Does anyone have any ideas?

Kind Regards,
Dayna

[Anonymous]

Hi Dayna,

Have you tried this:

(TIMETAKEN / (270000),'DD:hh:mm')

?

[jonathandienst · Accepted solution]

Dayna

If I am not mistaken, TIMETAKEN is in milliseconds. For a day length other than 24 hours, you have to calculate the days and hours separately.

The total number of hours is:

TIMETAKEN / (60 * 60 * 1000)

The days (which are the integer part of the interval) will be:

floor(TIMETAKEN / (60 * 60 * 1000) / 7.5)

The hours will be the remainder:

frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5
(can't use the Mod function as this only for integers)

But the hours must be divided by 24 to get the fractional part of the interval. So the resulting expression is:

interval(floor(TIMETAKEN / (60 * 60 * 1000) / 7.5) +
frac(TIMETAKEN / (60 * 60 * 1000) / 7.5) * 7.5 / 24)

See the attached as an example.

Hope that helps
Jonathan

[Dayna]

Hello All,

Jonathan, that's brilliant!! I have one query tho:

It works perfectly when 7.30 is being added (as one entry) but not when there's two entries of 5 (i.e. one on each day or two different records..)

Any idea?

Kind Regards,
Dayna

[Dayna]

Aha, my sum's weren't working correctly in the load script - in the expression it works perfectly.

Thanks!

interval(floor(sum(SUN_IH_Time) / (60 * 60 * 1000) / 7.5) +
 frac(sum(SUN_IH_Time) / (60 * 60 * 1000) / 7.5) * 7.5 / 24,'D-hh:mm')