Re: Returning timestamp with timezone at specified timezone irrespective of client timezone

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