Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

169
Views
Comparing TIME WITH TIME ZONE returns unexpected result

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
over 4 years ago · Santiago Trujillo
2 answers
Answer question

0

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;
over 4 years ago · Santiago Trujillo Report

0

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
over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!