How do you set a date in a mysql update from a milliseconds since epoch value?

Question

I want to fill out some null dates with default dates, which should be the epoch date.

eg set updateDate = somethingtoconvertEpochDateToDateTime(numberofMillisSinceEpoch)

Answer 1

The MySQL DATETIME only represents times to one second resolution, so you mat as well divide your 'millis' by 1000 and use

updateDate = FROM_UNIXTIME( numberofMillisSinceEpoch / 1000 )

If you really need to store date-time information to higher resolution, you could store the milliseconds since epoch in a BIGINT and roll your own conversion functions.

If you need to back fill with the current date time (i.e. now) you can use the UNIX_TIMESTAMP() function with no argument.