Posted by Gordon Burditt on 01/16/07 06:17

>I'm using PHP with MySQL 4.x and was having trouble converting a
>datetime from MySQL into a formatted string until I ran across this
>solution that converts a YYYY-MM-DD HH:MM:SS string into a Unix-style
>timestamp and then formats it.

Have you considered letting MySQL format the timestamp the way you
want it? See date_format().

>$timestamp = "2005-04-06 15:43:34";
>$time = strtotime($timestamp);
>print date('Y-m-d \a\t H:i', $time)."\n";
>
>However, it seems kind of counter productive. After all, aren't people
>(and RDBMs like MySQL) getting away from Unix timestamps for the a
>reason?

MySQL's underlying storage of dates doesn't use UNIX timestamps,
making it practical to use dates for a fairly wide range (although
it does have a Y10K problem and a Y0 problem) of historical dates,
so many genealogists can use it without any grief (not many can
trace their roots back to around the time of Jesus Christ). It can
convert to and from UNIX timestamps. I wish it had a way to do
things like add a number of seconds to a datetime giving another
datetime, and to subtract two datetimes giving a number of seconds.

PHP's underlying storage of dates, last time I looked, *IS* the UNIX
timestamp, with all the time range limits that involves.

>I don't necessarily think that my code will last until the
>2038 timestamp rollover but I would rather avoid timestamps if possible
>and am surprised that PHP doesn't seem to offer a better solution -
>like parsing the string into some sort of date object or array,

C's "struct tm" might be an appropriate type of thing to use (fields
are broken out into year, month, day, hour, minute, and second)
although there's some awkwardness about that 1900-year offset on
tm_year. The only general way to do math on those I've seen used is
turning it into a UNIX timestamp (and possibly back again).

>and
>then formatting THAT into the desired string. Is there such a thing
>right now?

>It looks like date_parse() is a step in the right direction
>(http://php.net/manual/en/function.date-parse.php) but it looks like it
>is not available in a snapshot release and that's only half of the
>equation anyway. I could always write my own function to do what I am
>after but I would like to use something built in if possible. Any
>ideas?

[Back to original message]