Systems Engineering and RDBMS

Getting previous date in korn shell on AIX (ksh)

Posted by decipherinfosys on July 28, 2009

Recently at one of our client site we have to write shell script, which runs every night and goes through the database errors from logs of previous day.  Now there are different ways of manipulating date in the korn shell it self but for all of that we have to write logic. Since we have to go through files of previous day, we have to be extra careful for range conditions. Ex. For 1st March we have to process data of 28th or 29th February based on the leap year. On 1st January we have to process data for 31st December of the previous year etc.

Instead of doing date arithmetic in the Korn shell, we decided to use Oracle database’s date logic since we can manipulate system date (sysdate) easily. In the following shell script we are showing 3 methods of obtaining the previous day’s date. Please copy the following contents into one file on AIX box and save it as a test.sh file and then execute the script. Before execution please make sure that you have proper privilege to execute the script.

#!/bin/ksh

DB_USER=${1}
DB_PSWD=${2}
DB_NAME=${3}

############
#Method 1:
############
v_prev_date=`TZ=bb24 date +%Y%m%d`
echo ‘Yesterday –> ‘$v_prev_date

##############################################
# Method 2:
# one way of getting system date from Oracle
# and pass it to shell script variable.
##############################################
SQLQUERY=”select to_char(sysdate – 1,’YYYYMMDD’),to_char(sysdate,’YYYYMMDD’) from dual”
print ”
set pagesize 0;
set feedback off;
set heading off;
$SQLQUERY;
“| sqlplus -S $DB_USER/$DB_PSWD@$DB_NAME > TEMP_FILE

v_prev_date=`cat TEMP_FILE | awk ‘{print $1}’`
v_curr_date=`cat TEMP_FILE | awk ‘{print $2}’`
print ‘Method 2 –> ‘$v_prev_date
print ‘Method 2 –> ‘$v_curr_date

###################################################
# Method 3:
# Alternate way of getting system date from Oracle
# and pass it to shell script variable.
###################################################
var1=`sqlplus -S $DB_USER/$DB_PSWD@$DB_NAME << EOF
set echo off term off feed off ver off pages 0
select to_char(sysdate – 1,’YYYYMMDD’), to_char(sysdate,’YYYYMMDD’) from dual;
exit;
EOF`

v_prev_date=`echo $var1 | awk ‘{print $1}’`
v_curr_date=`echo $var1 | awk ‘{print $2}’`

print ‘Method 3 –> ‘$v_prev_date
print ‘Method 3 –> ‘$v_curr_date

Upon executing above shell script, we will get following results.

./test.sh scott tiger orcl

Result is

Yesterday –> 20090726
Method 2 –> 20090726
Method 2 –> 20090727
Method 3 –> 20090726
Method 3 –> 20090727

To display first line in the output, we are using AIX’s date command along with TZ (timezone) variable. 24 is offset to get the previous date and bb is just the string. It can be any string. Instead of ‘bb’ one can use ‘aa’ or ‘deci’ etc. If instead of 24 we use 48, then it will display day before yesterday’s date.

In method 2, output of SQL is written to file (temp_file) and then we are reading the file to get the date and previous date both.

In method 3, instead of writing the output to file, we are assigning it straight to shell variable and then from that shell variable, we are using awk command to get the desired result. Since we are relying on database’s date every condition is taken care of. So be it a 1st January 2010 or 1st March of 2008, we are going to get correct result of 20091231 and 20080229 respectively.

This shell script also serves as an example of how we can assign SQL*Plus output to shell variable. In most scenarios, we pass shell variable to SQL script but in this case it is reverse.

Resources:

  • Article – here.
  • Unix.com article – here.

4 Responses to “Getting previous date in korn shell on AIX (ksh)”

  1. John said

    Hello,

    The way I’ve been doing this for years is with a local linux box “helper” machine. The way I set it up is with pubkey ssh passwordless login from AIX -> the helper linux box. Then you can get any of the cool date stuff that the linux date command has to offer. For yesterday I do this:

    RemoteShellHost=’helperlinuxIP’
    YesterdayDate=$(ssh -c blowfish $RemoteShellHost “date –date=’yesterday’ +%d” 2>/dev/null)

    I like your method one a lot. Does it work for more complex calculations like last year? It’s easily done with the above ssh method.

    John

    • since the offset is set in hours, you can multiply days by hours and you can go back upto that date. But mostly there is no need to go back one year to do something. Going back to one day or one week is regular
      practice to collect processed and/or error data or ftp files etc. Following is the output to go one year back.

      $ v_prev_date=`TZ=bb24 date +%Y%m%d`
      $ echo ‘Yesterday –> ‘$v_prev_date
      Yesterday –> 20090806

      #365*24
      $ v_prev_date=`TZ=bb8760 date +%Y%m%d`
      $ echo ‘Yesterday –> ‘$v_prev_date
      Yesterday –> 20080807

  2. John said

    This is great. Still, I DO need to go back one year frequently in shell scripts to run sales comparison reports.

    I often run the report twice, once for beginning of current year -> YESTERDAY. Then again for beginning of previous year, to YESTERDAY last year.

    It’s actually more complicated than that. Since in retail you want to compare the same day of the week to last years day of week (i.e. monday-sunday compared to monday-sunday).

    At any rate, thanks. I had no idea AIX could do this until I stumbled on this.

    John

  3. Anonymous said

    i loved it thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: