need help with date and sql

terrypeck36

$query = "SELECT * FROM items WHERE releasedate <= $today ORDER BY releasedate DESC LIMIT 0, 3";

i have a page that list my 3 newest items, i release one new item a day. i would like to slow down the rotation the items are displayed for 3 days or so before going on to the next 3 newest items?

can this be done?

bradgrafelman

What column type is releasedate ? MySQL DATE column, a Unix timestamp, ...?

If it's a DATE/DATETIME column, you can probably do something like this:

SELECT column1, column2, column3 FROM myTable WHERE releasedate <= CURRENT_DATE() - INTERVAL 3 DAYS

terrypeck36

it is a hardcoded integer

this doesnt seem to work for me

bradgrafelman

What kind of integer? A Unix timestamp? If so, you'll need to use this WHERE statement:

WHERE releasedate <= (UNIX_TIMESTAMP() - 60*60*24*3)

EDIT: Now that I think about it... and maybe I'm understanding you wrong... but I believe you've got your logic reversed. You want 'releasedate' values that are greater than or equal to (e.g. the same as or more recent) the Unix timestamp of 3 days ago. As such, your query should probably look like this:

WHERE releasedate >= (UNIX_TIMESTAMP() - 60*60*24*3)