I have one table that containts on date field. and I want retrive all records that inserted between current time and half hrs. before current time.
Select count(*) from (table_name>
where date_field between (current_time and current_time - 30 Min.)
Can anybody help...?
30 minutes = 1/48th of a day
between (sysdate and sysdate-1/48)
1. create filed "fld_timestamp" in a table.
2. use default value for this field
fld_timestamp date default sysdate,
(if u want to have information about INSERT statments only)
create trigger on the table, that will write sysdate
create or replace trigger xxxx.bi_bu_tbl
before insert or update on xxxx.tbl
for each row
:new.fld_timestamp := sysdate;
(if u want to have information about INSERT and UPDATE statments)
3. use select like this:
select ... from tbl
where (sysdate - fld_timestamp) * 24 < 0.5 -- in hours
or where (sysdate - fld_timestamp) * 24 * 60 < 30 -- in minutes
Click Here to Expand Forum to Full Width