Search code examples
postgresqldateintervals

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

Solution

  • 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'