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.
Source (Stackoverflow)