sql - Postgresql 从日期数组之间的日期范围中选择

Question

如果此查询返回请求范围内的日期。

select created_at from user where created_at between '2015-01-06 00:00:00.000000' and '2015-03-06 00:00:00.000000'

有没有办法获得这样的结果，但日期数组中包含多个日期。

只是为了举例说明我想说的话。我有这个日期数组，总是会有第一个和最后一个日期。

Array['2015-01-06 00:00:00.000000','2015-02-10 15:17:18.895000' <- First range
      '2017-10-05 14:41:04.191000','2017-10-11 14:49:36.454000' <- Second range

那么有没有办法把这样的脚本放在一起？

select created_at from win_users 
where (created_at between [First Date] and [Second Date])
or (created_at between [Third Date] and [Fourth Date])

但没有使用循环来连接 where 语句？

Answer 1

在这种情况下，一组日期非常不舒服。使用 daterange 的数组和 containtment operator <@ ，例如:

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(array[
    daterange('2015-01-06','2015-02-10'), 
    daterange('2017-10-05','2017-10-11')])

 id |     created_at      
----+---------------------
  1 | 2015-01-10 00:00:00
  3 | 2017-10-10 00:00:00
(2 rows)

如果您绝对想使用日期数组(老实说我不这么认为)，请使用此函数将其转换为 daterange 数组:

create or replace function date_pairs_to_ranges(date[])
returns daterange[] language sql as $$
    select array_agg(daterange(d1, d2))
    from unnest($1) with ordinality as u1(d1, o1)
    join unnest($1) with ordinality as u2(d2, o2)
    on o1/ 2* 2 < o1 and o2 = o1+ 1
$$;

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(
    date_pairs_to_ranges(array[
        '2015-01-06','2015-02-10',
        '2017-10-05','2017-10-11']::date[]))