php date formatting with MySQL SHOW TABLE STATUS

Question

probably missing something simple:

<?php
$con=mysql_connect('localhost','xxx','xxx');

$db=mysql_select_db('xxx');

$sql = mysql_query("SHOW TABLE STATUS WHERE `name`=\"my_table\"");

$foo=mysql_fetch_array($sql);

return $foo['Update_time'];

// returns 2010-03-31 16:51:06
?>

how do i format this. I tried using

date('l jS F Y @ H:i:s', $foo['Update_time']);

but this returns:

Thursday 1st January 1970 @ 01:33:30

presumably as date() expects a timestamp.

thanks..im sure this is very obvious but my minds gone blank

Answer 1

The date function expects an UNIX Timestamp as its second parameter -- and not a string containing a date.

This means you have to convert that string to a timestamp -- which can be done using the strtotime function :

$ts = strtotime($foo['Update_time']);
echo date('l jS F Y @ H:i:s', $ts);

Note 1 : the YYYY-MM-DD HH-MM-SS format is well understood by strtotime ; which is often helpful, when dealing with dates that come from MySQL.

Note 2 : Using date and strtotime, which work on timestamps (reprenseted as 32 bits integers, counting the number of seconds since 1970-01-01), you will be limited to dates between 1970 and 2038 ; if you need to work with dates outside of that range, you'll have to use the DateTime class.

Answer 2

This should work:

date('l jS F Y @ H:i:s', strtotime($foo['Update_time']));

strtotime() will take a date string and turn it into a timestamp, which you can then use with date().

Answer 3

You need to use strtotime function:

date('l jS F Y @ H:i:s', strtotime($foo['Update_time']));