How to convert timestamp field to ISO 8601 string in a given time zone? [duplicate]

Question

I have a "timestamp with time zone" field in a table.
I need to return is as iso 8601 string in a give time zone.
The closest thing that I managed to do is this:

select to_char(crtdate, 'YYYY-MM-DD"T"HH24:MI:SS.MSOF:"00"')  from t1

But it returns a timestamp in a system default time zone (UTC) i.e. something like this:

2017-07-12T02:46:26.194+00:00

while I need it be formatted for a specific time zone.
E.g.

2017-07-12T14:46:26.194+12:00

for "Pacific/Auckland".

Can someone please advise how this can be achieved?

we are on PG v 9.6.

Thank you,

Answer 1

You can play with GUC parameters datestyle and timezone inside a function to get what you want. Here is an example (however, it returns microseconds, so probably you'll need to tune it a bit):

create or replace function timestamp_iso8601(ts timestamptz, tz text) returns text as $$
declare
  res text;
begin
  set datestyle = 'ISO';
  perform set_config('timezone', tz, true);
  res := ts::timestamptz(3)::text;
  reset datestyle;
  reset timezone;
  return replace(res, ' ', 'T') || ':00';
end;
$$ language plpgsql volatile;

Results:

test=# select timestamp_iso8601(now()::timestamptz, 'Europe/Moscow');
       timestamp_iso8601
-------------------------------
 2017-07-12T08:56:58.692985+03:00

test=# select timestamp_iso8601(now()::timestamptz, 'Pacific/Auckland');
       timestamp_iso8601
-------------------------------
 2017-07-12T17:59:05.863483+12:00
(1 row)

Update: edited. You can use timestamptz(3), specifying the precision (by default, it will go with microseconds, while 3 will keep only milliseconds). Alternatively, you can use res := to_char(ts::timestamptz, 'IYYY-MM-DDT HH24:MI:SS:MSOF'); instead of ::timestamptz(3)::text conversion chain, and in this case (3) will not be needed.