Time diff calculations where date and time are in seperate columns

Question

I've got a query where I'm trying to get the hours in duration (eg 6.5 hours) between two different times.

In my database, time and date are held in different fields so I can efficiently query on just a startDate, or endDate as I never query specifically on time.

My query looks like this

SELECT COUNT(*), IFNULL(SUM(TIMEDIFF(endTime,startTime)),0) FROM events WHERE user=18

Sometimes an event will go overnight, so the difference between times needs to take into account the differences between the dates as well.

I've been trying

SELECT COUNT(*), IFNULL(SUM(TIMEDIFF(CONCAT(endDate,' ',endTime),CONCAT(startDate,' ',startTime))),0) FROM events WHERE user=18

Unfortunately I only get errors when I do this, and I can't seem to combine the two fields into a single timestamp.

Answer 1

Pretty sure your problem is that your concatenated values are being sent to TIMEDIFF() as strings rather than DATETIMEs. Try calling the DATETIME function on them:

SELECT COUNT(*), IFNULL(SUM(TIMEDIFF(DATETIME(CONCAT(endDate,' ',endTime)),DATETIME(CONCAT(startDate,' ',startTime)))),0) FROM events WHERE user=18

I don't have a MySQL DB in front of my to test that, but I think that or some similar form of it is what you are looking for. There's an example of it in the MySQL docs involving MICROSECOND:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Edit: Hmm... looks like TIMEDIFF is supposed to work with strings. Worth trying anyway.

Answer 2

TIMEDIFF(endDate,startDate) + TIMEDIFF(endTime,startTime)