CODEWITHSUNDEEP
sqloracle-databasedatetimestamp
JaymeB1986
JaymeB1986

Reputation: 11

Select Data From Multiple Days Between Certain Times (Spanning 2 days)

I need to know how many entries appear in my DB for the past 7 days with a timestamp between 23:00 & 01:00...

The Issue I have is the timestamp goes across 2 days and unsure if this is even possible in the one query.

So far I have come up with the below:

select trunc(timestamp) as DTE, extract(hour from timestamp) as HR, count(COLUMN) as Total
from TABLE 
where trunc(timestamp) >= '12-NOV-19' and 
      extract(hour from timestamp) in ('23','00','01') 
group by trunc(timestamp), extract(hour from timestamp)
order by 1,2 desc;

The result I am hoping for is something like this:

DTE         |  Total
20-NOV-19       5
19-NOV-19       4
18-NOV-19       4
17-NOV-19       6

Many thanks

Upvotes: 0

Views: 812

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Subtract or add an hour to derive the date. I'm not sure what date you want to assign to each period, but the idea is:

select trunc(timestamp - interval '1' hour) as DTE, 
       count(*) as Total
from t 
where trunc(timestamp - interval '1' hour) >= DATE '2019-11-12' and 
      extract(hour from timestamp) in (23, 0) 
group by trunc(timestamp - interval '1' hour)
order by 1 desc;

Note: If you want times between 11:00 p.m. and 1:00 a.m., then you want the hour to be 23 or 0.

Upvotes: 0

MT0
MT0

Reputation: 167962

Filter on the day first comparing it to TRUNC( SYSDATE ) - INTERVAL '7' DAY and then consider the hours by comparing the timestamp to itself truncated back to midnight with an offset of a number of hours.

select trunc(timestamp) as DTE,
       extract(hour from timestamp) as HR,
       count(COLUMN) as Total
from   TABLE 
WHERE  timestamp >= TRUNC( SYSDATE ) - INTERVAL '7' DAY
AND    (  timestamp <= TRUNC( timestamp ) + INTERVAL '01:00' HOUR TO MINUTE
       OR timestamp >= TRUNC( timestamp ) + INTERVAL '23:00' HOUR TO MINUTE
       )
group by trunc(timestamp), extract(hour from timestamp)
order by DTE, HR desc;

Upvotes: 1

Related Questions