CODEWITHSUNDEEP
sqloracle-database
deependra679
deependra679

Reputation: 33

How can I select records from yesterday's day from 6 AM to 6 AM of today

I have a PD_HEAT_DATA table with HEATID and HEATDEPARTURE_ACT columns. Data type of HEATDEPARTURE column is VARCHAR2 and it holds timestamps in the format YYYY-MM-DD HH24:MI:SS.

My requirement is to retrieve the records from PD_HEAT_DATA table between 6 AM of today and 6 AM of next day.

I have tried following code but it is not giving desired result:

select heatid, HEATDEPARTURE_ACT 
from pd_heat_data 
where HEATDEPARTURE_ACT between to_char(trunc(sysdate -1) + 6/24) 
                            and to_char(trunc (sysdate) + 6/24);

Datetime example:
HEATDEPARTURE_ACT
2019-07-23 23:11:11,359
2019-07-24 07:11:11,359

Upvotes: 0

Views: 1132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

There is no reason to convert date to strings for comparison. But, you do want to convert the string to a date!

So just try this:

where to_date(HEATDEPARTURE_ACT, 'YYYY-MM-DD HH24:MI:SS') >= trunc(sysdate -1) + 6/24 and
      to_date(HEATDEPARTURE_ACT, 'YYYY-MM-DD HH24:MI:SS') < trunc(sysate) + 6/24

That that this uses >= and < rather than between, because between includes both end points.

You can also phrase this using interval date arithmetic:

where to_date(HEATDEPARTURE_ACT, 'YYYY-MM-DD HH24:MI:SS') >= trunc(sysdate) - interval '18' hour and
      to_date(HEATDEPARTURE_ACT, 'YYYY-MM-DD HH24:MI:SS') < trunc(sysate) + interval '6' hour

Upvotes: 6

Related Questions