Re: Time zone offset in to_char()

[Alban Hertroijs <a.hertroijs@xxxxxxxxxxxxxxx>]

From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>

> 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 LOCAL  TIMEZONE = 'UTC';
     RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$

Turns out that the reason I cannot get this to work is that in my case the time zone value comes from a text parameter:

ciscache=> create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ text)

returns varchar(34)

language plpgsql

as $$

begin

        --perform set_config('timezone', tz_, true /* local */);

        set local timezone to tz_;

        return to_char(ts_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');

end;

$$;

CREATE FUNCTION

ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');

ERROR:  invalid value for parameter "TimeZone": "tz_"

CONTEXT:  SQL statement "set local timezone to tz_"

PL/pgSQL function todatetimeoffset(timestamp with time zone,text) line 4 at SQL statement

But set_config() doesn't have that issue.