Author: paul
Date: 2006-01-18 20:30:43 +0100 (Wed, 18 Jan 2006)
New Revision: 901
Log:
r2294@kite-hub: paul | 2006-01-18 11:14:29 -0600
Workaround for sum/avg with temporal values. (Bug#15595)
Modified:
trunk/
trunk/refman-4.1/data-types.xml
trunk/refman-4.1/functions.xml
trunk/refman-5.0/data-types.xml
trunk/refman-5.0/functions.xml
trunk/refman-5.1/data-types.xml
trunk/refman-5.1/functions.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6354
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2291
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6354
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2294
Modified: trunk/refman-4.1/data-types.xml
===================================================================
--- trunk/refman-4.1/data-types.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-4.1/data-types.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -879,6 +879,20 @@
<xref linkend="storage-requirements"/>.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
Modified: trunk/refman-4.1/functions.xml
===================================================================
--- trunk/refman-4.1/functions.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-4.1/functions.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -13745,6 +13745,20 @@
on all rows.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
Modified: trunk/refman-5.0/data-types.xml
===================================================================
--- trunk/refman-5.0/data-types.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-5.0/data-types.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -919,6 +919,20 @@
<xref linkend="storage-requirements"/>.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
@@ -4908,8 +4922,8 @@
<replaceable>L</replaceable> + 2 bytes, where
<literal><replaceable>L</replaceable> <=
<replaceable>M</replaceable></literal> and 256
- <literal><= <replaceable>M</replaceable> <=</literal>
- 65535 (see note below).</entry>
+ <literal><= <replaceable>M</replaceable>
+ <=</literal> 65535 (see note below).</entry>
</row>
<row>
<entry><literal>BINARY(<replaceable>M</replaceable>)</literal></entry>
Modified: trunk/refman-5.0/functions.xml
===================================================================
--- trunk/refman-5.0/functions.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-5.0/functions.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -13830,6 +13830,20 @@
on all rows.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
Modified: trunk/refman-5.1/data-types.xml
===================================================================
--- trunk/refman-5.1/data-types.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-5.1/data-types.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -875,6 +875,20 @@
<xref linkend="storage-requirements"/>.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
@@ -4732,8 +4746,8 @@
<emphasis>or</emphasis> <replaceable>L</replaceable> + 2
bytes, where <literal><replaceable>L</replaceable> <=
<replaceable>M</replaceable></literal> and 256
- <literal><= <replaceable>M</replaceable> <=</literal>
- 65535 (see note below).</entry>
+ <literal><= <replaceable>M</replaceable>
+ <=</literal> 65535 (see note below).</entry>
</row>
<row>
<entry><literal>BINARY(<replaceable>M</replaceable>)</literal></entry>
Modified: trunk/refman-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml 2006-01-18 19:21:44 UTC (rev 900)
+++ trunk/refman-5.1/functions.xml 2006-01-18 19:30:43 UTC (rev 901)
@@ -14205,6 +14205,20 @@
on all rows.
</para>
+ <para>
+ The <literal>SUM()</literal> and <literal>AVG()</literal>
+ aggregate functions do not work with temporal values. (They
+ convert the values to numbers, which loses the part after the
+ first non-numeric character.) To work around this, you can
+ convert to numeric units, perform the aggregate operation, and
+ convert back to a temporal value. Examples:
+ </para>
+
+<programlisting>
+SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<replaceable>time_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+SELECT FROM_DAYS(SUM(TO_DAYS(<replaceable>date_col</replaceable>))) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
<itemizedlist>
<listitem>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r901 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 18 Jan |
