CODEWITHSUNDEEP
oracle-databasetimestamp
salmon eater
salmon eater

Reputation: 49

Oracle timestamp and current_date

I have this code working:

ixdtl.timestamp between
to_date('2015/06/03', 'YYYY/MM/DD')+ INTERVAL '03:0:0.0' HOUR TO SECOND 
  AND to_date('2015/06/03', 'YYYY/MM/DD')+ INTERVAL '1 2:59:59.99' DAY TO SECOND
    AND 
    (
     icrun.RUNDATE BETWEEN to_date('2015/06/03', 'YYYY/MM/DD')
     AND to_date('2015/06/03', 'YYYY/MM/DD')+ INTERVAL '1' DAY 
     )

But now, in place of the string '2015/06/03', I need to use the variable current_date so that ixdtl.timestamp is between 3AM yesterday and 3AM today. Also, the rundate needs to be yesterday or today.

How can I do this?

Upvotes: 0

Views: 143

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 221370

Your predicate should read:

ixdtl.timestamp
  BETWEEN trunc(CURRENT_DATE) - INTERVAL '21' HOUR
  AND     trunc(CURRENT_DATE) + INTERVAL '3'  HOUR

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26363

CURRENT_DATE is the current date and time. If you want just the date part, use the TRUNC function.

To get 3AM yesterday and 3AM today, it's probably easier to subtract 21 hours from today at midnight for 3AM yesterday, add 3 hours to today at midnight for 3AM Today, and use >= / < instead of BETWEEN

ixdtl.timestamp >= TRUNC(CURRENT_DATE) - INTERVAL '21' HOUR AND
ixdtl.timestamp < TRUNC(CURRENT_DATE) + INTERVAL '3' HOUR

For today and yesterday, look for >= one day ago and < tomorrow.

ixdtl.RUNDATE >= TRUNC(CURRENT_DATE) - INTERVAL '1' DAY AND
ixdtl.RUNDATE < TRUNC(CURRENT_DATE) + INTERVAL '1' DAY

Put them all together and you get:

WHERE ixdtl.timestamp >= TRUNC(CURRENT_DATE) - INTERVAL '21' HOUR
  AND ixdtl.timestamp < TRUNC(CURRENT_DATE) + INTERVAL '3' HOUR
  AND ixdtl.RUNDATE >= TRUNC(CURRENT_DATE) - INTERVAL '1' DAY
  AND ixdtl.RUNDATE < TRUNC(CURRENT_DATE) + INTERVAL '1' DAY

Upvotes: 2

Related Questions