To get max/min Date/Timestamp from a file

Shell Programming and Scripting
1

I want to get maximum/minimum date/timestamp from a data file ?

Sample Input File

rec#,order_dt,ext_ts
1,2010-12-01,2010-12-01 17:55:23.222222
2,2011-11-05,2010-12-01 19:55:23.222222
3,2009-10-01,2010-12-01 18:55:23.222222

for above file

Maximum Order_dt = 2011-11-05
Minimum Order_dt = 2009-10-01
Maximum Ext_ts = 2010-12-01 19:55:23.222222
Minimum Ext_ts = 2010-12-01 17:55:23.222222

Can somebody help me on this ?

2

Give us sample data, three rows isn't enough to test.

3

ok.

sample.txt attached.

Maximum(LOGONDATE) 2011-06-15
Minimum(LOGONDATE) 2011-05-31
Maximum(EXT_TS) 2011-06-15 07:41:14
Minimum(EXT_TS) 2011-05-31 10:16:49

4

This code should work for you

If your seperator is pipe instead of comma use replace the dot with pipe inside -F"[-,:. ]"

awk -F"[-,:. ]" '

BEGIN{
min_date="99999999"
min_ext="99999999999999999999"

}
{
date=$2$3$4; ext=$5$6$7$8$9$10$11; 
if(min_date>date){min_date=date}; 
if(max_date<date){max_date=date};
if(min_ext>ext){min_ext=ext}
if(max_ext<ext){max_ext=ext}
}


END{

print "min date "min_date
print "max date " max_date
print "min ext "min_ext
print "max ext "max_ext
}' test.txt

Your sample file isn't matching the initial sample given here. may be few changes has to be done.

1 Like
5

Yes its working.
Thanks a lot :slight_smile:

6

This should do what you want using the sample.txt file as attached to your post.

awk -F'|' '

    BEGIN {
        # Get the heading line out of the way
        getline;
        h2=$2;
        h3=$3;
        # Then prime min/max LOGONDATE/EXT_TS
        getline;
        min_2=$2;
        max_2=$2;
        min_3=$3;
        max_3=$3;

        while (getline) {
            # for all other lines apart from heading and first, check if they are min/max
            if (min_2>$2) min_2=$2;
            if (max_2<$2) max_2=$2;
            if (min_3>$3) min_3=$3;
            if (max_3<$3) max_3=$3;
        }

        # at end of input display
        printf "%-20s = %-30s\n" ,"min_"h2 ,min_2
        printf "%-20s = %-30s\n" ,"max_"h2 ,max_2
        printf "%-20s = %-30s\n" ,"min_"h3 ,min_3
        printf "%-20s = %-30s\n" ,"max_"h3 ,max_3
    }
' sample.txt