在PHP / MySQL中使用时间和日期的首选方法？

Question

I've always used unix timestamps; like time() - stored in a database as an int - and then used associated functions like date(), mktime(), etc. I like the simplicity of it.

I'm aware there is a DateTime class, I have not used it myself though. Reading http://www.phptherightway.com/#date_and_time it seems that this is the preferred way to do things now. Is that correct? Is this the standard now?

If so, how is it stored in a database?

Or is using unix timestamp still perfectly acceptable?

Answer 1

The one thing a UNIX timestamp does not give you is a timezone. If you need timestamps associated with timezones, DateTime can do that for you. Note though that MySQL does not have a timezone aware DATETIME type (Postgres for example does), so if you're going to serialise a DateTime object into MySQL, you'll have to store the timezone separately anyway. This would look something like this:

$stmt = $db->prepare('INSERT INTO foo VALUES (:timestamp, :tz)');
$stmt->bind('timestamp', $dateTime->format('Y-m-d H:i:s'));
$stmt->bind('tz', $dateTime->getTimezone()->getName());

The other way around:

$data = $db->query('SELECT * FROM foo')->fetch();
$dateTime = new DateTime($data['timestamp'], new DateTimeZone($data['tz']));

This has the additional gotcha of MySQL now not being aware of the timestamps's timezone though, so you should standardise all timestamps to the same timezone when inserting into the database if you want MySQL to do correct queries on those timestamps.

Another advantage of the DateTime object though is that it can handle a much larger range of dates than an integer, especially on 32-bit systems. This is somewhat mitigated on 64-bit systems.

In summary:

• DateTime has a timezone, larger range of values and its interface is arguably more elegant than pushing integers through functions
• however, because DateTime has these additional features it may be a littler harder to serialise it correctly into a "lowest common denominator" data store like MySQL