On Sun, 2020-09-27 at 17:16 -0400, aNullValue (Drew Stemen) wrote:
> I've attempted to obtain help with this problem from several other places,
> but numerous
> individuals recommended I ask this mailing list.
>
> What I need is for the ability to return a timestamp with timezone, using the
> UTC
> offset that corresponds to a column-defined timezone, irrespective of the
> client/session configured timezone.
Try a function like this:
CREATE FUNCTION format_timestamp(
ts timestamp with time zone,
time_zone text
) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
tz text;
result text;
BEGIN
tz := current_setting('timezone');
PERFORM set_config('timezone', time_zone, TRUE);
result := ts AT TIME ZONE 'UTC' AT TIME ZONE 'UTC';
PERFORM set_config('timezone', tz, TRUE);
RETURN result;
END;$$;
SELECT format_timestamp(current_timestamp, '+08');
format_timestamp
-------------------------------
2020-09-28 17:15:25.083677+08
(1 row)
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
