Re: Outputting UTC offset with to_char()

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(at)momjian(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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