Получение результатов между двумя датами в PostgreSQL

У меня есть следующая таблица:

+-----------+-----------+------------+----------+
| id        | user_id   | start_date | end_date |
| (integer) | (integer) | (date)     | (date)   |
+-----------+-----------+------------+----------+

поля start_date а также end_date содержат значения даты, такие как YYYY-MM-DD,

Запись из этой таблицы может выглядеть так: (1, 120, 2012-04-09, 2012-04-13),

Я должен написать запрос, который может получить все результаты, соответствующие определенному периоду.

Проблема в том, что если я хочу получить результаты из 2012-01-01 в 2012-04-12Я получаю 0 результатов, хотя есть запись с start_date = "2012-04-09" а также end_date = "2012-04-13",

Источник

9 ответов

Решение
 SELECT *
   FROM mytable
  WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE);

Функции даты и времени - соответствующий раздел в документации.

Источник

Предполагая, что вы хотите все "перекрывающиеся" периоды времени, то есть все, которые имеют хотя бы один общий день.

Постарайтесь представить временные интервалы на прямой линии и передвигайте их перед глазами, и вы увидите необходимые условия.

SELECT *
FROM   tbl
WHERE  start_date <= '2012-04-12'::date
AND    end_date   >= '2012-01-01'::date;

Это иногда быстрее для меня, чем OVERLAPS - что является еще одним хорошим способом сделать это (как @Marco уже предоставил).

Обратите внимание на небольшуюразницу( по документации):

OVERLAPSавтоматически принимает более раннее значение пары в качестве начала. Каждый период времени считается представляющим полуоткрытый интервалstart <= time < end, если начало и конец не равны, в этом случае он представляет этот единственный момент времени. Это означает, например, что два периода времени с общей конечной точкой не перекрываются.

Жирный акцент мой.

Спектакль

Для больших таблиц правильный индекс может помочь производительности (много).

CREATE INDEX tbl_date_inverse_idx ON tbl(start_date, end_date DESC);

Возможно, с другим (ведущим) индексным столбцом, если у вас есть дополнительные условия выбора.

Обратите внимание на обратный порядок двух столбцов. Детальное объяснение:

Источник

У меня был тот же вопрос, и он ответил так, если это могло бы помочь.

select * 
from table
where start_date between '2012-01-01' and '2012-04-13'
or    end_date   between '2012-01-01' and '2012-04-13'
Источник

Чтобы запрос работал в любых региональных настройках, попробуйте отформатировать дату самостоятельно:

SELECT * 
  FROM testbed 
 WHERE start_date >= to_date('2012-01-01','YYYY-MM-DD')
   AND end_date <= to_date('2012-04-13','YYYY-MM-DD');
Источник

Смотря на даты, для которых это не работает - те, где день меньше или равен 12 - мне интересно, анализирует ли он даты как в формате ГГГГ-ДД-ММ?

Источник 
SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');
Источник

Давай попробуем range тип данных.

--образец данных.

      begin;
create temp table tbl(id  serial, user_id integer, start_date date, end_date date);
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-13');
insert into tbl(user_id, start_date, end_date) values(1, '2012-01-09', '2012-04-12');
insert into tbl(user_id, start_date, end_date) values(1, '2012-02-09', '2012-04-10');
insert into tbl(user_id, start_date, end_date) values(1, '2012-04-09', '2012-04-10');
commit;

добавить новый столбец диапазона дат.

      begin;
alter table tbl add column tbl_period daterange ;
update tbl set tbl_period = daterange(start_date,end_date);
commit;

- сейчас время теста.

      select * from tbl
    where tbl_period && daterange('2012-04-10' ::date, '2012-04-12'::date);

возвращает:

       id | user_id | start_date |  end_date  |       tbl_period
----+---------+------------+------------+-------------------------
  1 |       1 | 2012-04-09 | 2012-04-13 | [2012-04-09,2012-04-13)
  2 |       1 | 2012-01-09 | 2012-04-12 | [2012-01-09,2012-04-12)

дополнительная ссылка: https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE

Источник

Не в обиду, но для проверки производительности SQL я выполнил некоторые из вышеупомянутых решений в pgsql.

Позвольте мне поделиться с вами статистикой топ-3 подходов к решению, с которыми я столкнулся.

1) Взял: 1,58 мс

2) Взял: 2,87 мс

3) Взял: 3,95 мс

Теперь попробуйте это:

 SELECT * FROM table WHERE DATE_TRUNC('day', date ) >= Start Date AND DATE_TRUNC('day', date ) <= End Date

Теперь это решение заняло: 1.61 Ср.

И лучшее решение - первое, предложенное Marco Mariani.

Источник

Вы должны использовать метод извлечения части даты:

SELECT * FROM testbed WHERE start_date  ::date >= to_date('2012-09-08' ,'YYYY-MM-DD') and date::date <= to_date('2012-10-09' ,'YYYY-MM-DD')
Источник
Другие вопросы по тегам