如何在数据资源管理器中仅按日期分组

社区 数据与报告
1

我正在尝试获取本月按天统计的用户注册数量，但在按 created_at 字段分组时遇到了问题，因为该字段包含时间戳。有人知道如何仅按日期进行分组吗？

select count(*), created_at
from users
where created_at > '2018-07-01'
and admin = false
group by created_at
order by created_at desc
3

You want to round the datetime to the day. See
https://www.postgresql.org/docs/8.4/static/functions-datetime.html

I am on my phone, so can’t (or won’t) do a full solution, but this should get you close

SELECT EXTRACT(DOY FROM created_at) as day
1 个赞
4

Thanks Jay, I’m still running into the same issue:

query
query.png756×1080 86.9 KB

5

I think date_trunc could work

examples (note the right padded zeroes and oh-ones)

Examples:

SELECT date_trunc(‘hour’, TIMESTAMP ‘2001-02-16 20:38:40’); Result: 2001-02-16 20:00:00
SELECT date_trunc(‘year’, TIMESTAMP ‘2001-02-16 20:38:40’); Result: 2001-01-01 00:00:00

3 个赞
6

@John_Waltrip1, here’s the query I think you’re looking for. I switched to DAY, which is the day of the month, which seems easier for most humans that I know to understand than the day of the year (DOY).

select count(*), 
      EXTRACT(DAY FROM created_at) as day
from users
where created_at > LOCALTIMESTAMP - INTERVAL '14 days'
and admin = false
group by day
order by day desc
5 个赞
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.