CODEWITHSUNDEEP
databaseoracle-databasedate-arithmetic
Sam
Sam

Reputation: 873

oracle: query timestamp with variable dates

In all my scripts, I want to query Oracle to give me rows having a time stamp less than todays date (today's date at 12 am) and greater than the last day at (12 am) I have already come up with the following scripts:

WHERE DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP <= (SELECT SYSTIMESTAMP FROM DUAL) 
AND DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP >= (SELECT SYSTIMESTAMP - INTERVAL '2' DAY FROM dual)

but this is not exactly what I want, because it gives rows not started at 12 am, but rather from current time.

I am looking for a way to mix variable date (every day date) and constant time (12 am) together so my queries will be limited to:

WHERE DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP <= (today's date started at 12 am) 
AND DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP >= (yesterday's date started at 12 am

Upvotes: 0

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You don't need subqueries to access functions. I think you just want trunc():

WHERE DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP <= trunc(SYSTIMESTAMP) AND 
      DAILY_OPEN_POSITIONS.OPEN_TIMESTAMP >= trunc(SYSTIMESTAMP - INTERVAL '2' DAY)

However, I think it should be '1' day in the second condition.

Upvotes: 3

Related Questions