Bash script to convert the 2nd column in csv file from Unix time to readable date / time

Question

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

Answer 1

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

Answer 2

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

Answer 3

Using Miller (mlr) to format the Unix timestamp using strftime_local(). Note that the generated time+date is in the local timezone:

$ mlr --csv put '$DATE_TIME_CREATED = strftime_local($DATE_TIME_CREATED,"%c")' file
HOST,DATE_TIME_CREATED,USER_ID,EVENT_CLASS
xxxx,Wed Aug  9 13:43:00 2017,xxxx,xxxx
xxxx,Wed Aug  9 12:05:47 2017,xxxx,xxxx

See the strftime(3) manual on your system for formatting strings you can use. The %c format "is replaced by national representation of time and date".