Calling a query with now() minus one day

My client is reporting that the following query is not displaying the results right up until the end of the day (UK)

$query = “SELECT * from auctionstb where date >= now() ORDER BY date ASC”;

Is there any reason this would happen?

Anyway as a solution I am seeking assistance how to call the same query but instead of now() I want to use now() - 1

So it would basically display all results from yesterday onwards.

Any advice / help appreciated.



what datatype is the column? how are values inserted into that column?

The datatype is date and here is how I add date to the database

$theyear = $_POST[‘select_year’];
$themonth = $_POST[‘select_month’];
$theday = $_POST[‘select_day’];
$thedate = $theyear . “:” . $themonth . “:” . $theday;

$query = “INSERT INTO auctionstb(date, time, location, description, filename)
VALUES(‘$thedate’, ‘$thetimeslot’, ‘$thelocation’,‘$thedescription’,‘$cataloguename’)”;

okay, the first thing you want to do is start using the CURRENT_DATE function instead of NOW(), because NOW() includes time

what did you want again? auctions that started yesterday or later?


Basically each auction has a date when it is on. The auction should display on the site all day on this particular date however the client is reporting that they are not displaying all day and disappearing before the end of the day.

As a solution I therefore wanted to display them for one extra day.

So yes I would like to display auctions whose date is any date after and including yesterday.

Many thanks!


i have a feeling you were using a hammer to try to kill a fly

NOW() includes a time potion

so, for example, NOW() + INTERVAL 24 HOUR is actually 2008-05-30 14:09:37

however, your stored dates would be like 2008-05-29

at some point in the day, it only makes sense that an event would stop showing

it all depends on how you write your WHERE clause

NOW() is mysql’s synonym for the standard sql CURRENT_TIMESTAMP function, and includes both a date and a time

perhaps you should be using CURRENT_DATE in your comparisons?

Thanks r937 - I have now changed the relevant code to use CURRENT_TIMESTAMP instead.

So my code is now as follows:

$query = "SELECT * from auctionstb where date >= CURRENT_DATE ORDER BY date ASC"; 

Many thanks for your help on this one!