convert mysql timestamp to mktime

Question

Hi, I have the following MySQL timestamp: 2009-06-23 16:21:48 How can I convert it to a format like mktime()?

Answer 1

There is a MySQL function unix_timestamp. In your SQL query, instead of selecting the Datetime or Timestamp column directly, do this:

SELECT unix_timestamp(MyDatetimeColumn) FROM MyTable

Alternatively, if you have the string already, you could use the PHP function strtotime().

Answer 2

You could use the strtotime function.

Answer 3

ok, I was wrestling with this for a week (longer but i took a break from it).

I have two specific fields in tables

creationDate > timestamp > current_timestamp
editDate > timestamp > current_timestamp

they were pulling out either dec 31 1969, or just nothing... annoying... very annoying

in mysql query i did:

                unix_timestamp(creationDate) AS creationDate
                unix_timestamp(editDate) AS editDate

in php convert i did:

        $timestamp = $result_ar['creationDate'];
        $creationDate = date("Y-M-d (g:i:s a)", $timestamp)
                echo($creationDate);

        $editstamp = $result_ar['editDate'];
        $editDate = date("Y-M-d (g:i:s a)", $editstamp)
                echo($editDate);

this solved my problem for me returning

                2010-Jun-28 (5:33:39 pm)
                2010-Jun-28 (12:09:46 pm)

respectively.

I hope this helps someone out...