Hi,

while trying to create a query this afternoon dealing with intervals, I 
noticed the following behaviour and I wondered, if it is intended. (It was 
non-intuitive to me and if there was a warning in the docs i missed it.)
The lines marked   <---   make me wonder ...

tschwarz=> select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

tschwarz=> select '1'::interval;
 interval
----------
 00:00:01
(1 row)

tschwarz=> select -'1'::interval;
 ?column?
-----------
 -00:00:01
(1 row)

tschwarz=> select '-1'::interval;
 interval
----------
 -01:00        <---
(1 row)

tschwarz=> select -'-1'::interval;
 ?column?
----------
 01:00        <---
(1 row)


Now with 1.0 everything looks fine:


tschwarz=> select '1.0'::interval;
 interval
----------
 00:00:01
(1 row)

tschwarz=> select -'1.0'::interval;
 ?column?
-----------
 -00:00:01
(1 row)

tschwarz=> select '-1.0'::interval;
 interval
-----------
 -00:00:01
(1 row)

tschwarz=> select -'-1.0'::interval;
 ?column?
----------
 00:00:01
(1 row)


But look at 0.1:


tschwarz=> select '0.1'::interval;
  interval
-------------
 00:00:00.10
(1 row)

tschwarz=> select -'0.1'::interval;
   ?column?
--------------
 -00:00:00.10
(1 row)

tschwarz=> select '-0.1'::interval;
  interval
-------------
 00:00:00.10   <---
(1 row)

tschwarz=> select -'-0.1'::interval;
   ?column?
--------------
 -00:00:00.10   <---
(1 row)


Wed, 17 Dec 2003 20:56:38 +0100
Tilo Schwarz <mail@tilo-schwarz.de>
time interval behaviour seems odd
Tilo Schwarz <mail@tilo-schwarz.de> writes:
> while trying to create a query this afternoon dealing with intervals, I 
> noticed the following behaviour and I wondered, if it is intended.

> tschwarz=> select '-1'::interval;
>  interval
> ----------
>  -01:00        <---
> (1 row)

This appears to be intentional --- the comment in DecodeInterval says

                        /*
                         * Only a signed integer? Then must assume a
                         * timezone-like usage
                         */
                        type = DTK_HOUR;

whereas nearby code selects DTK_SECOND scaling for the cases of
fractional or unsigned numbers.  I'm not sure *why* it's intentional,
and am hesitant to change it without knowing what the rationale was.
There may be cases involving multiple fields that need the existing
behavior...

> tschwarz=> select '-0.1'::interval;
>   interval
> -------------
>  00:00:00.10   <---
> (1 row)

This is incontestably a bug.  Will fix for 7.4.1.

			regards, tom lane

Wed, 17 Dec 2003 16:31:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: time interval behaviour seems odd 
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tilo Schwarz <mail@tilo-schwarz.de> writes:
>> tschwarz=> select '-0.1'::interval;
>> interval
>> -------------
>> 00:00:00.10   <---
>> (1 row)

> This is incontestably a bug.  Will fix for 7.4.1.

I've applied the attached patch to 7.4 and HEAD.  It'd probably work in
7.3 as well, but I didn't look.

			regards, tom lane

*** src/backend/utils/adt/datetime.c.orig	Sat Nov 29 14:51:58 2003
--- src/backend/utils/adt/datetime.c	Wed Dec 17 16:33:25 2003
***************
*** 3005,3011 ****
  					if (*cp != '\0')
  						return DTERR_BAD_FORMAT;
  
! 					if (val < 0)
  						fval = -(fval);
  				}
  				else if (*cp == '\0')
--- 3005,3011 ----
  					if (*cp != '\0')
  						return DTERR_BAD_FORMAT;
  
! 					if (*field[i] == '-')
  						fval = -(fval);
  				}
  				else if (*cp == '\0')

Wed, 17 Dec 2003 16:47:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: time interval behaviour seems odd 