Breaking & Fixing Dates from MySQL to PHP

By  on  

The way that databases store "date" field data is:

2008-11-19 // year - month - day

The way that us humans read dates (at least in the U.S.) is:

11-19-2008 // month - day - year

I write a lot of administrative panel modules that either display dates or, better yet, allow customers to their own dates for articles, events, etc. I've created a couple of PHP functions that allow me to easily handle the transition from MySQL to PHP and visa versa.

The PHP: MySQL to PHP

	/* break a date */
	function break_date($date)
	{
		$date = explode('-',str_replace('/','-',$date)); 
		return $date[1].'-'.$date[2].'-'.$date[0];
	}

The PHP: PHP to MySQL

	/* fix a date */
	function fix_date($date)
	{
		$date = explode('-',str_replace('/','-',$date)); 
		return $date[2].'-'.$date[0].'-'.$date[1];
	}

You'll see that I automatically replace "/" within the given date, break apart the date string, and rearrange the items to make the user and the database happy. Hopefully this saves someone some time!

Recent Features

  • By
    CSS Filters

    CSS filter support recently landed within WebKit nightlies. CSS filters provide a method for modifying the rendering of a basic DOM element, image, or video. CSS filters allow for blurring, warping, and modifying the color intensity of elements. Let's have...

  • By
    7 Essential JavaScript Functions

    I remember the early days of JavaScript where you needed a simple function for just about everything because the browser vendors implemented features differently, and not just edge features, basic features, like addEventListener and attachEvent.  Times have changed but there are still a few functions each developer should...

Incredible Demos

  • By
    Highlight Table Rows, Columns, and Cells Using MooTools 1.2.3

    Row highlighting and individual cell highlighting in tables is pretty simple in every browser that supports :hover on all elements (basically everything except IE6). Column highlighting is a bit more difficult. Luckily MooTools 1.2.3 makes the process easy. The XHTML A normal table. The cells...

  • By
    MooTools dwCheckboxes Plugin

    Update / Fix: The checkboxes will no longer toggle when the "mouseup" event doesn't occur on a checkbox. Every morning I wake up to a bunch of emails in my Gmail inbox that I delete without reading. I end up clicking so many damn checkboxes...

