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

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

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