正确显示mysql时间戳

Question

Hey i'm trying to display a date/time from a timestamp field using:

echo $date = date( "D F j", $row['date'] );

However it returns: Wed December 3 When it should return today: Monday July 13

Timestamp from the DB row is: 2009-07-13 04:16:31

Thanks.

Answer 1

You should use

SELECT UNIX_TIMESTAMP(`date`) AS `date` FROM ...

This will give you the date in the correct format to pass in to PHP's date() function.

Edit:

SELECT *, UNIX_TIMESTAMP(`date`) AS `date` FROM songs WHERE date >= DATE_SUB( NOW( ) , INTERVAL 2 WEEK )

As a side note - it's best to avoid using NOW() in queries as it prevents them being cached - inject the date in you application instead.