...

View Full Version : Format Date



leroybobbins
04-04-2009, 06:30 AM
I am printing out a table with dates and number of leads. This is the code:

while($row = mysql_fetch_array($result)){

// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['leadStamp'];
echo "</td><td><B><center>";
echo $row['COUNT(leadID)'];
echo "</B></center></td></tr>";


}


echo "</table>";

It is printing the leadStamp like this: 2009-04-04 00:14:44

How do I get it to print like this: April 4, 2009

Len Whistler
04-04-2009, 06:54 AM
I believe you have to do a MySQL query, something like:


SELECT DATE_FORMAT(leadStamp, '%M %D, %Y') FROM tablename;

I don't think PHP will format a MySQL time stamp.



----

bdl
04-04-2009, 06:57 AM
It is printing the leadStamp like this: 2009-04-04 00:14:44

That's the default format a DATETIME or TIMESTAMP field would have, so I'm assuming it's one of those field types.



How do I get it to print like this: April 4, 2009
Well, you can use PHP to grab that value and perform some date manipulation on it, but it's MUCH easier and makes more sense to have MySQL perform all date formatting before PHP even sees the data.

I refer you to the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/) section on DATE and TIME functions (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html), specifically DATE_FORMAT (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format). There is plenty of documentation available, no need to rehash it here.

Old Pedant
04-04-2009, 06:58 AM
I think this is a PHP question, not a MySQL question.

You would normally do formatting such as this in the server language (PHP, ASP, JSP, etc.), not in the SQL.

Having said that...

Yes, you COULD do it in MySQL.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

So you would need something like


SELECT DATE_FORMAT(leadStamp,'%M %e, %Y'), COUNT(*)
FROM yourtable
GROUP BY DATE_FORMAT(leadStamp,'%M %e, %Y')
ORDER BY leadStamp

(Just guessing about what you SQL looked like without the DATE_FORMAT added.)

If this is easier than doing it in PHP, then why not? But normally you would control formatting in the PHP code. Or at least *I* would, in JSP and ASP. I don't use PHP, so dunno how hard/easy it is there.

Old Pedant
04-04-2009, 07:00 AM
LOL! Okay, I guess I stand corrected. I guess PHP makes it harder than does ASP or JSP. So yeah, do it with DATE_FORMAT in MySQL.

By the by, I used %e because you said "April 4". The %D used by Len would give you "April 4th" (note the "th").

Old Pedant
04-04-2009, 07:09 AM
Looking at your other thread, "COUNT by date," I think what you will want is something like

$query = "SELECT DATE_FORMAT(leadStamp,'$M $e, $Y') AS leadDate, COUNT(leadID) AS leadCount FROM leads WHERE Date(leadStamp) BETWEEN CUR_DATE-6 AND CUR_DATE GROUP BY DATE_FORMAT(leadStamp,'$M $e, $Y') ORDER BY Date(leadStamp)";


Notice how I have carefully gone back and forth between using DATE_FORMAT( ) [for the SELECT and for the GROUP BY] and using just DATE( ) [for the WHERE and ORDER BY]. It *IS* important!

bdl
04-04-2009, 07:13 AM
I think this is a PHP question, not a MySQL question.

You would normally do formatting such as this in the server language (PHP, ASP, JSP, etc.), not in the SQL.
...
If this is easier than doing it in PHP, then why not? But normally you would control formatting in the PHP code. Or at least *I* would, in JSP and ASP. I don't use PHP, so dunno how hard/easy it is there.
...
LOL! Okay, I guess I stand corrected. I guess PHP makes it harder than does ASP or JSP. So yeah, do it with DATE_FORMAT in MySQL.


Well like many things, TMTOWTDI. I'm of the school of thought that dictates all (or most) data that comes down the pipe is already properly formatted and ready for display. MySQL is more than capable of handling these tasks, so they should be done at that level.

Old Pedant
04-04-2009, 07:21 AM
Well, the difference is that JSP and ASP.NET code is compiled, whereas the SQL has to be pseudo-compiled and interpreted. Plus shoving a simple number (the internal form of a datetime value) across the DB/language barrier will involve fewer bytes than a formatted string.

Okay, I'm probably micro-optimizing for no good reason. I'm violating my own oft-given advice to only search for optimizations after you find that some part of your app is the bottleneck.

Consistent, I'm not. Tch.

bdl
04-04-2009, 03:19 PM
After thinking about this awhile, I'm more onboard with the idea of keeping the data clean and performing server side (or client side) alterations. I'd change my answer above to have MySQL turn the date field into a UNIX TIMESTAMP and have PHP perform a date() function call on that value to format it. Some would argue that an INT type field would be better to store the timestamp in the first place, but then you loose the ability to perform date calcs from within MySQL, which are not simply a formatting issue. It makes a big difference when you need the right resultset.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum