X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 689149DC9F0
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Dec 2005 23:57:45 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 95792-04
	for <pgsql-docs-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Dec 2005 23:57:49 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from candle.pha.pa.us (candle.pha.pa.us [70.90.9.53])
	by postgresql.org (Postfix) with ESMTP id D9A5A9DCBE3
	for <pgsql-docs@postgresql.org>; Wed, 21 Dec 2005 23:57:41 -0400 (AST)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id jBM3vl418749;
	Wed, 21 Dec 2005 22:57:47 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200512220357.jBM3vl418749@candle.pha.pa.us>
Subject: Re: [GENERAL] Converting seconds past midnight to a time
In-Reply-To: <1135186954l.17852l.3l@mofo>
To: "Karl O. Pinc" <kop@meme.com>
Date: Wed, 21 Dec 2005 22:57:47 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL-documentation <pgsql-docs@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL121 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.133 required=5 tests=[AWL=0.133]
X-Spam-Score: 0.133
X-Spam-Level: 
X-Archive-Number: 200512/26
X-Sequence-Number: 3374

Karl O. Pinc wrote:
> 
> On 12/21/2005 10:04:34 AM, Bruce Momjian wrote:
> 
> > I have applied the following documentation patch to HEAD and 8.1.X.
> 
> Thanks.  If only I pay attention when reading the documentation
> that will help tremendously. :-)

The previous example was just 3.5 * '1 hour'.  I think we need to add
some simpler ones that used other units, so that is done.

> > I also fixed an example '1 day' - '1 hour' which now computes
> > differently
> 
> >  <row>
> >   <entry> <literal>-</literal> </entry>
> >   <entry><literal>interval '1 day' - interval '1 hour'</literal></  
> > entry>
> > -  <entry><literal>interval '23:00'</literal></entry>
> > +  <entry><literal>interval '1 day -01:00:00'</literal></entry>
> > </row>
> 
> I'm sure you've thought of this so if all is well
> in Postgres land please don't bother to write
> but I figured I'd write anyway.
> 
> I find this a little spooky.  Is
>   interval '1 day' - interval '1 hour'
>    = double precision 23 * interval '1 hour'
> ?

Right, that was wrong.  The new documentation is correct:

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

The issue is that we don't know if that day was 24 hours or 24 +/- 1
hour.

> If so, do they have the same external (string) representation?
> It'd be wierd to have one value with two external
> representations.  It'd also be wierd the other way,
> to have two different internal values that for all
> purposes other than equality are the same.

We have buckets for seconds, days, and months, and justification
functions to convert up.

> Anyhow, I just took a little time looking at the
> on-line docs for 8.1 and it does not seem to have a
> lot to say about the canonical external interval
> representation other than:
> 
>   In ISO mode the output looks like
> [ quantity unit [ ... ] ] [ days ] [ hours:minutes:seconds ]
> 
> This does not seem to be the interval representation
> appearing in the example in the patch.

Uh, units could be 'days', etc.

> 
> (psql-patches not cc-ed.  OT?)

Yep, docs list added.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073