Convert UNIX timestamp

Discuss the database features

Convert UNIX timestamp

Postby kkaal » Thu Dec 06, 2007 11:19 am


I have a database which was populated with UNIX timestamps as Integer values.

Can anybody tell me how to convert this to OO formats?

Posts: 2
Joined: Thu Dec 06, 2007 11:17 am

Re: Convert UNIX timestamp

Postby probe1 » Thu Dec 06, 2007 12:52 pm

Unix timestamp is seconds since 1st January 1970.
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...> Calc>Calculate>start date as 30.12.1899]

Now you can format as you like.
Does this help?

DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
Posts: 277
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand

Re: Convert UNIX timestamp

Postby ambparker » Wed Nov 06, 2013 11:05 am

This is not something I discovered myself, rather it's something that I'll need every now and then when doing extemporary reports of UNIX-timestamped 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, 1970-01-01 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


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 OS-X.
User avatar
Posts: 13
Joined: Mon Oct 28, 2013 7:52 am
Location: USA

Re: Convert UNIX timestamp

Postby bns002.OOConfused » Sat Aug 31, 2019 11:50 pm

Ok... I tried the formula approach... slight variant that I found on "[Solved]" thread at viewtopic.php?t=1012
Code: Select all   Expand viewCollapse view

Should work just like
Code: Select all   Expand viewCollapse view

I'm getting a negaive year, and no time.
Code: Select all   Expand viewCollapse view
1565146800000   07/13/-13969 00:00:00

Any suggestions?
OpenOffice 4 on Windows 10
Posts: 1
Joined: Sat Aug 31, 2019 11:40 pm

Re: Convert UNIX timestamp

Postby MrProgrammer » Sun Sep 01, 2019 12:19 am

bns002.OOConfused wrote:I'm getting a negaive year, and no time.
1565146800000   07/13/-13969 00:00:00
As we've said in data processing for 50+ years: Garbage in; garbage out. Your "UNIX timestamp" is bogus. Current UNIX timestamps are about 1½ billion, ten decimal digits. Your value 1,565,146,800,000 (13 decimal digits, about 1½ trillion) would be the number of milliseconds since 1970-01-01, not the number of seconds. If this is the data you have, use formula =cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") or =cell/86400000+25569.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Posts: 3868
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Return to Base

Who is online

Users browsing this forum: No registered users and 13 guests