Message-ID: <4A127C2E.1020701@archonet.com>
Date: Tue, 19 May 2009 10:30:22 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090320)
MIME-Version: 1.0
To: Sebastien FLAESCH <sf@4js.com>
CC: pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
References: <4A127038.3010103@4js.com> <4A12737F.1020207@archonet.com>
	<4A127919.7080105@4js.com>
In-Reply-To: <4A127919.7080105@4js.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sebastien FLAESCH wrote:
> I think it should be clarified in the documentation...

Please don't top-quote. And yes, I think you're right.

Hmm a quick google for: [sql "interval second"] suggests that it's not 
the right thing. I see some mention of 2 digit precision for a leading 
field, but no "clipping".

Looking at the manuals and indeed a quick \dT I don't see "interval 
second" listed as a separate type though. A bit of exploring in 
pg_attribute with a test table suggests it's just using "interval" with 
a type modifier. Which you seem to confirm from the docs:

 > The PostgreSQL documentation says:
 >
 > The interval type has an additional option, which is to restrict the set
 > of stored
 > fields by writing one of these phrases:
 >
 >     YEAR
 >     MONTH
...
 > Does that mean that the [field] option of the INTERVAL type is just
 > there to save
 > storage space?

My trusty copy of the 8.3 source suggests that AdjustIntervalForTypmod() 
is the function we're interested in and it lives in 
backend/utils/adt/timestamp.c - it looks like it just zeroes out the 
fields you aren't interested in. No space saving.

So - not a bug, but perhaps not the behaviour you would expect.

> Actually I would like to use this new INTERVAL type to store 
> IBM/Informix INTERVALs,
> which can actually be used like this with DATETIME types:
> 
>  > create table t1 (
>  >     k int,
>  >     dt1 datetime hour to minute,
>  >     dt2 datetime hour to minute,
>  >     i interval hour(5) to minute );
> Table created.
> 
>  > insert into t1 values ( 1, '14:45', '05:10', '-145:10' );
> 1 row(s) inserted.
> 
>  > select dt1 - dt2 from t1;
> (expression)
>   9:35                        <- INTERVAL expression

  SELECT ('14:45'::time - '05:10'::time);
  ?column?
----------
  09:35:00
(1 row)


>  > select 15 * ( dt1 - dt2 ) from t1;
> (expression)
>        143:45                        <- INTERVAL expressio

=> SELECT 15 * ('14:45'::time - '05:10'::time);
  ?column?
-----------
  143:45:00
(1 row)

If you can live with the zero seconds appearing, it should all just 
work*. Other than formatting as text, I don't know of a way to suppress 
them though.

* Depending on whether you need to round up if you ever get odd seconds etc.

-- 
   Richard Huxton
   Archonet Ltd