Re: Outputting UTC offset with to_char() - Mailing list pgsql-hackers

Applied.  I referenced macros for some of the new constants, e.g.
SECS_PER_HOUR.

---------------------------------------------------------------------------

On Fri, Jun 28, 2013 at 10:04:49PM -0400, Bruce Momjian wrote:
> 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. +

> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> new file mode 100644
> index 7c009d8..5765ddf
> *** a/doc/src/sgml/func.sgml
> --- b/doc/src/sgml/func.sgml
> *************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
> *** 5645,5650 ****
> --- 5645,5654 ----
>           <entry><literal>tz</literal></entry>
>           <entry>lower case time-zone name</entry>
>          </row>
> +        <row>
> +         <entry><literal>OF</literal></entry>
> +         <entry>time-zone offset</entry>
> +        </row>
>         </tbody>
>        </tgroup>
>       </table>
> diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
> new file mode 100644
> index 7b85406..4c272ef
> *** a/src/backend/utils/adt/formatting.c
> --- b/src/backend/utils/adt/formatting.c
> *************** typedef enum
> *** 600,605 ****
> --- 600,606 ----
>       DCH_MS,
>       DCH_Month,
>       DCH_Mon,
> +     DCH_OF,
>       DCH_P_M,
>       DCH_PM,
>       DCH_Q,
> *************** static const KeyWord DCH_keywords[] = {
> *** 746,751 ****
> --- 747,753 ----
>       {"MS", 2, DCH_MS, TRUE, FROM_CHAR_DATE_NONE},
>       {"Month", 5, DCH_Month, FALSE, FROM_CHAR_DATE_GREGORIAN},
>       {"Mon", 3, DCH_Mon, FALSE, FROM_CHAR_DATE_GREGORIAN},
> +     {"OF", 2, DCH_OF, FALSE, FROM_CHAR_DATE_NONE},        /* O */
>       {"P.M.", 4, DCH_P_M, FALSE, FROM_CHAR_DATE_NONE},    /* P */
>       {"PM", 2, DCH_PM, FALSE, FROM_CHAR_DATE_NONE},
>       {"Q", 1, DCH_Q, TRUE, FROM_CHAR_DATE_NONE}, /* Q */
> *************** static const int DCH_index[KeyWord_INDEX
> *** 874,880 ****
>       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
>       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
>       -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
> !     DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, -1,
>       DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
>       -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
>       DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
> --- 876,882 ----
>       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
>       -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
>       -1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
> !     DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF,
>       DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
>       -1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
>       DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
> *************** DCH_to_char(FormatNode *node, bool is_in
> *** 2502,2507 ****
> --- 2504,2519 ----
>                       s += strlen(s);
>                   }
>                   break;
> +             case DCH_OF:
> +                 INVALID_FOR_INTERVAL;
> +                 sprintf(s, "%+0*ld", S_FM(n->suffix) ? 0 : 3, tm->tm_gmtoff / 3600);
> +                 s += strlen(s);
> +                 if (tm->tm_gmtoff % 3600 != 0)
> +                 {
> +                     sprintf(s, ":%02ld", (tm->tm_gmtoff % 3600) / 60);
> +                     s += strlen(s);
> +                 }
> +                 break;
>               case DCH_A_D:
>               case DCH_B_C:
>                   INVALID_FOR_INTERVAL;
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2915,2923 ****
>                   break;
>               case DCH_tz:
>               case DCH_TZ:
>                   ereport(ERROR,
>                           (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> !                          errmsg("\"TZ\"/\"tz\" format patterns are not supported in to_date")));
>               case DCH_A_D:
>               case DCH_B_C:
>               case DCH_a_d:
> --- 2927,2936 ----
>                   break;
>               case DCH_tz:
>               case DCH_TZ:
> +             case DCH_OF:
>                   ereport(ERROR,
>                           (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> !                          errmsg("\"TZ\"/\"tz\"/\"OF\" format patterns are not supported in to_date")));
>               case DCH_A_D:
>               case DCH_B_C:
>               case DCH_a_d:

> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +