Comparing TIME WITH TIME ZONE returns unexpected result

Question

Why does this query return false? Is it because of the 22:51:13.202248 +01:00 format?

SELECT now()::time at TIME ZONE 'Europe/London'; -- 22:51:13.202248 +01:00

SELECT now()::time at time zone 'Europe/London' > '22:00:00'::time
   AND now()::time < '23:35:00'::time as is_currently_open; -- false

Answer 1

I think you just need to adapt the timezone on the second predicate to get the result you want:

SELECT 
        now()::time at time zone 'Europe/London' > '22:00:00'::time 
    AND now()::time at time zone 'Europe/London' < '23:35:00'::time 
    ----------------^----------------> here
AS is_currently_open;

Answer 2

now()::time at time zone 'Europe/London'

... returns a value of time with time zone (timetz):

Then you compare it to time [without time zone]. Don't do this. The time value is coerced to timetz in the process and a time offset is appended according to the current timezone setting. Meaning, your expression will evaluate differently with different settings. What's more, DST rules are not applied properly. You want none of this! See:

db<>fiddle here

More generally, don't use time with time zone (timetz) at all. The type is broken by design and officially discouraged in Postgres. See:

• Postgres time with time zone equality

Use instead:

SELECT (now() AT TIME ZONE 'Europe/London')::time > '22:00:00'
   AND (now() AT TIME ZONE 'Europe/London')::time < '23:35:00' AS is_currently_open;

The right operand can be an untyped literal now, it will be coerced to time as it should.

BETWEEN is often the wrong tool for times and timestamps. See:

• How to add a day/night indicator to a timestamp column?

But it would seem that >= and <= are more appropriate for opening hours? Then BETWEEN fits the use case and makes it a bit simpler:

SELECT (now() AT TIME ZONE 'Europe/London')::time
       BETWEEN '22:00:00' AND '23:35:00' AS is_currently_open;

Related:

• Perform this hours of operation query in PostgreSQL