Ccna final exam - java, php, javascript, ios, cshap all in one. This is a collaboratively edited question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
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.
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
This worked the best for me:
ReplyDelete$datetime = strtotime($row->createdate);
$mysqldate = date("m/d/y g:i A", $datetime);
This is probably wrong as this dose not work.
ReplyDelete$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 );
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.
ReplyDeleteIf 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.
If using php5, you can also try
ReplyDelete$oDate = new DateTime($row->createdate);
$sDate = $oDate->format("m/d/y g:i A");
Use the date function:
ReplyDelete<?php
echo date("m/d/y g:i (A)", $DB_Date_Field);
?>
This website should help you format the date using the date_format mysql function http://www.mysqlformatdate.com
ReplyDeleteyou 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...
ReplyDeleteselect 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
ReplyDeleteThis worked for me:
$oDate = strtotime($row['PubDate']);
$sDate = date("m/d/y",$oDate);
echo $sDate
Pubdate being the column in mysql.
This should format a field in a SQL Query:
ReplyDeleteSELECT DATE_FORMAT( `fieldname` , '%d-%m-%Y' ) FROM tablename
You can have trouble with dates not returned in Unix Timestamp, so this works for me...
ReplyDeletereturn date("F j, Y g:i a", strtotime(substr($datestring, 0, 15)))
I am using php version 5.3.8 - ZS5.5.0
ReplyDeleteMySql 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.