Converting dates in AWK

Question

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.

Answer 1

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.

Answer 2

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