Saturday, 3 March 2007

SYSDATE manipulation

I quite often work with database rows that contain audit information which includes the last modified time. Oracle allows SYSDATE addition so that you can add or subtract a number of days (or fraction of days):
select SYSDATE-1 from dual;

would return the current time minus 24 hours. So, if you are in the process of debugging and looking at the database rows you could restrict the select statement to show only those modified by yourself in the last hour:
select * from foo
where
last_modified_date > SYSDATE-(1/24)
and
last_modified_by='bar';


or last half hour:
select * from foo
where
last_modified_date > SYSDATE-(1/48)
and
last_modified_by='bar';

0 comments:

Post a Comment