Convert from MySQL datetime to another format with

Question

I have a datetime column in MySQL.

How can I convert it to the display as mm/dd/yy H:M (AM/PM) using PHP?

Answer 1

You can also have your query return the time as a Unix timestamp. That would get rid of 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.

Answer 2

To correctly format a DateTime object in PHP for storing in MySQL use the standardised format that MySQL uses, which is ISO 8601.

PHP has had this format stored as a constant since version 5.1.1, and I highly recommend using it rather than manually typing the string each time.

$dtNow = new DateTime();
$mysqlDateTime = $dtNow->format(DateTime::ISO8601);

This, and a list of other PHP DateTime constants are available at http://php.net/manual/en/class.datetime.php#datetime.constants.types

Answer 3

Finally the right solution for PHP 5.3 and above:

$date = \DateTime::createFromFormat('Y-m-d H:i:s', $mysql_source_date);
echo $date->format('m/d/y h:i a');

Answer 4

Depending on your MySQL datetime configuration. Typically: 2011-12-31 07:55:13 format. This very simple function should do the magic:

function datetime()
{
    return date( 'Y-m-d H:i:s', time());
}

echo datetime(); // display example: 2011-12-31 07:55:13

Or a bit more advance to match the question.

function datetime($date_string = false)
{
    if (!$date_string)
    {
        $date_string = time();
    }
    return date("Y-m-d H:i:s", strtotime($date_string));
}

Answer 5

$date = "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['date'])))."'";