Miovision Gap Finding SQL function doesn't correctly identify entire missing days

[radumas]

Not only does it not alert us to these, but it does also fill in 0s for them instead of NULL

[radumas]

another one!

SELECT intersection_name , DATE(datetime_bin) dt, COUNT(DISTINCT datetime_bin), classification, SUM(volume)
	FROM miovision_api.volumes_15min
	NATURAL JOIN miovision_api.intersections
	NATURAL JOIN miovision_api.classifications
	WHERE Datetime_bin >= '2021-11-30' AND datetime_bin < '2021-12-03'
	GROUP BY intersection_name, dt, classification
	ORDER BY dt, intersection_name,classification

[radumas]

Related https://github.com/CityofToronto/bdit_data-sources/issues/370

[gabrielwol]

Related to this, find_gaps doesn't identify gaps that start before midnight or end after midnight next day. For example:

--this sensor stopped working at 2023-04-24 04:55:00. 
SELECT MAX(datetime_bin) 
FROM miovision_api.volumes
WHERE
    intersection_uid = 45
    AND datetime_bin >= '2023-04-24 00:00:00'
    AND datetime_bin < '2023-04-25 00:00:00'

--However last gap listed for sensor is "2023-04-24 04:19:00" to "2023-04-24 04:46:00"
SELECT *
FROM miovision_api.unacceptable_gaps
WHERE
    intersection_uid = 45
ORDER BY gap_end DESC
LIMIT 1

[gabrielwol]

Planning to solve this by adding time bins corresponding to start_date and end_date for each intersectoin, if they don't already exist.