From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Jim Nasby <jim.nasby@gmail.com>
To: Alban Hertroijs <a.hertroijs@nieuwestroom.nl>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Time zone offset in to_char()
Date: Thu, 11 Jan 2024 15:46:56 -0800
Message-ID: <f4fc9c8d-c8fb-4da2-a0f6-ace98cbc7b40@aklaver.com> (raw)
In-Reply-To: <cc4d16e8-3263-41e1-9a82-4410c0bdd347@gmail.com>
References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM>
<cc4d16e8-3263-41e1-9a82-4410c0bdd347@gmail.com>
On 1/11/24 15:32, Jim Nasby wrote:
> On 1/11/24 9:06 AM, Alban Hertroijs wrote:
>> I'm basically looking for a one-liner to convert a timestamptz (or a
>> timestamp w/o time zone if that turns out to be more convenient) to a
>> string format equal to what MS uses for their datetimeoffset type. I
>> got almost there with to_char(ts, 'YYYY-MM-DD HH24:MI:SS.US0
>> TZH:TZM'). Unfortunately(?), the server lives at time zone UTC, while
>> we need to convert to both UTC and Europe/Amsterdam zones. The above
>> always gives me +00 for the TZH output, while it should be +01 now and
>> +02 in the summer...
>
> The issue here is that timestamptz doesn't store the original timezone;
> it always converts whatever is passed in to UTC and stores that. When
> you read the timezone back, by default it will be in the timezone
> specified in the TimeZone GUC. While there's a bunch of ways you can set
> that, for what you're looking to do I don't think any of them are
> appropriate; instead you want to use either AT TIME ZONE or timezone():
>
> create table tstz(tstz timestamptz);
> insert into tstz values(now());
> SHOW timezone;
> TimeZone
> ----------
> CST6CDT
> (1 row)
>
> select * from tstz ;
> tstz
> ------------------------------
> 2024-01-11 17:29:00.04933-06
> (1 row)
>
> select timezone('UTC',tstz) from tstz ;
> timezone
> ---------------------------
> 2024-01-11 23:29:00.04933
> (1 row)
>
> select tstz AT TIME ZONE 'UTC' from tstz ;
> timezone
> ---------------------------
> 2024-01-11 23:29:00.04933
> (1 row)
The problem with this is as mentioned here:
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
"timestamp with time zone AT TIME ZONE zone → timestamp without time zone
Converts given time stamp with time zone to time stamp without time
zone, as the time would appear in that zone."
So when you do something like:
test=# set timezone = 'UTC';
SET
test=# select now() AT TIME ZONE 'UTC' ;
timezone
----------------------------
2024-01-11 23:44:46.021986
(1 row)
test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD
HH24:MI:SS.US0 TZH:TZM') ;
to_char
------------------------------------
2024-01-12 00:44:57.5421420 +00:00
(1 row)
You end up with string that does not the correct offset as the AT TIME
ZONE outputs a timestamp not timestamptz value.
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, jim.nasby@gmail.com, a.hertroijs@nieuwestroom.nl
Subject: Re: Time zone offset in to_char()
In-Reply-To: <f4fc9c8d-c8fb-4da2-a0f6-ace98cbc7b40@aklaver.com>
References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM> <cc4d16e8-3263-41e1-9a82-4410c0bdd347@gmail.com> <f4fc9c8d-c8fb-4da2-a0f6-ace98cbc7b40@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox