Best practice for using date-time stamps with PHP and MySQL

What is the best practice for using and storing date-time stamps with PHP and MySQL? I have observed that some popular applications (such as WordPress) use the DATETIME data type and others (such as vBulletin) use an INTEGER (length = 10) data type.

At first glance it seems I should use the DATETIME data type since by definition it stores the date and time but I was wondering why INTEGER storage is also widely used.

In my application I will be doing a lot of date/time comparison as in sending an email when an item is 2 days from expiration and taking other actions when an item or event has expired with resolution down to a minute.

I prefer not to use the TIMESTAMP data type because of it’s limited date range.

I have a fair amount of experience using PHP and MYSQL but I have used dates only in the past but not date-time stamps. I’d like to hear from some experienced programmers about the pros and cons of each of the two MySQL data types before I start this project. I wasn’t sure if this belongs in the PHP forum or the MySQL forum so I placed it here.

Thanks,
Steve

Essentially you need to understand that PHP uses unix timestamps as its underlying date/time format.

ie 123456789012

This represents the number of seconds since 00:00 on 1 Jan 1970. Arcane? maybe. Readable? Certainly not. Efficient? if you want to add 1 sedond, yes. (I still have 86400 etched into my brain [seconds in a day])

Mysql’s natural date/time format is 2011-11-25 12:00:00.

So to move a date between PHP and Mysql you either generally have to do some re-formatting.

OR

You force Mysql to follow PHPs native format. Hence the scenario you describe.

Once you fully appreciate the date/time comparison tools which Mysql holds you realise that you must biting the bullet and do the reformatting is usually the best path to take.

There are exceptions, and as long as you are confident that you want to break the guideline for operational/efficiency reasons – then that is absolutely fine.

PHPs own re-formatting toolset is now very easy to use – see especially the Datetime classes.

I am skating over the costs/benefits of each - just to give you a rule of thumb, really.

Good question though and it is a thorny issue – one you should take the time to nail down, otherwise part of your brain will be nagging you about “is this really right?”, when it could be listening to music.

Generally I prefer to use the data type that is suited for the purpose so in this case I’d use DATETIME for a column that contains date and time. Date time calculations are pretty easy both in PHP and MySQL. Sure, there will be some slight performance overhead as compared to integers but I think it’s too minimal to worry about. I prefer to see real dates and times when I browse my database than some cryptic integers.

But sometimes I can see people choose integers when they want to support many db types and this way they avoid having to deal with differences between databases in how they implement the date/time columns.

Thanks for your input. It seems there are advantages to both types. I have decided to go with the DATETIME data type since it is designed for that purpose. It will also be easier to manually edit the date/time in certain records if necessary.

The only benefit to using datetime over an integer is the raw readability of it in basic SQL clients. If you use a timestamp you will typically see the integer form - not human friendly.