command to change date format

[Warren_9]

Hi,

I've a log file and the each line have 5 column and want to change the date format in the 4th column.

ORIG -- A:B:C:YYYYMMDDhhmmss:E
TO -- A:B:C:YYYY-MM-DD hh:mm:ss:E

i can write a script to convert it but would like to know any simple way or command, like awk command.

THANKS!!
Warren

[Peter Godron]

Warren,
rather messy:
http://forums1.itrc.hp.com/service/forums/bizsupport/questionanswer.do?threadId=641226
might be quicker to use cut -c

[Peter Nikitka]

Hi,

... like awk solution ...
I keep lines consisting not of 5 columns and invalid date columns unchanged:

awk 'NF==5 {n=split($4,ndate,":"); if (n!=5) {print;next}
year=substr(ndate[4],1,4)
mon=substr(ndate[4],5,2)
day=substr(ndate[4],7,2)
hour=substr(ndate[4],9,2)
min=substr(ndate[4],11,2)
sec=substr(ndate[4],13)
printf("%s %s %s %s:%s:%s:%s-%s-%s %s:%s:%s:%s %s\n", $1,$2,$3,ndate[1],ndate[2],ndate[3],year,mon,day,hour,min,sec,ndate[5], $5); next}
{print}' logfile >logfile.new

mfG Peter

[Warren_9]

hi mfG,

the command does not work ... the output file is same as the input file...!?

i tried to debug but can't fix it.

Thanks,
WAR.

[James R. Ferguson · Accepted solution]

Hi Warren:

# perl -pe 's/(.:.:.:)(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d):(.)/$1$2-$3-$4 $5:$6:$7:$8/' file

...and if you want to replace "in-place" do:

# perl -iold -pe 's/(.:.:.:)(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d):(.)/$1$2-$3-$4 $5:$6:$7:$8/' file

...this will leave "file" updated with a backup in "file.old".

Regards!

...JRF...

[Warren_9]

SIGH!!

how come both the solution not working on my server....!!

#perl -pe 's/(.:.:.:)(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d):(.)/$5:$6:$7:$8/' haha.log
A:B:C:200606060606:E

....is it bcoz of 06-06-06....?? bad luck on today!?

anyway thanks a lots for the reply.

WAR.

[James R. Ferguson]

Hi Warren:

Please post a snippet of your file. The solutions proposed assume the format of you posted description.

Regards!

...JRF...

[James R. Ferguson]

Hi Warren:

You posted as string like "A:B:C:200606060606:E" which looks like it does *not* include the seconds (ss) part.

For that you would need:

# perl -pe 's/(.:.:.:)(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d):(.)/$1$2-$3-$4 $5:$6:$7/' file

...for instance:

# echo " echo "A:B:C:200606060606:E"|perl -pe 's/(.:.:.:)(\d\d\d\d)(\d\d)(\d\d)(\d\d)(\d\d):(.)/$1$2-$3-$4 $5:$6:$7/'

Regards!

...JRF...

[Warren_9]

ya.... it is my fault...

JRF, your answner is work and date field does not have "sec".

mfG, i modified the awk and working also.

sed 's/:/ /g' FILE |
awk '{split($4,ndate);
year=substr(ndate[1],1,4)
mon=substr(ndate[1],5,2)
day=substr(ndate[1],7,2)
hour=substr(ndate[1],9,2)
min=substr(ndate[1],11,2)
printf("%s:%s:%s:%s-%s-%s %s:%s:%s\n",$1,$2,$3,year,mon,day,hour,min,$5); next}
{print}'

thx again for the reply.

WAR.

[H.Merijn Brand (procura]

lt09:/tmp 113 > cat test.dta
A:B:C:20060606060606:E
A:B:C:20060504030201:E
A:B:C:20050403020102:E
lt09:/tmp 114 > perl -paF: -e'$F[3]=sprintf"%2d%02d-%02d-%02d %02d:%02d:%02d",unpack"(A2)*",$F[3]and$_=join":",@F' test.dta
A:B:C:2006-06-06 06:06:06:E
A:B:C:2006-05-04 03:02:01:E
A:B:C:2005-04-03 02:01:02:E
lt09:/tmp 115 >

But I think it is a stupid^wnot so wise idea, as it introduces two new ':' characters that are already used as field separators, making parsing the file harder.

Enjoy, Have FUN! H.Merijn