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
Advertisement
Answer
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 at all. The type is broken by design and officially discouraged in Postgres. See:time with time zone
(timetz
)
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:
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: