3

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

2
  • Downvoted because the problem consists of two separate problems and each can be found simply via a quick search.
    – ceremcem
    May 23, 2017 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, 2017 at 7:27

3 Answers 3

10

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
3
  • This is good. What if you don't know how many columns there are? can you put $2* or similar to say 'for column 2 and after'?
    – majorgear
    Sep 18, 2023 at 15:41
  • 1
    @majorgear, the second example I provide deal with unknown number of columns Sep 18, 2023 at 18:45
  • 1
    I missed that! It works great. thx
    – majorgear
    Sep 18, 2023 at 20:40
2

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
1

Or with your shell:

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

You must log in to answer this question.

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