homepage Welcome to WebmasterWorld Guest from 184.73.40.21
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Query based on date time difference between two entries
Wayder

10+ Year Member



 
Msg#: 4417654 posted 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

WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4417654 posted 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

10+ Year Member



 
Msg#: 4417654 posted 11:58 am on Feb 16, 2012 (gmt 0)

I will play with that. Thanks for the suggestion.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4417654 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved