Blogternals

This is one of those issues that seems to pop up a lot especially with programmers new to PHP and MySQL.  Seems that a lot of the people get hung up when trying to find the best way  (or any way for that matter) to manage dates in a MySQL database and then use them in PHP without a lot of headache. I have found several options that seem to work more or less, but I tend to fancy one over the rest and I will explain more about that later.

A little background is in order before we proceed though,  PHP uses Unix timestamps for all its date functionality, what does this mean you ask? Well, a timestamp is the number of seconds that have elapsed since midnight, January 1st 1970 GMT and PHP, now PHP has methods to convert these timestamps, but internally it uses the timestamp as an unsigned integer. Simple put, means PHP just uses an unsigned integer to tell time.

MySQL has gone out of its way and given use three different formats to store dates in but unfortunately now of these three are unsigned integers. The formats are DATETIME, DATE, and TIMESTAMP. DATETIME columns store date and time as a string in the form YYYY-MM-DD HH:MM:SS (e.g. 2006-12-25 13:43:15). 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. For our purposes here a TIMESTAMP column is just a DATETIME column that gets automatically updateded to the current time every whenever the contents of that record are altered.

Some of you intuitive people may be asking yourself at this point why not just store the the PHP timestamps as unsigned integers, here is why. If you just simply store that timestamps as unsigned integers and then wrote an SQL statement you loose a lot of functionality within MySQL because MySQL doesn’t know that your dates are dates.  You can still sort records on your date columns since PHP timestamps increase regularly, but if you want to use any of MySQL’s date and time functions on the data you’ll need to use FROM_UNIXTIME to get a MySQL DATETIME for the function to work on and this is will be a performance penalty in MySQL which is not the right place. In my opinion it is usually better to take performance hits outside the Database and take it at the script level.  However, if you’re just going to be using the database to store the dates and any manipulation will be take place in PHP then this could be an option. If you ever change your mind about using the dates as dates in MySQL you can see the headache if you try to port it!

OK, so I am going to talk about the option I feel is the most appropriate. I feel that you should use PHP to do the conversion and let MySQL do what is does best, store and sort data. the reason I prefer this over any other method is simple, scalability. If you are using an unsigned integer you have to change the format any way, so why not let this happen at or after the conversion? Next if you are doing your conversion in MySQL then it will take longer to retrieve data, and on a high end data warehouse this could cause a bottle neck or even worse traffic jam so to speak of data flow. Also if you do your conversion in the datebase you still have to format it for display in the script, so why not just do it all in the script and keep the expensive transactions out of the data warehouse.

OK, here are the examples on how to do this the way I feel is the best way.

24
25
26
27
28
29
30
31
32
33
34
35
36

 
    ....
        // $mysqldate is ready to be inserted into a DB
        // $phpdate is ready to used in a PHP script
 
        // For use with DATESTAMP type or TIMESTAMP type
        $mysqldate = date( 'Y-m-d H:i:s', $phpdate );
        $phpdate = strtotime( $mysqldate );
 
        // For use with DATE type
        $mysqldate = date( 'Y-m-d', $phpdate ); // H:i:s will be 00:00:00
        $phpdate = strtotime( $mysqldate );
    ....

Enjoy and Have a Good’n!