Mysql DateTime组15分钟 [英] Mysql DateTime group by 15 mins
问题描述
我有一张这样的表格
CREATE TABLE`time_table`(
`id` INT 10)NOT NULL AUTO_INCREMENT,
`creationDate` DATETIME NOT NULL,
PRIMARY KEY(`id`)
)
我基本上存储表中某些记录的创建时间。我知道如果我想得到在15分钟间隔内创建的记录的数量,我将使用这样的东西
SELECT FLOOR(UNIX_TIMESTAMP(creationDate)/ 900)AS t,
COUNT(*)FROM time_table
GROUP BY t
给我这样的东西
t COUNT(*)
1434187 1
1434188 3
1434189 2
1434190 2
第一列的感觉?如果我想要给我看看像
t COUNT(*)
2:00 pm - 2:15 pm 1
2:15 pm - 2:30 pm 3
2:30 pm - 2:45 pm 2
2:45 pm - 3:00 pm 2
我明白,通过一些操纵,我可以在下午2点15分得到1434187。即使这可能是一个好的开始....然后有一些逻辑,我可以显示整个时期。谢谢!
一种方法是使用>
code> 为了获得一个范围内的所有内容,但您可能会发现这更简单:
SELECT ...
/ pre>
GROUP BY(4 * HOUR(thistime)+ FLOOR(MINUTE(thistime)/ 15))
从
http://forums.mysql.com/read.php?10,202789,202807I have a table that looks like this
CREATE TABLE `time_table` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `creationDate` DATETIME NOT NULL, PRIMARY KEY (`id`) )
I basically store the creation time of certain records in the table. I know if I want to get a count of the records that were created in 15 mins interval I will use something like this
SELECT FLOOR(UNIX_TIMESTAMP(creationDate)/900) AS t, COUNT(*) FROM time_table GROUP BY t
That gives me something like this
t COUNT(*) 1434187 1 1434188 3 1434189 2 1434190 2
How do I make sense of the first column? If I want it to show me something like
t COUNT(*) 2:00pm - 2:15pm 1 2:15pm - 2:30pm 3 2:30pm - 2:45pm 2 2:45pm - 3:00pm 2
I understand that with some manipulation I could get 1434187 to show up at 2:15pm. Even that might be a good start....then with some logic I could show the entire period. Thanks!
解决方案One way is to just use
>
and<
in order to get everything within a range, but you may find this to be simpler:SELECT ... GROUP BY ( 4 * HOUR( thistime ) + FLOOR( MINUTE( thistime ) / 15 ))
from http://forums.mysql.com/read.php?10,202789,202807
这篇关于Mysql DateTime组15分钟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!