Translate MySQL dates

July 6, 2006 1:07 PM

How do I translate a MySQL date (like '1152215517') into standard dd/mm/yyyy (or any other human readable format)?

I am browsing through a MySQL DB on a remote server and want to be able to decypher the "tstamp" field. I do not have PHP or MySQL on my PC, but I do have cygwin, python ruby & eclipse, in case that helps.

posted by signal to Computers & Internet (11 answers total)

Best answer: In perl use:

perl -e 'print scalar localtime () . "\n"';'

Python etc I don't know.

Google for "localtime epoch "

Basically it's the number of (non leap) seconds since 1/1/1970
posted by hardcode at 1:15 PM on July 6, 2006

Dammit, the bit between the brackets after locatime should be your tstamp value.
posted by hardcode at 1:16 PM on July 6, 2006

Instead of:

SELECT tstamp FROM tablename

...Try rewriting your query as:

SELECT FROM_UNIXTIME(tstamp) FROM tablename

posted by Doofus Magoo at 1:17 PM on July 6, 2006

perl -e 'print scalar localtime (1152215517) . "\n";'
posted by hardcode at 1:17 PM on July 6, 2006

Also, here's an online conversion thingy.
posted by Doofus Magoo at 1:20 PM on July 6, 2006

Response by poster: hardcode: googled a bit for "localtime epoch python", and figured out that:
time.strftime('%a, %d %b %Y %H:%M:%S', time.localtime(1152215517))
works. Thanks!
Doofus Magoo: thanks, that's pretty useful.
posted by signal at 1:31 PM on July 6, 2006

Response by poster: That's in python, BTW (alsa, don't have PERL).
posted by signal at 1:31 PM on July 6, 2006

Good, nice to see that Python can do it too. Cheers!
posted by hardcode at 1:37 PM on July 6, 2006

Well, since this is in MySQL, I would suggest using MySQL to format the date rather than an outside language. This will make it easier if you move your queries from one language to another. MySQL has builtin date/time functions that will do just what you need.

The from_unixtime function as mentioned by Doofus Magoo above should be all you need. There are other functions available to format the date to a specific format if need be, but the from_unixtime function will make it easily readable.
posted by chrisroberts at 4:58 PM on July 6, 2006

Response by poster: chrisroberts: I'm not doing a query, I'm browsing a MySQL DB using PHPMyAdmin, so I just needed a quick and dirty way to convert dates, so I could understand what was going on.
So the python works just fine.
posted by signal at 5:12 PM on July 6, 2006

Well, if you are browsing a database using PHPMyAdmin, you can query the database directly through the PHPMyAdmin interface and use from_unixtime to get the formatted time. But if the python thing works and it's not something you need for long term, do what's easiest.
posted by chrisroberts at 11:02 PM on July 6, 2006