SQL Command for UNIX Date conversion?
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
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
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
can be represented as
Jun 2, 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
2nd June 2008 11:27AM
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.
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
thats what php.net is for?