CODEWITHSUNDEEP
bashshellawkgawk
lys1030
lys1030

Reputation: 283

Use gawk to convert from human readable time in a file to unix time?

I am new to gawk. Based on this thread, I already made a gawk function to convert datetime such as "07,JUN,2015,06,PM" to unix time (millisecond): 

$ cat tst.awk
function cvttime(t, a) {
    split(t,a,/[,: ]+/)

    # fa0,07,DEC,2014,10,AM,862.209018
    #  =>
    #    a[2] = "07"                date
    #    a[3] = "DEC"               month
    #    a[4] = "2014"              year
    #    a[5] = "06"                time
    #    a[6] = "AM"                AM/PM

    if ( (a[6] == "PM") && (a[5] < 12) ) {
        a[5] += 12
    }

    match("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",a[3])
    a[3] = (RSTART+2)/3

    return( a[1]","mktime(a[4]" "a[3]" "a[2]" "a[5]" 00 0")"000,"a[7])
}

BEGIN {
    mdt = "fa0,07,DEC,2014,10,AM,862.209018"    
    ms = cvttime(mdt)
    print ms
}

In terminal, the following command gives me the correct unix time:

$ TZ=UTC gawk -f tst.awk

returns:

fa0,1417946400000,862.209018

Now, I have a file "input.csv" containing: 

aa1,07,DEC,2014,06,AM,282.485988
ac3,07,DEC,2014,07,AM,97.6757181
ef3,07,DEC,2014,08,AM,112.816554
ag3,07,DEC,2014,09,AM,101.479961
fa0,07,DEC,2014,10,AM,862.209018

How should I modified the gawk function and the shell command to pass in "input.csv" and generate "output.csv" containing:

aa1,1417932000000,282.485988
ac3,1417935600000,97.6757181
ef3,1417939200000,112.816554
ag3,1417942800000,101.479961
fa0,1417946400000,862.209018

Thanks in advance!

Upvotes: 1

Views: 356

Answers (2)

Davison
Davison

Reputation: 71

You could use something like this:

awk -F, '{"date -d "$3"\" \""$2"\" \""$5"\" \""$6"\" \""$4" '+%s'" | getline d; print $1","d"000,"$NF""}' input.csv > output.csv

or 

awk -F, '{"date -u -d "$3"\" \""$2"\" \""$5"\" \""$6"\" \""$4" '+%s'" | getline d; print $1","d"000,"$NF""}' input.csv > output.csv

If you want set the flag -u from date command

-u, --utc, --universal print or set Coordinated Universal Time

Upvotes: 1

Zombo
Zombo

Reputation: 1

Well this is confusing because your input times do not match your output times, but I think this does what you want:

BEGIN {
  FS = OFS = ","
}
{
  # fix year
  $4 += 2000
  # fix month
  match("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC", $3)
  $3 = (RSTART + 2) / 3
  # fix hour
  if ($6 == "PM" && $5 < 12)
    $5 += 12
  print $1, mktime($4 " " $3 " " $2 " " $5 " 0 0") * 1000, $NF
}

Upvotes: 0

Related Questions