On 1/12/24 02:01, Alban Hertroijs wrote:
ciscache=> select ToDatetimeOffset(current_timestamp,
'Europe/Amsterdam'), to_char(current_timestamp, '-MM-DD
HH24:MI:SS.US0 TZH:TZM');
ERROR: invalid value for parameter "TimeZone": "tz_"
CONTEXT: SQL statement "set local timezone to tz_"
PL/pgSQ
On 1/12/24 00:51, Alban Hertroijs wrote:
Following up on my own mail from yesterday evening, here's the output
that shows the function using set_config 'leaking' the timezone change
to outside the function (the first select vs. the 2^nd select) into the
(same) transaction, whereas the functio
From: Adrian Klaver
> I didn’t succeed in calling SET LOCAL TIMEZONE from within the function.
> Could be I missed something, then Google (stackoverflow) pointed me to
> set_config().
CREATE OR REPLACE FUNCTION public.tz_fnc()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
SET LOCA
> set_config ( setting_name text, new_value text, is_local boolean ) → text
>
> Sets the parameter setting_name to new_value, and returns that value. If
> is_local is true, the new value will only apply during the current
> transaction. If you want the new value to apply for the rest of the curr
On 1/11/24 6:20 PM, Jim Nasby wrote:
On 1/11/24 5:53 PM, Tom Lane wrote:
Adrian Klaver writes:
test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-MM-DD
HH24:MI:SS.US0 TZH:TZM') ;
to_char
2024-01-12 00:44:57.5421420 +00:00
On 1/11/24 5:53 PM, Tom Lane wrote:
Adrian Klaver writes:
test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-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
Adrian Klaver writes:
> test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-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
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 almo
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, '-MM-D
On 1/11/24 11:04, Alban Hertroys wrote:
I did manage to apply it to the second function header, which I think behaves
such that the time zone change stays within function scope. Right now I’m not
100% sure that I verified that. More to check tomorrow.
CREATE OR REPLACE FUNCTION public.tz
On 1/11/24 11:04, Alban Hertroys wrote:
I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could
be I missed something, then Google (stackoverflow) pointed me to set_config().
CREATE OR REPLACE FUNCTION public.tz_fnc()
RETURNS void
LANGUAGE plpgsql
AS $function$
BE
> On 11 Jan 2024, at 18:27, Adrian Klaver wrote:
>
> On 1/11/24 08:48, Adrian Klaver wrote:
>> On 1/11/24 08:04, Alban Hertroijs wrote:
>
>>> The drawback, as mentioned, being that we need to maintain those functions
>>> in each deployment, which is a bit of a hassle (albeit a minor one) beca
> On 11 Jan 2024, at 17:43, Adrian Klaver wrote:
>
> On 1/11/24 07:06, Alban Hertroijs wrote:
>> Hi all,
>
>> In the above, I worked around the issue using a couple of user-defined
>> functions in PG. That should give a reasonable idea of the desired
>> functionality, but it's not an ideal s
Alban Hertroijs wrote:
> 1). The first function has as a drawback that it changes the time zone for
> the entire transaction (not sufficiently isolated to my tastes)
But if you add in the function declaration
SET timezone TO 'Europe/Amsterdam'
like in your 2nd function, or simply
S
On 1/11/24 08:48, Adrian Klaver wrote:
On 1/11/24 08:04, Alban Hertroijs wrote:
The drawback, as mentioned, being that we need to maintain those
functions in each deployment, which is a bit of a hassle (albeit a
minor one) because we need to customise both the TDV side and the
PostgreSQL sid
On 1/11/24 08:04, Alban Hertroijs wrote:
> In the above, I worked around the issue using a couple of
user-defined functions in PG. That should give a reasonable idea of
the desired functionality, but it's not an ideal solution to my problem:
> 1). The first function has as a dra
On 1/11/24 07:06, Alban Hertroijs wrote:
Hi all,
In the above, I worked around the issue using a couple of user-defined
functions in PG. That should give a reasonable idea of the desired
functionality, but it's not an ideal solution to my problem:
1). The first function has as a drawback tha
> In the above, I worked around the issue using a couple of user-defined
> functions in PG. That should give a reasonable idea of the desired
> functionality, but it's not an ideal solution to my problem:
> 1). The first function has as a drawback that it changes the time zone for
> the entire t
Am 11.01.2024 um 16:06 schrieb Alban Hertroijs :
Hi all,
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_ch
> Am 11.01.2024 um 16:06 schrieb Alban Hertroijs :
>
> Hi all,
>
> 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
> t
> > In the above, I worked around the issue using a couple of user-defined
> > functions in PG. That should give a reasonable idea of the desired
> > functionality, but it's not an ideal solution to my problem:
> > 1). The first function has as a drawback that it changes the time zone for
> > th
> In the above, I worked around the issue using a couple of user-defined
> functions in PG. That should give a reasonable idea of the desired
> functionality, but it's not an ideal solution to my problem:
> 1). The first function has as a drawback that it changes the time zone for
> the entire t
Hi all,
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, '-MM-DD HH24:MI:SS.US0 TZH:TZM'). Unfortu
23 matches
Mail list logo
