Help with a formula in script: Time Difference

[stuwannop]

Hi All

I have two date fields called createdon and modifiedon. The formats are both dd/mm/yyyy hh:mm in a table and in the script I want to calculate the time difference between the two. I want it to report the time difference in hh:mm. Can someone help me out with a forumla please?

Also, part 2 (if possible): If the difference between the two is > 4 hours then I want it to return the value 2:00 (2 hours).

Finally, and this is the really tricky bit - can I get the formula to only calculate the difference between the hours of 9am and 5pm? i.e. If the record was createdon 26/06/2012 16:30 and modified on 27/06/2012 at 9:30 then I want it to return a result of 01:00 (1 hour)

Any takers?

Thanks in advance for any help.

Stuart

[swuehl]

First, set your default timestamp format to

SET TimestampFormat='DD/MM/YYYY hh:mm';

or use timestamp#() function to interprete your timestamp fields correctly.

Then, you can calculate the difference of both fields (if located in one table) and use interval to format accordingly:

LOAD

createdon,

modifiedon,

interval(if( (modified - createdon) > maketime(4),maketime(2), modifiedon - createdon), 'hh:mm') as TimeDiff,

...

FROM ...;

Your last request is a bit more tricky, but I think there were some similar threads quite recently, just search the forum.

Regards,

Stefan

edit:

Or try something like this (untested):

LOAD *,

interval( if(TimeDiffPart1 > maketime(4),maketime(2), TimeDiffPart1), 'hh:mm') as TimeDiff;

LOAD *,

if(floor(modifiedon) > floor(createdon), createdonPart1+modifiedonPart1, modifiedon - createdon) as TimeDiffPart1;

LOAD

createdon,

modifiedon,

maketime(17)-frac(createdon), as createdonPart1,

frac(modifiedon) - maketime(9) as modifiedonPart1

...

FROM ...;

[stuwannop]

Many thanks for the prompt response! I will give that a go and let you know how I get on.

Stu

[stuwannop]

Worked a treat - thanks a lot - now for the tricky bit....

[swuehl]

Hope you have seen my edit in my last post. Just tested with a couple of timestamps and it seemed to work (well, there is a typo in the createdonPart1 line, remove the comma after the frac() function ).