Using PHP with MySQL Datetime

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.

$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? 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, 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?

-Brandon

>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?

..oO(Gordon Burditt)

>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.

You should be able to do that with DATE_ADD() and TIME_TO_SEC().

Micha

> >$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?
>
> PHP's underlying storage of dates, last time I looked, *IS* the UNIX
> timestamp, with all the time range limits that involves.

There's an "underlying" storage for dates? I wasn't aware that PHP had
a true date type for a timestamp for "lie under." Timestamps are just
integers. PHP may have a lot of functions for parsing strings into
integer timestamps and building strings from those integers but that
doesn't mean there's any trick to using representing a date with
another data type. I was just hoping that PHP would have something
built in for storing dates in a format other than timestamps. From the
look of the parse_date() function I mentioned, it looks like something
might be in the works but hasn't made its way to an official release
yet.

Actually, the PEAR libraries have a true date data type but I'm not
sure where my app is ultimately going to be deployed so I'd rather not
rely on it.

>There's an "underlying" storage for dates? I wasn't aware that PHP had
>a true date type for a timestamp for "lie under."

It doesn't need a *TRUE* date type, it just needs some way to store
dates (and for the date library functions to use as input and
output). And a 32-bit integer is pretty lame nowadays. And that
seems to be what PHP uses for the date library functions (on 32-bit
machines, anyway).

>Timestamps are just
>integers.

This is not true in general, unless you're saying that "all bits
is just bits" and trinary is impossible. For example, in MySQL, a
timestamp has pieces year, month, day, hour, minute, and second.
In MS-DOS, a file timestamp also had these fields, but the number
of seconds was missing a bit so only even seconds were possible,
and the range of years were very limited. And some programs just
store dates as strings, with or without a time zone.

Oh, yes, it does matter whether a timestamp is implicitly in local
time, UTC, or something else. POSIX timestamps really have no good
way to store "the date of a transaction", meaning the year/month/day
local time a particular transaction happened. That date doesn't
change when the user moves from one timezone to another, and it can
have legal implications more significant than the UTC date/time.

>PHP may have a lot of functions for parsing strings into
>integer timestamps and building strings from those integers but that
>doesn't mean there's any trick to using representing a date with
>another data type.

There is if you have to re-create all the functions for dealing
with time because, say, they have insufficient range.


>I was just hoping that PHP would have something
>built in for storing dates in a format other than timestamps.

I consider "something built in for storing dates in a format" to
*BE* a timestamp by definition, even if it's not a POSIX timestamp.

>From the
>look of the parse_date() function I mentioned, it looks like something
>might be in the works but hasn't made its way to an official release
>yet.

>Actually, the PEAR libraries have a true date data type but I'm not
>sure where my app is ultimately going to be deployed so I'd rather not
>rely on it.
>

> >There's an "underlying" storage for dates? I wasn't aware that PHP had
> >a true date type for a timestamp for "lie under."
>
> It doesn't need a *TRUE* date type, it just needs some way to store
> dates (and for the date library functions to use as input and
> output). And a 32-bit integer is pretty lame nowadays. And that
> seems to be what PHP uses for the date library functions (on 32-bit
> machines, anyway).

All I wanted to know was whether I was missing a set of functions that
deal with a true date type (something that stores the parts of a date
separately and takes timezones into account) or if integer timestamps
were all that PHP provides functions for right now.

By the way, integer timestamps are definitely not 32-bits. If they
were, timestamps would have overflowed in less than two months. Any
place I've seen them in a typed language (C/C++, Java, etc.), they have
been 64-bit "long" integers.

> >Timestamps are just
> >integers.
>
> This is not true in general, unless you're saying that "all bits
> is just bits" and trinary is impossible. For example, in MySQL, a
> timestamp has pieces year, month, day, hour, minute, and second.
> In MS-DOS, a file timestamp also had these fields, but the number
> of seconds was missing a bit so only even seconds were possible,
> and the range of years were very limited. And some programs just
> store dates as strings, with or without a time zone.

It sounds like you might be using the word "timestamp" in a much more
general way but a true timestamp is, by definition, just a long integer
representing the time since Jan 1, 1970 Greenwich Mean Time. What you
are describing are other representations of a date/time, which is
exactly what I am after.

In fact, since you mentioned MySQL, the "timestamp" type in MySQL 4.1+
is ACTUALLY a "datetime" object, which is a more complex representation
of a date and time including a timezone. Prior to version 4.1, MySQL's
timestamp type was actually a true POSIX timestamp - all you got back
from a query was a number.

> >I was just hoping that PHP would have something
> >built in for storing dates in a format other than timestamps.
>
> I consider "something built in for storing dates in a format" to
> *BE* a timestamp by definition, even if it's not a POSIX timestamp.

So are you saying that there IS an alternative in PHP? Because so far,
I haven't found one. Just look at the documentation at PHP.net and
look at the example I gave.

$timeStr = "2005-04-06 15:43:34";
$time = strtotime($timeStr);
print date('Y-m-d \a\t H:i', $time)."\n";

$timeStr is just a plain old string and strtotime() parses it into a
POSIX timestamp, which is then passed around to wherever it needs to go
and most of PHP's functions take UNIX timestamps.

..oO(Brandon)

>By the way, integer timestamps are definitely not 32-bits.

The usual Unix timestamp is still a 32-bit signed integer.

>If they
>were, timestamps would have overflowed in less than two months.

Such an "integer timestamp" counts seconds, not milli seconds or
whatever. With a signed 32-bit integer you can count up to at least
2^31 seconds, which is enough for more than 68 years. With negative
timestamps the range is doubled.

Year 2038 problem
http://en.wikipedia.org/wiki/Year_2038_problem

Micha