用PHP在MySQL中分组时间戳 [英] Grouping timestamps in MySQL with PHP
问题描述
我想使用 time（）以时间码记录MySql中的某些活动。现在，我正在积累数千条记录，我希望以小时/天/月等组的形式输出数据。
分组时间码的建议方法是什么在MySQL中？
示例数据：
- 1248651289
- 1248651299
- 1248651386
- 1248651588
- 1248651700
- 1248651707
- 1248651737
- 1248651808 >
- 1248652269
示例代码：
$ sql =SELECT COUNT（timecode）FROM timecodeTable;
// GROUP BY round（timecode / 3600，1）// group by hour ??
编辑：
可以编辑两个分组我应该更清楚一点：可以对一天中的24小时进行分组，但我更感兴趣的是随着时间的推移进行分组，因此每年都会返回365个结果，以便跟踪到位，因此总计每天都会过去，然后才能够选择一个日期范围，并查看更多的细节，在选定的时间访问小时/分钟。
这就是为什么我将它命名为使用PHP，正如我所期望的这可能会更容易与一个PHP循环产生的小时/天等？
彼得
SELECT COUNT（*），HOUR（timecode）
FROM timecodeTable
GROUP BY HOUR（timecode）;
根据上述数据，您的结果集看起来如此：
+ ---------- + ---------------- +
| COUNT（*）| HOUR（时间码）|
+ ---------- + ---------------- +
| 10 | 18 |
+ ---------- + ---------------- +
还可以找到更多相关函数 here 。
编辑
根据您的评论输出结果进行自己的测试后，我确定您的数据库处于史诗级失败状态。 :)你正在使用INT作为TIMESTAMPs。这绝不是一个好主意。没有正当理由使用INT代替TIMESTAMP / DATETIME。
也就是说，您必须修改我上面的示例，如下所示：
SELECT COUNT（*），HOUR（FROM_UNIXTIME（timecode））
FROM timecodeTable
GROUP BY HOUR（FROM_UNIXTIME（timecode） ）;
编辑2
SELECT
COUNT（*），
YEAR（timecode），
DAYOFYEAR（时间码），
HOUR（时间码）
FROM timecodeTable
GROUP BY YEAR（时间码），DAYOFYEAR（时间码），HOUR（时间码） ;
请注意，为简洁起见，我省略了FROM_UNIXTIME（）。
I want to log certain activities in MySql with a timecode using time(). Now I'm accumulating thousands of records, I want to output the data by sets of hours/days/months etc.
What would be the suggested method for grouping time codes in MySQL?
Example data:
- 1248651289
- 1248651299
- 1248651386
- 1248651588
- 1248651647
- 1248651700
- 1248651707
- 1248651737
- 1248651808
- 1248652269
Example code:
$sql = "SELECT COUNT(timecode) FROM timecodeTable";
//GROUP BY round(timecode/3600, 1) //group by hour??
Edit: There's two groupings that can be made so I should make that clearer: The 24 hours in the day can be grouped but I'm more interested in grouping over time so returning 365 results for each year the tracking is in place, so total's for each day passed, then being able to select a range of dates and see more details on hours/minutes accessed over those times selected.
This is why I've titled it as using PHP, as I'd expect this might be easier with a PHP loop to generate the hours/days etc?
Peter
SELECT COUNT(*), HOUR(timecode)
FROM timecodeTable
GROUP BY HOUR(timecode);
Your result set, given the above data, would look as such:
+----------+----------------+
| COUNT(*) | HOUR(timecode) |
+----------+----------------+
| 10 | 18 |
+----------+----------------+
Many more related functions can be found here.
Edit
After doing some tests of my own based on the output of your comment I determined that your database is in a state of epic fail. :) You're using INT's as TIMESTAMPs. This is never a good idea. There's no justifiable reason to use an INT in place of TIMESTAMP/DATETIME.
That said, you'd have to modify my above example as follows:
SELECT COUNT(*), HOUR(FROM_UNIXTIME(timecode))
FROM timecodeTable
GROUP BY HOUR(FROM_UNIXTIME(timecode));
Edit 2
You can use additional GROUP BY clauses to achieve this:
SELECT
COUNT(*),
YEAR(timecode),
DAYOFYEAR(timecode),
HOUR(timecode)
FROM timecodeTable
GROUP BY YEAR(timecode), DAYOFYEAR(timecode), HOUR(timecode);
Note, I omitted the FROM_UNIXTIME() for brevity.
这篇关于用PHP在MySQL中分组时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
