0

I am pretty new to MySQL, and am looking at a table (through a query) that has three date fields. However, they appear to be in seconds (but I could be wrong), but ultimately, I need to convert them to a valid date/time.

The numbers are:

1366272682
1366239600
1366272682

I think one of these dates is 18th April 2013.

Can someone let me know how I can convert them within the query (or indeed if I am right).

Thank you.

juergen d
  • 201,996
  • 37
  • 293
  • 362
AndyDB
  • 413
  • 6
  • 22
  • This is called unixtime and is the amount of seconds since jan 1st 1970. Unixtime is awesome :) – KrekkieD May 18 '14 at 13:08
  • possible duplicate of [Convert Unix timestamp into human readable date using MySQL](http://stackoverflow.com/questions/6267564/convert-unix-timestamp-into-human-readable-date-using-mysql) – h2ooooooo May 18 '14 at 13:09

1 Answers1

4

Those "numbers" are actually Unix Timestamps. Use FROM_UNIXTIME() to convert them into human friendly formats:

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

For example:

SELECT FROM_UNIXTIME(1366272682, '%e%D %M %Y')
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Perfect - thank you so much everyone for your help - it's all working. PS - How useful is that...... – AndyDB May 18 '14 at 13:28