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 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. 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…

  1. 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 );
  2. 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...";
  3. 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.

So to summarise, for those who’ve skipped straight to the last paragraph, most of the time I use option 3 but occasionally I use option 2 because I need MySQL to know the field contains a date.

76 thoughts on “Dates in PHP and MySQL

  1. spinster

    MySQL’s DATE_FORMAT function can’t be used to alter the format of the data in the table – dates are stored inone specific format only. This function is intended for altering the format of the data when it is pulled form the table. i.e.

    SELECT DATE_FORMAT(Date,%d/%m/%Y) FROM movies

  2. Regarding dates after in 2038 and thereafter, you should be able to do something like this (at least that is what I’ve used), if you are using PHP 5.2 or later, iirc:

    $dateTime = new DateTime("now", new DateTimeZone('America/New_York'));
    $mysqldate = $dateTime->format("Y-m-d H:i:s");
    echo $mysqldate;

    Obviously adjust for your time zone.

  3. HI,
    i have a MYSQL database, in the table nEventlog, the datetime is stored in second in the column ndatetime.

    How can i convert the datetime in second (format) to YYYY/MM/DD HH:MM:SS (format) ?

    i know in mssql query its ((CONVERT([varchar](50),getdate(),(103))+’ ‘)+CONVERT([varchar](50),getdate(),(8))).

    Can you please help ?

    many thanks

  4. Pingback: convert user's entered date format into mysql date format - Hot Scripts Forums

  5. I’m trying to understand this, but I’m php retarded. I’ve done this,
    $today = date(‘Y-m-d’);
    $expires = date(‘Y-m-d’, strtotime(“+90 days”));
    $warn = date(‘Y-m-d’, strtotime(“+83 days”));

    when I put these into my database, the only part that gets stored is the year, so, I get 2011 in all three fields. which doesn’t help me at all.

    How do I get the php to insert the WHOLE date into the database? I want to keep it as YYYY-MM-DD format.

  6. Amazing article! Helped me solve my own php to mysql conversions. Thanks a million Richard!

  7. One of the most simple way…

    $date = date_create(’2000-01-01′);
    echo date_format($date, ‘d-m-Y H:i:s’);

  8. Regards option 1., the following won’t work with mixed US/UK timezones:
    $phpdate = strtotime( $mysqldate );
    since the month/date swaps around.
    Instead, one solution (if you want code to run on PHP < 5.2.0) is as follows:
    list($y, $m, $d) = split('[/.-]', $mysqldate);
    $phpdate = mktime(0, 0, 0, $m, $d, $y);

  9. @Zenning:

    “when I put these into my database, the only part that gets stored is the year, so, I get 2011 in all three fields.”

    Post your sql. The php part looks right.

  10. I’ve used the truncante – trunc(date) but php returns an error. Do I’ve to alter the date field to Date instead of DateTime to avoid the hh:mm:ss bit.

  11. I am always scared when I read articles like this. How can anyone recommend storing a unix timestamp in a database table? What if you have to switch to a different programming language one day? What happens after 2038?
    Your data almost always outlives the application. Just store them as they are supposed to be stored and don’t let limitations of a programming language influence database data type decisions.

  12. That looks quite nice, but I was wondering if there was a way one could change the default settings in MySQL so that the data format automatically saves in British format.

  13. How would I use Richard’s example above,

    $mysqldate = date( ‘Y-m-d H:i:s’, time() );

    to get the future date and time, for example I want to add 30 days in the future. thanx,

  14. This article is super helpful, thanks for posting, I think i’ll go with your recommended mysql datetime method.

  15. Pingback: Keeping PHP and mySQL dates in sync « cotsweb.com Blog

  16. Pingback: Dates in PHP and MySQL • PHP Help Coding Programming

  17. Pingback: DB Insert IDs and PHP times « EvoDynamic

  18. Just wanted to know: What should be the size of the int column of the mysql table where I’ll be storing the timestamps generated from php. The maximum size is 60, but I want the most optimized size to make database more memory efficient. Thanks and great post.

  19. How can i compute the difference of an sql date (say 2012-01-13 12:29:09) to the date today(2012-08-04 12:29:09)? thanks..

  20. Datetime is internally a 64 bit integer that operates a lot like the “unix time” (seconds from epoch). It’s stored as seconds from 1000-01-01. Datetime is in no way shape or form a string… Otherwise indeices against it would be stupidly inefficient. Doing WHERE tbl1_time = ’2012-01-01 00:00:00′ is like integer comparison (other than the parsing step for the input date, which isn’t too bad).

    Obviously PHP sees it as a string, but you can easily remedy that by casting it back using strtotime.

  21. Pingback: php and mysql time conversion | code@butterflybone

  22. Pingback: Dates in Laravel/PHP and MySQL | Brendan Codes

  23. Pingback: PHP Timestamp vs MySQL TimeStamp | TropicalMonkey Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>