Select by time reference

Forum Moderators: open

Message Too Old, No Replies

8:53 pm on Nov 22, 2009 (gmt 0)

Hi all,

Having the following query, how is possible to select a row calculating a time reference.

$query = mysql_query("Select id, title, article, time From articles where time > $timeref Order by id desc")

So now I want to show the article for 1 day only or 24h.
date is stored on db as time()

6:51 pm on Nov 23, 2009 (gmt 0)

Well . . . time is not a date format?

'12:23:45'

You need a datetime format.

'2009-11-23 10:50:43'

You may be able to do it if there are both date and time fields, but it would be a bit more convoluted than having a datetime column.

6:59 pm on Nov 23, 2009 (gmt 0)

If you want to just show articles for the last 24 hours, this query should do the trick

$query = mysql_query("SELECT `id`, `title`, `article`, `time` FROM `articles` WHERE `time` >= DATE_SUB(NOW(),INTERVAL 1 DAY) ORDER BY `id` DESC;");