How to pull values ​​for each start and end year of a list of years?

Question

PostgreSQL

There is a table which stores date and number:
| id | t | item_code |
----------------------------------------------------
| 1 | 2008-02-04 | 11 |
| 2 | 2008-04-04 | 12 |
| 3 | 2008-06-04 | 13 |
| 4 | 2008-09-04 | 14 |
| 5 | 2009-02-04 | 21 |
| 6 | 2009-04-04 | 22 |
| 7 | 2009-06-04 | 23 |
| 8 | 2009-09-04 | 24 |
| 9 | 2010-02-04 | 31 |
|10| 2010-04-04 | 32 |
|11| 2010-06-04 | 33 |
|12| 2010-09-04 | 34 |

I would like to get the output:

| 2008-02-04 | 2008-09-04 | 11 | 14 |
| 2009-02-04 | 2009-09-04 | 21 | 24 |
| 2010-02-04 | 2010-09-04 | 31 | 34 |

Those. it turns out that in each line I need the minimum and maximum date of each year + values ​​\u200b\u200bfor each of these dates.

Is it even possible to do this without resorting to cursors and everything like that? It would be desirable to solve by normal request. But the cycle bothers me. He's needed here. Well, or combine into a heap of union.

Tried like this:

SELECT MIN(t::timestamp::date) AS startyear, MAX(t::timestamp::date) as endyear, item_code
FROM item_codes
GROUP BY item_code
order by startyear

... but some garbage came out.

Answer 1

Group by year from date.
Group by date_trunc('year', DATE)

Answer 2

как-то так:

WITH item_codes_ordered as (
        SELECT ic.*,
            COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM ic.t) ORDER BY ic.id ASC) as a,
            COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM ic.t) ORDER BY ic.id DESC) as d
        FROM item_codes ic
    )
SELECT ic_a.t as t_asc,
    ic_a.item_code as item_code_asc,
    ic_d.t as t_desc,
    ic_d.item_code as item_code_desc
FROM item_codes_ordered ic_a
INNER JOIN item_codes_ordered ic_d ON ic_d.d = 1 AND EXTRACT(YEAR FROM ic_d.t) = EXTRACT(YEAR FROM ic_a.t)
WHERE ic_a.a = 1