Outputting UTC offset with to_char()

On Sun, Oct 21, 2012 at 05:40:40PM -0400, Andrew Dunstan wrote:
>
> I'm not sure if this has come up before.
>
> A client was just finding difficulties because to_char() doesn't
> support formatting the timezone part of a timestamptz numerically
> (i.e. as +-hhmm) instead of using a timezone name. Is there any
> reason for that? Would it be something worth having?

Great idea!  I have developed the attached patch to do this:

    test=> SELECT to_char(current_timestamp, 'OF');
     to_char
    ---------
     -04
    (1 row)

    test=> SELECT to_char(current_timestamp, 'TMOF');
     to_char
    ---------
     -04
    (1 row)

    test=> SET timezone = 'Asia/Calcutta';
    SET
    test=> SELECT to_char(current_timestamp, 'OF');
     to_char
    ---------
     +05:30
    (1 row)

    test=> SELECT to_char(current_timestamp, 'FMOF');
     to_char
    ---------
     +5:30
    (1 row)

I went with the optional colon and minutes because this is how we output
it:

    test=> SELECT current_timestamp;
                  now
    -------------------------------
     2013-06-28 22:02:24.773587-04
                               ---
    (1 row)

    test=> set timezone = 'Asia/Calcutta';
    SET
    test=> SELECT current_timestamp;
                   now
    ----------------------------------
     2013-06-29 07:32:29.157565+05:30
                               ------
    (1 row)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +