Re: Getting a leading zero on negative intervals with to_char?

On Wed, May 13, 2015 at 01:40:41PM -0400, Bruce Momjian wrote:
> > 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.

Applied.

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

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +