Wednesday, May 11

How to FORMAT a UnixTimestamp to Proper Date in Mysql Query

To store date in the database, the preferred method is to have it in the unix timestamp format.
For eg,
$date = strtotime("now"); // 1305116135

To retrieve the date, we can use php function date() to get the date back in string, formatted according to the given format string

For eg,
$date = date('Y m d', $date); // 11-05-2011

However instead of doing it in the Php code, we can directly do it while querying in Mysql

For eg
SELECT
DATE(FROM_UNIXTIME(date_field)) as new_date
FROM table_name;

We can also format the date using the date_format function in mysql
For eg
SELECT
DATE_FORMAT(DATE(FROM_UNIXTIME(date_field)), '%d-%m-%Y') as new_date
FROM table_name;


Be the first one to Comment!!!

Post a Comment