1

My input file is a comma separated .csv. I'd like to convert the 2nd columns from unix time to a readable date time format like this...

before   1502280000
after    8/10/17 08:00:00

I'd also like to ignore the first row as it is headers.

I'm running in a bash shell on Solaris 10

the first two rows of my csv look like this

HOST,DATE_TIME_CREATED,USER_ID,EVENT_CLASS
xxxx,1502286180,xxxx,xxxx
xxxx,1502280347,xxxx,xxxx

looking for output of

HOST,DATE_TIME_CREATED,USER_ID,EVENT_CLASS
xxxx,Wed Aug  9 09:43:00 EDT 2017,xxxx,xxxx
xxxx,Wed Aug  9 08:05:47 EDT 2017,xxxx,xxxx
8
  • How does 1502280000 convert to 8/10/17 08:00:00. Can you clarify the format of your source date with something like so: YYMMDDHHMM
    – jesse_b
    Aug 11, 2017 at 14:36
  • my source data is a unix date time stamp if I use format-unix clock at the cmd line I get the readable time
    – Jeff C
    Aug 11, 2017 at 14:38
  • [mvf@radwhtdb01:/usr/mvf/bin] $ format-unix-clock 1502280000 Wed Aug 9 08:00:00 EDT 2017
    – Jeff C
    Aug 11, 2017 at 14:38
  • @JeffC, Wed Aug 9 is not 8/10/17 at all Aug 11, 2017 at 14:43
  • It will be better, if you put input and desired output data examples in to your question.
    – MiniMax
    Aug 11, 2017 at 14:44

2 Answers 2

4

I would strongly recommend the time format YYYY-mm-dd HH:MM:SS -- that format is unambiguous, and it sorts lexically and chronologically.

You can use perl for this:

perl -MPOSIX=strftime -F, -ane '
    $F[1] = strftime("%F %T", localtime $F[1]) if $. > 1; 
    print join ",", @F
' file
2
  • thanks glenn this looks great, now i just need to overwrite my input file, with the new data, instead of printing to the screen.
    – Jeff C
    Aug 11, 2017 at 15:18
  • That's easy: perl -i -M.... Aug 11, 2017 at 16:36
1

The awk approach:

awk -F, 'NR>1{printf"%s,",$1;
             system("printf \"$(date -d @"$2")\"");printf",%s,%s\n",$3,$4}
' infile.txt
HOST,DATE_TIME_CREATED,USER_ID,EVENT_CLASS
xxxx,Wed Aug  9 09:43:00 EDT 2017,xxxx,xxxx
xxxx,Wed Aug  9 08:05:47 EDT 2017,xxxx,xxxx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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