Re: [GENERAL] Converting seconds past midnight to a time
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 19073Import Notes
Reply to msg id not found: 1135186954l.17852l.3l@mofo
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 centuriesOh 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 19073Attachments:
/rtmp/difftext/plainDownload+19-19
Import Notes
Reply to msg id not found: 1135270179l.6578l.0l@mofo | Resolved by subject fallback