Find entries with array of dates if any is currently available within a certain range in postgres

Question

I have a postgres table with columns:

id: text
availabilities: integer[]

A certain ID can has multiply availabilities (different days (not continuous) in a range for up to a few years). Each availability is a Unix timestamp (in seconds) for a certain day. Hours, minutes, seconds, ms are set to 0, i.e. a timestamp represents the start of a day.

Question: How can I find all IDs very fast, which contain at least one availability inbetween a certain from-to range (also timestamp)? I can also store them differently in an array, e.g "days since epoch", if needed (to get 1 (day) steps instead of 86400 (second) steps).

However, if possible (and speed is roughly same), I want to use an array and on row per each entry.

Example:

Data (0 = day-1, 86400 = day-2, ...)

| id |          availabilities            |

|  1 | [0 , 86400, 172800, 259200         ]
|  2 | [    86400,         259200         ]
|  3 | [                         , 345600 ]
|  4 | [         , 172800,                ]
|  5 | [0,                                ]

Now I want to get a list of IDs which contains at least 1 availability which:

• is between 86400 AND 259200 --> ID 1, 2, 4
• is between 172800 AND 172800 --> ID 1, 4
• is between 259200 AND (max-int) --> ID 1,2,3

Answer 1

In PostgreSQL unnest function is the best function for converting array elements to rows and gets the best performance. You can use this function. Sample Query:

with mytable as (
        select 1 as id, '{12,2500,6000,200}'::int[] as pint
        union all
        select 2 as id, '{0,200,3500,150}'::int[]
        union all
        select 4 as id, '{20,10,8500,1100,9000,25000}'::int[]
    )
select id, unnest(pint) as pt from mytable;

-- Return  
1   12
1   2500
1   6000
1   200
2   0
2   200
2   3500
2   150
4   20
4   10
4   8500
4   1100
4   9000
4   25000