Results 1 to 4 of 4

Thread: Check if time is between time range

  1. #1
    Join Date
    Aug 2009

    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
    Pleasanton, CA
    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
    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?


  4. #4
    Join Date
    Aug 2009
    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) {
    "INBETWEEN<br />";    
        } else {
    "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