postgresql datetime timezone timestamp-with-timezone

Postgres timestamptz column and user's timezone

After changing table to use timestamptz for all time columns I see very odd behavior when do select based on time range. When user's timezone is UTC, it works, result is correct. When user's timezone is not UTC, result is zero. I'd expect that result shouldn't depend on user's timezone.

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

In query explain I see that Postgres convert time constants correctly. In first case (UTC) it shows

'2016-07-25 07:00:00+00'::timestamp with timezone 
'2016-07-25 08:00:00+00'::timestamp with timezone

Second case (GB) shows

'2016-07-25 08:00:00+01'::timestamp with timezone 
'2016-07-25 09:00:00+01'::timestamp with timezone

What's even more odd, it doesn't work completely only when time difference is one hour. When I filter for two hours, from 8AM to 10AM, query returns non-zero result. It works like this

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

It looks like there is a bug in range conversions. Starting time is converted but end time is not.

I wonder if somebody saw this issue before. Postgres is 9.3.4, running on Ubuntu.

Definition of modified column

...
modified timestamp with time zone NOT NULL DEFAULT now(),
...

EXPLAIN

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

Solution

This seems impossible, and the only explanation that comes to mind is that the index is corrupted.

REINDEX INDEX modified_idx;

will rebuild the index, hopefully that will fix the problem.