SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot samohtwerdna's Avatar
    Join Date
    Jul 2007
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using 'between' query does not get last date

    hello all,

    I have been using a simple query to retrieve records from my DB between a date range that is entered by the user.

    Code:
    WHERE recordtime BETWEEN "' .$_POST['date_0'].'" AND "'.$_POST['date_1'].'"
    my problem is that if I have records on the day of $_POST['date_1'] they will not show up. So lets say that a user entered 2009-01-01 for the start date and 2009-01-12 for the end date - my query returns all records from 1/1 to 1/11 - Why is this?? how do I include the records from 1/12 (or what ever the end date is)?

    Thanks,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this is happening because the values stored in the column are DATETIMEs, and all values for the last day have a time component, but the upper bound for your range is midnight on the morning of the last day

    your example is --
    Code:
    WHERE recordtime BETWEEN '2009-01-01' AND '2009-01-12'
    this is equivalent to --
    Code:
    WHERE recordtime BETWEEN '2009-01-01 00:00:00' AND '2009-01-12 00:00:00'
    and of course then any time on the 12th will not be included

    you ~could~ change it to this --
    Code:
    WHERE recordtime BETWEEN '2009-01-01' AND '2009-01-13'
    and this will correctly include all times on the 12th, but it will also incorrectly include '2009-01-13 00:00:00'

    another way some people deal with this is ----
    Code:
    WHERE recordtime BETWEEN '2009-01-01 00:00:00' AND '2009-01-12 23:59:59'
    but this is also incorrect, or at least has the potential to be incorrect, if you do not write the upper end value accurately enough down to the millisecond or whatever (and if the underlying technology changes, to specify time down to microseconds, you have to re-write that upper limit)

    the best way to handle this is to use a range that is open-ended at the upper end --
    Code:
    WHERE recordtime >= '2009-01-01' 
      AND recordtime  < '2009-01-13'
    can you see how that would always work, no matter how fine a time division is implemented?

    furhermore, it also works really nicely, without alteration, if you're storing DATEs instead of DATETIMEs

    in other words, the upper open end approach works correctly in all circumstances

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot samohtwerdna's Avatar
    Join Date
    Jul 2007
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply,

    I thought this might be the case. However, since my application deals with an end user, I will probably not want to explain that they need to pick the next day for the end date. I suppose I could simply add a day to the $_POST['date_1'] amount though.

    I think I will try adding the 23:59:59 because I doubt records will be recorded after 11:00 p.m. anyway.

    Thanks again for the reply

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samohtwerdna View Post
    I suppose I could simply add a day to the $_POST['date_1'] amount though.
    now you're thinking

    Quote Originally Posted by samohtwerdna View Post
    I think I will try adding the 23:59:59 because I doubt records will be recorded after 11:00 p.m. anyway.
    now you're not

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot samohtwerdna's Avatar
    Join Date
    Jul 2007
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - I get the point

    to add a day will I need to add the total seconds? I imagine I can't just say $_POST['date_1'] + 1 since date_1 will equal something like "2009-05-13"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i can't answer, because i don't do php

    i'll move the thread to the php forum for you, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $_POST['date_1'] = date('Y-m-d'strtotime('+1 day'strtotime($_POST['date_1']))); 


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
  •