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 8 of 8
  1. #1
    New Coder
    Join Date
    May 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert mysql date

    Hello,

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

    index.tpl

    PHP Code:
            {section name=data loop=$news}
            <
    h3>{$news[data].news_title}</h3>
            <
    p></p>
            {
    $news[data].message}
            <
    h4>Posted by <a href="http://domain.com/member.php?user={$news[data].author}">{$news[data].author}</aon {$news[data].news_date}</h4>
            {/
    section
    index.php

    PHP Code:
    $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);

    $smarty->display('index.tpl'); 

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

  • #3
    New Coder
    Join Date
    May 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    I would advise to format the date in the way you need right in the SELECT list of the query using 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:
    Code:
    SELECT DATE_FORMAT(`date`, '%M %e, %Y, %l:%i %p') AS date1
         , myfield1
         , myfield2
      FROM news 
    ORDER 
        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:
    PHP Code:
    $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()).
    Last edited by SKDevelopment; 02-17-2010 at 11:22 AM.

  • #5
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    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.

    HTH,

    Dai

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Quote Originally Posted by DaiWelsh View Post
    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...
    Last edited by SKDevelopment; 02-17-2010 at 01:23 PM.

  • #7
    New Coder
    Join Date
    Aug 2003
    Location
    Derby, UK
    Posts
    97
    Thanks
    0
    Thanked 14 Times in 14 Posts
    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,

    Dai

  • #8
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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:
    PHP Code:
    <?php 
    $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);

    $smarty->display('index.tpl');  
    ?>
    My point is: assign() seems to takes one or 2 arguments (please see this). So other arguments are most probably ignored. Then section 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.


  •  

    Posting Permissions

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