Wednesday, January 7, 2009

Selecting on dates using the interval clause.

When I've wanted to select all records in the last day or the last couple hours I've done a query like the following:


-- select records from the last day
select count(*) from table where some_date > sysdate - 1;

-- Select records from the last 3 hours
select count(*) from table where some_date > sysdate - 3/24;


Starting in 9i there's another way to do this.

-- select records from the last day
select count(*) from table where some_date > sysdate - interval '1' day;

-- Select records from the last 3 hours
select count(*) from table where some_date > sysdate - interval '3' hour;

No comments: