Breaking & Fixing Dates from MySQL to PHP

Discussion

1. Jochen

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

   @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. Paul Davis

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

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

    @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. Binny V A

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

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

    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. Valentin Bora

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

    @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. torkil

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

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

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

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

    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. Jay Salvat

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

    @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. mescript

    @Jay is best one
31. Doug

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

    @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. Maor

    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.. :-)