Query based on date time difference between two entries

Forum Moderators: open

Message Too Old, No Replies

Query based on date time difference between two entries

Wayder

10:56 pm on Feb 14, 2012 (gmt 0)

I'm just wondering if this is possible (I'm doubtful though).

row start_datetime(datetime) end_datetime(datetime)
1 2012-02-06 08:00:00 2012-02-06 19:00:00
2 2012-02-07 08:00:00 2012-02-07 20:00:00

Is it possible to query based on time difference between end_datetime row 1 and start_datetime row 2

i.e. SELECT * FROM table WHERE difference between end_datetime and start_datetime > 24 hrs (or other time)

I have been selecting multiple rows with the DATE_SUB(UTC_DATE(),INTERVAL 2 WEEK) and then looping through the result calculating the difference as I go but I'm trying to find a better way.

Thanks

phranque

10:34 am on Feb 16, 2012 (gmt 0)

you could probably doing something using a subquery:
http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-subqueries.html [dev.mysql.com]

something like this (using table aliases for clarity):
SELECT * FROM table AS t1 WHERE (SELECT count(*) FROM table AS t2 WHERE ABS(TIMEDIFF(t1.start_datetime, t2.end_datetime)) > 24:00:00) > 0

just a guess - haven't tested this...

Wayder

11:58 am on Feb 16, 2012 (gmt 0)

I will play with that. Thanks for the suggestion.

rocknbil

4:28 pm on Feb 16, 2012 (gmt 0)

Sounds like the exact same question here [webmasterworld.com], I'd use timediff() [dev.mysql.com] as suggested in that thread.