time interval behaviour seems odd
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)
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
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')