PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to compare two times in two different timezones if they are the same moment in time?

Here’s a scenario: Users are saving time only in a database as a string, e.g. 20:00. They expect something to happen every day at 8 PM. But 8 PM means a different time for different people given each lives in a different timezone, so the stored time is relative to each person.

Every hour, a server running in UTC runs a query to pull people for whom something should happen (we store their time zone separately).

The query boils down to comparing UTC server time (left side) with their relative stored time in the database (right side):

SELECT '20:00'::time = '12:00'::time AT TIME ZONE 'PST'

But there’s an issue on the right side: Although '12:00'::time is sort of a timezone-less object, '12:00'::time IN TIME ZONE 'PST' returns 04:00:00-08 as if we would be converting UTC time to PST time, which is not what we want.

Alternatively, I tried comparing these as strings using:

SELECT to_char('20:00'::time AT TIME ZONE 'PST', 'HH24:MI') = '12:00'

But this throws an error function to_char(time with time zone, unknown) does not exist. It seems that to_char cannot take time with zone?

So, trying to compare times or strings, I was not able to make either approach work. Any ideas on how to make either approach work? Any alternative approaches? Or any way to force postgres to treat 12:00 as 12 AM PST?

Tags:

2

Answers


  1. 0

    There is one so similar to your question. Please read the answer in this one

    Login or Signup to reply.
  2. 0

    query boils down to comparing UTC server time (left side)

    Strictly speaking you’re comparing a timezone unaware type on the left to timezone aware type on the right, which results in upcasting the one on the left by assuming the default TimeZone.

    It boils down to conversion rules and currently configured (default) settings:

    1. You can use at time zone as many times as you need, each time flipping the input between with and without the time zone. In this case you probably want just one more on either side of the equation:
      cast the left operand to a timezone-aware time in 'PST':

      SELECT '20:00'::time at time zone 'PST' = '12:00'::time AT TIME ZONE 'PST';

      or add another shift from 'PST' to 'UTC' on the right to both shift it and drop its timezone after that:

      SELECT '20:00'::time = '12:00'::time AT TIME ZONE 'PST' at time zone 'UTC';

    2. When you compare time to timetz Postgres uses its type coercion/conversion rules to find a matching = operator, because there’s no built-in time=timetz:

      select oprleft::regtype,oprright::regtype,* 
from pg_operator 
where '{time,timetz}'::regtype[] && array[oprleft,oprright]::regtype[]
and oprname='=';
      oprleft oprright oid oprname oprnamespace oprowner oprkind oprcanmerge oprcanhash oprleft oprright oprresult oprcom oprnegate oprcode oprrest oprjoin
      time without time zone time without time zone 1108 = 11 10 b t t 1083 1083 16 1108 1109 time_eq eqsel eqjoinsel
      time with time zone time with time zone 1550 = 11 10 b t t 1266 1266 16 1550 1551 timetz_eq eqsel eqjoinsel

      There’s only a time=time and a timetz=timetz. If you try to mix them, those rules will pick the timezone-aware variant as the preference:

      select typname 
from pg_type 
where typname ilike '%time%'
  and typispreferred;
      typname
      timestamptz

      As a result, you end up with time::timetz=timetz operation.

    3. When the cast time::timetz happens, Postgres assumes the default TimeZone with zero regard for the context of this cast. Even though you clearly specified 'PST' on the right, the left will use whatever TimeZone is set as the default at your transaction, session, user, database or system level right now – unless overriden.

    4. '12:00'::time IN TIME ZONE 'PST' returns 04:00:00-08

      According to this, it would indicate that you’re one 9.6 or earlier. Latest supported version is 12, until November 14th 2024.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search