Format mysql datetime with php

Question

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

Answer 1

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.

Answer 2

What we need to know is how the date is stored within the SQL. Is it Timestamp or Datetime or unixtime?

Answer 3

Use the date function:

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

Answer 4

This worked the best for me:

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

Answer 5

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

Answer 6

If using php5, you can also try

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

Answer 7

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.

Answer 8

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

Answer 9

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