Postgres - join generate_series to an existing dataset

Question

I have a data set that looks something like this:

'item': 'test', 'assignee_id': 1, 'date': datetime.date(2023, 3, 1), 'hours_worked': 1
'item': 'test', 'assignee_id': 1, 'date': datetime.date(2023, 3, 4), 'hours_worked': 2
'item': 'test', 'assignee_id': 2, 'date': datetime.date(2023, 3, 2), 'hours_worked': 2

As you can see, there is hours worked on a particular day for a particular person, but this data set is missing the empty space of days that aren't included in this data set (2023/03/02, 2023/03/02) for assignee 1.

I'd like to split this across both the days but also the assignee and the item.

I've been able to generate a series of dates using the generate_series function:

SELECT t.day::date 
FROM   generate_series(timestamp '2023-01-01'
                     , timestamp '2004-02-01'
                     , interval  '1 day') AS t(day);

but I don't know how to join these 2 datasets together. Is there an operation I could use to join across multiple things?

Nothing in PostgreSQL itself looks like datetime.date(2023, 3, 1). That appears to be something the client would do, but you didn't indicate your client. — jjanes
– jjanes, Commented Mar 29, 2023 at 17:04

Belayer · Accepted Answer · 2023-03-29 17:03:27Z

Capture the result of the generate_series() within a CTE. This builds a localized view. Once built then then use as any other table or view. For example:

with cal(day) as 
     ( select t.day::date 
         from generate_series(timestamp '2023-01-01'
             , timestamp '2004-02-01'
             , interval  '1 day') as t(day) 
     ) 
select * 
  from cal 
  left join sometable 
    on cal.day = sometable.some_date_col;

You can just directly generate_series() as a sub-query to the table:

select * 
  from (select t.day::date 
         from generate_series(timestamp '2023-01-01'
             , timestamp '2004-02-01'
             , interval  '1 day') as t(day)
       ) col
  left join sometable 
    on cal.day = sometable.some_date_col;

I typically prefer the first, but that is strictly a personal choice. I think it reads easier and easier to see what is happening, But that is strictly a choice.

Collectives™ on Stack Overflow

Postgres - join generate_series to an existing dataset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related