Hi,
I have a database which was populated with UNIX timestamps as Integer values.
Can anybody tell me how to convert this to OO formats?
Thanks
Klaus
Convert UNIX timestamp
Re: Convert UNIX timestamp
Unix timestamp is seconds since 1st January 1970.
So:
divide by number of seconds per day (86400), gives you the number of days since then, add 25569 (which is 1.1.1970 0:00h) in OOo Calc [Tools>Options...>OpenOffice.org Calc>Calculate>start date as 30.12.1899]
Now you can format as you like.
Does this help?
So:
divide by number of seconds per day (86400), gives you the number of days since then, add 25569 (which is 1.1.1970 0:00h) in OOo Calc [Tools>Options...>OpenOffice.org Calc>Calculate>start date as 30.12.1899]
Now you can format as you like.
Does this help?
Re: Convert UNIX timestamp
This is not something I discovered myself, rather it's something that I'll need every now and then when doing extemporary reports of UNIXtimestamped data. The problem is: you have a time series of data in a spreadsheet, and the time is expressed as UNIX timestamps (seconds since the epoch, 19700101 00:00:00 UTC, and not taking into account leap seconds): how can you convert those timestamps in a readable date like, e.g., "01/07/2012 01:00:00"?
I found a very useful suggestion in the OpenOffice community forum. As it seems, OpenOffice's "day 0" is December 12th, 1899; that implies that January 1st, 1970 is day 25569 for OpenOffice. Now, if you divide a UNIX timestamp by 86400 (the number of seconds in a normal day), that will give you the number of days between the epoch and that timestamp (and some decimal, that you can use to calculate the time of day). And if you sum that number with 25569, you have an OpenOffice day for that timestamp.
Alright, let's put all the pieces together: let's say cell A2 contains a UNIX timestamp 1341104400, then this formula
=A2/86400+25569
will return a number. And if you format that cell as a date, DD/MM/YYYY HH:MM:SS, then you'll read a pretty "01/07/2012 01:00:00" there.
Nice, isn't it? I am sure I'll be referring back to this post every once in a while
I found a very useful suggestion in the OpenOffice community forum. As it seems, OpenOffice's "day 0" is December 12th, 1899; that implies that January 1st, 1970 is day 25569 for OpenOffice. Now, if you divide a UNIX timestamp by 86400 (the number of seconds in a normal day), that will give you the number of days between the epoch and that timestamp (and some decimal, that you can use to calculate the time of day). And if you sum that number with 25569, you have an OpenOffice day for that timestamp.
Alright, let's put all the pieces together: let's say cell A2 contains a UNIX timestamp 1341104400, then this formula
=A2/86400+25569
will return a number. And if you format that cell as a date, DD/MM/YYYY HH:MM:SS, then you'll read a pretty "01/07/2012 01:00:00" there.
Nice, isn't it? I am sure I'll be referring back to this post every once in a while
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OSX.

 Posts: 1
 Joined: Sat Aug 31, 2019 11:40 pm
Re: Convert UNIX timestamp
Ok... I tried the formula approach... slight variant that I found on "[Solved]" thread at viewtopic.php?t=1012
Should work just like
I'm getting a negaive year, and no time.
Any suggestions?
Code: Select all
=J3/86400+DATEVALUE("1/1/1970")
Code: Select all
=A2/86400+25569
Code: Select all
1565146800000 07/13/13969 00:00:00
OpenOffice 4 on Windows 10
Re: Convert UNIX timestamp
This formula worked in LibreOffice 6:
Code: Select all
= DATE( 1970, 1, 1 ) + ( A1 / 86400 )
LibreOffice 6.4.6 on Win8.x