SYSDATE-2 1

[htran]

I am running a query to capture data in the last 48 hours and wonder if someone can shed some lights for me on this:

This is part of the query....

SELECT FIELD1 FROM TABLE1 WHERE ADD_DATE > SYSDATE-2

The query runs every day at 7:00 PM. For example, when it runs at 7:00 PM today, I want to capture data (ADD_DATE) from 6:59:59 PM of today (June 26) back to 6:59:59 PM data of June 24 which is 48 hours ago.

The above statement does not seem to pull every thing which I need.

Please help....

 

[SantaMufasa]

The above statement does not seem to pull every thing which I need.

What makes you believe that the code is not working satisfactorily? Can you post the ADD_DATE contents (including time) of rows that a) appeared that should not have appeared or b) did not appear that should have appeared in your output? The code you can use to confirm time components include some variation of the following function:

...to_char(ADD_DATE,'yyyy-mm-dd hh24:mi:ss')...

Please post your findings so that we can help to resolve your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

I would do it as follows


SELECT FIELD1 FROM TABLE1 WHERE ADD_DATE between trunc(SYSDATE-1) + 7/24 and trunc(sysdate) + 7/24;

Then no matter when it runs, it will return 7am yesterday through 7am today.

Bill
Oracle DBA/Developer
New York State, USA

 

[htran]

Hi Mufasa,

I manually compared the time of the data (orders) coming to us and can confirm that the query does not pick up every thing.

Hi Bill,

I need to go back 48 hours so the codes should look like this:

SELECT FIELD1 FROM TABLE1 WHERE ADD_DATE between trunc(SYSDATE-2) + 19/24 and trunc(sysdate) + 19/24;

The 19/24 simply means that the query is run at 7:00 PM each night and collecting the ADD_DATE data in the last 48 hours starting from the run time of 7:00 PM.

Thanks...

 

[carp]

I am guessing that the records that aren't being picked up are very close to 7 pm two days ago. I think what you may be seeing is the latency between when your job begins its run and when the query is actually executed. If this is more than about a second, you could be losing records. Otherwise, "BETWEEN sysdate - 2 AND sysdate" should bring up anything within the desired time range. So to get around that problem, you are going to have to be explicit about your time window. To get everything between 1900 two days ago and 18:59:59 today, you might try something like:

SELECT field_name FROM my_table
WHERE add_date BETWEEN (TRUNC(sysdate - 2) + 19/24) 
                   AND (TRUNC(sysdate) + 19/24 + 59/1440 + 59/86400);

 

[htran]

Hi Carp,

I will try your code tonight and then compare the output tomorrow.

Thanks...

 

[Beilstwh]

Wouldn't

SELECT field_name FROM my_table
WHERE add_date BETWEEN (TRUNC(sysdate - 2) + 19/24)
AND (TRUNC(sysdate) + 19/24 + 59/1440 + 59/86400);


return between 7pm two days ago to 7:59:59 today? If he wanted 7PM 2 days ago to 6:59:59 today, wouldn't he want

SELECT field_name FROM my_table
WHERE add_date BETWEEN TRUNC(sysdate - 2) + (19/24)
AND TRUNC(sysdate) + (19/24) - (1/86400);



Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

Bill -
Absolutely right. 19/24 was a cut/paste artifact that I missed; it should have been 18/24.

 

[htran]

Hi all,

So the correct code should be:

SELECT field_name FROM my_table
WHERE add_date BETWEEN TRUNC(sysdate - 2) + (18/24)
AND TRUNC(sysdate) + (18/24) - (1/86400);

Meaning replacing the 19/24 with 18/24.

Please reconfirm.

 

[Beilstwh]

No, it should be

SELECT field_name FROM my_table
WHERE add_date BETWEEN TRUNC(sysdate - 2) + (19/24)
AND TRUNC(sysdate) + (19/24) - (1/86400);

Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

htran -
Just so you understand what is happening here, what

SELECT field_name FROM my_table
WHERE add_date BETWEEN TRUNC(sysdate - 2) + (19/24)
                   AND TRUNC(sysdate) + (19/24) - (1/86400);

is doing is finding the time at midnight two days ago (TRUNC(sysdate - 2) and the last second before 1900 hours of today. It's the second part that can be derived two ways.
TRUNC(sysdate) + 18/24 + 59/1440 + 59/86400 would be read "midnight + 18 hours + 59 minutes + 59 seconds". What Bill correctly observes that this is also given more efficiently by TRUNC(sysdate) + 19/24 - 1/86400, which would be read "Midnight + 19 hours - 1 second". Thanks for catching my error AND improving the approach; have a star on me, Bill!