Discussion

  1. Why not use a more versatile function that accepts the format you wish as an extra argument?
    Like this:

    print("function convertMySqlDate( $format, $datetime ){
        $year  = substr( $datetime, 0, 4);
        $month     = substr( $datetime, 5, 2);
        $day   = substr( $datetime, 8, 2);
    
        $hour  = substr( $datetime, 11,2);
        $min   = substr( $datetime, 14,2);
        $sec   = substr( $datetime, 17,2);
    
        return date( $format, gmmktime($hour,$min,$sec,$month,$day,$year) );
    }");
    

    You can do stuff like:

    print("convertMySqlDate( "d/m/Y H", "2008-11-19 13:35:20" );");
    print("convertMySqlDate( "jS \of F Y", "2008-11-19" );");
    

    cheers!

  2. @Jochen: I’ll take a look at this — looks promising!

  3. riosatiy

    Question rephrased (sorry):
    Why is the date in the middle of US time format? It makes no sense?
    Year-Month-Date Hour:Minute:Second has a very logical order

    You would not put the current time as 08:55:27 or 27:08 am?

  4. riosatiy

    Also as a more constructive post; you can use this method to get what you seek without even using php.

    SELECT DATE_FORMAT(date_field, ‘%m %d, %Y, %l:%i%p’) as newdate FROM table_name;

  5. riosatiy

    Also as a more constructive post; you can use this method to get what you seek without even using php.

    SELECT DATE_FORMAT(date_field, ‘%m %d,
    %Y, %l:%i%p’) as newdate FROM
    table_name;

  6. riosatiy

    Also as a more constructive post; you can use this method to get what you seek without even using php.

    SELECT DATE_FORMAT(date_field, ‘%m %d, %Y, %l:%i%p’) as newdate FROM table_name;

  7. riosatiy

    Also as a more constructive post; you can use this method to get what you seek without even using php.

    SELECT DATE_FORMAT(date_field, ‘%m %d,
    %Y, %l:%i%p’) as newdate FROM
    table_name

  8. riosatiy

    Also as a more constructive post you can use this method to get what you seek without even using php.

    SELECT DATE_FORMAT(date_field, ‘%m %d,
    %Y, %l:%i%p’) as newdate FROM
    table_name;

  9. I agree with riosatiy. And that’s exactly how we roll in the UK! :D (the date format)

  10. Just to clarify, The reason dates are stored as 2008-11-19 is because you can sort them faster since they are already in the correct order. For example as an integer:

    20081119
    20081118

    It is obvious which date preceded the other by simply subtracting the two integers.

    11-19-2008
    10-19-2009

    When sorted these dates will get out of order.

    With this in mind however like David is doing, always format the date for the humans!

  11. @Mark: Yep. I’m not complaining about the Y-M-D format, but we simply cannot show users the date in that fashion.

  12. Patrick

    And why can you not show dates in that fashion, exactly?

  13. A better way to do this would be: date('m-d-Y', strtotime($date));

  14. I usually store dates/times in UNSIGNED INT fields, populated with time() on insert or update. After that, you cand format your INT (timestamp) however you like, just by passing the number as a second argument to the date function.

  15. Agree with Patrick. A human can understand 2008-11-19 rather easily.

    But when date formatting is needed… whats wrong with date()?

    date('Y-m-d',strotime(str_replace('-','/',$date))) // turns 11-19-2008 into 11/19/2008 into 2008-19-11
    date('d/m/Y',strotime($date)) // turns 2008-11-19 into 11/19/2008
    
  16. Jeff Hartman

    Unix timestamps… So much easier.

    I usually store dates/times in
    UNSIGNED INT fields, …

    What happens when you have a date before the unix epoch? Anything before Jan 1, 1970 will store a value of 0. Use a signed column.

  17. Thanks for pointing that out. I have made a mental note to take that into account next time I design a table with such a timestamp.

  18. @All: One reason I don’t use the timestamp option is because I like to be able to read the date using PHPMyAdmin’s table view. It allows me to change something quick if I need to.

  19. Jeff Hartman

    It’s also important to point our that if you need to store unix timestamp dates before ~Dec 1901 and after ~Jan 2038 then you should look into another method as that is the limit for 32-bit signed integer.

  20. You could also just fetch the data from MySQL in the correct format in the first place. Let’s say you were fetching blog post headlines and dates:

    SELECT title, DATE_FORMAT(posted_date, ‘%m-%d-%Y’) AS posted_at FROM blog_items;

    Check out the date_format function at mysql.com.

  21. Ah, I was about to point out the strtotime() function, but then I saw that Binny V A beat me to it.

  22. Maikel D

    I don’t use the Date field in MySQL.
    I personally store the date in a INT field.
    In that field, I put the value from the PHP Time function:

    time();

    And when I get it out of the database, I can output the date in any way I like, eg:

    $date = date('Y m D', $dateValueFromDB);

    This is the easiest way in my opinion.

    I hope that made sense :)

  23. In my experience it is best to process date formatting in SQL for a couple reasons. I’ve found it to be faster. My PHP code is cleaner. And I don’t need to worry about dates before 1970 if I use PHP native functions to format the date.

    DATE_FORMAT(dateTime, ‘%M %d, %Y’) as formattedDate

    Consult the MySQL docs (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format) for more output options.

  24. Sorry David, I read your article fully. I understand now that this is to store user input into your DB and not pulling from the DB.

    I would use regular expressions to do this in PHP. And stick with Perl style regular expressions because I’ve heard that PHP6 is supposed to discontinue ereg style.

    Here is a sample that can be expanded much further to account for multiple formats:

     if(preg_match('/([0-9]{1,2})[\/\.-]([0-9]{1,2})[\/\.-]([0-9]{4})/', $date, $matches)){
        $month     = sprintf("%02d", $matches[1]);
        $day    = sprintf("%02d", $matches[2]);
        $year  = sprintf("%04d", $matches[3]);
    }
    

    This segment of code also formats the numbers so that something like 1-1-2008 will have $month = "01", $day = "01", and $year = "2008".

    Hope that helps, and sorry for any confusion.

  25. I’ll weigh in and say that for most date related values I use an INT field and store the unix timestamp. It’s easier that way because I can store other templates for use in the date() for displaying the data in the browser.

    I’m not sure how the date fields work but sorting on time and finding out if something occurred earlier is as is as using standard comparison operators.

    I agree that the limitation is that it’s not easily read from a DB admin panel but I rarely have needed that functionality.

  26. Daniele

    Why not?
    SELECT DATE_FORMAT(date,’%m-%d-%Y’) AS date ….

  27. Hi David,
    The simplest way is the strtotime way.

    function break_date($date)  
    {  
    	return date('Y-m-d', strtotime($date);
    }  
    
    
    function fix_date($date)  
    {  
    	return date('m/d/Y', strtotime($date);
    }
  28. @Jay: Nice script, but you’re missing “)”‘s at the end of each strtotime() to close the date function.

  29. Wes

    In response to Mark’s comment, you might want to look at the ISO standards to see why dates are stored in the format they are:
    Wikipedia article

  30. @Jay is best one

  31. Hi David,

    This really helped me out. Thanks a lot!

    Question:
    First, I admit, I’m a newbie at PHP and mySQL so maybe this question is a bit too naive for this post. Just wondering how to go from the new MM-DD-YYYY format to something like “December 31, 07”

    Would I do it in the function or inline in the output code somewhere? Thanks

  32. thanhquanky

    @Doug:

    $old = strtotime($yourtime);
    $new = date("F d, y",$old);
  33. Nagendra

    I want to convert the date obtaining from the database(mysql) into another format(2009-09-18 into 18th Sep’09) using php. Please help me.
    Thanking you in advance,
    Nagendra.

  34. @Nagendra: Use the date_format function in MySQL to fetch the date on a different format. For instance:

    select date_format(date, "%D %b %y") AS `formatted_date`
    

    This would convert the YYYY-MM-DD column “date” into your wanted formatting.

  35. Eman

    I realize this is an older post, but I’d like to add that (since php5) there is also the DateTime class, which lets you format the date similar to php’s date function, like below, however I would suggest formatting date in Sql…

    $datetime = new DateTime('2008-11-19'); // (or database variable)
    echo $datetime->format('D dS m, Y'); // prints Wed 19th 11, 2008
  36. There’s another simple way to do that. I just posted something about it last week. The post itself in Hebrew, but you can still view the short code there and see..

    First way is to select from db using DATE_FORMAT functions.

    Second way is to format date with PHP on the returned date from DB.

    Heres’ the link –
    http://www.maorb.info/tech/2010-01/format-date-php-mysql

    You may reply there in English if you want to.. :-)

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!