9

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?

why.you.and.i
  • 497
  • 8
  • 20

4 Answers4

10

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)
glglgl
  • 89,107
  • 13
  • 149
  • 217
  • trued this, but dates after 1998 return NULL – trf Jan 08 '19 at 19:42
  • @trf Well, these dates can be used without this workaround. The point was to use negative UNIX timestamps. The reason for you getting NULL is that the "UNIX time oerflow date" of 2038 is pulled 40 years before, thus 1998. – glglgl Jan 08 '19 at 20:54
9

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

gengisdave
  • 1,969
  • 5
  • 21
  • 22
Manasse
  • 150
  • 1
  • 10
  • This is the one that worked for me, specifically the second one listed. Not to say that the accepted answer isn't the right one, But I like that this simply displays the date properly with no calculation requiring a constant. – trf Jan 09 '19 at 21:29
0
SELECT DATE_ADD(CAST('1970-01-01 00:00:00' AS DATETIME), INTERVAL `time_created` SECOND) FROM `member`
Braulio J. Solano
  • 162
  • 2
  • 7
-3

To my knowledge there is no such thing as UNIX time prior to 1/1/1970 00:00 UTC. More at Wikipedia.

Peter Rowell
  • 17,605
  • 2
  • 49
  • 65
  • Does it mean i shouldn't use UNIX time if i'm going to store dates older than 1970 (like birthdays)? – why.you.and.i Aug 24 '11 at 06:11
  • 2
    Sure there is. From your very own link: `This can be extended backwards from the epoch too, using negative numbers; thus 1957-10-04T00:00:00Z, 4 472 days before the epoch, is represented by the Unix time number −4 472 × 86 400 = -386 380 800.` – Jacob Aug 24 '11 at 06:48
  • @cularis: I missed that part and, to be frank, I've never seen it used like that. The only time that I, personally, needed to do anything with historical dates I had stuff going back to the 1200s, so UNIX time never came up. I noted that a sibling article to the one I linked to says ["Some systems correctly handle negative time values, while others do not."](http://en.wikipedia.org/wiki/Time_t). I guess that's what I was thinking of. – Peter Rowell Aug 24 '11 at 15:24
  • That article also says that you cannot depend on `time_t` to be signed. And in true Wikipedia fashion, it is incomplete, missing any mention of the best reason, IMHO, for `time_t` to be signed: it allows time arithmetic to work sanely. That is to say, `time_t(-1)` doesn't always mean "one second before the epoch." It can also be the result of subtracting two `time_t`s differing by one second, if the second subtrahend is the later value. If you then add that delta to another `time_t`, it subtracts 1 second, as you probably expected. But again, you cannot depend on this behavior. – Warren Young Aug 24 '11 at 20:36
  • Peter Rowell´s answer is correct. the unix timestamp is not defined for date before 1970. Using negative values is nothing more as a "needed" hack. Wait for the year 2038 ;) – Mirko Brunner Oct 08 '14 at 18:09
  • @MirkoBrunner: Since that [fateful day](http://kernelnewbies.org/y2038) is 24 years from now, I can only hope that we are all running 64-bit machines w/64-bit filesystems by then. :-) Of course, the *embedded systems* may still be 32-bit, so ...? – Peter Rowell Oct 08 '14 at 23:58
  • @PeterRowell Nothing prevents a 32 bit system to `typedef uint64_t time_t;`. OTOH, switching to 64 bit might not be enough if they just took some old code and recompiled it. – glglgl Feb 05 '15 at 15:20
  • @glglgl: True, true. In the end you have to code for now and the *foreseeable* future of the program; anything beyond that starts to fall into the You're Not Going To Need It pattern. And besides, by 2038 the robots will probably have taken over and all of this will be moot. :-) – Peter Rowell Feb 05 '15 at 19:31
  • @PeterRowell Mmm... that's what they said back in the 70s and 80s about the year 2000... – glglgl Feb 05 '15 at 19:34
  • 1
    @glglgl I know, I was there! Although I spent a grand total of 4 weeks in the summer of '74 programming in COBOL on an aging Honeywell system, I can still remember encountering the 2-digit year representation and thinking, "They're going to have to deal with that some day ..." – Peter Rowell Feb 05 '15 at 23:28