keep-7days-pgsql.sh

#!/bin/bash

###
#       think got it at today (95/01/02) about use `date` command to get date for deal timestamp with pgsql,
#       sigh, i faint !! Do too much scripts.
###

# fixed 2005/12/31
# purpose: in order to keep only 7 days data in pgsql for maillist
# delete from table where timstamp < '2005-12-31%'
# Jan=1         31 days
# Feb=2         28 days (but it is 29 days in 2008 year,not take care about it yet)
# Mar=3         31 days
# Apr=4         30 days
# May=5         31 days
# Jun=6         30 days
# Jul=7         31 days
# Agu=8         31 days
# Sep=9         30 days
# Oct=10        31 days
# Nov=11        30 days
# Dec=12        31 days
dbname='maillist'
dbuser='maillist'
dbhost='localhost'

nowday=`date +%d`
nowmon=`date +%m`
nowyear=`date +%Y`
echo "nowday=$nowday"
echo "nowmon=$nowmon"
echo "nowyear=$nowyear"
echo "nowdate=$nowyear-$nowmon-$nowday"

nokeepday=`expr $nowday - 6`
echo "first nokeepday=$nokeepday"

if [ $nokeepday == 0 ]; then
        if [ $nowmon == 05 ] || [ $nowmon == 07 ] || [ $nowmon == 10 ] || [ $nowmon == 12 ]; then
                nokeepday="30"
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        elif [ $nowmon == 03 ]; then
                nokeepday="28"
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        elif [ $nowmon == 01 ]; then
                nowyear=`expr $nowyear - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-12-31
                echo "nokeepdate=$nowyear-12-31"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        else
                nokeepday=31
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        fi
elif [ $nokeepday -lt 0 ]; then
        if [ $nowmon == 05 ] || [ $nowmon == 07 ] || [ $nowmon == 10 ] || [ $nowmon == 12 ]; then
                nokeepday=`echo $nokeepday | cut -d'-' -f2`
                nokeepday=`expr 30 - $nokeepday`
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        elif [ $nowmon == 03 ]; then
                nokeepday=`echo $nokeepday | cut -d'-' -f2`
                nokeepday=`expr 28 - $nokeepday`
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        elif [ $nowmon == 01 ]; then
                nokeepday=`echo $nokeepday | cut -d'-' -f2`
                nokeepday=`expr 31 - $nokeepday`
                nowyear=`expr $nowyear - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-12-$nokeepday
                echo "nokeepdate=$nowyear-12-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        else
                nokeepday=`echo $nokeepday | cut -d'-' -f2`
                nokeepday=`expr 31 - $nokeepday`
                nowmon=`expr $nowmon - 1`
                echo "result nokeepday=$nokeepday"
                nokeepdate=$nowyear-$nowmon-$nokeepday
                echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
                # deal alemail table
                echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
                # deal maillog table
                echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        fi
else
        echo "result nokeepday=$nokeepday"
        nokeepdate=$nowyear-$nowmon-$nokeepday
        echo "nokeepdate=$nowyear-$nowmon-$nokeepday"
        # deal alemail table
        echo "delete from alemail where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
        # deal maillog table
        echo "delete from maillog where timestamp < '$nokeepdate%';" | psql $dbname -U $dbuser -h $dbhost
fi