How can I convert MySQL date stored as negative (signed) bigint to date format?

Question

I have birth dates stored in a MySQL database that I need converted to a readable date format yyyy-mm-dd hh:mm:ss. I cannot use the MySQL's From_Unix function as many birth dates are before 1970 and the function returns NULL. (i.e. -866138400000 which is 07/21/1942)

I have tried to use ticks but that is also returning NULL:

(FROM_DAYS(365 + (req.PATIENTDOB / 864000000000)) + INTERVAL (req.PATIENTDOB % 864000000000) / 10000000 SECOND) AS ptDob

Any advance would be greatly appreciated. Thank you.

Answer 1

I have no idea why you're making things so complicated. Just divide by 1000 to get seconds instead of microseconds and subtract that from 1970-01-01.

mysql > select '1970-01-01' + interval -866138400000/1000 second;
+---------------------------------------------------+
| '1970-01-01' + interval -866138400000/1000 second |
+---------------------------------------------------+
| 1942-07-22 06:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

So your query would actually be this of course:

select '1970-01-01' + interval your_column / 1000 second from your_table;

This query proves, that your assumption, that it would be 1942-07-21 is wrong. 1942-07-22 is correct.

mysql > select timestampdiff(second, '1942-07-21', '1970-01-01');
+---------------------------------------------------+
| timestampdiff(second, '1942-07-21', '1970-01-01') |
+---------------------------------------------------+
|                                         866246400 |
+---------------------------------------------------+

Answer 2

Found an answer while researching Negative Epochs. I was able to use the From_Unixtime function after all!

select date_format((DATE_ADD(FROM_UNIXTIME(0), interval -866138400000/ 1000 second)),'%Y-%m-%d') as ptdate; -> "1942-07-21"

Link to Reference > Look under Negative Epochs section