[SOLVED] SOLVED: awk: d/m/yyyy h:mm to yyyy-mm-dd hh:mm:ss

geetalicious · 05-04-2011, 11:36 PM

Hi,

I'm trying to process a field
'd/m/yyyy h:mm' to 'yyyy-mm-dd hh:mm:ss' for sql inserts using awk.

I have figured out how to split date only using the following, but am a little confused on include the time part.

Code:

split(b,a,"/")
sprintf("%s-%02d-%02d",a[3],a[2],a[1])

Thanks in advance

druuna · 05-05-2011, 02:41 AM

Hi,

Do you have to use awk?

You could use the date command to change the format to just about anything you like:

Code:

$ ORGDATE="3/11/2010 6:59"
$ date --date="$ORGDATE" '+%Y-%m-%d %H:%M:%S'
2010-03-11 06:59:00

Have a look at the date man page to check out all the other % options.

Hope this helps.

Nominal Animal · 05-05-2011, 03:18 AM

Quote:

Originally Posted by geetalicious

Hi,

I'm trying to process a field
'd/m/yyyy h:mm' to 'yyyy-mm-dd hh:mm:ss' for sql inserts using awk.

Assume you have the field in variable timestamp. First, split it to e.g. timefield array, setting the seconds part to zero if not specified:

Code:

timefields = split(timestamp, timefield, /[\t\n\v\f\r \/:]+/)
if (timefields < 6)
    timefield[6] = 0

Note that it would be a good idea to check you do have at least five timefields, otherwise the timestamp is garbled. Also, you could check the century, unless you are sure your timestamps have the full year.

Finally, reformat the fields the way you prefer. Note that I use %.0f instead of %d for seconds, for proper rounding in case it has a fractional part.

Code:

formattedtime = sprintf("%04d-%02d-%02d %02d:%02d:%02.0f",
                        timefield[3], timefield[2], timefield[1],
                        timefield[4], timefield[5], timefield[6])

Hope this helps,