Re: Add support for AT LOCAL

Vik Fearing <vik@postgresfriends.org>

From: Vik Fearing <vik@postgresfriends.org>

To: Laurenz Albe <laurenz.albe@cybertec.at>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2023-06-06T08:24:55Z

Lists: pgsql-hackers

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

Commits

Dodge a compiler bug affecting timetz_zone/timetz_izone.
- f4f55d5124a9 13.13 landed
- e98f47d570a2 11.22 landed
- d8c2877b9efa 12.17 landed
- 85c9dda81887 14.10 landed
- 5463319acd19 15.5 landed
- 1ad3a7bd396a 16.1 landed
- 19fa977311b9 17.0 landed
Back-patch test cases for timetz_zone/timetz_izone.
- fdf8b70a51cf 13.13 landed
- dc159b95d921 14.10 landed
- b18781c2df44 12.17 landed
- 4a427b82cf19 16.1 landed
- 29231dbd40cb 15.5 landed
- 05b4614decac 11.22 landed
Add regression test coverage for timetz_izone().
- 2f047203074c 17.0 landed
Add support for AT LOCAL
- 97957fdbaa42 17.0 landed
doc: Mention timezone(zone, time) in section for AT TIME ZONE
- 7e1f544827ec 17.0 landed
Use HASH_BLOBS for xidhash.
- a1b8aa1e4eec 14.0 cited