SQL Command for UNIX Date conversion?

[kuau]

I have a table with email addresses and dates in UNIX time-from-epoch format. As these are unintelligible to me, I would like to convert them to yyyymmdd. So if the table has 3 columns: email, UNIXdate, newdate, does anyone know the correct command to use to convert the UNIX dates? I'm thinking something like this:

UPDATE `tablename` SET `newdate` = DATE_FORMAT(`UNIXdate`, yyyymmdd);

I just guessed at the last part. Please correct me. Thanks. :) e

[kuau]

I figured this out myself. In case anyone else needs to convert UNIX dates, this is how I did it:

UPDATE `tablename` SET `newdate` = FROM_UNIXTIME( `UNIXDate` );

Hope that helps somebody out there. e

[boogyman]

they are indeed not human readable, however they offer the ability to easily convert or display in any type of format you wish using your server-side language... example


1212420466

can be represented as



Jun 2, 2008 11:27am


or



6/2/08 11:27


or



2nd June 2008 11:27AM


very easily in php, because you take the date format in unix time, and using a pre-determined set of variables and the date() function you can display it those few and MANY MANY other ways without having to go through a whole new set of conversions from a "human-readable" format to unix then back to whatever format you wish to use.

[kuau]

Dear B: Yes, I can see how that format would probably be more efficient internally, but I like to work hands-on with the data in the databases and I find it reassuring to see at a glance what is going on with the data. I trust my eyes more than my php haha. e :)

[boogyman]

thats what php.net is for?