Count the number of 7AM or 7PM occurrences between two datetimes

Question

Is there an easy way to do this? By fully between, I mean don't count the 7am or 7pm datetimes that are equal to the start or end time.

I imagine this can be done using the unix timestamp in seconds and a bit of algebra, but I can't figure it out.

I'm happy to use something in PLSQL or plain SQL.

Examples:

start             end               num_7am_7pm_between_dates
2012-06-16 05:00  2012-06-16 08:00  1  
2012-06-16 16:00  2012-06-16 20:00  1
2012-06-16 05:00  2012-06-16 07:00  0
2012-06-16 07:00  2012-06-16 19:00  0
2012-06-16 08:00  2012-06-16 15:00  0
2012-06-16 05:00  2012-06-16 19:01  2
2012-06-16 05:00  2012-06-18 20:00  6 

Answer 1

I think this could be reduced further but I don't have Oracle at my disposal to completely test this Oracle SQL:

SELECT StartDate
     , EndDate
     , CASE WHEN TRUNC(EndDate) - TRUNC(StartDate) < 1
             AND TO_CHAR(EndDate, 'HH24') > 19
             AND TO_CHAR(StartDate, 'HH24') < 7
            THEN 2
            WHEN TRUNC(EndDate) - TRUNC(StartDate) < 1
             AND (TO_CHAR(EndDate, 'HH24') > 19
              OR  TO_CHAR(StartDate, 'HH24') < 7)
            THEN 1
            WHEN TRUNC(EndDate) - TRUNC(StartDate) > 0
             AND TO_CHAR(EndDate, 'HH24') > 19
             AND TO_CHAR(StartDate, 'HH24') < 7
            THEN 2 + ((TRUNC(EndDate) - TRUNC(StartDate)) * 2)
            WHEN TRUNC(EndDate) - TRUNC(StartDate) > 0
             AND TO_CHAR(EndDate, 'HH24') > 19
              OR TO_CHAR(StartDate, 'HH24') < 7
            THEN 1 + ((TRUNC(EndDate) - TRUNC(StartDate)) * 2)
            ELSE 0
       END
FROM MyTable;

Thanks to @A.B.Cade for the Fiddle, it looks like my CASE Logic can be condensed further to:

SELECT SDate
     , EDate
     , CASE WHEN TO_CHAR(EDate, 'HH24') > 19
             AND TO_CHAR(SDate, 'HH24') < 7
            THEN 2 + ((TRUNC(EDate) - TRUNC(SDate)) * 2)
            WHEN TO_CHAR(EDate, 'HH24') > 19
              OR TO_CHAR(SDate, 'HH24') < 7
            THEN 1 + ((TRUNC(EDate) - TRUNC(SDate)) * 2)
            ELSE 0
       END AS MyCalc2
  FROM MyTable;

Answer 2

I had fun writing the following solution:

with date_range as (
  select min(sdate) as sdate, max(edate) as edate
  from t
),
all_dates as (
  select sdate + (level-1)/24 as hour
  from date_range
  connect by level <= (edate-sdate) * 24 + 1
),
counts as (
  select t.id, count(*) as c
  from all_dates, t
  where to_char(hour, 'HH') = '07'
  and hour > t.sdate and hour < t.edate
  group by t.id
)
select t.sdate, t.edate, nvl(counts.c, 0)
from t, counts
where t.id = counts.id(+)
order by t.id;

I added an id column to the table in case the range of dates aren't unique.

http://www.sqlfiddle.com/#!4/5fa19/13

Answer 3

This may not have the best performance but might work for you:

select sdate, edate, count(*)
  from (select distinct edate, sdate, sdate + (level / 24) hr
          from t
        connect by sdate + (level / 24) <= edate )
 where to_char(hr, 'hh') = '07'
group by sdate, edate

---

UPDATE: As to @FlorinGhita's comment - fixed the query to include zero occurences

select sdate, edate, sum( decode(to_char(hr, 'hh'), '07',1,0))
  from (select distinct edate, sdate, sdate + (level / 24) hr
          from t
        connect by sdate + (level / 24) <= edate )
group by sdate, edate

Answer 4

Do like this (in SQL)

declare  @table table ( start datetime, ends datetime) 
insert into @table select'2012-06-16 05:00','2012-06-16 08:00'  --1  
insert into @table select'2012-06-16 16:00','2012-06-16 20:00'  --1
insert into @table select'2012-06-16 05:00','2012-06-16 07:00'  --0
insert into @table select'2012-06-16 07:00','2012-06-16 19:00'  --0
insert into @table select'2012-06-16 08:00','2012-06-16 15:00'  --0
insert into @table select'2012-06-16 05:00','2012-06-16 19:01'  --2
insert into @table select'2012-06-16 05:00','2012-06-18 20:00'  --6 
insert into @table select'2012-06-16 07:00','2012-06-18 07:00'  --3


Declare @From DATETIME 
Declare @To DATETIME 

select @From = MIN(start) from @table 
select @To = max(ends) from @table 

;with CTE AS 
( 
      SELECT distinct 
    DATEADD(DD,DATEDIFF(D,0,start),0)+'07:00' AS AimTime
    FROM @table 
 ),CTE1 AS 
 ( 
    Select AimTime
    FROM CTE 

    UNION ALL 

    Select DATEADD(hour, 12, AimTime)
From CTE1 
WHERE AimTime< @To 
 )


 select start,ends, count(AimTime)
 from CTE1 right join @table t 
 on t.start < CTE1.AimTime and t.ends > CTE1.AimTime
 group by start,ends