Sunday, June 10, 2012

Format mysql datetime with php


I have a datetime column in mysql which I need to convert to mm/dd/yy H:M (AM/PM) using PHP.



Source: Tips4all

11 comments:

  1. This worked the best for me:

    $datetime = strtotime($row->createdate);
    $mysqldate = date("m/d/y g:i A", $datetime);

    ReplyDelete
  2. This is probably wrong as this dose not work.

    $mysqldate = date("m/d/y g:i A", $datetime);


    This works for me:

    $phpdate = strtotime( $mysqldate );
    $mysqldate = date( 'Y-m-d H:i:s', $phpdate );

    ReplyDelete
  3. An easier way would be to format the date directly in the MySQL query, instead of PHP. See the MySQL manual entry for DATE_FORMAT.

    If you'd rather do it in PHP, then you need the date function, but you'll have to convert your database value into a timestamp first.

    ReplyDelete
  4. If using php5, you can also try

    $oDate = new DateTime($row->createdate);
    $sDate = $oDate->format("m/d/y g:i A");

    ReplyDelete
  5. Use the date function:

    <?php
    echo date("m/d/y g:i (A)", $DB_Date_Field);
    ?>

    ReplyDelete
  6. This website should help you format the date using the date_format mysql function http://www.mysqlformatdate.com

    ReplyDelete
  7. you can also have your query return the time as a unix timestamp. That would get rid fo the need to call strtotime() and make things a bit less intensive on the PHP side...

    select UNIX_TIMESTAMP(timsstamp) as unixtime from the_table where id = 1234;


    then in PHP just use the date() function to format it whichever way you'd like.

    <?php
    echo date('l jS \of F Y h:i:s A', $row->unixtime);
    ?>


    or

    <?php
    echo date('F j, Y, g:i a', $row->unixtime);
    ?>


    I like this approach as opposed to using mysql's DATE_FORMAT function because it allows you to reuse the same query to grab the data and allows you to alter the formatting in PHP.

    it's annoying to have two different queries just to change the way the date looks in the UI

    ReplyDelete
  8. Using PHP Version 4.4.9 & MySQL 5.0

    This worked for me:

    $oDate = strtotime($row['PubDate']);
    $sDate = date("m/d/y",$oDate);
    echo $sDate


    Pubdate being the column in mysql.

    ReplyDelete
  9. This should format a field in a SQL Query:

    SELECT DATE_FORMAT( `fieldname` , '%d-%m-%Y' ) FROM tablename

    ReplyDelete
  10. You can have trouble with dates not returned in Unix Timestamp, so this works for me...

    return date("F j, Y g:i a", strtotime(substr($datestring, 0, 15)))

    ReplyDelete
  11. I am using php version 5.3.8 - ZS5.5.0

    MySql server version 5.1.50-community

    $mysqldatetime = strtotime($your_query['your_datetime_column']);
    $phpdatetime = date("d.m.Y - H:i:s",$mysqldatetime); // your datetime format
    echo $phpdatetime;


    Working code.

    ReplyDelete