How to convert UNIX time before 1970 to date format in MySQL?

Question

I have a database using unix time for its dates (I use mySQL). I want to get dates in the daily date format. This is my request:

SELECT FROM_UNIXTIME(time_created) FROM member

This works fine with dates after 1970 (e.g. 1314162229), but doesn't work until dates before 1970 (e.g. -769338000). Is there any work here?

Answer 1

A possible workaround would be to have consistent convenience corresponding to seconds for a certain number of years (preferably a multiple of 4). You can add this constant, translate the time, and then subtract the number of selected years.

Example: select 40 years.

Define a 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)

unix x 1930 20xx .

select subdate(from_unixtime(x+1262304000), interval 40 year);

-769338000

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)

Answer 2

:

MySQL:

SELECT DATE_ADD(FROM_UNIXTIME(0), interval YOURTIMESTAMPHERE second);

:

SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval YOURTIMESTAMPHERE second), '%Y-%m-%d');

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

: http://www.epochconverter.com/programming/mysql-from-unixtime.php#negavtiveEpoch

Answer 3

SELECT DATE_ADD(CAST('1970-01-01 00:00:00' AS DATETIME), INTERVAL `time_created` SECOND) FROM `member`

Answer 4

As far as I know, there is no such thing as a UNIX time until 01/01/1970 00:00 UTC. More on Wikipedia .