Sed print with evaluated date command on back reference

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

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)

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

Perl is handy here:

perl -MPOSIX -F'\|' -lane '
    next unless $F[2] == "80";
    print(strftime("%Y-%m-%d %T", localtime $F[0]), " : ", $F[1])
' input.file

This might work for you:

 sed -n 's/^\([0-9]*\)|\(.*\)|80$/echo "$(date -d @\1) : \2"/p' file | sh

or if you have GNU sed:

 sed -n 's/^\([0-9]*\)|\(.*\)|80$/echo "$(date -d @\1) : \2"/ep' file

Using awk:

$(awk -F'|' '/80$/{printf("echo $(date -d @%s) : %s;",$1,$2);}' /path/to/file)

The date command will be executed before the sed command.

It may be easiest to break out perl or python for this job, or you can use some kind of bash loop otherwise.

Inspired by Rafe's answer above:

awk -F"|" '$3 == '80' { system("date -d @" $1 " | tr -d \"\n\""); print " :", $2 }' myfile.txt

For my version of date, it seems that -d is the argument, instead of -r, and that it requires a leading @. The other change here is that the system call executes (so date actually does the printing, newline and all - hence the tr) and returns the exit code. We don't really care to print the exit code (0), so we move the system call outside of any awk prints.