On Thu, Sep 20, 2012 at 09:42:33AM +0800, Craig Ringer wrote:
> Hi all
>
> I'm wondering if there's any way to convince `to_char` to add a leading zero to
> the hours in negative intervals. The current behaviour feels wrong, in that
> FMHH24:MM and HH24:MM produce the same output for negative intervals:
>
> regress=# WITH x(i) AS (VALUES (INTERVAL '9:00'),(INTERVAL '-9:00'),
> (INTERVAL '11:00'),(INTERVAL '-11:00'),(INTERVAL '101:00'),(INTERVAL
> '-101:00') )
> SELECT i as "interval", to_char(i,'HH24:MM') as "HH24:MM", to_char
> (i,'FMHH24:MM') AS "FMHH24:MM" FROM x;
> interval | HH24:MM | FMHH24:MM
> ------------+---------+-----------
> 09:00:00 | 09:00 | 9:00
> -09:00:00 | -9:00 | -9:00
> 11:00:00 | 11:00 | 11:00
> -11:00:00 | -11:00 | -11:00
> 101:00:00 | 101:00 | 101:00
> -101:00:00 | -101:00 | -101:00
> (6 rows)
>
>
> I can't find any way to produce the output '-09:00' . There's no apparent way
> to add an additional width-specifier. HH24 is clearly not constrained to be 2
> digits wide, since "-11" and "101" and "-101" are all output by "HH24". It
> seems like "-9" should be "-09" with the HH24 specifier, and "-9" with the
> "FMHH24" specifier.
>
> Opinions?
>
> Unless I'm doing something woefully wrong, Oracle compatibility doesn't seem to
> be an issue because we format intervals wildly differently to Oracle anyway:
>
> http://sqlfiddle.com/#!4/d41d8/2751
>
> and it looks like Oracle handling of intervals isn't much like Pg anyway:
>
> http://stackoverflow.com/questions/970249/format-interval-with-to-char
>
>
> Arose from trying to find a non-ugly solution to this SO post:
>
> http://stackoverflow.com/questions/12335438/server-timezone-offset-value/
> 12338490#12338490
[This is for 9.6.]
I looked over this report from 2012, and the behavior still exists. I
think we have not seen more reports about this because negative
hours/years is not something people regularly use, but you found a need
for it.
I think the big question is whether YYYY (4) or HH24 (2) represents
characters. or digits for zero-padding. printf() assumes it is
characters, e.g. %02d outputs "-2" not "-02", but I think our API
suggests it is digits, meaning the minus sign is not part of the
specific length, i.e. a minus sign is not a digit.
I have developed the attached unified-diff patch which changes the
behavior to not consider the negative sign as a digit in all the places
I thought it was reasonable.
--
Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 84e4db8..1005c52 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -2426,7 +2426,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
* display time as shown on a 12-hour clock, even for
* intervals
*/
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2,
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_hour >= 0) ? 2 : 3,
tm->tm_hour % (HOURS_PER_DAY / 2) == 0 ? HOURS_PER_DAY / 2 :
tm->tm_hour % (HOURS_PER_DAY / 2));
if (S_THth(n->suffix))
@@ -2434,19 +2434,22 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
s += strlen(s);
break;
case DCH_HH24:
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, tm->tm_hour);
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_hour >= 0) ? 2 : 3,
+ tm->tm_hour);
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
case DCH_MI:
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, tm->tm_min);
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_min >= 0) ? 2 : 3,
+ tm->tm_min);
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
break;
case DCH_SS:
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, tm->tm_sec);
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_sec >= 0) ? 2 : 3,
+ tm->tm_sec);
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
@@ -2503,7 +2506,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
break;
case DCH_OF:
INVALID_FOR_INTERVAL;
- sprintf(s, "%+0*d", S_FM(n->suffix) ? 0 : 3, (int) tm->tm_gmtoff / SECS_PER_HOUR);
+ sprintf(s, "%+0*d", S_FM(n->suffix) ? 0 : (tm->tm_gmtoff >= 0) ? 3 : 4,
+ (int) tm->tm_gmtoff / SECS_PER_HOUR);
s += strlen(s);
if ((int) tm->tm_gmtoff % SECS_PER_HOUR != 0)
{
@@ -2653,7 +2657,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
s += strlen(s);
break;
case DCH_MM:
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, tm->tm_mon);
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (tm->tm_mon >= 0) ? 2 : 3,
+ tm->tm_mon);
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
@@ -2828,7 +2833,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
i = tm->tm_year / 100 - 1;
}
if (i <= 99 && i >= -99)
- sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
+ sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : (i >= 0) ? 2 : 3, i);
else
sprintf(s, "%d", i);
if (S_THth(n->suffix))
@@ -2846,7 +2851,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
case DCH_YYYY:
case DCH_IYYY:
sprintf(s, "%0*d",
- S_FM(n->suffix) ? 0 : 4,
+ S_FM(n->suffix) ? 0 :
+ (ADJUST_YEAR(tm->tm_year, is_interval) >= 0) ? 4 : 5,
(n->key->id == DCH_YYYY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
@@ -2860,7 +2866,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
case DCH_YYY:
case DCH_IYY:
sprintf(s, "%0*d",
- S_FM(n->suffix) ? 0 : 3,
+ S_FM(n->suffix) ? 0 :
+ (ADJUST_YEAR(tm->tm_year, is_interval) >= 0) ? 3 : 4,
(n->key->id == DCH_YYY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
@@ -2874,7 +2881,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
case DCH_YY:
case DCH_IY:
sprintf(s, "%0*d",
- S_FM(n->suffix) ? 0 : 2,
+ S_FM(n->suffix) ? 0 :
+ (ADJUST_YEAR(tm->tm_year, is_interval) >= 0) ? 2 : 3,
(n->key->id == DCH_YY ?
ADJUST_YEAR(tm->tm_year, is_interval) :
ADJUST_YEAR(date2isoyear(tm->tm_year,
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index a092fc2..e9f5e77 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -948,8 +948,8 @@ SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth F
SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
FROM TIMESTAMP_TBL;
- to_char_3 | to_char
------------+-------------------------------------------------
+ to_char_3 | to_char
+-----------+--------------------------------------------------
|
|
| 1,970 1970 970 70 0 20 1 01 01 001 01 5 2440588
@@ -992,7 +992,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
- | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
+ | 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index e5a2130..40a2254 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1029,8 +1029,8 @@ SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth F
SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
FROM TIMESTAMPTZ_TBL;
- to_char_3 | to_char
------------+-------------------------------------------------
+ to_char_3 | to_char
+-----------+--------------------------------------------------
|
|
| 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
@@ -1074,7 +1074,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
- | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
+ | 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
