Search code examples
oracle-databaseoracle11g

How to select data between yesterday at specific time to today specific time in oracle?

I have one Requirement where I have to show the records between specific date and time every day eg. between yesterday 9 Am to Today 9 Am 

Select * from sales where saledate between '24-OCT-17 09:00:00' to '25-OCT-17 09:00:00'

in oracle.

but not getting idea how to do this.

Solution

  • 9 AM today can be coded as

    trunc(sysdate) + 9/24

    and 9 AM yesterday as

    trunc(sysdate) - 1 + 9/24

    Alternatively,

    trunc(sysdate) + interval '9' hour

    and

    trunc(sysdate) - interval '1' day + interval '9' hour

    The calculations for "yesterday" can be simplified ( - 15/24 instead of - 1 + 9/24, and similarly for interval ) - but it is better to write the code the way I did, since it is clearer - easier to understand and maintain.

    trunc(sysdate) means midnight (00:00:00) at the beginning of today. In date arithmetic, 1 means one day; 9/24 means 9 hours.

    The WHERE clause may be written as

    where saledate >= trunc(sysdate) - 1 + 9/24 and saledate < trunc(sysdate) + 9/24