1

I have a file with the following input sample data:

1137921146.499 180900 61.153.158.197 1409 
1137921158.698 181622 61.153.158.197 1409
1137921758.163 180026 221.226.124.114 1374
1137921802.016 179485 121.13.128.132 1409

the 1st column is unix epoch timestamp which i need to convert to human readable format plus i want the data to be delimited as follows

Sun Jan 22 01:12:26 PST 2006|180900|61.153.158.197|1409   
Sun Jan 22 01:12:38 PST 2006|181622|61.153.158.19|1409

i tried to add delimeters using sed 's/ {1,}/|/g' and converting the date using date -d @1137921146.499. But i am unable to club these two together in one command..

  • Downvoted because the problem consists of two separate problems and each can be found simply via a quick search. – ceremcem May 23 '17 at 7:11
  • 1
    After the edit it's okay, I think. He knows how to solve each problem, but not how to do combine it – Philippos May 23 '17 at 7:27
4

You can use awk program like this:

awk '{ print strftime("%c",$1)"|" $2"|"$3"|"$4 }' file

the core is to use strftime function to convert epoch to date format

Here is the output:

#awk '{ print strftime("%c",$1)"|" $2"|"$3"|"$4 }' file
Sun Jan 22 10:12:26 2006|180900|61.153.158.197|1409
Sun Jan 22 10:12:38 2006|181622|61.153.158.197|1409
Sun Jan 22 10:22:38 2006|180026|221.226.124.114|1374
Sun Jan 22 10:23:22 2006|179485|121.13.128.132|1409

P.S. Or you can use implicit output delimiter:

awk  'BEGIN { OFS="|"} {$1= strftime("%c",$1) }1' file
1

Or with your shell:

while read timestamp pid ip port; do
  echo "$(date -d @$timestamp)|$pid|$ip|$port"
done <yourfile
1

Using what you already know:

  • GNU date can convert a timestamp to a formatted date by giving it @timestamp.
  • Replacing spaces by | will give you the output you want.

To that, we add

  • GNU date may operate on a file, converting dates in one batch.

To batch convert dates with GNU date we have to extract the timestamps and prefix them with @:

$ sed 's/^\([^ ]*\).*$/@\1/' data.in
@1137921146.499
@1137921158.698
@1137921758.163
@1137921802.016

The sed expression substitutes each line with the first space-delimited field prefixed with @.

With bash (and ksh93, or any shell that understands process substitutions):

$ date -f <( sed 's/^\([^ ]*\).*$/@\1/' data.in )
Sun Jan 22 10:12:26 CET 2006
Sun Jan 22 10:12:38 CET 2006
Sun Jan 22 10:22:38 CET 2006
Sun Jan 22 10:23:22 CET 2006

Then we need to take the other fields of the input data and replace the delimiters:

$ cut -d ' ' -f 2- data.in | tr ' ' '|'
180900|61.153.158.197|1409
181622|61.153.158.197|1409
180026|221.226.124.114|1374
179485|121.13.128.132|1409

Then we paste these two things together with a | as delimiter:

$ paste -d '|' <( date -f <( sed 's/^\([^ ]*\).*$/@\1/' data.in ) ) <( cut -d ' ' -f 2- data.in | tr ' ' '|' )
Sun Jan 22 10:12:26 CET 2006|180900|61.153.158.197|1409
Sun Jan 22 10:12:38 CET 2006|181622|61.153.158.197|1409
Sun Jan 22 10:22:38 CET 2006|180026|221.226.124.114|1374
Sun Jan 22 10:23:22 CET 2006|179485|121.13.128.132|1409

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.