CODEWITHSUNDEEP
sqlpostgresql
gb_spectrum
gb_spectrum

Reputation: 2301

Postgres - how to format a date array column

When I query my test_table I get back the following rows:

SELECT *
FROM test_table;

id    event_dates_array
1     {'2012-01-01', '2013-01-01'}
2     {'2015-01-01', '2016-01-01'}

The event_dates_array is a date array column.

I want the query to return the event_dates_array formatted so that the date arrays only show the years, like so:

id    event_dates_array
1     {'2012', '2013'}
2     {'2015', '2016'}

You can do this with normal date columns like so: date_part('year', event_dates)

But how do you do it for date array columns?

Upvotes: 0

Views: 851

Answers (2)

Marth
Marth

Reputation: 24812

You can use a JOIN LATERAL to access each date in the array and aggregate each date_part:

WITH data(id, event_dates_array) AS (
    VALUES ('1', ARRAY ['2012-01-01', '2013-01-01']::date[])
         , ('2', ARRAY ['2015-01-01', '2016-01-01']::date[])
)
SELECT id, event_years
FROM data
CROSS JOIN LATERAL (
    SELECT array_agg(date_part('year', date)::int) AS event_years
    FROM unnest(event_dates_array) AS date
) AS f

returns

+--+-----------+
|id|event_years|
+--+-----------+
|1 |{2012,2013}|
|2 |{2015,2016}|
+--+-----------+

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

If all date literals has the month and day portion as -01-01, then using

SELECT id, REPLACE(event_dates_array, '-01', '') AS event_years_array
  FROM test_table;

id  event_years_array
1   {'2012', '2013'}
2   {'2015', '2016'}

will be enough.

Demo

Upvotes: 1

Related Questions