Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1q6S0B-0003yp-Vx
	for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Jun 2023 08:25:15 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1q6S09-000874-PP
	for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Jun 2023 08:25:13 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <vik@postgresfriends.org>)
	id 1q6S09-00086v-FX
	for pgsql-hackers@lists.postgresql.org; Tue, 06 Jun 2023 08:25:13 +0000
Received: from relay8-d.mail.gandi.net ([2001:4b98:dc4:8::228])
	by makus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <vik@postgresfriends.org>)
	id 1q6Rzy-000Utd-V0
	for pgsql-hackers@lists.postgresql.org; Tue, 06 Jun 2023 08:25:12 +0000
X-GND-Sasl: vik@postgresfriends.org
X-GND-Sasl: vik@postgresfriends.org
Received: by mail.gandi.net (Postfix) with ESMTPSA id DBADB1BF206;
	Tue,  6 Jun 2023 08:24:56 +0000 (UTC)
Message-ID: <ef0aa5f1-8ea6-088c-3a3a-5804c12f79b1@postgresfriends.org>
Date: Tue, 6 Jun 2023 04:24:55 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Add support for AT LOCAL
Content-Language: en-US
To: Laurenz Albe <laurenz.albe@cybertec.at>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <8e25dec4-5667-c1a5-6581-167d710c2182@postgresfriends.org>
 <9ff5cddd07372817ec7894202214804d46cd6176.camel@cybertec.at>
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <9ff5cddd07372817ec7894202214804d46cd6176.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/ef0aa5f1-8ea6-088c-3a3a-5804c12f79b1%40postgresfriends.org>
Precedence: bulk

On 6/6/23 03:56, Laurenz Albe wrote:
> On Mon, 2023-06-05 at 23:13 -0400, Vik Fearing wrote:
>> The Standard defines time zone conversion as follows:
>>
>> <datetime factor> ::=
>>     <datetime primary> [ <time zone> ]
>>
>> <time zone> ::=
>>     AT <time zone specifier>
>>
>> <time zone specifier> ::=
>>       LOCAL
>>     | TIME ZONE <interval primary>
>>
>>
>> While looking at something else, I noticed we do not support AT LOCAL.
>> The local time zone is defined as that of *the session*, not the server,
>> which can make this quite interesting in views where the view will
>> automatically adjust to the session's time zone.
>>
>> Patch against 3f1aaaa180 attached.
> 
> +1 on the idea; it should be faily trivial, if not very useful.

Thanks.

> At a quick glance, it looks like you resolve "timezone" at the time
> the query is parsed.  Shouldn't the resolution happen at query
> execution time?

current_setting(text) is stable, and my tests show that it is calculated 
at execution time.


postgres=# prepare x as values (now() at local);
PREPARE
postgres=# set timezone to 'UTC';
SET
postgres=# execute x;
           column1
----------------------------
  2023-06-06 08:23:02.088634
(1 row)

postgres=# set timezone to 'Asia/Pyongyang';
SET
postgres=# execute x;
           column1
----------------------------
  2023-06-06 17:23:14.837219
(1 row)


Am I missing something?
-- 
Vik Fearing