querying table based on date

[Bobafart]

Hi,

I have a number of data items in a table called "mytable"

each item has a column named "lastDateDisplayed" which is a UNIX TIMESTAMP. Each time the image is displayed (a random image is chosen every 24 hours via crontab) I update the "lastDateDisplayed" column with the current date the image is being displayed

I don't want an image to be randomly displayed within a 1 month interval. It is suffienct to assume that 1 month = 30 days.. it doesn't need to be exactly the number of days in the current month.

so, how do I SELECT a random image from mytable that hasn't been displayed for a month?

A month in UNIX TIME would be:
1 month (assuming 30 days) = 30 * 24 * 60 * 60 = 2592000

SELECT id FROM mytable WHERE lastDateDisplayed > 2592000 ORDER BY random() LIMIT 1

... ?? doens't work...

[Fumigator]

Too bad you didn't use a date or datetime data type, as you could use date functions in your query.

As you have it, you have to subtract that value you came up with (259200) and subtract it from the current unix timestamp value and then compare that with your last displayed unix timestamp value.

[djm0219]

This should do it for you.

SELECT id FROM mytable WHERE datediff(now(),from_unixtime(`lastDateDisplayed`)) > 30 ORDER BY random() LIMIT 1

[Prince87]

wht if v r using Windows??

[djm0219]

What does using Windows have to do with it? That query will work no matter what the OS is.