Date Manipulation

Shell Programming and Scripting
1

I have a file with a field containing the following:

"7/3/2009 7:07:12 PM","xxxx"

I need to be able to split this field up into two into a different format with the time being converted into 24 hour:

so that i can get the following:

"20090307","19:07:12","xxxx"

2

date +%Y%m%d This format will output like current date in the following format
20100302

date %R:%S will give 24_hour:minute:second
Example 15:09:52

3

Thanks for the reply,

I have over 1000 different date and time formats, what you have said above if i did that in my script it would work of system date, how can i get it to read the actual date and time specified in the file

4

Is your file format is fixed character length ???

5

Simplified to understand...can be optimized.

#!/usr/bin/perl
$str='"7/3/2009 09:07:12 PM","xxxx"' ;
$str =~ s/\"//g ;
@arr=split(/,/, $str);
@datestr=split(/ /,$arr[0]);
@daystr=split(/\//,$datestr[0]);
@timearr=split(/:/, $datestr[1]);
$merid=$datestr[2];

if ($merid =~ m/PM/ )
{
	if ( $merid ge 10 ) {
           $hour = 12 + $timearr[0];
           }
        else {
           $hour= 24 - $timearr[0];
       }
}
else
{
$hour=$timearr[0];
}

if ($daystr[0] =~ m/\d\d/) {
       $month=$daystr[0];
  }
else
{
    $month="0".$daystr[0];
}
if ($daystr[1] =~ m/\d\d/) {
       $day=$daystr[1];
  }
else
{
    $day="0".$daystr[1];
}
$year=$daystr[2];

$daystr='"'.$year.$day.$month.'", ';
$timestr='"'.$hour.":".$timearr[1].":".$timearr[2].'", ';
$val='"'.$arr[1].'"';

printf("%s%s%s",$daystr,$timestr,$val,);

cheers,
Devaraj Takhellambam

6

You can use the following script to read the data from file and printing it in the required format.

arr=`sed 's/"//g;s/,/ /g;' file`

for((i=0;i<${#arr[@]};i++))
do
date=`echo ${arr[$i]} | cut -d ' ' -f 1`

day=`echo $date | cut -d '/' -f 1`

mon=`echo $date | cut -d '/' -f 2`

year=`echo $date | cut -d '/' -f 3 `

time=`echo ${arr[$i]} | cut -d ' ' -f 2`

format=`echo ${arr[$i]} | cut -d ' ' -f 3`

data=`echo ${arr[$i]} | cut -d ' ' -f 4-`

hour=`echo $time | cut -d ':' -f 1 `

Minsec=`echo $time | cut -d ':' -f 2-`

if [[ $format == 'PM' ]]
then
let hour+=12
fi
mon=`printf "%02d" $mon`
day=`printf "%02d" $day`
echo \"$year$mon$day\",\"$hour:$Minsec\",\"$data\"
done
7

thanks for all the replies,

vivekraj, i get a syntax error in your code

syntax error at line 12 : `((' unexpected

I'm using KSH (solaris)

8

If you have gawk, and try to learn awk, here is tip for you:

$ cat time_format.awk
#!/usr/bin/gawk -f
BEGIN {
time=mktime("2009 03 07 17 07 12")
format ="\"%Y%m%d\",\"%H:%M:%S\"";
print strftime(format, time);
exit;
}

$ gawk -f time_format.awk
"20090307","17:07:12"
9

The following filter (reads from standard input and writes to standard output) could probably have been simplified to a reasonably succinct AWK oneliner if one could assume that there are no commas or spaces in the "xxxx" field (in which i assume xxxx is a placeholder for other data). Without such assurances, splitting on spaces or commas would be unwise.

#!/bin/sh

while read line; do
    echo "$(echo "${line%%,*}" | awk -F'[\":/ ]' '{printf("\"%s%02d%02d\",\"%02d:%s:%s\"\n",$4,$3,$2,$8=="PM"?$5+12:$5,$6,$7)}'),${line#*,}"
done