Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difference of 2 date values in seconds

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

5 Replies
Not applicable
Author

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

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

johnw
Champion III
Champion III

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.