CODEWITHSUNDEEP
phpmysqldatabasedatetime
breez
breez

Reputation: 75

mysql timestamp convertion

What is the correct way to convert from mysql timestamp (YYYY-MM-DD HH:MM:SS) to my own chosen format, something like "August 5, 2010"? iow how can I make the mysql timestamp php date() compatible?

Upvotes: 0

Views: 445

Answers (4)

Mchl
Mchl

Reputation: 62387

Third option: use MySQL's DATE_FORMAT() function (although I prefer ircmaxell's way)

Upvotes: 1

Artefacto
Artefacto

Reputation: 97835

See date and strtotime. For manipulations in other time zones, see date_default_timezone_set and the DateTime class.

Upvotes: 0

Marco Ceppi
Marco Ceppi

Reputation: 7702

You can have MySQL return it as an Epoch timestamp which php's date function can handle or use the following function:

$epoch = strtotime('YYYY-MM-DD HH:MM:SS');

php Manual: strtotime

Upvotes: 1

ircmaxell
ircmaxell

Reputation: 165201

There are two ways...

First, you can do it in php with strtotime()...

$time = strtotime('2010-05-05 05:05:05');
echo date('F j, Y', $time);

Or, you can convert it to unix time in MySQL with UNIX_TIMESTAMP():

SELECT UNIX_TIMESTAMP(`your timestamp column`) FROM blahblah....

You'd then need to format it with date() in php after fetching it.

Or, you can do the entire thing right in MySQL with DATE_FORMAT():

SELECT DATE_FORMAT(`your timestamp column`, '%M %e, %Y') FROM ...

Upvotes: 4

Related Questions