This question already has an answer here:

I tried the various solutions offered in the question of which mine appears to be a duplicate. Some of them did not actually convert the time, one of them did convert the time but the year was wrong and it did not replace the existing value in the file, it added a new column to the output. The answer I accepted below met my needs perfectly.

Sorry if this is a duplicate, I searched extensively and didn't find questions matching my situation. I use Linux frequently but my "programming" skills are limited to simple shell scripts.

I have a csv file with a column of date/time stamps written in the epoch (i.e. Unix) format. Please see below example. I would like to convert and replace the Unix time with the ISO 8601 format.

Example of existing output:

foo, 1439856000000000000, foo, foo
foo, 1439856360000000000, foo, foo
foo, 1439856720000000000, foo, foo
foo, 1439857080000000000, foo, foo

Example of Desired output:

foo, YYYY-MM-DD HH:MM:SS.nnnnnnnnn, foo, foo
foo, YYYY-MM-DD HH:MM:SS.nnnnnnnnn, foo, foo
foo, YYYY-MM-DD HH:MM:SS.nnnnnnnnn, foo, foo
foo, YYYY-MM-DD HH:MM:SS.nnnnnnnnn, foo, foo

marked as duplicate by Stephen Harris, drewbenn, thrig, Jeff Schaller, Anthon Aug 17 '16 at 17:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Are the timestamps always in the same columns (that you know ahead of time), or in random columns? (Also: don't forget to write out the time zone!) – drewbenn Aug 17 '16 at 16:25
  • As far as I know they are, yes. – Emily Shaffer Aug 19 '16 at 14:40
  • From what I can see, the two questions have the exact same requirements, so it is indeed a duplicate. If the answers present in the other question are not good, a new one needs to be put there. – Julie Pelletier Aug 19 '16 at 17:13

I'd use perl:

perl -MPOSIX -pe 's{\b\d{10}(?=\d{9}\b)}{
  strftime("%Y-%m-%d %T.", localtime $&)}ge'

(here giving the time in local time, use gmtime instead of localtime for GMT times)

  • This did exactly what I wanted. It replaced the existing UNIX time with the format I needed. – Emily Shaffer Aug 19 '16 at 15:07
  • Can you please put this answer on the duplicated question? – Julie Pelletier Aug 19 '16 at 17:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.