Re: [GENERAL] Converting seconds past midnight to a time - Mailing list pgsql-patches

Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> 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>