SQLite分组/按小时，天，周，年 [英] SQLite group by/count hours, days, weeks, year

问题描述

假设我们有一个只有一个按钮的简单Android应用程序.
单击该按钮记录单击的日期和时间(以sqlite表示).
我不知道该记录保留该日期时间的较长或字符串的最佳格式是什么.
目的是使报告显示以下内容:
1.在所选日期进行的点击总和，按小时分组.
2.在所选星期中按天分组的点击总和.
3.所选月份的点击总次数，按周分组.
4.按月份分组的选定年份的点击总和.

Assume that we have a simple Android application that has only one button.
Click on that button record the date and time of that click (in sqlite).
I wondering what is the best format for that records keep a long or a string of that date-time.
The purpose is to make reports that show:
1. sum of clicks that made for chosen day grouped by hours.
2. sum of clicks that made for chosen week grouped by days.
3. sum of clicks that made for chosen month grouped by weeks.
4. sum of clicks that made for chosen year grouped by month.

如何创建此类数据库以及如何进行此类查询?

How to create such database and how do I do such queries?

推荐答案

我赞成使用Unix时间戳(自"epoch"以来的秒数)，因为它们便于范围计算并且最可靠日期时间库.

I'd vote for using Unix timestamps (number of seconds since the "epoch"), given that they're convenient for range calculation and understood by most robust date-time libraries.

SQLite提供了一些帮助程序功能，用于处理Unix时间戳.这里最有用的是strftime.

SQLite provides a few helper functions for working with Unix timestamps. The most useful one here is going to be strftime.

您可以使用INSERT中的strftime('%s', 'now')插入当前的Unix时间戳.

You can insert the current Unix timestamp by using strftime('%s', 'now') in your INSERT.

稍后，如果您知道自己感兴趣的特定时间范围，则可以计算该范围的最小和最大时间戳，并在它们之间选择行:

Later, if you know a particular time range you're interested in, you can calculate the minimum and maximum timestamps for that range and select rows between them:

SELECT * FROM data 
WHERE timestamp >= strftime('%s', '2012-12-25 00:00:00') 
AND timestamp < strftime('%s', '2012-12-25 01:00:00');

或者，假设您想按月份计算一年的请求量:

Or, supposing you want to count a year's requests by month:

SELECT strftime('%m', timestamp), count(*) FROM Data
WHERE timestamp >= strftime('%s', '2012-01-01 00:00:00') 
AND timestamp < strftime('%s', '2013-01-01 00:00:00') 
GROUP BY strftime('%m', timestamp);

通过巧妙地使用strftime提供的格式选项，您可以很快地解决大多数查询.

Through clever use of the format options strftime provides, you can probably work out most of those queries pretty quickly.

这篇关于SQLite分组/按小时，天，周，年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！