Half Hrs. Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Half Hrs. Query

  1. #1
    Join Date
    Mar 2001
    Posts
    36

    Question

    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...?

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    30 minutes = 1/48th of a day

    between (sysdate and sysdate-1/48)

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width