Using AWK; System, Date: Convert data field from c

Question

Hello i have some csv files like that:

"N.º","Fecha Tiempo, GMT-03:00","Temp, °C (LGR S/N: 10466185, SEN S/N: 10466185, LBL: Temperatura)","Acoplador separado (LGR S/N: 10466185)","Acoplador adjunto (LGR S/N: 10466185)","Host conectado (LGR S/N: 10466185)","Parado (LGR S/N: 10466185)","Final de archivo (LGR S/N: 10466185)"
1,03/03/14 01:00:00 PM,25.477,Registrado,,,,
2,03/03/14 02:00:00 PM,24.508,,,,,
3,03/03/14 03:00:00 PM,26.891,,,,,
4,03/03/14 04:00:00 PM,25.525,,,,,
5,03/03/14 05:00:00 PM,27.358,,,,,

Then i wanna convert the second field of data-hour in two fields: date, hour

I'm ok with split date and hour, but when i try to convert hours in am-pm to hours in 24hrs i failed.

Using for all files this command:

awk -F"," '{print $2}' *.csv|awk '{print $1","$2" "$3}'

I'm arriving to that command, in particular:

echo "11:04:44 PM" | awk -F,  -v hora=$1 '{system("date --date=$hora +%T");print $hora}'
00:00:00
11:04:44 PM

The problem is the variable inside system(date... beacuse it returns 0 or empty.

Then the question is about how to do thath. And finnally how to insert tath changes inside the file.

Thanks, very thanks!

Answer 1

Thanks! Now, after many hours i can convert the time using 'date', then the code is:

echo "11:04:44 PM" | awk -F, -v hora=$1 '{system("date --date=\""$hora"\" +%T");print $hora}'

With thaat you can compare the time in 24hrs and AM/PM

The details was the '\"' before and after the '$hora' variable

;)

Then, to converte the complete DAte-Hour from the csv file you have to put:

awk -F"," '{if (FNR>=3) print $2}' *.csv | awk '{print $1","$2" "$3}'| awk -F, '{system("printf "$1", &  date --date=\""$2"\" +%T")}'

Now, i have to design a new file qith the id and values columns....

Answer 2

On my machine (Mac OS), the command you need is

echo "11:22:33 AM" | awk '{split($1,a,":"); if($2=="PM") {a[1]=a[1]+12;} print a[1] ":" a[2] ":" a[3]}'

This does the splitting of the time manually (rather than relying on date which is a bit platform dependent) and adds 12 to the time if it's PM.

So the whole thing becomes:

awk -F"," '{print $2}' *.csv | awk '{split($1,a,":"); if($2=="PM") {a[1]=a[1]+12;} print a[1] ":" a[2] ":" a[3]}'

Although you really want to skip the first line in the file, so

awk -F"," 'if(NR>1){print $2}' *.csv | awk '{split($1,a,":"); if($2=="PM") {a[1]=a[1]+12;} print a[1] ":" a[2] ":" a[3]}'