Mysql: How convert mysql date in php, back and foward

Dates in PHP and MySQL
I see a lot of people on forums and on my training courses asking about the 

best way (or any way) to manage dates stored in a MySQL database and used in PHP. 

Three options follow, but first the problem.

 

PHP uses unix timestamps for all its date functionality. 

It has methods to convert these timestamps into pretty much any text format you 

could want but internally it uses the timestamp format. A timestamp is simply an integer. 

Specifically, it's the number of seconds that have elapsed since 

midnight on January 1st 1970 (greenwich mean time).




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

DATETIME columns store date and time in some internal format (I've not found what that is) 

for efficiency but always converts them to/from a string in the form YYYY-MM-DD HH:MM:SS 

(e.g. 2006-12-25 13:43:15) when accessing them. DATE columns use just the date part of 

this format - YYYY-MM-DD (e.g. 2006-12-25). TIMESTAMP columns, despite their name, are 

nothing like the unix timestamps used in PHP. A TIMESTAMP column is simply a DATETIME column

 that automatically updates to the current time every time the contents of that record are 

altered. (That's a simplification but broadly true and the details are not important here).

 In particular, since version 4.1 of MySQL the TIMESTAMP format is exactly the same as the 

DATETIME format.





So the problem is how to work with these two very different date formats - 

the PHP timestamp integer and the MySQL DATETIME string. There are three common 

solutions...




One common solution is to store the dates in DATETIME 
fields and use PHPs date() and strtotime() functions to 
convert between PHP timestamps and MySQL DATETIMEs. 
The methods would be used as follows -



$mysqldate = date( 'Y-m-d H:i:s', $phpdate );
$phpdate = strtotime( $mysqldate );
Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access 
the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME
 will convert from PHP timestamp to DATETIME. The methods are used 
within the SQL query. So we insert and update dates using queries like 
this -


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

Our last option is simply to use the PHP timestamp format 
everywhere. Since a PHP timestamp is a signed integer, use an integer 
field in MySQL to store the timestamp in. This way there's no conversion
 and we can just move PHP timestamps into and out of the database 
without any issues at all.


Be aware, however, that by using an integer field to store your dates
 you lose a lot of functionality within MySQL because MySQL doesn't know
 that your dates are dates. You can still sort records on your date 
fields since php timestamps increase regularly over time, but if you 
want to use any of MySQL's date and time functions on the data then 
you'll need to use FROM_UNIXTIME to get a MySQL DATETIME for the 
function to work on.


However, if you're just using the database to store the date 
information and any manipulation of it will take place in PHP then 
there's no problems.



So finally we come to the choice of which to use. For me, if you 
don't need to manipulate the dates within MySQL then there's no contest 
and the last option is the best. It's simple to use and is the most 
efficient in terms of storage space in the data table and speed of 
execution when reading and writing the data.





However, some queries will be more complicated because your date is 
not in a date field (e.g. select all users who's birthday is today) and 
you may lose out in the long run. If this is the case it may be better 
to use either option 1 or 2. Which of these you use depends on whether 
you'd rather place the work on MySQL or PHP. I tend to use option 2 but 
there's no right or wrong answer - take your pick.