topic Difference of 2 date values in seconds in QlikView

Difference of 2 date values in seconds

Fri, 13 Nov 2009 19:15:03 GMT

I thought it would be easy, get the difference of 2 date/time values in seconds.

However - whatever I tried (and I tried a lot) - I end up either in a date or a null value

num#((time1 - time2) * 86400)

interval#(time1 - time2,'ss')

etc.

Thanks, Jürg

Difference of 2 date values in seconds

Fri, 13 Nov 2009 20:49:52 GMT

did you try (interval(time(time1)-time(time2),'ss') ?

Are you sure time1 and time2 are really in time format ?

Difference of 2 date values in seconds

Fri, 13 Nov 2009 21:02:56 GMT

Thanks Spastor

Had also my doubts about the format, I am actually reading the date/time from a csv file and I assume I have always seen string values and not dates.

I have changed my load statement now to use

timestamp#(SCHEDULEDDEPARTURETIME, 'DD/MM/YYYY hh:mm:ss') as SCHEDULEDDEPARTURETIME,

and am able to calculate seconds using

(hour(SCHEDULEDDEPARTURETIME)*3600 + minute(SCHEDULEDDEPARTURETIME) * 60 + second(SCHEDULEDDEPARTURETIME))
- (hour(OBSERVED_DEPARTURE_TIME)*3600 + minute(OBSERVED_DEPARTURE_TIME) * 60 + second(OBSERVED_DEPARTURE_TIME)) as Departure_Deviation,

which still looks a bit over-complicated to me.

Using your suggested interval formula returns null.

Jürg

Difference of 2 date values in seconds

Fri, 13 Nov 2009 22:36:54 GMT

Hi Jürg,

here are a short example with different formats.

The expression is a little bit smaller as yours.

Have a nice weekend!

Rainer

Difference of 2 date values in seconds

Fri, 13 Nov 2009 23:47:00 GMT

Hi Rainer

Finally, using correct date type values, I am able to calculate seconds using:

(num(SCHEDULED_DEPARTURE_TIME) - num(OBSERVED_DEPARTURE_TIME)) * 86400

which looks very familiar with other environments date/time calculations

Thanks for the help

Difference of 2 date values in seconds

johnw — Sat, 14 Nov 2009 00:27:09 GMT

With the times now being actual QlikView times, it should be as simple as (SCHEDULED_DEPARTURE_TIME-OBSERVED_DEPARTURE_TIME)*86400. The num() function probably isn't doing anything here.