Help needed with date compare

Shell Programming and Scripting
1

Hello,

I have this unix script which selects rows from DB where current time is greater than expired time (column). So this will give all the records that are expired 1 day ago, 2 days ago, 3 days ago, etc.. I need help modifying in such that it should give records that are only expired 1 day ago (24-hour period)

#-------------------------------------------------------------
# Log into database and execute stored procedure
#-------------------------------------------------------------
sqlplus -S myLogin/myPwd@myServer >> /tmp/myLogFile << EOF
    whenever sqlerror exit 1
    set echo off
    set pagesize 0
    set feedback off
    set trimout on
    set trimspool on
    set heading off
    SPOOL /tmp/myExpiredList
select my_id, my_expr_tmstp from MY_SCHEMA.MY_TABLE where current_timestamp > my_expr_tmstp ;                                                    
SPOOL OFF
EOF

Thanks in advance - jak

2

How about using sysdate?

where current_timestamp > sysdate - 1;
3

Hi Yoda,

I need to compare current time against the column my_expr_tmstp

4

Hi, if you want only the records with my_expr_tmstp in the previous day change the script in this way:

select my_id, my_expr_tmstp from MY_SCHEMA.MY_TABLE 
where my_expr_tmstp = trunc(current_timestamp)-1 ;
5

I have done this long time back (probably 3 years now). I have almost forgotten syntax for each and everything.

with your sql statement from which you are generating a logfile in tmp directory. you can for surely save the result on another file. (don't know the syntax).

Then just put a normal command of date (take time out) and verify.. (by using awk / sed cut only the time column.

Not sure if this was helpful,

6

Thanks franz - i will test it out