using 'between' query does not get last date

[samohtwerdna]

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.

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,

[r937]

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 --

WHERE recordtime BETWEEN '2009-01-01' AND '2009-01-12'

this is equivalent to --

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 --

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 ----

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 --

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

[samohtwerdna]

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

[r937]

I suppose I could simply add a day to the $_POST['date_1'] amount though.

now you're thinking

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

[samohtwerdna]

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"

[r937]

i can't answer, because i don't do php

i'll move the thread to the php forum for you, though

[decowski]

$_POST['date_1'] = date('Y-m-d', strtotime('+1 day', strtotime($_POST['date_1'])));