Subject: Re: [GENERAL] Converting seconds past midnight to a time

From: Bruce Momjian <[email-masked]>
Date: Wed, 21 Dec 2005 22:57:47 -0500 (EST)

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
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 22 Dec 2005 16:49:53 -0500 (EST)

OK, I have applied the attached patch to HEAD and 8.1.X to mention the
storage system used by intervals.  We don't deal with leap seconds.  We
have no way to determine them in a portable way.

---------------------------------------------------------------------------

Karl O. Pinc wrote:
> 
> On 12/21/2005 09:57:47 PM, Bruce Momjian wrote:
> > Karl O. Pinc wrote:
> 
> That makes sense but could use some explaination somewhere,
> all units >= day will not "simplify".  I guess it's obvious
> for months, and even years, but I aways forget about leap
> seconds.  Remembering that there's a distinction between
> 1 day and 24 hours, does not come naturally.
> 
> This definately could use explaination.  There are certain
> "boundries" that interval operations do not "reduce"
> across.
> 24 hours != 1 day
> 365 days != 1 year
> 400 years != 4 centuries
> 
> Oh boy, now I've confused myself.  Is there any pattern
> to leap seconds comperable to the 400 year leap year?
> If there is that throws other "boundry"s into the mix,
> but if not then that impilies that any unit bigger
> than a day cannot be "combined" and "simplified".
> I.e. 7 days != 1 week because the leap second can
> come at the end of the week.   Help, my brain is full.
> (And what about months?  Is  "interval '1 month'"
> even equal to "interval '1 month'" or is this like
> NULL where the test is always false?  I presume not
> but clearly the month is another one of those units
> that won't "combine" with any other unit.)
> 
> Thing is, I need to know what the interval I write
> really means.
> 
> Karl <[email-masked]>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v
retrieving revision 1.163
diff -c -c -r1.163 datatype.sgml
*** doc/src/sgml/datatype.sgml	22 Oct 2005 19:33:57 -0000	1.163
--- doc/src/sgml/datatype.sgml	22 Dec 2005 21:44:08 -0000
***************
*** 1841,1849 ****
       </para>
  
       <para>
!       The optional precision
!       <replaceable>p</replaceable> should be between 0 and 6, and
!       defaults to the precision of the input literal.
       </para>
      </sect3>
  
--- 1841,1860 ----
       </para>
  
       <para>
!       The optional subsecond precision <replaceable>p</replaceable> should 
!       be between 0 and 6, and defaults to the precision of the input literal.
!      </para>
! 
!      <para>
!       Internally <type>interval</> values are stored as months, days,
!       and seconds. This is done because the number of days in a month
!       varies, and a day can have 23 or 25 hours if a daylight savings
!       time adjustment is involved. Because intervals are usually created
!       from constant strings or <type>timestamp</> subtraction, this
!       storage method works well in most cases. Functions
!       <function>justify_days</> and <function>justify_hours</> are
!       available for adjusting days and hours that overflow their normal
!       periods.
       </para>
      </sect3>
  
***************
*** 1936,1942 ****
        <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>, 
        <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>, 
        <literal>LOCALTIMESTAMP</literal>.  The latter four accept an 
!       optional precision specification.  (See <xref 
        linkend="functions-datetime-current">.)  Note however that these are
        SQL functions and are <emphasis>not</> recognized as data input strings.
       </para>
--- 1947,1953 ----
        <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>, 
        <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>, 
        <literal>LOCALTIMESTAMP</literal>.  The latter four accept an 
!       optional subsecond precision specification.  (See <xref 
        linkend="functions-datetime-current">.)  Note however that these are
        SQL functions and are <emphasis>not</> recognized as data input strings.
       </para>