CODEWITHSUNDEEP
mysqlsqldateselect
Tiago
Tiago

Reputation: 653

Mysql GROUP BY DATE from text column

I have a DB with a text column that will store the date and time as DD/MM/YYYY HH:MM:SS (example: 27-11-2015 17:01:53).

Now I would like to make some stats with PHP, but I just can't solve the problem with dates.

I would like to count all results that have the same day (DD/MM/YYYY) despite the hour created.

I've already tryed many solutions but none works.

By best attempt was this one, but gives me NULL as the date:

SELECT STR_TO_DATE(DATECOLUMN, '%d/%m/%Y'), COUNT(*) 
FROM TABLE 
GROUP BY STR_TO_DATE(DATECOLUMN, '%d/%m/%Y')

I would like to make the search in interval based too and I would like some help in searchings for a date interval this way as the "normal" way doesn't work. Using the below code will not work to make intervals.

WHERE DATE(DATECOLUM) >= DATE(NOW()) - INTERVAL 7 DAY

Could someone help me trying to solve this? Thanks!

Upvotes: 1

Views: 628

Answers (2)

Tiago
Tiago

Reputation: 653

Well,

I think I found the solution and it's working pretty good. For those what would need it in future:

SELECT DATE_FORMAT(STR_TO_DATE(DATEC_OLUMN , "%d-%m-%Y %H:%i:%S"), "%d/%m/%y") AS date, 
       COUNT(*) 
FROM TABLE_NAME 
GROUP BY DATE_FORMAT(STR_TO_DATE(DATE_COLUMN, "%d-%m-%Y %H:%i:%S"), "%d/%m/%y")

The code above will transform my text column string in DD/MM/YYYY HH:MM:SS to DD/MM/YYYY and will group it and count it by date

Upvotes: 0

genespos
genespos

Reputation: 3311

You can try:

SELECT date_format(str_to_date(DATECOLUMN, '%d/%m/%Y'), '%d/%m/%Y') AS MyDate, COUNT(*)
FROM TABLE 
GROUP BY MyDate

For interval and group you can try this:

SELECT COUNT(*), MyDate
FROM TABLE, (
    SELECT date_format(str_to_date(DATECOLUMN, '%d/%m/%Y'), '%d/%m/%Y') AS MyDate
    FROM TABLE) Tmp 
WHERE date_format(str_to_date(MyDate, '%d/%m/%Y'), '%Y-%m-%d') >= NOW() - INTERVAL 7 DAY
GROUP BY MyDate

Upvotes: 1

Related Questions