Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: Format Date

  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Format Date

    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

  • #2
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    I believe you have to do a MySQL query, something like:

    PHP Code:
    SELECT DATE_FORMAT(leadStamp'%M %D, %Y'FROM tablename
    I don't think PHP will format a MySQL time stamp.



    ----
    Leonard Whistler

  • #3
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by leroybobbins View Post
    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.


    Quote Originally Posted by leroybobbins View Post
    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 section on DATE and TIME functions, specifically DATE_FORMAT. There is plenty of documentation available, no need to rehash it here.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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/...on_date-format

    So you would need something like
    Code:
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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").

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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!
    Last edited by Old Pedant; 04-04-2009 at 07:16 AM.

  • #7
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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.

  • #9
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •