www.webdeveloper.com
Results 1 to 4 of 4

Thread: Check if time is between time range

  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Check if time is between time range

    This seemed like it should have been simple, but turns out it's not (to my anyway).

    I'm trying to select records that have a time of day that falls between two other times. On the site, basically events are added that happened at a certain time of day. The user can then select a time range to see all events that fall between those times of the day.

    Let's say I have a row with a time = '09:00'
    SELECT * FROM table WHERE time BETWEEN '08:00' AND '10:30'
    then returns the row.

    Now let's say I have a row with time = '00:00' (midnight)
    SELECT * FROM table WHERE time BETWEEN '23:00' AND '01:30'
    then does not return the row.

    Now, logically, midnight is between 11:00 PM and 01:30 AM, but the commands assume you're talking about times on the same day, so you don't get the right results.

    How do I overcome this? I'm starting to think MySQL isn't going to allow it. I'm using PHP with MySQL, any function I can use?

    Thanks in advance for some help!

  2. #2
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    How do YOU know that the 1:30AM is in fact the next day. If you can't tell, neither can the SQL query.
    You need to add a date to a datetime field for that query to work.

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Well, YOU know that when a user chooses a time range, the second time will ALWAYS be "later" than the first time...

    Thus when you choose 23:00 as a start time, the end time 01:30 will logically be the next day and not 21 and a half hours before the start time...

    Basically I now know this is not going to work from a MySQL query perspective... Anyone want to give the PHP function to make this work a shot?

    Should this post then be moved to PHP?

    Thanks!

  4. #4
    Join Date
    Aug 2009
    Posts
    3
    Ok, this is what I've done on the PHP side for anyone who wishes to do the same kind of thing:

    PHP Code:
    $timearray explode(":"$time);
        
    $timeint = (int)$timearray[0] + ((int)$timearray[1] / 60);
        if (
    $timeint 1$timeint += 24;
        
        
    $fromarray explode(":"$from);
        
    $fromint = (int)$fromarray[0];
        
        
    $toarray explode(":"$to);
        
    $toint = (int)$toarray[0];
        
        if ((
    $fromint 12 && $fromint $toint) || $fromint == 0) {
            
    $toint += 24;
            if (
    $timeint <= 12$timeint += 24;
        }
        
        if (
    $timeint >= $fromint && $timeint <= $toint) {
            echo 
    "INBETWEEN<br />";    
        } else {
            echo 
    "NOT INBETWEEN<br />";    
        } 
    This seems to work 100%. If you find an issue with it please let me know. Just to clarify:
    This is for when you ask a user to select a start and end time and you want to know if a given time falls between those 2 times. So it's not bound by the date, it's merely a time of day thing...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles