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
    Commented 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
    Commented 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
    Commented Aug 11, 2017 at 14:38
  • @JeffC, Wed Aug 9 is not 8/10/17 at all Commented Aug 11, 2017 at 14:43
  • It will be better, if you put input and desired output data examples in to your question.
    – MiniMax
    Commented Aug 11, 2017 at 14:44

3 Answers 3

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
    Commented Aug 11, 2017 at 15:18
  • That's easy: perl -i -M.... Commented 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
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .