weekends.sql

with weekends AS (
	-- generate all weekends between 2017-01-01 and next weekend
	select 
	
	DATEADD(day, 7*n, '2017-01-01') AS ref_date, 
	CAST(NEXT_DAY(DATEADD(day, 7*n, '2017-01-01'), 'Saturday') AS timestamp) AS saturday, 
	CAST(NEXT_DAY(DATEADD(day, 7*n, '2017-01-01'), 'Sunday') AS timestamp) + interval '86399 second' AS sunday
	
	from numbers
	where DATEADD(day, 7*n, '2017-01-01') <= getdate() + interval '7 day'
),
weekends_in_window AS (
	select ref_date, saturday, sunday, DATEDIFF(seconds,
	
	-- MAX(saturday, min_ts)
	CASE TIMESTAMP_CMP(saturday, '2018-02-02 15:23:47'::timestamp) WHEN 1 THEN saturday ELSE '2018-02-02 15:23:47'::timestamp END,
						    
	-- MIN(sunday, max_ts)
	CASE TIMESTAMP_CMP(sunday, '2018-02-16 13:15:12'::timestamp) WHEN 1 THEN '2018-02-16 13:15:12'::timestamp ELSE sunday END
	
	) AS weekend_seconds
	FROM weekends
	where (
		-- saturday >= min_ts && sunday <= max_ts
		(saturday >= '2018-02-02 15:23:47'::timestamp AND sunday <= '2018-02-16 13:15:12'::timestamp) OR
		
		-- saturday <= min_ts && sunday >= min_ts
		(saturday <= '2018-02-02 15:23:47'::timestamp AND sunday >= '2018-02-02 15:23:47'::timestamp) OR
		
		-- saturday <= max_ts && sunday >= max_ts
 		(saturday <= '2018-02-16 13:15:12'::timestamp AND sunday >= '2018-02-16 13:15:12'::timestamp) 
	)
)
select DATEDIFF(seconds, '2018-02-02 15:23:47'::timestamp, '2018-02-16 13:15:12'::timestamp) - sum(weekend_seconds)  from weekends_in_window