SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Date Conversion

    I have a MySQL database that I need to pull for a client and work with MS Access for a bit of time. Right now, all of the dates in the database tables are being stored as integers appearing like: "1258657399".

    Any idea how I can run a sql statement on the database to convert these dates to normal fomrat such as June 9, 2011?

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Sure, in PHP, you can use:-
    PHP Code:
    echo date('F j, Y'$int); 
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but I am hoping to run a sql statement right from within phpmyadmin to convert all of the fields. Is that possible? I am able to change the structure of the table field to varchar from int.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You should be changing it to a DATETIME field though, otherwise you'd be in a worse mess than you are now.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm sure there's a standard way of doing this, but I'd create another table and select everything from this table (converting the columns as you go) and insert it into the new table.

    Check out INSERT INTO ... SELECT ...
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, yea I figured out how to do it in a round about way, same idea. Export table to CSV, open in Excel and create new column using formula to convert the field. Works fine since I need to massage some of the data anyways before brining it into Access.

    Thanks!

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Great news.

    Please tell me you're now using a DATETIME datatype though, you'll be glad you did later down the line; trust me.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep! All is working fine, thanks!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dutton View Post
    Any idea how I can run a sql statement on the database to convert these dates to normal fomrat such as June 9, 2011?
    sorry i missed all the excitement

    to answer your original question, yes, just do this --
    Code:
    SELECT FROM_UNIXTIME(thesedates,'%M %e, %Y') ...
    simple, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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