Получение результатов между двумя датами в 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".

Ответ 1

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

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

Ответ 2

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

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

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);

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

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

Оптимизация запросов по диапазону временных меток (два столбца)

Ответ 3

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

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'

Ответ 4

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

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');

Ответ 5

Глядя на даты, для которых он не работает - те, где день меньше или равен 12 - Мне интересно, обрабатывает ли он даты в формате YYYY-DD-MM?

Ответ 6

Вы должны использовать метод выборки даты:

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

Ответ 7

Без обид, но для проверки производительности sql я выполнил часть вышеупомянутого решения pgsql.

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

1) Взято: 1,58 MS Avg

2) Взял: 2.87 MS Avg

3) Взял: 3,95 MS Avg

Теперь попробуйте следующее:

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

Теперь это решение получило: 1,61 Сред.

И лучшее решение - это первое, что предлагается marco-mariani

Ответ 8

SELECT *
FROM ecs_table
WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE + interval '1');