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?
+4
A:
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 ...
ircmaxell
2010-08-04 12:58:57
Bad idea to make it with MySQL. You're at the mercy of whatever timezone your database picks, which may or may not be the one PHP is using (let alone that it's more likely it won't have the timezone data set up-to-date).
Artefacto
2010-08-04 13:02:00
@Artefacto Not a bad idea at all. In fact, it's a VERY good idea. In fact, I only trust MySQL with dates (because of replication and load balancing). It can lead to headaches if you mix between PHP and MySQL, but as long as you either pick one and stick with it or test properly you will be fine...
ircmaxell
2010-08-04 13:04:14
What? You trust MySQL with dates *because* of replication and load balancing? What do replication and load balancing have to do something like parsing/formatting a date? Mixing PHP and MySQL is a recipe for disaster and trusting MySQL alone is not a whole lot better (does it handle timezones, daylight savings time and rule changes correctly and can have its rules kept up-to-date [throughout the year?](http://derickrethans.nl/storing-date-time-in-database.html)).
Artefacto
2010-08-04 13:10:19
We have a geographically replicated master-master setup (1 server in US GMT -5, and 1 server in UK, GMT + 0). MySQL manages the difference in timezone settings between the servers for us. So if we use `NOW()` here, it'll be the same as if we use `NOW()` there without any input from us (MySQL stores the timezone shift info in the binary logs). Without that, we'd need to manually correct each date to/from timezones and UTC in PHP (easy for one date, easy to forget for an entire application with many developers)... We've been doing this for 3 years with no issue. So yes, I do trust it...
ircmaxell
2010-08-04 13:13:46
@irc A timezone shift is as good as nothing, hell it's preferable to store a timestamp instead, at least you won't be tricked by thinking it actually has nay value. I hope you're not storing any future dates in your database (e.g. you're building a calendar application). If you are, make yourself a favor and read the page I've linked to. I also gather that your application only shows the time in the timezone of the server, because otherwise you'd have to do those timezone conversions anyway.
Artefacto
2010-08-04 13:34:46
A:
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');
Marco Ceppi
2010-08-04 12:59:06
A:
See date and strtotime. For manipulations in other time zones, see date_default_timezone_set and the DateTime class.
Artefacto
2010-08-04 13:00:10
+1
A:
Third option: use MySQL's DATE_FORMAT() function (although I prefer ircmaxell's way)
Mchl
2010-08-04 13:01:10