SQL 按天分组时间戳，而不是按时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14770829/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grouping timestamps by day, not by time
提问by Teifion
I've got a table storing user access/view logs for the webservice I run. It tracks the time as a timestamp though I'm finding when I do aggregate reporting I only care about the day, not the time.
我有一个表，用于存储我运行的 Web 服务的用户访问/查看日志。它将时间作为时间戳进行跟踪，尽管我发现当我进行汇总报告时，我只关心当天，而不是时间。
I'm currently running the following query:
我目前正在运行以下查询：
SELECT
user_logs.timestamp
FROM
user_logs
WHERE
user_logs.timestamp >= %(timestamp_1)s
AND user_logs.timestamp <= %(timestamp_2)s
ORDER BY
user_logs.timestamp
There are often other where conditions but they shouldn't matter to the question. I'm using Postgres but I'd assume whatever feature is used will work in other languages.
通常还有其他 where 条件，但它们与问题无关。我正在使用 Postgres，但我认为使用的任何功能都可以在其他语言中使用。
I pull the results into a Python script which counts the number of views per date but it'd make much more sense to me if the database could group and count for me.
我将结果提取到一个 Python 脚本中，该脚本计算每个日期的查看次数，但如果数据库可以对我进行分组和计数，对我来说会更有意义。
How do I strip it down so it'll group by the day and ignore the time?
我如何剥离它以便它按天分组并忽略时间？
回答by Clodoaldo Neto
SELECT date_trunc('day', user_logs.timestamp) "day", count(*) views
FROM user_logs
WHERE user_logs.timestamp >= %(timestamp_1)s
AND user_logs.timestamp <= %(timestamp_2)s
group by 1
ORDER BY 1
