Re: Time zone offset in to_char()
| От | Alban Hertroijs |
|---|---|
| Тема | Re: Time zone offset in to_char() |
| Дата | |
| Msg-id | DB8P189MB104685ABEAAB813986343B4CE26F2@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM обсуждение исходный текст |
| Ответ на | Re: Time zone offset in to_char() (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Ответы |
Re: Time zone offset in to_char()
|
| Список | pgsql-general |
From: Adrian Klaver <adrian.klaver@aklaver.com>> 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.
В списке pgsql-general по дате отправления: