[Solved] Timestamp conversion

Discuss the spreadsheet application

[Solved] Timestamp conversion

Postby johnfcturner » Wed Dec 19, 2007 9:00 pm

Does anyone have a macro that will convert a long list of unix epoch timestamps to dates? If there were a macro to do this, I would be very grateful; at the moment I am cutting and pasting them into the date -d function in a command prompt or doing it online, which is very very slow.
Last edited by Hagar Delest on Tue Jun 10, 2008 3:57 pm, edited 2 times in total.
Reason: tagged the thread as solved.
johnfcturner
 
Posts: 4
Joined: Wed Dec 19, 2007 8:54 pm

Re: timestamp conversion

Postby acknak » Wed Dec 19, 2007 9:29 pm

It would be a lot simpler to make a suggestion if you gave a couple of samples.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17424
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: timestamp conversion

Postby johnfcturner » Wed Dec 19, 2007 9:37 pm

I have the data as a text file, with 589 entries as a column at the moment, though I can manipulate it easily into comma or other 'symbol-delimited' file (though I can't find a way to replace with tabs in OO.....).

The data look like

1197048832
1197048833
1197050634
1197050635
1197053082
1197053083
1197053802
1197053803
1197054072
1197054073
1197054747
1197054748
1197055242
1197055243
1197055497
1197055498

and they are linux timestamps, in seconds, counting from t=0 as 1st Jan 1970. I'd like to convert them to day:month:year:hour:min:sec if possible, and I want to avoid doing this entry by entry - I could do this already but I am lazy and don't have time.

I have tried the extension 'DateTime2' but it appears not to handle multiple entries.

John
johnfcturner
 
Posts: 4
Joined: Wed Dec 19, 2007 8:54 pm

Re: timestamp conversion

Postby probe1 » Thu Dec 20, 2007 12:23 am

As you are posting in 'Calc' - you may like a formular, see:
Convert UNIX timestamp

Will give you the Calc date value, then format to your needs (Format>Cells).



I have tried the extension 'DateTime2' but it appears not to handle multiple entries.

Sorry, please explain: you should be able to insert into a single cell, a range of cells, into multiple ranges.
Pls state OOo version and OS you are using in case of any error.

The extension does not convert dates... so it doesn't solve your question. The posting linked above should help you.
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
User avatar
probe1
 
Posts: 237
Joined: Mon Oct 08, 2007 1:34 am
Location: Chonburi Thailand Asia

Re: timestamp conversion

Postby johnfcturner » Thu Dec 20, 2007 12:28 am

Hi Winfried,

I can convert a single cell to give me the correct human date and time but when I try to apply the extension to the whole column, it repeats year-month-day for the whole lot as per the first cell.

My 'end-around' came from the web at http://www.epochconverter.com/epoch/batch-convert.php which converted the lot into an html table, which I cut and pasted into an OO document (.odt, using OO 2.3.1 on fedora 7) and I will be able to get that to a spreadsheet in a few clicks, so the problem is solved functionally, albeit externally to OO.......

John
johnfcturner
 
Posts: 4
Joined: Wed Dec 19, 2007 8:54 pm

Re: timestamp conversion

Postby huw » Thu Dec 20, 2007 11:40 am

If you want to do it in Calc in future
Code: Select all   Expand viewCollapse view
=A1/86400+DATEVALUE("1/1/1970")
works with a timestamp in A1 and the formula cell formatted DD/MM/YYYY HH:MM:SS

Then just drag-fill down.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

[SOLVED]Re: timestamp conversion

Postby johnfcturner » Thu Dec 20, 2007 12:12 pm

That is a great deal easier and I will use it next time I have to do this

John
johnfcturner
 
Posts: 4
Joined: Wed Dec 19, 2007 8:54 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests