Converting a date to friday date and finding Min/Max date

Dear all,

I have 2 questions.

I have a file with many rows which has date of the format YYYYMMDD.

  1. I need to change the date to that weeks friday date(Ex: 20120716(monday) to 20120720). Satuday/Sunday has to be changed to next week friday date too.

  2. After converting the date to friday date, The Minimum and maximum dates has to be derived.

Kindly help.
Thanks a lot in advance...

Is the only output to be the friday after the min and max dates?

What OS, what shell?

Please post the expected output to match your input.

What does "next week friday date" mean? Please explain.

I took it to mean July 28th to be rounded up to August 3rd.

I put those julian date functions into awk, since awk seems to work the same everywhere no matter your shell.

mute@ovh:~$ printf '%s\n' 20120728 20120726 20120520 | ./julian.awk
Friday after:
Min: 2012/05/25
Max: 2012/08/03

#!/usr/bin/awk -f

function mk_j(y,m,d,    a, j)
{
        a = int((14 - m) / 12);
        y = y + 4800 - a;
        m = m + 12 * a - 3;
        j = d + int((153*m + 2)/5) + 365*y + int(y/4) - int(y/100) + int(y/400) - 32045
        return j
}

function mk_g(jd,       l,n,i,j,d,m,y)
{
        l = jd + 68569
        n = int(( 4 * l ) / 146097)
        l = l - int(( 146097 * n + 3 ) / 4)
        i = int(4000 * ( l + 1 ) / 1461001)
        l = l - int(( 1461 * i ) / 4) + 31
        j = int(( 80 * l ) / 2447);
        d = l - int(( 2447 * j ) / 80);
        l = int(j / 11);
        m = j + 2 - ( 12 * l );
        y = 100 * ( n - 49 ) + i + l

        return sprintf("%04d/%02d/%02d",y,m,d)
}

{
        y=substr($0,1,4)
        m=substr($0,5,2)
        d=substr($0,7,2)
        dates[++i]=mk_j(y,m,d)
}

function next_fri(jd,   dow) {
        dow = jd % 7
        # friday=4
        return jd + ( (dow > 4) ? 7 : 0 ) + (4 - dow)
}

END {
        min=max=dates[1]
        for (i in dates) {
                if (dates > max) max=dates
                if (dates < min) min=dates
        }

        printf("Friday after:\nMin: %s\nMax: %s\n",
                mk_g(next_fri(min)), mk_g(next_fri(max)));
}

It is a bit lengthy. If only output you want is that next friday, there are day-of-week algorithm that wouldn't require converting to-and-from julian. You could use sort -n on that column and grab the first and last, then depends on OS if you can use date -d to get next friday...

This is bash function for day of week, since GNU date did allow "next friday" but not "2012-07-27 next friday" :frowning:


mkdow() {
        local y=$1 m=$2 d=$3
        echo $((
                ((m<3?y--:y-2)+23*m/9+4+d+y/4+y/100*25/4)%7
        ))
}

could then just add days yourself until it is friday (like next_friday i wrote in awk)

1 Like

Hi,

another way

inputfile

20120624
20120625
20120626
20120627
20120628
20120629
20120630
20120701
20120702
20120703
20120704
20120705
20120706
20120707
20120708
20120709
20120710
20120711
20120712
20120713
20120714
20120715
20120716
20120717
20120718
20120719
20120720
20120721
20120722

The code

awk '{oneday=24*60*60; ctm=mktime(substr($1,1,4) " " substr($1,5,2) " " substr($1,7,2) " 00 00 00"); dow=strftime("%u",ctm); toadd=(dow<=5?5-dow:12-dow)*oneday; print strftime("%Y%m%d",ctm+toadd);}' infile

Output :

20120629
20120629
20120629
20120629
20120629
20120629
20120706
20120706
20120706
20120706
20120706
20120706
20120706
20120713
20120713
20120713
20120713
20120713
20120713
20120713
20120720
20120720
20120720
20120720
20120720
20120720
20120720
20120727
20120727

Hi All,

Thanks a lot to all for the replies

@neutronscott: Its AIX and Bourne shell i guess.
and we have to calcuate the min and max date for the dates that are present in the data.

@Methy:if the input is as below,
20120711(Wed)
20120719(Thu)
20120721(sat)

Then my output should be like this,
20120713
20120720
20120727(Friday after the saturday)

Have you found a way to incorporate these suggestions into what you need, or do you need more help? If a clear complete sample of input (3 lines maybe) and exact output is given, a complete working example would be more probable. :slight_smile:

knowing neither date -d nor bash being available everywhere I could not resist:

while read i; do date -d"$i+$((6-($(date -d$i +%u)+1)%7))days" +%Y%m%d; done <infile

The innermost date yields the day of week (%u) with which, using bash's arithmetic expressions, the difference to next Friday is computed. This then is the input to the outer date -d"sth+xdays" command.
Give it a try!

Hi Neutronscott,

Above both codes are not wokring :(...

awk: 0602-553 Function mktime is not defined.
The input line number is 1. The file is test.
The source line number is 1.

if the input is as below in a file,
20120711(Wed)
20120719(Thu)
20120721(sat)

Then my output should be like this in another file,
20120713
20120720
20120727(Friday after the saturday)

and Min date(20120713) and Max date(20120727) should come in another file.

My box is AIX...and the shell is Korn shell...kindly help :slight_smile:

---------- Post updated at 05:23 PM ---------- Previous update was at 05:16 PM ----------

Dear RudiC,

i got the below error :frowning:

date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]
date: Not a recognized flag: d
Usage: date [-u] [+"Field Descriptors"]

As expected, unfortunately. Here's a collection of date manipulation ideas:

http://www.unix.com/answers-frequently-asked-questions/13785-yesterdays-date-date-arithmetic.html

1 Like

date math is rather interesting, and i read some of those threads RudiC suggested yesterday. unfortunately, my understanding of ksh is not as well as bash. i was unable to get the functions operating the same:

$ printf '%s\n' 20120711 20120719 20120721 | bash ./julian
2012/07/11 (jd:2456120 dow:2) + 2 = 20120713
2012/07/19 (jd:2456128 dow:3) + 1 = 20120720
2012/07/21 (jd:2456130 dow:5) + 6 = 20120727
$ printf '%s\n' 20120711 20120719 20120721 | ksh ./julian
2012/07/11 (jd:2456120 dow:2) + 2 = 20120712
2012/07/19 (jd:2456130 dow:5) + 6 = 20120801
2012/07/21 (jd:2456130 dow:5) + 6 = 20120801

:wall:

probably someone wiser could do it easier by parsing the output of cal(1) but the awk code did produce same results across the awks which i have.

$ printf '%s\n' 20120711 20120719 20120721 | awk -f julian.awk
20120713
20120720
20120727

Min: 20120713
Max: 20120727

#!/usr/bin/awk -f
function mk_j(y,m,d,    a, j) {
        a=int((14-m)/12);y=y+4800-a;m=m+12*a-3
        return d+int((153*m+2)/5)+365*y+int(y/4)-int(y/100)+int(y/400)-32045
}
function mk_g(jd,       l,n,i,j,d,m,y) {
        l=jd+68569;n=int((4*l)/146097);l=l-int((146097*n+3)/4)
        i=int(4000*(l+1)/1461001);l=l-int((1461*i)/4)+31
        j=int((80*l)/2447);d=l-int((2447*j)/80);l=int(j/11);m=j+2-(12*l);
        y=100*(n-49)+i+l;return sprintf("%04d%02d%02d",y,m,d)
}
function next_fri(jd,   dow) { dow=jd%7;return mk_g(jd+((dow>4)?7:0)+(4-dow)) }
1 { f=0;y=substr($f,1,4);m=substr($f,5,2);d=substr($f,7,2);
  dates[++i]=mk_j(y,m,d);print next_fri(dates)
}
END {
        min=max=dates[1]
        for (i in dates) {
                if (dates > max) max=dates
                if (dates < min) min=dates
        }
        printf("\nMin: %s\nMax: %s\n",
                next_fri(min), next_fri(max));
}

you can redirect the output of the min/max to a different file like this if you wish

printf("\nMin: %s\nMax: %s\n", next_fri(min), next_fri(max)) > "minmax"
1 Like