Did you wonder what is the best way to manage dates stored in a MySQL database and used with PHP application?

PHP way

PHP uses unix timestamps for all its date functionality. Functions to format and convert this timestamps are available. Timestamp is simply an unsigned integer - it’s the number of seconds that have elapsed since midnight on January 1st 1970 (greenwich mean time).

MySQL way

MySQL has three date types for use in columns. These are DATETIME, DATE, and TIMESTAMP.

• DATETIME - stores date and time as a string in the form YYYY-MM-DD HH:MM:SS (e.g. 2009-04-25 13:43:15).
• DATE - use just the date part of this format - YYYY-MM-DD (e.g. 2006-12-25).
• TIMESTAMP - a DATETIME column that automatically updates to the current time every time the contents of that record are altered. Don't confuse this type with UNIX timestamp! 

As we can see this two formats are not compatible with each other. In our application we need to convert between them. And here is the question - which format is better to be stored in the database?!

1. Use DATETIME field, convert with PHP

We store our dates and times in MySQL format. And then we convert to PHP in our application.

$date_mysql = date( 'Y-m-d H:i:s', $date_php );
$date_php = strtotime( $date_mysql );

2. Use DATETIME field, convert with MySQL

MySQL has build in function that can covert dates.

• UNIX_TIMESTAMP - converts from DATETIM to PHP UNIX TIMESTAMP
• FROM_UNIXTIME - converts from PHP UNIX TIMESTAMP TO DATETIME

$query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";
$query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";

3. Use UNIX TIMESTAMP

We can use integer field in MySQL and store UNIX timestamp in our db. This approach is very handy since no conversion is needed and I prefer it. However you must think ahead and use it ONLY when no dates before 1. January 1970 are used!
For example: this format is not useful for storing _birthday_ data but we can store _product order_ dates in it.