Converting a Long into a Date/Time (MySQL)

In MySQL, sometimes people store timestamps as long values (time since epoch, etc.)

In order to turn that into a date, you need to do the following in MySQL:

SELECT FROM_UNIXTIME(event_time) FROM MY_TABLE

or if you like a date nicely formatted, then:

SELECT DATE_FORMAT(FROM_UNIXTIME(event_time), '%d/%m/%y %h:%i%p') FROM MY_TABLE

NB: This assumes that your event_time is in seconds. If you are using Java, for example, then you will have probably stored your timestamp in millisconds (as kicked out by System,currentTimeMillis() ) so in this instance, MySQL will rather rudely just return NULL.

To fix this, simply divide by 1000 first as follows:

SELECT FROM_UNIXTIME(event_time/1000) FROM MY_TABLE

or if you like a date nicely formatted, then:

SELECT DATE_FORMAT(FROM_UNIXTIME(event_time/1000), '%d/%m/%y %h:%i%p') FROM MY_TABLE