public inbox for [email protected]
help / color / mirror / Atom feedRe: [GENERAL] Converting seconds past midnight to a time
2+ messages / 1 participants
[nested] [flat]
* Re: [GENERAL] Converting seconds past midnight to a time
@ 2005-12-22 03:57 Bruce Momjian <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2005-12-22 03:57 UTC (permalink / raw)
To: Karl O. Pinc <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-docs
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 protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [GENERAL] Converting seconds past midnight to a time
@ 2005-12-22 21:49 Bruce Momjian <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2005-12-22 21:49 UTC (permalink / raw)
To: Karl O. Pinc <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-docs
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 <[email protected]>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (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>
Attachments:
[text/plain] /rtmp/diff (2.4K, 2-%2Frtmp%2Fdiff)
download | inline:
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>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-22 21:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-22 03:57 Re: [GENERAL] Converting seconds past midnight to a time Bruce Momjian <[email protected]>
2005-12-22 21:49 Re: [GENERAL] Converting seconds past midnight to a time Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox