Half Hrs. Query

[pinakpatel]

Hi,

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.

e.g.
Select count(*) from (table_name>
where date_field between (current_time and current_time - 30 Min.)

Can anybody help...?

[stecal]

30 minutes = 1/48th of a day

between (sysdate and sysdate-1/48)

[Shestakov]

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)

or

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
declare
:new.fld_timestamp := sysdate;
end;
/

(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