#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,671
    Rep Power
    171

    Convert data stored as strtotime('now') to date format inside Mysql.


    Hi;

    I have stored date in database as strtotime('now'). I use the query in a few websites. I wonder if it's possible to convert that to date('d/m/Y') INSIDE MySql (not with Php).
    PHP Code:
    function list_leads($where=" "$sort='enquiries.id DESC')
     {
       
    $sql '
             SELECT enquiries.id,
             enquiries.name,
             enquiries.phone,
             enquiries.alt_phone,
             enquiries.email, 
             enquiries.contact_time,
             enquiries.message, 
             enquiries.date_added,
             enquiries.agent, 
             enquiries.status as S,
             enquiries.callback_time,
             agents.username,
             lead_status.status
             FROM enquiries
             LEFT OUTER JOIN agents ON agents.id = enquiries.agent
             LEFT OUTER JOIN lead_status ON lead_status.id = enquiries.status
             '
    .$where.'
             ORDER BY '
    .$sort.'
             LIMIT 2000
         '
    ;
     
    $query $this->db->query($sql);
     return 
    $query->result();

    Thanks
    Last edited by zxcvbnm; July 9th, 2013 at 07:08 PM.
  2. #2
  3. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Technically this is a MySQL question, not PHP...
    So you're storing the date/time as epoch seconds; just a big integer?

    SELECT FROM_UNIXTIME(column,'%d/%m/%Y') FROM ...

    Edit:
    Using strtotime('now') is kind of odd, too. Just use time().
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,671
    Rep Power
    171
    Originally Posted by dmittner
    Technically this is a MySQL question, not PHP...
    So you're storing the date/time as epoch seconds; just a big integer?

    SELECT FROM_UNIXTIME(column,'%d/%m/%Y') FROM ...

    Edit:
    Using strtotime('now') is kind of odd, too. Just use time().
    It worked! Thanks

IMN logo majestic logo threadwatch logo seochat tools logo