CODEWITHSUNDEEP
postgresqldateintervals
Anil
Anil

Reputation: 141

Postgres select date on the basis of interval array

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

Upvotes: 0

Views: 427

Answers (1)

S-Man
S-Man

Reputation: 23666

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'

Upvotes: 1

Related Questions