3

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,

spoonboy
  • 2,570
  • 5
  • 32
  • 56
  • @a_horse_with_no_name this will give a timestamp in Postgres format, not ISO 8601 – Nick Jul 12 '17 at 05:35
  • do you need to get that value for current `timezone` GUC setting (set by `set timezone = ...;`) or you want to dynamically (w/o touching GUC) get ISO8601 values for various timezones? – Nick Jul 12 '17 at 05:37
  • @a_horse_with_no_name internally, there is no any format, that's right, but there is a default output format, and this is what matters here. What is more important, your comments lead to not the direction the topic starter needs -- `at time zone ...` will give you a value w/o any timezone information, while the author needs ISO 8601, with hours shift (however, `at time zone ...` can help to build a proper custom function) – Nick Jul 12 '17 at 05:47
  • If you have any doubts that "there is" output format – https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT "The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German." – Nick Jul 12 '17 at 05:48
  • This might help: https://stackoverflow.com/q/10797720/330315 or maybe [this](http://www.postgresonline.com/journal/archives/257-Working-with-Timezones.html) –  Jul 12 '17 at 05:59
  • Well, formally you are right, but this discussion can lead us to things «3 can be less than 2 if you ("your SQL client") redefine the numbers and use them in the order 1, 3, 2, 4, 5...». If an SQL client doesn't use `datestyle` and `timezone` to display timestamps -- that's ф bad SQL client, at least for work with Postgres. – Nick Jul 12 '17 at 06:12
  • @Nick: shrug. I prefer SQL clients where I can specifically configure the format of the display in the client. –  Jul 12 '17 at 06:23
  • BTW, the question is not the same. You refer to very similar one, but again, here the author needs a special format. – Nick Jul 12 '17 at 07:00
  • The question is not the same. – spoonboy Jul 12 '17 at 10:00

1 Answers1

4

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.

Nick
  • 2,423
  • 13
  • 21
  • Not really correct attaching the time zone offset minutes as `|| ':00'`. this approach does not take in to account that there are places like Sri Lanka where the offset is UTC+05:30. – mantonovic Jan 29 '20 at 15:07