[Solved] Timestamp conversion

[johnfcturner]

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.

[acknak]

It would be a lot simpler to make a suggestion if you gave a couple of samples.

[johnfcturner]

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

[probe1]

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.

[johnfcturner]

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

[huw]

If you want to do it in Calc in future

Code: Select all

=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.

[johnfcturner]

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

John