Postgres - join a table with data on certain dates to a table with all dates in a range

Question

I have a table, 'dailymetrics' with data on certain dates. I want to join it with a table with all dates in a range so that I can then average over a date range. I'm trying to generate the date range I want and then do a left outer join with the table 'dailymetrics'. However, the output only includes dates that already exist in the 'dailymetrics' table. How do I get the output table to include all dates in the range?

The Query

SELECT day::date, dm.person_id, dm.latency
FROM generate_series('2015-08-01', '2015-08-10', INTERVAL '1 day') day
LEFT OUTER JOIN sleeptracking_dailymetrics as dm
ON dm.date=day
ORDER BY dm.person_id, day DESC;

sample of current output:

   day     | person_id |    latency    
------------+-----------+---------------
2015-08-08 |       847 |            56
2015-08-06 |       847 |            60
2015-08-05 |       847 |            88
2015-08-04 |       847 |            46
2015-08-03 |       847 |            24
2015-08-01 |       847 |             0

Rows for 2015-08-07 and 2015-08-02 aren't there.

Gordon Linoff · Accepted Answer · 2015-08-10 23:17:44Z

1

I am guessing that you have more than one person in the data and you oversimplified your query. You need to do a cross join between the days and the people and then do a left join:

SELECT day::date, p.person_id, dm.latency
FROM (select distinct person_id from sleeptracking_dailymetrics) p cross join
     generate_series('2015-08-01', '2015-08-10', INTERVAL '1 day') day left join
     sleeptracking_dailymetrics dm
     ON dm.date = day and dm.person_id = p.person_id
ORDER BY p.person_id, day DESC;

The cross join generates all the rows. The left join then brings in the data you want.

edited Aug 10, 2015 at 23:17

answered Aug 10, 2015 at 20:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jzkelter Over a year ago

I do have more than one person in the data, but I'm getting the exact same output with your suggestion.

jzkelter Over a year ago

It works if you: ORDER BY p.person_id, day DESC; Thanks for the help!

Collectives™ on Stack Overflow

Postgres - join a table with data on certain dates to a table with all dates in a range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related