Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
did you try (interval(time(time1)-time(time2),'ss') ?
Are you sure time1 and time2 are really in time format ?
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
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
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
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.