Horton

Re: Inconsistency of timezones in postgresql

Christophe Pettus <xof@thebuild.com>

View thread
From: Christophe Pettus <xof@thebuild.com>
To: Chris BSomething <xpusostomos@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Date: 2024-08-02T05:08:00Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: add example of sign mismatch with POSIX/ISO-8601 time zones


> On Aug 1, 2024, at 21:55, Chris BSomething <xpusostomos@gmail.com> wrote:
> It says that it assumes that the "value is in the NAMED timezone". What actually happens is it assumes the value is in your environmental time zone, and DISPLAYS it in your current zone.

No, that's not correct.  There are two separate conversions happening: first, it converts the TIMESTAMP value to a TIMESTAMPTZ value.  In doing so, it assumes TIMESTAMP value represents a timestamp at the time zone specified with AT TIME ZONE.  Now, you have a TIMESTAMPTZ, which (internally) is in UTC.  When that is displayed, it's converted to the session timezone.

xof=# select '2024-01-02 00:00'::timestamp;
      timestamp       
---------------------
 2024-01-02 00:00:00
(1 row)


xof=# show timezone;
  TimeZone   
------------
 US/Pacific
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'US/Pacific';
        timezone         
------------------------
 2024-01-02 00:00:00-08
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
        timezone        
 ------------------------
 2024-01-01 16:00:00-08
(1 row)

xof=# set timezone = 'UTC';
SET
xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
        timezone         
------------------------
 2024-01-02 00:00:00+00
(1 row)

This can indeed be confusing, but it works as documented.