Convert Unix Time to Time / Date Format

Antonimo · General User Joined: 03 Aug 2005 Posts: 20

I have a MySql dump that contains a large number of records storing times as Unix timestamps (number of seconds since standard epoch of 1/1/1970)
eg UNIX time 1162033701 is 10/28/2006 11:08am GMT

I would like to convert them to a more "easy-to-report" time / date format.

Is anybody aware of an OOo macro that I can download, or a formula that can make this conversion?

Many thanks,

Villeroy · Posted: Wed Nov 22, 2006 11:43 am Post subject:

Dates in spreadsheets are just numbers, counting days since a given null-date (Calc's default: 1899-12-30). You may format any number as date.
A1 having the epoch time, convert the seconds to days and add start of epoch:
=A1/86400+DATE(1970;1;1)
86400 is the number of seconds per day (24*60*60)
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

Antonimo · General User Joined: 03 Aug 2005 Posts: 20

Hi Villeroy,

Thank you very much - nice simple equation!

Villeroy · Posted: Thu Nov 23, 2006 6:00 am Post subject:

Yes, it's simple. Forgot to mention that it can return GMT only. You may involve another cell (-12 to +12) for adjustment:
=A1/86400+DATE(1970;1;1) +$Setup.$A$1
assuming A1 on a (hidden) sheet "Setup".
Or without extra cells:
Ctrl+F3
Name: GMT_Offset
Refers to: 1 [=Central European Time]
Button "Add", "OK"
=A1/86400+DATE(1970;1;1) +GMT_Offset
"Named ranges" can refer to numbers, strings, formulae and relative ranges.
I prefer the term "named references".
Well, there might be some way for calculating daylight saving times as well. This would require some lookup in an extra table of countries.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06