Getting a leading zero on negative intervals with to_char?

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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@momjian.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                             +

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