View Full Version : Convert mysql date

02-17-2010, 02:56 AM

I'm having issues with converting my mysql date in the database to something more readable. How can I format the mysql date correctly?


{section name=data loop=$news}
<h4>Posted by <a href="http://domain.com/member.php?user={$news[data].author}">{$news[data].author}</a> on {$news[data].news_date}</h4>


$sql = mysql_query("SELECT * FROM news ORDER BY date DESC");

while ($row = mysql_fetch_assoc($sql)) {
$author[] = $row;
$news_title[] = $row;
$message[] = $row;
$news_date[] = date("F j, Y g:i a", strtotime($row['date']));

$smarty->assign('news', $author, $news_title, $message, $news_date);


02-17-2010, 04:46 AM
I see your template and the code to pull the records, but what exactly are you having trouble with? What data / format is your `date` field? What is it doing / not doing?

Have you read the PHP manual entry for PHP's date() (http://www.php.net/date) function?

02-17-2010, 05:44 AM
When you view the index.php page, the date is YYYY-MM-DD. I've been trying to change that format but can't get it working.

I want the date to be February 16, 2010, 9:40 pm instead of 2010-02-16.

The date field is when the news article was created.

02-17-2010, 09:54 AM
I would advise to format the date in the way you need right in the SELECT list of the query using DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format).

Maybe this is my personal preference, but I prefer to move work which could be conveniently done at the DB server to the DB server.

Also it would be a better practice not to use the so called "evil star" * in the query and list the fields you would like to select explicitly.

Edit: In your case the query could be something like:

SELECT DATE_FORMAT(`date`, '%M %e, %Y, %l:%i %p') AS date1
, myfield1
, myfield2
FROM news
BY date DESC

It should format dates like February 16, 2010, 9:40 PM right in the query.

Of course in the while loop you would need to use date1 instead of date in this case:

$news_date[] = $row['date1'];

Edit2: Still surprisingly MySQL seems to be unable to show "AM" or "PM" in lower case with DATE_FORMAT(). So if this is not suitable, you could still have to format the date with the function date() in PHP (or concatenate 2 DATE_FORMAT() in the SQL query, 2nd specially for "am"/"pm" converted to lower case with LOWER()).

02-17-2010, 10:47 AM
You could easily write a simple function using split/explode and mktime to convert this into a php date, however the usual answer seems to be to use strtotime (http://www.php.net/strtotime) as this should do it for you and is likely to be far more efficient than a hand-rolled PHP function.



02-17-2010, 12:17 PM
the usual answer seems to be to use strtotime (http://www.php.net/strtotime) as this should do it for you and is likely to be far more efficient than a hand-rolled PHP function.

I am sorry, but I would not call efficient converting a date from string to the UNIX timestamp and then back to string at the PHP side (even if this is more efficient than a custom PHP function). This is why I have suggested to use DATE_FORMAT() right in the query. Still if the query is not used often, it could be that efficiency is not that much a problem here...

02-17-2010, 12:33 PM
I agree that the date_format in the query could be more efficient but I personally find it less flexible to do the date conversion in the sql and would rather get dates out in standard mysql format, convert to phhp date format, use as required then change back to mysql format if I need to write back.

How efficient or otherwise that is depends on what you do with the date in between but as I say I prefer it for convenience and as you said it is a personal preference thing, didn't mean to question your approach, just offering an alternative view :)

When I said strtotime was more efficient I meant than using your own php coded function to do the same conversion, not that it was more efficient than if you could avoid doing the conversion in php at all.

Hope that clarifies,


02-17-2010, 01:20 PM
2 DaiWelsh: Yes, I agree completely. This is a personal preference of course. And it is difficult to say how this would influence the task efficiency in general (without any benchmarking) especially when Smarty is used. This place could be certainly be not a bottleneck. So I agree completely with your point.

2 the OP: I do not use Smarty so I briefly looked at the manual ... Maybe this would do the trick for you:

$sql = mysql_query("SELECT * FROM news ORDER BY date DESC");

$a = array();
while ($row = mysql_fetch_assoc($sql)) {
$row['news_date'] = date("F j, Y g:i a", strtotime($row['date']));
$a[] = $row;
$smarty->assign('news', $a);


My point is: assign() seems to takes one or 2 arguments (please see this (http://www.smarty.net/manual/en/api.assign.php)). So other arguments are most probably ignored. Then section (http://www.smarty.net/manual/en/language.function.section.php) would work with the 2nd argument (array $a in the code I have provided). Still I am sorry, I do not have time to install Smarty now to check the idea.