Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Time difference

I'm trying to calculate the difference between 2 date/time stamps, the issue I'm having is that I only want to do it where the 2 dates are the same.

I've tried using the ceil/floor functions within an if statement, but it's not working.

Something like: if(Date1 = Date2, max(datetime2) - min(datetime1), 0) as Loadtime

Any ideas on this?

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

Detail:

LOAD

  *,

  date(floor([Start Date/Time])) as Date

;

LOAD

  Table,

  Timestamp(Timestamp#([Start Date/Time],'MM/DD/YY hh:mm:ss tt')) as [Start Date/Time],

  Timestamp(Timestamp#([End Date/Time],'MM/DD/YY hh:mm:ss tt')) as [End Date/Time]

INLINE [

Table,    Start Date/Time,          End Date/Time

1,          4/1/14 12:45:00 PM,          4/1/14 12:46:00 PM

2,          4/1/14 12:45:00 PM,          4/1/14 12:49:00 PM

3,          4/1/14 12:48:00 PM,          4/1/14 12:50:00 PM

4,          4/1/14 12:49:00 PM,          4/1/14 12:51:00 PM

5,          4/1/14 12:51:00 PM,          4/1/14 12:56:00 PM

1,          4/2/14 12:45:00 PM,          4/2/14 12:49:00 PM

2,          4/2/14 12:45:00 PM,          4/2/14 12:53:00 PM

3,          4/2/14 12:49:00 PM,          4/2/14 12:51:00 PM

4,          4/2/14 12:53:00 PM,          4/2/14 12:56:00 PM

5,          4/2/14 12:58:00 PM,          4/2/14 01:12:00 PM

]

;

Summary:

LOAD

  Date,

  interval(max([End Date/Time]) - min([Start Date/Time]), 'hh:mm:ss') as [Load Duration]

RESIDENT Detail

GROUP BY Date

;

-Rob

http://masterssummit.com

http://robwunderlich.com

View solution in original post

7 Replies
joshabbott
Creator III
Creator III

  Try this

=if(month(date1) = month(date2) and day(date1) = day(date2) and year(date1) = year(date2), date1- date2,0)

Not applicable
Author

That didn't seem to work either.

These are load time for tables. Different tables can load at the same time & therefore I cannot simply subtract the time periods. I need the Minimum Start Time for the day & the Maximum End Time for the day to determine how long it took to run as a whole process.

joshabbott
Creator III
Creator III


I'm not understanding.  What are the value's in your date fields?  The code above that I added checks if the date is the same down to the day, but still possibly different based on the hours/minutes/seconds.  Could you write up a quick little sample of what date1, date2, and your result based on those values being?

Not applicable
Author

Example:

Table     Start Date/Time          End Date/Time

1          4/1/14 12:45:00          4/1/14 12:46:00

2          4/1/14 12:45:00          4/1/14 12:49:00

3          4/1/14 12:48:00          4/1/14 12:50:00

4          4/1/14 12:49:00          4/1/14 12:51:00

5          4/1/14 12:51:00          4/1/14 12:56:00

1          4/2/14 12:45:00          4/2/14 12:49:00

2          4/2/14 12:45:00          4/2/14 12:53:00

3          4/2/14 12:49:00          4/2/14 12:51:00

4          4/2/14 12:53:00          4/2/14 12:56:00

5          4/2/14 12:58:00          4/2/14 01:12:00

I would like to show that for 4/1/14 the time to load the tables were 12:56:00 - 12:45:00 for a total of 12 minutes for 4/1.

for 4/2, I'd like to show that the time to load was 01:12:00 - 12:45:00 for a total of 27 minutes. So that I can graph the daily load times by date.

I hope that helps explain it better.

Anonymous
Not applicable
Author

Hello

You can calculate the difference in minutes or seconds and do the conversions for hours, days, etc ... For example:

=Minute((now() - today()))

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

Detail:

LOAD

  *,

  date(floor([Start Date/Time])) as Date

;

LOAD

  Table,

  Timestamp(Timestamp#([Start Date/Time],'MM/DD/YY hh:mm:ss tt')) as [Start Date/Time],

  Timestamp(Timestamp#([End Date/Time],'MM/DD/YY hh:mm:ss tt')) as [End Date/Time]

INLINE [

Table,    Start Date/Time,          End Date/Time

1,          4/1/14 12:45:00 PM,          4/1/14 12:46:00 PM

2,          4/1/14 12:45:00 PM,          4/1/14 12:49:00 PM

3,          4/1/14 12:48:00 PM,          4/1/14 12:50:00 PM

4,          4/1/14 12:49:00 PM,          4/1/14 12:51:00 PM

5,          4/1/14 12:51:00 PM,          4/1/14 12:56:00 PM

1,          4/2/14 12:45:00 PM,          4/2/14 12:49:00 PM

2,          4/2/14 12:45:00 PM,          4/2/14 12:53:00 PM

3,          4/2/14 12:49:00 PM,          4/2/14 12:51:00 PM

4,          4/2/14 12:53:00 PM,          4/2/14 12:56:00 PM

5,          4/2/14 12:58:00 PM,          4/2/14 01:12:00 PM

]

;

Summary:

LOAD

  Date,

  interval(max([End Date/Time]) - min([Start Date/Time]), 'hh:mm:ss') as [Load Duration]

RESIDENT Detail

GROUP BY Date

;

-Rob

http://masterssummit.com

http://robwunderlich.com

Not applicable
Author

Thank you Rob! I was able to get that to work!