To group by date only in the Data Explorer, John_Waltrip1 was having trouble as the created_at field includes timestamps. pfaffman suggested rounding the datetime to the day using the EXTRACT function. However, this led to the same issue. Mittineague proposed using the date_trunc function, which can truncate datetime to a specific date part, such as the day. pfaffman provided a revised query using EXTRACT(DAY FROM created_at) to group by the day of the month. An alternative solution using date_trunc('day', created_at) was not explicitly provided but can be inferred from Mittineague’s suggestion.
I’m trying to get the number of user signups by day for this month and run into trouble when trying to group on the created_at field because it includes the timestamp. Anyone know how to just group on the date?
select count(*), created_at
from users
where created_at > '2018-07-01'
and admin = false
group by created_at
order by created_at desc
You want to round the datetime to the day. See
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
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
@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