Daniel S
Daniel S

Reputation: 609

Bash data processing on certain column

i have a text file in this format:

a0,b0,c0,27/Aug/2014:23:58,e0
a1,b1,c1,27/Aug/2014:23:58,e1
a2,b2,c2,27/Aug/2014:23:58,e2
a3,b3,c3,27/Aug/2014:23:58,e3
a4,b4,c4,27/Aug/2014:23:58,e4

and at the end i need to come up with

a0,b0,c0,28 Aug 2014 05:58,e0
a1,b1,c1,28 Aug 2014 05:58,e1
a2,b2,c2,28 Aug 2014 05:58,e2
a3,b3,c3,28 Aug 2014 05:58,e3
a4,b4,c4,28 Aug 2014 05:58,e4

and i have to

sed 's/\//\ /g' | sed 's/:/\ /'

to format the date

and then i need to do something similar :

date -d "19 Aug 2014 13:51:23 6 hours"

to get the hour to advance 6 hours

but the problem i stumble upon is how can i get all these actions only on that row?

Upvotes: 0

Views: 92

Answers (4)

potong
potong

Reputation: 58578

This might work for you (GNU sed & shell):

sed -r 'h;s/.*,.*,.*,(.*),.*/\1/;s/\// /g;s/:/ /;s/.*/date -d "& 6 hours" +"%d %b %Y %H:%M"/e;H;g;s/([^,]*)(,[^,]*)\n(.*)/\3\2/' file

Extract the date from the file, use the date command to add 6 hours and format the return date, then substitute the new date back into the original line.

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 204731

With GNU awk for time functions:

$ cat tst.awk
BEGIN{ FS=OFS="," }
{
    split($4,t,/[\/:]/)
    mthNr = (match("JanFebMarAprMayJunJulAugSepOctNovDec",t[2])+2)/3
    secs  = mktime(t[3]" "mthNr" "t[1]" "t[4]" "t[5]" 0") + (6*60*60)
    $4    = strftime("%d %b %y %H:%M", secs)
    print
}
$
$ awk -f tst.awk file
a0,b0,c0,28 Aug 14 05:58,e0
a1,b1,c1,28 Aug 14 05:58,e1
a2,b2,c2,28 Aug 14 05:58,e2
a3,b3,c3,28 Aug 14 05:58,e3
a4,b4,c4,28 Aug 14 05:58,e4

Upvotes: 1

choroba
choroba

Reputation: 242443

Perl to the rescue:

perl -MTime::Piece -naF, -e '$t = Time::Piece->strptime($F[3], "%d/%b/%Y:%H:%M") + 6 * 60 * 60;
                             $F[3] = $t->strftime("%d %b %Y %H:%M");
                             print join ",", @F;' input-file

Explanation:

  • Time::Piece is a module that handles dates and times (both formatting and arithmetics).
  • strptime is the function to parse dates to objects.
  • 6 * 60 * 60 is the number of seconds in 6 hours.
  • $F[3] is the fourth column when Perl is invoked with -a, plus F, tells it to split columns on commas.
  • strftime formats the object back to a string.

Upvotes: 2

fedorqui
fedorqui

Reputation: 290525

Some date formatting through awk:

$ awk -F, -v OFS="," '{gsub("/"," ",$4); split($4,a,":"); cmd="date \"+%d %b %Y %H:%M\" -d \""a[1]" "a[2]":"a[3]" 6 hours \""; cmd | getline var; $4=var}1' file
a0,b0,c0,28 Aug 2014 05:58,e0
a1,b1,c1,28 Aug 2014 05:58,e1
a2,b2,c2,28 Aug 2014 05:58,e2
a3,b3,c3,28 Aug 2014 05:58,e3
a4,b4,c4,28 Aug 2014 05:58,e4

Explanation

  • -F, -v OFS="," set comma as input and output field separators.
  • gsub("/"," ",$4); split($4,a,":") remove / on date and slice using : as delimiter.
  • cmd="date \"+%d %b %Y %H:%M\" -d \""a[1]" "a[2]":"a[3]" 6 hours \""; cmd | getline var calculate $date 6 hours and format accordingly. Value stored in var.
  • $4=var store in $4
  • 1 prints

Upvotes: 0

Related Questions