Re: BUG #15698: to_char doesn't return expected value with negative INTERVAL

Thanks Bruce for your answer.
It helps me a lot and should fix my use case.

Le mar. 9 avr. 2019 à 19:23, Bruce Momjian <bruce(at)momjian(dot)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(dot)celles(at)gmail(dot)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():
>
> \set var -(3 * 60 * 60 * 1000 + 7*60 * 1000 + 12345)
>
> 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
>
> I hope this helps.
>
> --
> 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. +
> + Ancient Roman grave inscription +
>