Is there an easy way to get a Unix timestamp from an SQL timestamp in php?

Question

My database table has a column that contains SQL timestamps (eg. 2009-05-30 19:43:41). I need the Unix timestamp equivalent (an integer) in my php program.

$posts = mysql_query("SELECT * FROM Posts ORDER BY Created DESC");
$array = mysql_fetch_array($posts);
echo $array[Created];

Where it now echoes the SQL timestamp, I want a Unix timestamp. Is there an easy way to do this?

Answer 1

strtotime() will happily take a SQL timestamp and convert it to UNIX time:

echo strtotime($array['Created']);

Note that it is bad practice to not use quotes around your array keys. According to the docs:

Always use quotes around a string literal array index. For example, $foo['bar'] is correct, while $foo[bar] is not.

This is wrong, but it works. The reason is that this code has an undefined constant (bar) rather than a string ('bar' - notice the quotes). PHP may in future define constants which, unfortunately for such code, have the same name. It works because PHP automatically converts a bare string (an unquoted string which does not correspond to any known symbol) into a string which contains the bare string. For instance, if there is no defined constant named bar, then PHP will substitute in the string 'bar' and use that.

Answer 2

Given a regular datetime field, you can do this in query with the MySQL function UNIX_TIMESTAMP():

$posts = mysql_query("SELECT *
                             , UNIX_TIMESTAMP(Created)
                      FROM     Posts
                      ORDER BY Created DESC");

you also should know that the PHP function mysql_fetch_array returns both numeric associative keys for a query result. I find this redundant, so I like to be more more specific and use:

$array = mysql_fetch_array($posts, MYSQL_ASSOC);

Also, and as @Paolo Bergantino indicated, quoting your echo would be a good decision:

echo $array['Created'];

One last comment, you're using "SELECT *" -- it is worth reading discussion on the pros and cons of "SELECT *" syntax.