I have several tables, where all the data is recorded, day by day.
However, I need to make a select that brings all the records of the last day of each month.
I’ve looked around here on Stack and haven’t found it.
I need the query for Postgresql.
The data type is data.
The data is from 2016-08-02 to today.
Example:
table name = snacks
| Name (str) | Amount (int) | Date (date) |
|---|---|---|
| Cookie | 3 | 2018-01-28 |
| Brownie | 5 | 2018-01-28 |
| Cookie | 8 | 2018-01-29 |
| Brownie | 6 | 2018-01-29 |
| Cookie | 9 | 2018-01-30 |
| Brownie | 4 | 2018-01-30 |
| Cookie | 15 | 2018-01-31 |
| Brownie | 14 | 2018-01-31 |
…..
| Name (str) | Amount (int) | Date (date) |
|---|---|---|
| Cookie | 24 | 2018-02-25 |
| Brownie | 32 | 2018-02-25 |
| Cookie | 20 | 2018-02-26 |
| Brownie | 15 | 2018-02-26 |
| Cookie | 12 | 2018-02-27 |
| Brownie | 7 | 2018-02-27 |
| Cookie | 5 | 2018-02-28 |
| Brownie | 6 | 2018-02-28 |
I tried with date_trunc and distinct, but I didn’t get the return of the end of each month.
The result should be:
| Name (str) | Amount (int) | Date (date) |
|---|---|---|
| Cookie | 15 | 2018-01-31 |
| Brownie | 14 | 2018-01-31 |
| Cookie | 5 | 2018-02-28 |
| Brownie | 6 | 2018-02-28 |
>Solution :
You could compare the month of the date with the month of next day
Select * from snacks
where EXTRACT(MONTH FROM snacks.d) != EXTRACT(MONTH FROM snacks.d + 1)