I Putu Susila I Putu Susila - 2 months ago 12
MySQL Question

MariaDB/MySQL TO_SECONDS and AGGREGATE functions

I want to use TO_SECONDS with aggregate functions (AVG, COUNT) to summarize my table. However, the result was not what I expected. Here is an example table:

MariaDB [test]> select * from mytable;
+----+---------------------+------+
| id | ts | val |
+----+---------------------+------+
| 1 | 2016-01-01 01:02:03 | 1 |
| 2 | 2016-01-01 01:02:04 | 2 |
| 3 | 2016-01-01 01:02:04 | 3 |
| 4 | 2016-01-01 01:02:05 | 4 |
| 5 | 2016-01-01 01:02:05 | 5 |
+----+---------------------+------+


Query #1 (OK):

MariaDB [test]> select to_seconds(ts) as tsec from mytable;
+-------------+
| tsec |
+-------------+
| 63618829323 |
| 63618829324 |
| 63618829324 |
| 63618829325 |
| 63618829325 |
+-------------+


Query #2 (?):

MariaDB [test]> select to_seconds(ts) as tsec, avg(val) mval from mytable group by tsec;
+------------+------+
| tsec | mval |
+------------+------+
| 2147483647 | 3 |
+------------+------+


Expected result:

+-------------+------+
| tsec | mval |
+-------------+------+
| 63618829323 | 1 |
| 63618829324 | 2.5 |
| 63618829325 | 4.5 |
+-------------+------+


SQL Fiddle: http://sqlfiddle.com/#!9/17616a/6

MariaDB version> mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1

Of course I can use other DATE/TIME functions (UNIX_TIMESTAMP, etc) to perform the task. However, I want to know why the result is different.

What am I missing? Do I misunderstood the usage of TO_SECONDS?

Answer

This is a strange data type problem. The following does work:

select cast(to_seconds(ts) as decimal(20, 0)) as tsec, avg(val)
from mytable
group by tsec;

I don't know why the return value of to_seconds() would be large enough to store the value when you select it, but then gets converted to an integer when you use group by.

Comments