Limit results based on unix timestamp..

[esiason14]

Limit results based on unix timestamp.....can anyone point me in the right direction. I would like to limit results to the "last 90 days". Is that possible?

[TobyRT]

is it actually stored as a unix timestamp string in the db, or do you just convert to a unix timestamp for display purposes?

If the latter, try something like:

SELECT * FROM my_table WHERE DATE_FORMAT(date,"%Y-%m-%d") <= DATE_FORMAT(NOW(),"%Y-%m-%d") AND date > DATE_SUB(DATE_FORMAT(NOW(),"%Y-%m-%d"), INTERVAL 90 DAY)

[esiason14]

TobyRT, it is actually stored as a unix timestamp

[TobyRT]

Ah.

Then you need to do some calculations. As I recall the timestamp is based on the number of seconds since Jan 01, 1970.

You just have to work out the number of seconds in 90 days, get the unix timestamp for now, and subtract the former from the latter.

There's several posts in here on that!

[cahva]

No need to do calculations yourself. Mysql has funtions to convert unix timestamps to dates and dates to unix timestamp.

FROM_UNIXTIME(datefield) - convert unix timestamp to normal date yyyy-mm-dd hh:mm:ss
UNIX_TIMESTAMP(datefield) - convert normal date to unix timestamp

Read mysql date and time functions. That I myself use so often that its in my bookmarks 🙂

[esiason14]

OK, thanks, guys. I'll read up on it!