Re: Outputting UTC offset with to_char()
От | Bruce Momjian |
---|---|
Тема | Re: Outputting UTC offset with to_char() |
Дата | |
Msg-id | 20130701174116.GE16348@momjian.us обсуждение исходный текст |
Ответ на | Outputting UTC offset with to_char() (Bruce Momjian <bruce@momjian.us>) |
Список | 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. +
В списке pgsql-hackers по дате отправления: