Tom Lane wrote:
> "Karl O. Pinc" <[EMAIL PROTECTED]> writes:
> > What is the best way to convert an integer number of
> > seconds past midnight into a time?
>
> Intermediate 'interval' value seems to work:
>
> regression=# select (99.44 * '1 second'::interval)::time;
> time
> -------------
> 00:01:39.44
> (1 row)
>
> > On a related note is there some reason why
> > interval + int
> > does not result in the interval plus int number
> > of seconds?
>
> Again, multiplying the number by '1 sec'::interval would get you there.
I have applied the following documentation patch to HEAD and 8.1.X.
It is unified diff because those are easier with single-line changes
like this, where the surrounding text isn't as significant.
I also fixed an example '1 day' - '1 hour' which now computes
differently, and added trailing zero seconds, which is our display
default.
--
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: func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.298
diff -c -u -r1.298 func.sgml
--- func.sgml 11 Dec 2005 10:54:27 -0000 1.298
+++ func.sgml 21 Dec 2005 15:59:27 -0000
@@ -5120,37 +5120,37 @@
<row>
<entry> <literal>+</literal> </entry>
<entry><literal>date '2001-09-28' + interval '1 hour'</literal></entry>
- <entry><literal>timestamp '2001-09-28 01:00'</literal></entry>
+ <entry><literal>timestamp '2001-09-28 01:00:00'</literal></entry>
</row>
<row>
<entry> <literal>+</literal> </entry>
<entry><literal>date '2001-09-28' + time '03:00'</literal></entry>
- <entry><literal>timestamp '2001-09-28 03:00'</literal></entry>
+ <entry><literal>timestamp '2001-09-28 03:00:00'</literal></entry>
</row>
<row>
<entry> <literal>+</literal> </entry>
<entry><literal>interval '1 day' + interval '1 hour'</literal></entry>
- <entry><literal>interval '1 day 01:00'</literal></entry>
+ <entry><literal>interval '1 day 01:00:00'</literal></entry>
</row>
<row>
<entry> <literal>+</literal> </entry>
<entry><literal>timestamp '2001-09-28 01:00' + interval '23
hours'</literal></entry>
- <entry><literal>timestamp '2001-09-29 00:00'</literal></entry>
+ <entry><literal>timestamp '2001-09-29 00:00:00'</literal></entry>
</row>
<row>
<entry> <literal>+</literal> </entry>
<entry><literal>time '01:00' + interval '3 hours'</literal></entry>
- <entry><literal>time '04:00'</literal></entry>
+ <entry><literal>time '04:00:00'</literal></entry>
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>- interval '23 hours'</literal></entry>
- <entry><literal>interval '-23:00'</literal></entry>
+ <entry><literal>interval '-23:00:00'</literal></entry>
</row>
<row>
@@ -5168,49 +5168,55 @@
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>date '2001-09-28' - interval '1 hour'</literal></entry>
- <entry><literal>timestamp '2001-09-27 23:00'</literal></entry>
+ <entry><literal>timestamp '2001-09-27 23:00:00'</literal></entry>
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>time '05:00' - time '03:00'</literal></entry>
- <entry><literal>interval '02:00'</literal></entry>
+ <entry><literal>interval '02:00:00'</literal></entry>
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>time '05:00' - interval '2 hours'</literal></entry>
- <entry><literal>time '03:00'</literal></entry>
+ <entry><literal>time '03:00:00'</literal></entry>
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>timestamp '2001-09-28 23:00' - interval '23
hours'</literal></entry>
- <entry><literal>timestamp '2001-09-28 00:00'</literal></entry>
+ <entry><literal>timestamp '2001-09-28 00:00:00'</literal></entry>
</row>
<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>
<row>
<entry> <literal>-</literal> </entry>
<entry><literal>timestamp '2001-09-29 03:00' - timestamp '2001-09-27
12:00'</literal></entry>
- <entry><literal>interval '1 day 15:00'</literal></entry>
+ <entry><literal>interval '1 day 15:00:00'</literal></entry>
</row>
<row>
<entry> <literal>*</literal> </entry>
- <entry><literal>interval '1 hour' * double precision
'3.5'</literal></entry>
- <entry><literal>interval '03:30'</literal></entry>
+ <entry><literal>900 * interval '1 second'</literal></entry>
+ <entry><literal>interval '00:15:00'</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>*</literal> </entry>
+ <entry><literal>double precision '3.5' * interval '1
hour'</literal></entry>
+ <entry><literal>interval '03:30:00'</literal></entry>
</row>
<row>
<entry> <literal>/</literal> </entry>
<entry><literal>interval '1 hour' / double precision
'1.5'</literal></entry>
- <entry><literal>interval '00:40'</literal></entry>
+ <entry><literal>interval '00:40:00'</literal></entry>
</row>
</tbody>
</tgroup>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
