How does one convert seconds (or milliseconds) to a timestamp (or just a string that looks like a date) in mySql

Question

I'm looking at this documentation http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_maketime And I KNOW I must be missing it, but I just don't see a function here that I can pass seconds (or milliseconds) to, and get back either a Timestamp, or just a string representing the date.

Please help.

Answer 1

If you are working with seconds since 1970 (i.e. Unix timestamps then FROM_UNIXTIME()) could well be what you want.

FROM_UNIXTIME 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.
The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.

Answer 2

Check out FROM_UNIXTIME. That converts the number of seconds since midnight Jaunuary 1, 1970 into a timestamp.

If you have the time in seconds since midnight of your current day, then use the MAKETIME function.

MAKETIME( seconds / (60*60),
          seconds / 60,
          seconds % 60 )