Postgres - how to format a date array column

Question

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?

Marth · Accepted Answer · 2020-10-04 13:35:39Z

2

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}|
+--+-----------+

edited Oct 4, 2020 at 13:35

answered Sep 29, 2020 at 9:05

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

You can use cross join instead of join and then remove the on clause.

Barbaros Özhan · Accepted Answer · 2020-09-29 09:12:44Z

1

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

edited Sep 29, 2020 at 9:12

answered Sep 29, 2020 at 9:05

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Postgres - how to format a date array column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related