Postgres select date on the basis of interval array

Question

I required a custom solution to a problem in which users can choose a number of different intervals for data comparisons.

Example:

CURRENT_DATE - interval '1 day'
CURRENT_DATE - interval '4 day'
CURRENT_DATE - interval '7 day'

AND so on

So I am looking for a solution in which I can pass the array of integers in intervals like

select CURRENT_DATE - interval '1day' * any(ARRAY[1,4,7])

But it is not possible because

op ANY/ALL (array) requires an operator to yield boolean

If the user specifies 1,4,7 do you want 3 rows returned with three different dates? How exactly do you want to use those values? — user330315
– user330315, Commented Feb 9, 2021 at 11:23
yes, you are right. The number of rows will equal to the length of the array — Anil
– Anil, Commented Feb 9, 2021 at 11:25

S-Man · Accepted Answer · 2021-02-09 11:34:13Z

1

demos:db<>fiddle

You can use unnest() to extract the array elements into one record per element and then return the CURRENT_DATE minus each of these elements:

SELECT
    CURRENT_DATE - array_element
FROM unnest(ARRAY[1,4,7]) as array_element

Naturally, you can put the unnest() into the SELECT list:

SELECT CURRENT_DATE - unnest(ARRAY[1,4,7])

Edit:

If you need another date range than days you can use intervals for that:

SELECT CURRENT_DATE - unnest(ARRAY[1,4,7]) * interval '1 week'

edited Feb 9, 2021 at 11:34

answered Feb 9, 2021 at 11:29

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

Anil Over a year ago

If we want to use the week array in place of the date array? Can you please suggest that too? select CURRENT_DATE - interval '1week' * any(ARRAY[1,4,7])

Collectives™ on Stack Overflow

Postgres select date on the basis of interval array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related