# How to group by date only in data explorer
**URL:** https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [7월 22, 2018, 4:45오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868 "2018-07-22T04:45:17Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@John\_Waltrip1](https://meta.discourse.org/u/John_Waltrip1)
#### Post date: [7월 22, 2018, 4:45오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/1 "2018-07-22T04:45:17Z")
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
```
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [7월 22, 2018, 5:19오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/3 "2018-07-22T05:19:50Z")
You want to round the datetime to the day. See
> **[9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/8.4/functions-datetime.html)**
>
> 9.9. Date/Time Functions and Operators # 9.9.1. EXTRACT, date\_part 9.9.2. date\_trunc 9.9.3. date\_bin 9.9.4. AT TIME ZONE and AT LOCAL 9.9.5. …
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
```
---
### Author:  [@John\_Waltrip1](https://meta.discourse.org/u/John_Waltrip1)
#### Post date: [7월 22, 2018, 5:38오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/4 "2018-07-22T05:38:33Z")
Thanks Jay, I’m still running into the same issue:
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [7월 22, 2018, 5:41오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/5 "2018-07-22T05:41:29Z")
I think `date_trunc` could work
> **[9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/9.1/functions-datetime.html)**
>
> 9.9. Date/Time Functions and Operators # 9.9.1. EXTRACT, date\_part 9.9.2. date\_trunc 9.9.3. date\_bin 9.9.4. AT TIME ZONE and AT LOCAL 9.9.5. …
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
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [7월 22, 2018, 8:23오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/6 "2018-07-22T08:23:49Z")
@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).
```sql
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
```
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [8월 21, 2018, 8:24오전 UTC](https://meta.discourse.org/t/how-to-group-by-date-only-in-data-explorer/92868/7 "2018-08-21T08:24:08Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.