CODEWITHSUNDEEP
sqlpostgresqldate
Denis
Denis

Reputation: 13

Data for previous year in PostgreSQL

I have a table with this structure:

date amount
2021-01-04 1
2021-01-11 2
2021-03-18 3
... ...
2022-01-03 4
2022-01-10 5
2022-01-17 6
... ...
2023-01-02 8
2023-01-09 9
2023-01-16 10

I need to get for each date "amount" a year ago:

date amount year_ago
2021-01-04 1 -
2021-01-11 2 -
2021-03-18 3 -
... ... -
2022-01-03 4 1
2022-01-10 5 2
2022-01-17 6 3
... ... ...
2023-01-02 8 4
2023-01-09 9 5
2023-01-16 10 6

I have tried to use LEFT SELF JOIN and window function with RANGE. But it doesn't work because dates are little different with a day

How I can solve my problem?

Upvotes: 1

Views: 53

Answers (1)

lemon
lemon

Reputation: 15482

Inside the LEFT JOIN you can use:

  • - INTERVAL '1 year' to subtract a year
  • + INTERVAL '1 day' to add a day
SELECT this_year.date_, 
       this_year.amount, 
       last_year.amount
FROM      tab this_year
LEFT JOIN tab last_year
       ON last_year.date_ = this_year.date_ - INTERVAL '1 year' 
                                            + INTERVAL '1 day'

Check the demo here.

Upvotes: 1

Related Questions