Sed with inner command and with regex group reference

Question

I've been struggling with the following problem:

I have a file with the following content

1521471079313,219,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,177,0,35
1521471080337,263,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,169,0,0
1521471081404,245,HTTP Request 12,200,OK,PROD 50 rpm 1-10,text,true,,5134,201,1,1,210,0,37
1521471082453,125,HTTP Request 13,200,OK,PROD 50 rpm 1-10,text,true,,8910,201,1,1,106,0,0
1521471083381,217,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,188,0,0
1521471084402,303,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,226,0,41

the first item in the list is an epoc timestamp and I want to convert it to a human readable one.

I tried the following command

cat file.csv|sed -E  "s/^([0-9]*)(,.*)/$(date -r \1 '+%m-%d-%Y:%H:%M:%S')\2/p"

And it seemed to work but then I saw that it would convert it to:

01-01-1970:01:00:01,245,HTTP Request 13,200,OK,PROD 50 rpm 1-10,text,true,,8910,201,1,1,219,0,43
01-01-1970:01:00:01,276,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,217,0,0
01-01-1970:01:00:01,276,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,217,0,0
01-01-1970:01:00:01,242,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,216,0,34
01-01-1970:01:00:01,242,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,216,0,34
01-01-1970:01:00:01,147,HTTP Request 12,200,OK,PROD 50 rpm 1-10,text,true,,5134,201,1,1,119,0,0
01-01-1970:01:00:01,147,HTTP Request 12,200,OK,PROD 50 rpm 1-10,text,true,,5134,201,1,1,119,0,0

all timestamps look like "the beginning of time :-)" and not what I wanted.

I know that I have a command substitution in the sed and also two group references based on the former regex in the sed command but why it does not work stumps me.

Answer 1

You can parse a csv file easily with GNU awk. Your $1 value is an EPOCH value in milli-seconds. You can use strftime() call to print the human readable format, after dividing the value by 1000 (i.e. to convert to seconds)

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

and for in-place edit, use gawk or move the output to a temporary file and revert it back to the original

tmpfile=$(mktemp /tmp/abc.XXXXXX)
awk 'BEGIN{FS=OFS=","}{$1=strftime("%c",($1/1000))}1' file > "$tmpfile"
mv "$tmpfile" file

Answer 2

This works for me:

sed -r "s/^([0-9]+)(,.*)/echo \$(date -d @\1)\2/" sampl3.log > log.sh && bash log.sh  

But you have to be secure, that no evil commands are contained in the log, and adapt your date format.

Mi 2. Jul 01:21:53 CEST 50183,219,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,177,0,35
Mi 2. Jul 01:38:57 CEST 50183,263,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,169,0,0
Mi 2. Jul 01:56:44 CEST 50183,245,HTTP Request 12,200,OK,PROD 50 rpm 1-10,text,true,,5134,201,1,1,210,0,37
Mi 2. Jul 02:14:13 CEST 50183,125,HTTP Request 13,200,OK,PROD 50 rpm 1-10,text,true,,8910,201,1,1,106,0,0
Mi 2. Jul 02:29:41 CEST 50183,217,HTTP Request 14,200,OK,PROD 50 rpm 1-10,text,true,,17665,204,1,1,188,0,0
Mi 2. Jul 02:46:42 CEST 50183,303,HTTP Request 11,200,OK,PROD 50 rpm 1-10,text,true,,30268,202,1,1,226,0,41