按日期范围内的间隔MySQL组 [英] MySQL group by intervals in a date range
问题描述
从表中选择SUM(字节)where stime> x和stime< Y
group by(10秒间隔)
有没有办法做到这一点?或者,在Python中本地处理它会更快吗?甚至对于一个500K的行表?
编辑
我的错误是,时间存储为无符号双精度值而不是INT。
我正在使用 GROUP BY(FLOOR(stime / I))
,其中I是所需的时间间隔。
我使用了来自答案和同事的建议。最终结果如下:
从argusTable_2009_10_22
中选择FROM_UNIXTIME(stime),bytes
其中stime> ; (UNIX_TIMESTAMP() - 600)
分层(stime / 10)
舍入解决方案,但结果不一致。
机会
I am going to be graphing netflow data stored in a MySQL database, and I need an efficient way to get the relevant data points. They records are stored with the date as an int for seconds since epoch. I Would like to be able to something like:
Select SUM(bytes) from table where stime > x and stime < Y
group by (10 second intervals)
Is there anyway to do this? or, would it be faster to handle it locally in python? even for a 500K row table?
EDIT
My Mistake, the time is stored as an unsigned double instead of an INT.
I'm currently using GROUP BY (FLOOR(stime / I))
where I is the desired interval.
I used suggestions from both answers and a coworker. End result is as follows:
Select FROM_UNIXTIME(stime), bytes
from argusTable_2009_10_22
where stime > (UNIX_TIMESTAMP()-600)
group by floor(stime /10)
I tried the rounding solution as well, but the results were inconsistent.
Chance
这篇关于按日期范围内的间隔MySQL组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!