Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: difference between dates

    Hi, a table has a date column of this form: 4/11/2004 10:47:46 PM. (DATE). Is there a way to select records in the past 24 hrs (including seconds)?

    Thanks in anticipation

  2. #2
    Join Date
    Jan 2004
    Location
    Bordeaux, France
    Posts
    320
    The problem is to compute the limit timestamp for records : now - 24h00

    If you are using ksh you can use the following script (GetDate() from PHV Unix FAQ on www.tek-tips.com) :
    Code:
    #!/ur/bin/ksh
    
    # Usage : GetDate nDays [format]
    # Examples :
    #    echo "Yesterday: $(GetDate -1)"
    #    echo "Tomorrow: $(GetDate 1)"
    #    echo "Next week: $(GetDate 7 '+%Y-%m-%d')"
    
    GetDate(){
      typeset -i nDays=$1; format=$2
      typeset -i localOffset=$(echo $TZ |
       sed 's![^-0-9]*\([-0-9]*\).*!\1!')
      TZ=X$((localOffset-24*nDays)) date $format
    }
    
    awk '
    function timestamp(date, hour, ampm    ,d,h) {
       split(date,d,"/");
       split(hour,h,":");
       if ( tolower(ampm) == "pm") h[1] += 12;
       return sprintf("%04d%02d%02d%02d%02d%02d",d[3],d[1],d[2],h[1],h[2],h[3]);
    }
    timestamp($1,$2,$3) >= limit
    ' limit=$(GetDate -1 '+%Y%m%d%H%M%S')  input_file
    If your shell isn't ksh, you can compute the limit within the awk script :
    Code:
    #!/usr/bin/awk -f
    
    function isleapyear(year) {
       return ( year % 4 == 0 && (year % 100 != 0 || year % 400 == 0))
    }
    
    function yesterday_timestamp(   date_cmde) {
       date_cmde = "date +'%Y %m %d %H%M%S'";
       date_cmde | getline;
       close(date_cmde);
       if (--$3 == 0) {
          if (--$2 == 0) {
             $2 = 12;
             $1 -= $1;
          }
          $3 = substr("00312831303130313130313031",$2*2+1,2);
          if ($2 == 2 && isleapyear($1)) $2 = 29;
       }
       return sprintf("%04d%02d%02d%06d",$1,$2,$3,$4)
    }
    function timestamp(date, hour, ampm    ,d,h) {
       split(date,d,"/");
       split(hour,h,":");
       if ( tolower(ampm) == "pm") h[1] += 12;
       return sprintf("%04d%02d%02d%02d%02d%02d",d[3],d[1],d[2],h[1],h[2],h[3]);
    }
    
    BEGIN {   limit = yesterday_timestamp() }
    
    timestamp($1,$2,$3) >= limit
    ' input_file
    Jean-Pierre.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •