clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811948 fiddles created (25312 in the last week).

create table test_data ( id serial primary key, key text, timestamp timestamp with time zone ); INSERT INTO test_data (key, timestamp) VALUES ('Source_A', '2018-03-15 01:07:06.603029+00'), ('Source_B', '2018-03-15 10:00:01.603029+00'), ('Source_A', '2018-03-15 11:05:06.603029+00'), ('Source_B', '2018-03-15 15:09:06.603029+00'), ('Source_B', '2018-03-15 16:09:06.603029+00');
5 rows affected
 hidden batch(es)


SELECT TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600), date_trunc('hour', timestamp) from test_data
to_timestamp date_trunc
2018-03-15 01:00:00+00 2018-03-15 01:00:00+00
2018-03-15 10:00:00+00 2018-03-15 10:00:00+00
2018-03-15 11:00:00+00 2018-03-15 11:00:00+00
2018-03-15 15:00:00+00 2018-03-15 15:00:00+00
2018-03-15 16:00:00+00 2018-03-15 16:00:00+00
 hidden batch(es)


SELECT COUNT(hours)-1 AS missing_hours, 'Source_A' AS key FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') AS hours WHERE hours NOT IN ( SELECT TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600) AS time_bit FROM test_data WHERE key = 'Source_A' GROUP BY time_bit)
missing_hours key
22 Source_A
 hidden batch(es)


WITH period as ( SELECT COUNT(*) as total_hours FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') gs ), key_counts as ( SELECT key, COUNT(*) as hours FROM ( SELECT distinct key, date_trunc('hour', timestamp) FROM test_data --apply period limit here ) kq GROUP BY KEY ) SELECT key, total_hours-hours as missing_hours FROM period, key_counts
key missing_hours
Source_A 23
Source_B 22
 hidden batch(es)