0

I have a database (in CSV) with unix timestamps. I try to convert them in LibreOffice Calc into a human readable date. Everything is ok... except a one-day-lag.

For example, my timestamp is -518144400 (in E2 cell). My function is : =E2/86400+DATEVAL("1/1/1970"). I obtain 19572,9583333333 which correspond to 1953-07-31. This on-line calculator confirm the result.

What is the problem ? Just that the right answer is 1953-08-01. First, I thought the timestamps contained a mistake. But, in this PHP calendar, if I paste -518144400 as parameter in the URL, it works. The on-line calendar associate this timestamp to (what I think is) the right answer.

I don't understand what happens. What I missed ? One solution could be adding +1 in my function to correct. But I'm not satisfied, I'd like to understand...

tbodt
  • 16,609
  • 6
  • 58
  • 83
jonathan
  • 149
  • 4
  • 11

1 Answers1

1

It depends on conversion time zone, I mean that -518144400 (Timestamp) is equal to 1953-07-31 in GMT

While it will be 1953-08-01 in all other Time Zone where Time Relative to GTM is +1 or more.

H. Mahida
  • 2,356
  • 1
  • 12
  • 23
  • OK. Thank for explanations. So, is there any way to modify properly the Time Zone ? (I mean a different way to add +1 to my function) ? – jonathan Jun 27 '14 at 11:38
  • Yes you have to add it to time as Unix-epoch time is GMT. Do you want to do that in Excel ? – H. Mahida Jun 27 '14 at 12:09
  • 1
    In Excel (or Calc) would be great. :) With your suggestion I've tested one new thing. In France, where I am and from my datas come, the Time ZOne is GMT +1. If I convert -518144400 into a date AND hour, I get "1953-07-31 23:00:00". You 're right, it's not a one-day-lag but an one-hour-lag (3600 sec). So, my function became =(E2+3600)/86400+DATEVAL("1/1/1970"), and the result is correct (i.e 1953-08-01 00:00:00). Is there any error or I'm wrong somewhere ? – jonathan Jun 27 '14 at 17:18