Date Time difference

[#1]

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?

[joshabbott]

  Try this

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

[#3]

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]

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?

[#5]

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]

Hello

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

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

[rwunderlich · Accepted solution]

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

[#8]

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