[php] Convert from MySQL datetime to another format with PHP

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?

This should format a field in an SQL query:

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

To convert a date retrieved from MySQL into the format requested (mm/dd/yy H:M (AM/PM)):

// $datetime is something like: 2014-01-31 13:05:59
$time = strtotime($datetimeFromMysql);
$myFormatForView = date("m/d/y g:i A", $time);
// $myFormatForView is something like: 01/31/14 1:05 PM

Refer to the PHP date formatting options to adjust the format.

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

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.

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 is the column in MySQL.

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

$valid_date = date( 'm/d/y g:i A', strtotime($date));

Reference: http://php.net/manual/en/function.date.php

If you are using PHP 5, you can also try

$oDate = new DateTime($row->createdate);
$sDate = $oDate->format("Y-m-d H:i:s");

Use the date function:

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

Forget all. Just use:

$date = date("Y-m-d H:i:s",strtotime(str_replace('/','-',$date)))

Finally the right solution for PHP 5.3 and above: (added optional Timezone to the Example like mentioned in the comments)

$date = \DateTime::createFromFormat('Y-m-d H:i:s', $mysql_source_date, new \DateTimeZone('UTC'));
$date->setTimezone(new \DateTimeZone('Europe/Berlin')); // optional
echo $date->format('m/d/y h:i a');

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

This will work...

echo date('m/d/y H:i (A)',strtotime($data_from_mysql));

The approach I suggest works like the following. First, you create a basic datetime object from a mysql-formatted string; and then you format it the way you like. Luckily, mysql datetime is ISO8601-compliant, so the code itself could look quite simple and elegant. Keep in mind though that datetime column doesn't have a timezone information, so you need to convert it appropriately.

Here's the code:

(new ISO8601Formatted(
    new FromISO8601('2038-01-19 11:14:07'),
    'm/d/Y h:iA'
))
    ->value();

It outputs 01/19/2038 11:14AM -- hopefully what you expect.

This example uses meringue library. You can check out some more of it if you fancy.

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));
}

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.

SELECT 
 DATE_FORMAT(demo.dateFrom, '%e.%M.%Y') as dateFrom,
 DATE_FORMAT(demo.dateUntil, '%e.%M.%Y') as dateUntil
FROM demo

If you dont want to change every function in your PHP code, to show the expected date format, change it at the source - your database.

It is important to name the rows with the as operator as in the example above (as dateFrom, as dateUntil). The names you write there are the names, the rows will be called in your result.

The output of this example will be

[Day of the month, numeric (0..31)].[Month name (January..December)].[Year, numeric, four digits]

Example: 5.August.2015

Change the dots with the separator of choice and check the DATE_FORMAT(date,format) function for more date formats.