MySQL count elements and group by date, ignore time

Question

I have a table storing payment transactions. They are stored with datetime in this way:

day-month-year hour:min:sec (Example: 18-11-2019 11:54:41)

I want to count how many transactions per day.

If I use this query:

SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY paid

it returns me every single row, but I want to group by date, ignoring time.

@CherryDT nothing works, maybe because my date format is european? — Giovanni Palerma, Mar 09 '20 at 00:58
You said it is "stored with datetime" - I assumed you mean it is a `DATETIME` column, then you'd just use `GROUP BY DATE(paid)` — CherryDT, Mar 09 '20 at 01:00
Does this answer your question? [MySQL/SQL: Group by date only on a Datetime column](https://stackoverflow.com/questions/366603/mysql-sql-group-by-date-only-on-a-datetime-column) — Don't Panic, Mar 09 '20 at 02:04

score 4 · Accepted Answer · answered Mar 09 '20 at 01:04

4

If your column in an actual DATETIME, the DATE function should do:

SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY DATE(paid)

If it is a VARCHAR or TEXT with a custom format, like the example you specified (18-11-2019 11:54:41), you'd need to parse it first using STR_TO_DATE:

SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY DATE(STR_TO_DATE(paid, '%d-%m-%Y %H:%i:%s'))

answered Mar 09 '20 at 01:04

CherryDT

25,571
5
49
74

Plainly, it's not a datetime column – Strawberry Mar 09 '20 at 07:11

Nick · Answer 2 · 2020-03-09T01:26:49.940

You should always use the DATETIME column format to store dates, not VARCHAR. You can always deal with presentation issues (e.g. converting to European format) in your application layer. If that was the case you could simply GROUP BY DATE(paid) i.e.

SELECT COUNT(id), paid 
FROM `payments` 
WHERE paid IS NOT NULL 
GROUP BY DATE(paid)

If your column is VARCHAR or TEXT you need to convert it to a value you can group by. If the format is DD-MM-YYYY, you can simply use LEFT(paid, 10) to extract the date portion and group by that i.e.

SELECT COUNT(id), paid 
FROM `payments` 
WHERE paid IS NOT NULL 
GROUP BY LEFT(paid, 10)

If you're dates are stored as D-M-YYYY you will need to convert them using STR_TO_DATE e.g.

SELECT COUNT(id), paid 
FROM `payments` 
WHERE paid IS NOT NULL 
GROUP BY STR_TO_DATE(paid, '%e-%c-%Y')

Note you can ignore the time portion of the value when converting and so avoid the need for the DATE function.

MySQL count elements and group by date, ignore time

2 Answers2