CODEWITHSUNDEEP
mysqlsql
Dean Whitehouse
Dean Whitehouse

Reputation: 894

MySQL TIMESTAMP Column - Group by Day

I'm struggling with grouping a MySQL TIMESTAMP column by day without any specific where clause.

The goal is to output a list of individual days within the database, currently I get an error regarding non-aggregated columns.

SELECT list is not in GROUP BY clause and contains nonaggregated column created_at which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

My query is:

SELECT created_at FROM audits GROUP BY DATE('created_at');

Upvotes: 1

Views: 606

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use date() in both the select and group by. Or just use select distinct:

SELECT DISTINCT DATE(created_at)
FROM audits;

I should note that your query has two errors. The expression DATE('created_at') is taking the DATE() value of a string. In this context, date() returns NULL, which isn't useful.

Even if you drop the single quotes (which may be an artifact of the posting), you will still get the error that you state. The SELECT DISTINCT DATE(created_at) or SELECT DATE(created_at)/GROUP BY will fix that problem.

Upvotes: 2

karthik reddy
karthik reddy

Reputation: 479

you can try below queries:

SELECT created_at FROM audits GROUP BY date_format('created_at','%d');

SELECT date_format('created_at','%d')as created_at_month,count(*) FROM audits GROUP BY date_format('created_at','%d');

similarly use // for year : '%Y' // for month : '%m' // for hour : '%H'

Upvotes: 0

Imre_G
Imre_G

Reputation: 2535

In your current query there is no group by necessary because you are not using a sum or a count function. I think that you currently have datetimes stored in the created_at field. What you want is to cast that to the date format which goes something like 'CAST(created_at AS date). This gives you the days without the time. Full query:

Select CAST(created_at AS Date) AS New_Date FROM audits

Upvotes: 0

Related Questions