From: Richard Huxton <dev@archonet.com>
To: Sebastien FLAESCH <sf@4js.com>
Cc: pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
Date: Tue, 19 May 2009 10:30:22 +0100
Message-ID: <4A127C2E.1020701@archonet.com> (raw)
In-Reply-To: <4A127919.7080105@4js.com>
References: <4A127038.3010103@4js.com>
<4A12737F.1020207@archonet.com>
<4A127919.7080105@4js.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: dev@archonet.com, sf@4js.com, pgsql-general@postgresql.org
Subject: Re: INTERVAL SECOND limited to 59 seconds?
In-Reply-To: <4A127C2E.1020701@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox