После изменения таблицы для использования timestamptz для всех столбцов времени я вижу очень странное поведение при выборе на основе временного диапазона. Когда часовой пояс пользователя UTC, он работает, результат правильный. Если часовой пояс пользователя отличается от UTC, результат равен нулю. Я ожидаю, что результат не должен зависеть от часового пояса пользователя.
set timezone = 'UTC';
select count(*)
from table1
where modified >= timestamp '2016-07-25 08:00' at time zone 'GB'
and modified < timestamp '2016-07-25 09:00' at time zone 'GB'
;
set timezone = 'GB';
select count(*)
from table1
where modified >= timestamp '2016-07-25 08:00' at time zone 'GB'
and modified < timestamp '2016-07-25 09:00' at time zone 'GB'
;
В объяснении запроса я вижу, что Postgres правильно конвертирует константы времени. В первом случае (UTC) это показывает
'2016-07-25 07:00:00+00'::timestamp with timezone
'2016-07-25 08:00:00+00'::timestamp with timezone
Второй случай (GB) показывает
'2016-07-25 08:00:00+01'::timestamp with timezone
'2016-07-25 09:00:00+01'::timestamp with timezone
Что еще более странно, он не работает полностью только при разнице во времени в один час. Когда я фильтрую в течение двух часов, с 8 до 10 утра, запрос возвращает ненулевой результат. Это работает так
count(*) from 08:00 to 09:00 - zero
count(*) from 09:00 to 10:00 - zero
count(*) from 08:00 to 10:00 = actual count from 09:00 to 10:00
Похоже, есть ошибка в преобразовании диапазона. Время начала преобразуется, а время окончания — нет.
Интересно, видел ли кто-нибудь эту проблему раньше. Postgres версии 9.3.4 работает на Ubuntu.
Определение столбца modified
...
modified timestamp with time zone NOT NULL DEFAULT now(),
...
ОБЪЯСНЯТЬ
Aggregate (cost=374.84..374.85 rows=1 width=0)
Output: count(*)
-> Index Only Scan using modified_idx on public.table1 (cost=0.56..362.14 rows=5079 width=0)
Output: modified
Index Cond: ((table1.modified >= '2016-07-25 08:00:00+01'::timestamp with time zone) AND (table1.modified < '2016-07-25 09:00:00+01'::timestamp with time zone))