views:

274

answers:

2

I have a file containing many columns of text, including a timestamp along the lines of "Fri Jan 02 18:23" and I need to convert that date into MM/DD/YYYY HH:MM format.

I have been trying to use the standard 'date' tool with awk getline to do the conversion, but I can't quite figure out how to pass the fields into the 'date' command in the format it expects (quoted with " or 's,) as getline needs the command string enclosed in quotes too.

Something like "date -d '$1 $2 $3 $4' +'%D %H:%M'" | getline var

Now that I think about it, I guess what I'm really asking is how to embed awk variables into a string.

A: 

you can try this. Assuming just the date you specified is in the file

awk '
{
    cmd ="date \"+%m/%d/%Y %H:%M\" -d \""$1" "$2" "$3" "$4"\""
    cmd | getline var
    print var
    close(cmd)
}' file

output

$ ./shell.sh
01/02/2010 18:23

and if you are not using GNU tools, like if you are in Solaris for example, use nawk

nawk 'BEGIN{
   m=split("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec",d,"|")
   for(o=1;o<=m;o++){
      months[d[o]]=sprintf("%02d",o)
   }
   cmd="date +%Y"
   cmd|getline yr
   close(cmd)
}
{
    day=$3
    mth=months[$2]
    print mth"/"day"/"yr" "$4
} ' file
ghostdog74
Relying on a non-standard 'date' flag and then a pipe isn't even as legitimate as calling an inline Perl script to do the work.
gary
@gary: a bit harsh. If he has gnu tools, why not use their capabilities?
glenn jackman
@glenn: Maybe ghostdog has gnu, but OP was asking about awk. And rather than executing an external command (date) for each line, he really should have thought more.
gary
@gary, firstly, i may have interpreted the qns wrongly. since OP uses date -d , I assume he uses GNU date. Then if he has GNU date, i assume he has gawk. Secondly, i am only showing him how to do it the way he had wanted it. I added in the rest myself, but FWIW, he may only want to process 1 or few lines. Of course i don't recommend calling date for every line like what i say here http://stackoverflow.com/questions/2114958/processing-apache-logs-quickly/2115940#2115940. Lastly, OP should come and clarify what OS he is using before we even make conclusions
ghostdog74
Thanks for taking the time to answer my question. Building the 'cmd' string before the getline and pipe is all I actually needed. I'm on Ubuntu 9.10, and only working a few dozen lines at a time.
PStibbons
@ghostdog: OK, my error. OP did have GNU. I guess I thought an experienced user should have at least suggested a solution without an external cmd for each line, like Dennis's original or your second solution.
gary
+2  A: 

If you're using gawk, you don't need the external date which can be expensive to call repeatedly:

awk '
BEGIN{
   m=split("Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec",d,"|")
   for(o=1;o<=m;o++){
      months[d[o]]=sprintf("%02d",o)
    }
format = "%m/%d/%Y %H:%M"
}
{
split($4,time,":")
date = (strftime("%Y") " " months[$2] " " $3 " " time[1] " " time[2] " 0")
print strftime(format, mktime(date))
}'

Thanks to ghostdog74 for the months array from this answer.

Dennis Williamson
+1 for letting awk do all the work. Requires gawk for `strftime()`
glenn jackman
"If you're using gawk...". Thanks for the +1.
Dennis Williamson