I'm using MySQL, and storing dates as unix timestamps in the database. Can I convert these to formatted dates within the SQL query, or can I only do it in PHP?

MySQL seems to have a lot of functions to work with MySQL-format dates, but they only appear to convert TO unix dates, not FROM them.

Is this true?

  • Bob

    I was hoping for something a little more flexible - that seems to just deliver a couple of optional formats. I went ahead and coded it in PHP and it worked out ok.

    Thanks for the pointer though!

      If you combine FROM_UNIXTIME() with DATE_FORMAT(), you should be able to output it in pretty much any format, e.g.:

      SELECT DATE_FORMAT(FROM_UNIXTIME(`date_col`), '%e %b %Y') AS `date_formatted` . . .

        Good tip, NogDog. Now I learned something too... 😉

          22 days later

          Hi NogDog,

          Was hoping for a bit of assistance.

          you mentioned the following:

          If you combine FROM_UNIXTIME() with DATE_FORMAT(), you should be able to output it in pretty much any format, e.g.:
          Code:

          SELECT DATE_FORMAT(FROM_UNIXTIME(`date_col`), '%e %b %Y') AS `date_formatted` . . .

          I have a similar situation, but goes one step further: i'm using a SELECT COUNT query in order to setup pagination, and this query I have works great and returns all results it should:

          SELECT COUNT(DATE_FORMAT(FROM_UNIXTIME(`date`), '%Y')) AS `buzzyear` FROM `buzz` WHERE `showPublic` = 'y'

          However, I need it to only return a count of those rows which I specify for the year, which I get through PHP, like so:

          $year = $_GET['year']; //this is what we want SQL to return
$query = "SELECT ...";

          Can you point me to the mysql function I could use in conjunction with this?

          Thank you very much!

            I just found the solution, and the query was much simpler than I originally thought.

            THanks!

              Write a Reply...