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

Question

I have a database using unix time for its dates ( i am using mySQL). I want to retrieve the dates in daily date format. This is my query:

SELECT FROM_UNIXTIME(time_created) FROM member

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

Answer 1

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)