How to convert UNIX time before 1970 to date format in MySQL?
A possible workaround would be to have a constant handy corresponding to the seconds in a certain number of years (preferrably a multiple of 4). You could add this constant, translate the time and then subtract the number of years chosen.
Example: choose 40 years.
Determine the constant:
MySQL [files]> select adddate(from_unixtime(0), interval 40 year);+---------------------------------------------+| adddate(from_unixtime(0), interval 40 year) |+---------------------------------------------+| 2010-01-01 01:00:00 |+---------------------------------------------+1 row in set (0.09 sec)MySQL [files]> select unix_timestamp(adddate(from_unixtime(0), interval 40 year));+-------------------------------------------------------------+| unix_timestamp(adddate(from_unixtime(0), interval 40 year)) |+-------------------------------------------------------------+| 1262304000 |+-------------------------------------------------------------+1 row in set (0.09 sec)
Now you can every unix timestamp x
between 1930 and 20xx and use it.
select subdate(from_unixtime(x+1262304000), interval 40 year);
With your example -769338000
, you get
MySQL [files]> select subdate(from_unixtime(-769338000+1262304000), interval 40 year);+-----------------------------------------------------------------+| subdate(from_unixtime(-769338000+1262304000), interval 40 year) |+-----------------------------------------------------------------+| 1945-08-15 17:00:00 |+-----------------------------------------------------------------+1 row in set (0.09 sec)
I found a new way:
converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval YOURTIMESTAMPHERE second);
converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval YOURTIMESTAMPHERE second), '%Y-%m-%d');
And back
SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );
source:http://www.epochconverter.com/programming/mysql-from-unixtime.php#negavtiveEpoch
SELECT DATE_ADD(CAST('1970-01-01 00:00:00' AS DATETIME), INTERVAL `time_created` SECOND) FROM `member`