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
  • 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 '17 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 '17 at 14:38
  • [mvf@radwhtdb01:/usr/mvf/bin] $ format-unix-clock 1502280000 Wed Aug 9 08:00:00 EDT 2017 – Jeff C Aug 11 '17 at 14:38
  • @JeffC, Wed Aug 9 is not 8/10/17 at all – RomanPerekhrest Aug 11 '17 at 14:43
  • It will be better, if you put input and desired output data examples in to your question. – MiniMax Aug 11 '17 at 14:44
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
  • 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 '17 at 15:18
  • That's easy: perl -i -M.... – glenn jackman Aug 11 '17 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.