Subtract 2 date columns in .csv file and get output as number of days

Shell Programming and Scripting
1

Hi,

I have one .csv file. I have 2 date columns present in file, column 2 and column 3.

I need to calculate how many days exist between 2 dates.

I am trying to subtract date column 2 from date column 3.
Eg: my file look likes

s.no, Start_date,End_Date

1, 7/29/2012,10/27/2012
2, 7/29/2012,8/11/2012

my output should be

90
13

like this.

I am not able to get command to calculate how many days exist between these 2 dates.
after that, As i need to check how many sundays exists between the dates. so I will divide number of days/7.
Please help.

2

Check out the following earlier posting in this forum: Yesterdays Date/Date Arithmetic

Then note that dividing the number of days by 7 will not tell you how many Sundays are between two dates unless the number of days is evenly divisible by 7. As an example, the number of Sundays between 12/29/2012 and 12/31/2012 is 1, but the number of Sundays between 1/29/2013 and 1/31/2013 is 0 even though the number of days between the start and end date is the same in both cases.

3

Can anyone please provide the simple function to subract date column 2 from date column 3 to get number of days as given in example above.

Thanks

4

It depends on what date utility you're using. If you're on a system where the desciprtion of the -d option on the date man page is something like:

     -d dst  Set the kernel's value for daylight saving time.  If dst is non-
             zero, future calls to gettimeofday(2) will return a non-zero for
             tz_dsttime

or if there is no -d option, you need to use one of the methods on the web site I referred you to in an earlier message in this thread. If you're on a system where the date man page description is something like:

       -d, --date=STRING
	      display time described by STRING, not `now'

Then you can use:

date -d "$value" "+%s"

to get the time in seconds for the given dates specifed by having $value set to the second field on the two lines you're talking about and then convert the seconds since the Epoch to days since the Epoch and subtract. You can use "+%a" to get the abbreviated weekday name corresponding to the given dates.

Neither -d nor +%s are defined by the standards, so any use of either of these is not portable between different systems.

5

Thanks for the help.

when I used this format like this:

date -d "12/12/2012" +%s

It's giving me result. But i am not able to give my file name with this format as need to change 2nd date column value into number from each line of my file.
Suppose my file name is File1.csv
I tried this

date -d "$2" +%s File1.csv. But it's not working.

6

Since there are spaces in the string you want output, you need quotes. Try:

date -d "$2" "+%s File1.csv"

This should work, but I can't test it on the systems available to me for testing. (None of them define the date -d option this way and none of them support %s as an output string format specifier.)

7

when I am using this function it's giving only 1 output as

1357624800 test1.csv

But I have around 100 records in my file and all records have date given in 2nd column

I should get list of numbers as output e.g.

1357624800
1357624822 like this. But getting wrong output.

8

This shows how calculating can be done

date1=$(date -d "$(echo "1, 7/29/2012,10/27/2012" | awk -F"[ ,]" '{print $3}')" "+%s")
date2=$(date -d "$(echo "1, 7/29/2012,10/27/2012" | awk -F"[ ,]" '{print $4}')" "+%s")
diff=$(($date2 - $date1))
echo "days =" $(($diff/86400))
days = 90
9 
while read line
do  
expr $(( $(date -d $(echo $line | awk -F"," '{print $3}') '+%s') / 86400)) - $(( $(date -d $(echo $line | awk -F"," '{print $2}') '+%s') / 86400 ))
done < filename.csv
10

Perl (without external modules):

perl -MTime::Local -F, -ane'
  @sd = split /\//, $F[1];
  @ed = split /\//, $F[2];
  printf "%s %d\n", $F[0], 
  ( timelocal(0, 0, 0, $ed[1], $ed[0] - 1, $ed[2] - 1900) -
    timelocal(0, 0, 0, $sd[1], $sd[0] - 1, $sd[2] - 1900) ) / 86400  
  ' infile

It seems that you're on Linux (your date implementation supports -d), so
probably you could use GNU awk as well:

awk -F, '{
  split($2, sd, "/")
  split($3, ed, "/")
  print $1, 
   (mktime(ed[3] s ed[1] s ed[2] s 0 s 0 s 0) - mktime(sd[3] s sd[1] s sd[2] s 0 s 0 s 0)) / 86400
  }' s=' ' infile