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 <http://sqlfiddle.com/#%214/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
--
Craig Ringer
