Le mar. 9 avr. 2019 à 19:23, Bruce Momjian <bruce@momjian.us> a écrit :
On Sat, Mar 16, 2019 at 08:11:19PM +0000, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15698 > Logged by: Sébastien Celles > Email address: s.celles@gmail.com > PostgreSQL version: 10.5 > Operating system: Windows 10 > Description: > > Hello, > > This is my first bug report here (despite I'm using PostgreSQL since many > years !) > > I don't know if it's a bug... but the following query > > SELECT to_char(-(3 * 60 * 60 * 1000 + 7*60 * 1000 + 12345) * INTERVAL '1 > millisecond', 'HH24:MI:SS.MS') as interv; > > doesn't return the result I was expecting. > > It returns: > > '-03:-07:-12.-345' > > I was expecting > > '-03:07:12.345' > > If it's not a bug (but a feature ;-) )... is there a way to return result as > I was expecting.
I am sorry for my delay in replying.
Yes, I agree the current output looks odd. You would think that to_char() could just roll the sign up to a single mention, but intervals store values in three parts:
typedef struct { TimeOffset time; /* all time units other than days, months and * years */ int32 day; /* days, after time for alignment */ int32 month; /* months and years, after time for alignment */ } Interval;
Those parts can have different signs. Here is an example:
SELECT to_char('-1 month 2 days -3 hours'::interval, 'MM DD HH') AS interv; interv ------------ -01 02 -03
Therefore, we output _all_ units with separate signs. (I don't know how I would pass a single negative value into to_char() for timestamp/timestamptz.)
The values only cross the three unit boundaries when we call "justify" functions:
List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------------+------------------+---------------------+------ pg_catalog | justify_days | interval | interval | func pg_catalog | justify_hours | interval | interval | func pg_catalog | justify_interval | interval | interval | func
Here is a psql query that optionally outputs the negative sign of your calculation, and then passes the absolute value to to_char():
SELECT CASE WHEN :var < 0 THEN '-' END || to_char(abs(:var) * INTERVAL '1 millisecond', 'HH24:MI:SS.MS') as interv; interv --------------- -03:07:12.345
