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)

Accepted Answer

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.

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

Solution

Problem