Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

171
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda