Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

170
Visualizações
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 Respostas
Responde à pergunta

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 Relatório

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda