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 |
+----+---------------------+------+
MariaDB [test]> select to_seconds(ts) as tsec from mytable;
+-------------+
| tsec |
+-------------+
| 63618829323 |
| 63618829324 |
| 63618829324 |
| 63618829325 |
| 63618829325 |
+-------------+
MariaDB [test]> select to_seconds(ts) as tsec, avg(val) mval from mytable group by tsec;
+------------+------+
| tsec | mval |
+------------+------+
| 2147483647 | 3 |
+------------+------+
+-------------+------+
| tsec | mval |
+-------------+------+
| 63618829323 | 1 |
| 63618829324 | 2.5 |
| 63618829325 | 4.5 |
+-------------+------+
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
.