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

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15698
Logged by:          Sébastien Celles
Email address:      [hidden email]
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.

Kind regards

Reply | Threaded
Open this post in threaded view
|

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

Bruce Momjian
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:      [hidden email]
> 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  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

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

s.celles@gmail.com
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 <[hidden email]> 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:      [hidden email]
> 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  <[hidden email]>        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 +