CODEWITHSUNDEEP
shellunixawk
TyC
TyC

Reputation: 792

Round down to nearest quarter hour using AWK w/ file input

I have a comma delimited file where one field contains a datetime in the following format:

01/15/2014 13:53

I need to round the time portion of this field down to the nearest quarter hour. I can get awk to perform the rounding with just the date/time as the input, but I'm having trouble getting awk to read the file & perform the rounding. Here's the awk command used for rounding the date/time as direct input:

echo "04/17/2014 12:59" | awk '{split($2, a, ":"); printf "%s %s:%02d", $1, a[1],int(a[2]/15)*15}'

How would I perform this same rounding using an entire file as input with the date/time being the 3rd field in a comma delimited file?

Upvotes: 1

Views: 572

Answers (2)

janos
janos

Reputation: 124646

Using perl would be compact and straightforward:

$ echo "04/17/2014 12:59" | perl -pe 's|(?<=:)\d\d\b|int($&/15)*15|ge'
04/17/2014 12:45

Explanation:

  • The -pe flag of perl is to run the expression for each line, possibly modifying it, and then print the resulting line, behaving similar to sed or awk
  • The (?<=:)\d\d\b expression matches two digits following a : without including the : in the matched pattern (a zero-width positive look-behind). The \b marks a word boundary, so this won't match ":123"
  • The $& in the replacement is the matched pattern, in this example that's 59
  • The e flag in the s///e command is to evaluate the replacement as a perl script, allowing to perform the int(... / 15) * 15 arithmetic on the matched pattern
  • Instead of / in the s/// command we use | as the separator so that we can use / in the replacement pattern

Upvotes: 3

glenn jackman
glenn jackman

Reputation: 246799

awk -F, -v OFS=, '
    function round_time(t,   a) {
        split(t,a,/:/)
        return sprintf("%s:%02d", a[1], int(a[2] / 15)*15)
    }
    {$3 = round_time($3); print}
' << END
a,b,01/15/2014 13:53,d
e,f,01/15/2014 14:13,h
END

a,b,01/15/2014 13:45,d
e,f,01/15/2014 14:00,h

Upvotes: 2

Related Questions