PostgreSQL - assign exact date based on day of month

ginsberg 0
Viewed 34

I don't even know where to start with this one, or if it's even possible to do in PostgreSQL (or if I'll just need to handle it in my code)

I have a table of Bills, which specify which day of the month each one is due

id bill_name day_of_month
1 Mortgage 5
2 Car Payment 11
3 Water 14
4 Electricity 27

and I have a table of 4 week Periods

id start end
1 07 Sep 2022 04 Oct 2022
2 05 Sep 2022 01 Nov 2022
3 02 Nov 2022 29 Dec 2022

I want to join the two tables together and determine if each bill falls within the start and end of a period and then assign its exact date

I want to end up with an exact_date column

start end bill_name day_of_month exact_date
07 Sep 2022 04 Oct 2022 Car Payment 11 11 Sep 2022
07 Sep 2022 04 Oct 2022 Water 14 14 Sept 2022
07 Sep 2022 04 Oct 2022 Electricity 27 27 Sep 2022
05 Oct 2022 01 Nov 2022 Mortgage 5 5 Oct 2022
05 Oct 2022 01 Nov 2022 Car Payment 11 11 Oct 2022
05 Oct 2022 01 Nov 2022 Water 14 14 Oct 2022
05 Oct 2022 01 Nov 2022 Electricity 27 27 Oct 2022
02 Nov 2022 29 Dec 2022 Mortgage 5 5 Nov 2022
02 Nov 2022 29 Dec 2022 Car Payment 11 11 Nov 2022
02 Nov 2022 29 Dec 2022 Water 14 14 Nov 2022
02 Nov 2022 29 Dec 2022 Electricity 27 27 Nov 2022

Is this possible? Any help in the right direction would be greatly appreciated

date join database sql postgresql
2 Answers

Use generate_series():

select p.start, p.end, b.bill_name, b.day_of_month, 
       gs.exact_date::date
  from periods p
       cross join lateral generate_series(
                            p.start, p.end, interval '1 day') as gs(exact_date)
       join bills b on b.day_of_month = extract(day from gs.exact_date)
 order by exact_date;

Fiddle here

Mike Organek0

You can cross join the tables and add the exact date(-1)

SELECT "start", "end","bill_name", "day_of_month" ,date_trunc('month', "start")::date + "day_of_month" - 1
FROM periods CROSS JOIN due

| start               | end                  | bill\_name | day\_of\_month | ?column? |
|:--------------------|:---------------------|:-----------|---------------:|:---------|
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Mortgage    | 5             | 2022-09-05 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Car Payment | 11            | 2022-09-11 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Water       | 14            | 2022-09-14 |
| 2022-09-07 02:00:00 | 2022-10-04 02:00:00  | Electricity | 27            | 2022-09-27 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Mortgage    | 5             | 2022-10-05 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Car Payment | 11            | 2022-10-11 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Water       | 14            | 2022-10-14 |
| 2022-10-05 02:00:00 | 2022-11-01 01:00:00  | Electricity | 27            | 2022-10-27 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Mortgage    | 5             | 2022-11-05 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Car Payment | 11            | 2022-11-11 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Water       | 14            | 2022-11-14 |
| 2022-11-02 01:00:00 | 2022-12-29 01:00:00  | Electricity | 27            | 2022-11-27 |

SELECT 12

fiddle

nbk0
Related