Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Getting DB time results when times on different days

  1. #1
    Join Date
    Apr 2008
    Location
    Limoges, France
    Posts
    395
    Thanks
    13
    Thanked 61 Times in 61 Posts

    Default Getting DB time results when times on different days

    sql column's start_time and end_time in the format: 00:00:00 The field type is TIME

    I want to select rows where the current time is between start and end times.

    When start_time and end_time are on the same day, you can:

    Code:
    SELECT * FROM table WHERE start_time < CURRENT_TIME() AND end_time > CURRENT_TIME();
    The problem is when the start_time is 23:00:00 and the end_time is 03:00:00 .

    I would like to get the result directly from a sql query, but I'm not sure it is possible. If it is, then lets try that.

    Otherwise I'll need to use PHP to end up with the right query.

    Any ideas?

  2. #2
    Join Date
    Oct 2008
    Location
    Sweden
    Posts
    2,023
    Thanks
    17
    Thanked 319 Times in 318 Posts
    Blog Entries
    3

    Default

    Try:
    Code:
    SELECT * FROM table WHERE start_time < CURRENT_TIME() OR end_time > CURRENT_TIME();
    Good luck!
    Eddy Proca
    I love Dropbox. Get it through my girlfriend's referral link (I reached my limit) and both you and her get 500 MB extra! Thanks and you're welcome!

  3. #3
    Join Date
    Apr 2008
    Location
    Limoges, France
    Posts
    395
    Thanks
    13
    Thanked 61 Times in 61 Posts

    Default

    Good idea, but not successful.

    The problem is, start_time 23:00:00 and end_time 03:00:00 .

    If the current time is 01:00:00

    Code:
    SELECT * FROM time WHERE 23:00:00 < 01:00:00 AND 03:00:00 > 01:00:00 ;
    23:00 is never less than 01:00:00, thus no rows are returned.

    Now, 23:00:00 is less than 01:00:00 the next day. However when only storing time in the db there is no date associated with it. This is the problem.

  4. #4
    Join Date
    Oct 2008
    Location
    Sweden
    Posts
    2,023
    Thanks
    17
    Thanked 319 Times in 318 Posts
    Blog Entries
    3

    Default

    Yes, that isn't successful, that is why you should use the one I suggested:
    Code:
    SELECT * FROM time WHERE 23:00:00 < 01:00:00 OR 03:00:00 > 01:00:00 ;
    Note the OR instead of the AND.

    Good luck!
    Eddy Proca
    I love Dropbox. Get it through my girlfriend's referral link (I reached my limit) and both you and her get 500 MB extra! Thanks and you're welcome!

  5. #5
    Join Date
    Apr 2008
    Location
    Limoges, France
    Posts
    395
    Thanks
    13
    Thanked 61 Times in 61 Posts

    Default

    Right,

    The last query I posted wasn't yours. Although when I said, "but not successful," I was referring to your query.

    SELECT * FROM time WHERE 23:00:00 < 01:00:00 AND 03:00:00 > 01:00:00 ; is what I want, but when the current time is between 23:00 and 03:00, this query returns 0 rows.

  6. #6
    Join Date
    Oct 2008
    Location
    Sweden
    Posts
    2,023
    Thanks
    17
    Thanked 319 Times in 318 Posts
    Blog Entries
    3

    Default

    Try this then:
    Code:
    SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME());
    Good luck!
    Eddy Proca
    I love Dropbox. Get it through my girlfriend's referral link (I reached my limit) and both you and her get 500 MB extra! Thanks and you're welcome!

  7. #7
    Join Date
    Apr 2008
    Location
    Limoges, France
    Posts
    395
    Thanks
    13
    Thanked 61 Times in 61 Posts

    Default

    You may be right! I've got to test this again. I'll let you know.

  8. #8
    Join Date
    Apr 2008
    Location
    Limoges, France
    Posts
    395
    Thanks
    13
    Thanked 61 Times in 61 Posts

    Default

    Nope. Your query returns rows outside of what I need.

    if start_time is 21:00 and end_time is 03:00, then this row should only be returned is when the time is between 9PM and 3AM.

    My original query is "correct", however when dealing with times only in SQL, the problem arrives when your query involves a different day than the current one.

    Good thought, but it isn't going to work.

  9. #9
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    It's so weird, I had exactly this sort of problem today with one of my scripts

    But luckily for me, it's for a workplace so it only needs to look for times within 8:00 and 18:00, otherwise it would output an error.

    Maybe something like

    SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME() AND `date` = $nextday);

    Then setup a $nextday variable to add 1 to the day your currently on.

    I haven't thought about this a lot so I may be going off on a huuge tangent but try it anyway.

  10. #10
    Join Date
    Oct 2008
    Location
    Sweden
    Posts
    2,023
    Thanks
    17
    Thanked 319 Times in 318 Posts
    Blog Entries
    3

    Default

    I don't really see why my suggestion wouldn't work. You could see the time period as two per day. The first one would be from the beginning of the day until the end_time and the second period would be after the start_time and until the end of the day. Therefore, this should return either if the current time is less than the end_time or greater than the start_time. So only one condition must be met.

    If you use this:
    Code:
    SELECT * FROM table WHERE (start_time < CURRENT_TIME()) OR (end_time > CURRENT_TIME());
    and your start_time is 21:00:00 and your end_time is 03:00:00 then it will be:
    Code:
    SELECT * FROM table WHERE (21:00:00 < CURRENT_TIME()) OR (03:00:00 > CURRENT_TIME());
    You can then look at some of the different possibilities for current_time:
    • 01:00:00 - returns because second criterion is met (03:00:00 > 01:00:00)
    • 02:00:00 - returns because second criterion is met (03:00:00 > 02:00:00)
    • 02:59:59 - returns because second criterion is met (03:00:00 > 02:59:59)
    • 03:00:00 - does not return because no criterion is met
    • 04:00:00 - does not return because no criterion is met
    • 05:00:00 - does not return because no criterion is met
    • 06:00:00 - does not return because no criterion is met
    • 07:00:00 - does not return because no criterion is met
    • 08:00:00 - does not return because no criterion is met
    • 09:00:00 - does not return because no criterion is met
    • 10:00:00 - does not return because no criterion is met
    • 11:00:00 - does not return because no criterion is met
    • 12:00:00 - does not return because no criterion is met
    • 13:00:00 - does not return because no criterion is met
    • 14:00:00 - does not return because no criterion is met
    • 15:00:00 - does not return because no criterion is met
    • 16:00:00 - does not return because no criterion is met
    • 17:00:00 - does not return because no criterion is met
    • 18:00:00 - does not return because no criterion is met
    • 19:00:00 - does not return because no criterion is met
    • 20:00:00 - does not return because no criterion is met
    • 21:00:00 - does not return because no criterion is met
    • 21:00:01 - returns because first criterion is met (21:00:00 < 21:00:01)
    • 22:00:00 - returns because first criterion is met (21:00:00 < 22:00:00)
    • 23:00:00 - returns because first criterion is met (21:00:00 < 23:00:00)
    • 24:00:00 - returns because first criterion is met (21:00:00 < 24:00:00)
    • 00:00:00 - returns because second criterion is met (03:00:00 > 00:00:00)

    I wasn't sure if it's 24:00:00 or 00:00:00 but as you can see, this should work as you need it to work.
    Eddy Proca
    I love Dropbox. Get it through my girlfriend's referral link (I reached my limit) and both you and her get 500 MB extra! Thanks and you're welcome!

Bookmarks

Posting Permissions

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