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

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:
>
> > > > 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 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.
>
> 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 <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
>

--
  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
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>