At my work, there is a system that inserts data into the DB hourly. From time to time some data is missing randomly, for example, 2 hours of a day in January, 10 hours in February, 24 hours in May, etc. So I need a SQL query that shows me the missing hours so that I can back-fill the missing data later. Since I know that data must be filled hourly, I use the following query to achieve my goal, in particular, to find the hours of dates from 2017 to 2018 when data were missing:
\copy (select missing_date
from generate_series('2017-01-01'::timestamp, '2018-01-01'::timestamp, '1 hour'::interval) missing_date
where to_char(missing_date, 'YYYY-MM-DD HH24') not in (select to_char(dt, 'YYYY-MM-DD HH24') from my_table_name)
order by missing_date) to '/tmp/missing_date.csv' with csv delimiter ',';
Now let's break the query into parts.
\copy (select *** from ***) to 'path_to_file' with csv delimiter ','
to write the query result into a csv file using ',' as the delimiter.
generate_series(start_datetime, end_datetime, interval) [as] column_name
to generate a datetime sequence using the interval.
Since the type of the 'dt' column is timestamp and data are filled not exactly at the beginning of an hour, I use to_char(dt, datetime_format) to convert the timestamp to a proper format.
The query might seem to be obvious for SQL experts, but I think it might be useful for those guys who are to PostgreSQL.