Odd date problem.

pcghost

I am trying to retrieve a record from mysql for a particular date (chosen by a user, stored in a $var) and the date before it for comparison.

I have searched everywhere and RTFM'ed and all the tutorials and posts talk about it using today (or some function of now) as the starting point. I need to use the variable as the starting point and get it and one day prior.

The fields in the database are datetime fields, so I tried converting it to a UNIX_TIMESTAMP and subtracting 84600 to get a timestamp equal to the day before, but then I end up with a range of over a years time?

I know there must be an easy way to use variables withing strtotime and such, but I can't find anything on it.😕

devinemke

$date = '2005-02-25';
$date_minus_1_day = date('Y-m-d', strtotime($date . ' -1 day'));
$sql = "SELECT * FROM table WHERE datetime BETWEEN '$date' AND '$date_minus_1_day'";

pcghost

That works. Thanks. I figured there was an easy way to do this that I was just plain missing. It is Friday after all, my brain is about mush after a solid week of non-stop coding.

Thanks again..🙂

TheDefender

Just make sure your dates fall within the date range aceptible for the strtotime function. (Jan 1, 1970 through Dec 31, 2037) If you're always dealing with dates in that range, then you're fine.

nemonoman

First, for a BETWEEN statement to work, you need to use the lesser value first.

BETWEEN 20 AND 1

returns nothing

Next, when, when, when WHEN will people realize that MySQL provides MUCH, MUCH better date handling than PHP. I can't begin to emphasize how much more ease and flexibility MySQL supports.

SELECT *
FROM mytable
WHERE datefield
BETWEEN CURRENT_DATE()-INTERVAL 1 DAY and CURRENT_DATE

Will do the trick.

If you want to make the date variable:
$mydate='2005-02-27';
$result=mysql_query("
SELECT *
FROM mytable
WHERE datefield
BETWEEN '$mydate'-INTERVAL 1 DAY and '$mydate'");