Counting time periods with awk?

Question

Data is in file data.csv with the next format:

11;Study area;Class name G1-T1-(1031-1-32517);English;Monday;17:30;20:30;S31;Peter Gabriel

From fields 6 (start time) and 7 (end time) I need to obtain the number of 30 minutes periods between them, and print them in 7th field place, I mean, I need to take out end time and put the resulting periods instead, so the line would result in:

11;Study area;Class name G1-T1-(1031-1-32517);English;Monday;17:30;6;S31;Peter Gabriel

The idea was to split the hours from the minutes, multiply the hours by 60, add the minutes. All of this applyied to the start and end times, then do the substraction, and finally divide by 30:

Start time: 17:30 → (17*60)+30 = 1050

End time: 20:30 → (20*60)+30 = 1230

Substraction: 1230 - 1050 = 180/30 = 6 (periods of 30 minutes)

The code I've come up with is the following one:

awk -F';' -v OFS=';' '{printf "%s;%s;%s;%s;%s;%s;%s;%s;%s\n", $1,$3,$4,$5,$6,split($7,a,":"),a[1]*60+a[2]-split($6,b,":"),b[1]*60+b[2]/30,$8,$9,$10}' data.csv

The result I get is obviously not the desired one, giving me a series of problems...

11;Study area;Class name G1-T1-(1031-1-32517);English;Monday;17:30;2;1228;1021;S31

After field 6 (start time), I get the amount of elements the hour is splited in: 2 (Not needed).

Then 1228, that seems to come from substracting the amount of elements the starting time is splited in (2) from 1230 (result of the operation in end time)

Then 1021, I don't know from where

Then S31, which is correct but has been displaced one field further, making disapear original last field: Peter Gabriel

I could not figure out how to apply the division by 30 to the substraction result (I've tried with different parenthesis types), and I'm not certain if it is actually applied only to the second member of the substraction...

Probably my approach is totally wrong, sorry, I still can not figure out awk, I'm trying though.

Answer 1

Using awk

$  awk 'BEGIN{FS=OFS=";"} {split($6,a,":");split($7,b,":");$7=(((b[1]*60)+b[2])-((a[1]*60)+a[2]))/30}1' input_file
11;Study area;Class name G1-T1-(1031-1-32517);English;Monday;17:30;6;S31;Peter Gabriel