I have a table with some animal sightings data where each sighting entry has a region, there can be 0 to many entries per region per day.

I need to create a query where there is one row per day (all days even without sightings) per region. So I thought to join the table with a generate_series command like so:

WITH dates AS (
    SELECT
        MAX(DATE(sighting_time)) AS max_date,
        MIN(DATE(sighting_time)) AS min_date
    FROM sightings
)
SELECT
    generate_series(dates.min_date::date, dates.max_date::date, '1 day') as gen_date
FROM dates

and do a left join with the original table. This does fill in the gaps where there are no entries for either region but I need it to fill in the date for each region that doesn't have an entry for that day, basically an empty row for that region maybe even with the region name still there. I could also create separate queries that explicitly filter for each region and then union them but I do not want to have to hard code the regions, this should work even if we add more regions.

Any help is appreciated, thanks!

I have a table with some animal sightings data where each sighting entry has a region field. There can be 0 to many entries per region per day.

I need to create a query where there is one row per day (all days even without sightings) per region. So I thought to join the table with a generate_series command like so:

WITH dates AS (
    SELECT
        MAX(DATE(sighting_time)) AS max_date,
        MIN(DATE(sighting_time)) AS min_date
    FROM sightings
)
SELECT
    generate_series(dates.min_date::date, dates.max_date::date, '1 day') as gen_date
FROM dates

and do a left join with the original table. This does fill in the gaps where there are no entries for either region but I need it to fill in the date for each region that doesn't have an entry for that day, basically an empty row for that region maybe even with the region name still there. 

I could also create separate queries that explicitly filter for each region and then union them but I do not want to have to hard code the regions, this should work even if we add more regions.

Any help is appreciated, thanks!