Sed print with evaluated date command on back reference

Question

I have a file (as one often does) with dates in *nix time as seconds from the Epoch, followed by a message and a final "thread" field I am wanting to select. All separated with a '|' as exported from a sqlite DB...

e.g

1306003700|SENT|21
1277237887|SENT|119
1274345263|SENT|115
1261168663|RECV|21
1306832459|SENT|80
1306835346|RECV|80

Basically, I can use sed easily enough to select and print lines that match the "thread" field and print the respective times with messages, thus:

> cat file | sed -n "s/^\([0-9]*\)\|\(.*\)\|80$/\1 : \2/p"
1306832459 : SENT
1306835346 : RECV

But what I really want to do is also pass the time field through the unix date command, so:

> cat file | sed -n "s/^\([0-9]*\)\|\(.*\)\|80$/`date -r \1` : \2/p"

But this doesn't seem to work - even though it seems to accept it. It just prints out the same (start of Epoch) date:

Thu  1 Jan 1970 01:00:01 BST : SENT
Thu  1 Jan 1970 01:00:01 BST : RECV

How/can I evaluate/interpolate the back reference \1 to the date command?

Maybe sed isn't the way to match these lines (and format the output in one go)...

Answer 1

awk is perfect for this.

awk -F"|" '$3 == '80' { print system("date -r " $1), ":", $2 }' myfile.txt

Should work.(Can't guarantee that the system call is right though, didn't test it)

Answer 2

This pure bash

wanted=80
(IFS=\|; while read sec message thread
do
        [[ $thread == $wanted ]] && echo $(date -r $sec) : $message
done) < datafile.txt

print

Tue May 31 11:00:59 CEST 2011 : SENT
Tue May 31 11:49:06 CEST 2011 : RECV

You can quote variables in " " for the better safety...