Sql group by date without time

Question

I am new to sql and I would like to create a query that will read all of my article id for every day. but the date column problem also contains time .. so how can I make a request to group simply by date without time?

eg:

id|article_id|date (timestamp) 1|22|2014-01-10 13:30:10 1|23|2014-02-10 12:30:10 

Thanks shay

Answer 1

Try the following:

 select count(article_id) from YourTable group by date(date) 

Answer 2

Try the following:

 select date(date), count(article_id) from YourTable group by date(date); 

Answer 3

 $dayCount = 0; $previousDay = ""; $query = $database->prepare(' SELECT DATE_FORMAT(date, '%d/%m/%Y') AS date FROM my_table ORDER BY date'); $query->execute(); while ($output = $query->fetch()) { $day = $output['date']; if($previousDay != $day) { $dayCount ++; } else { echo $dayCount; $dayCount = 0; } $previousDay = $day; } 

May find DATE_FORMAT output here:
http://dev.mysql.com/doc/refman/5.0/fr/date-and-time-functions.html

Answer 4

To group by date without time => use the following

NOTE: myDateTime is in date time format

GROUP BY CAST (myDateTime AS DATE)

Answer 5

it will work

 SELECT COUNT(id),to_char(to_date('2014-01-10 13:30:10','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') AS date_part FROM your_table_name GROUP BY to_char(to_date('2014-01-10 13:30:10','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') 

Answer 6

I'm too late to race. Hope this can help someone in the future.

There are 2 solutions for this.

• You can use DATE as a filter parameter in a date label in GROUP BY as follows

  SELECT COUNT(id) as article_count, DATE(date) GROUP BY DATE(date) ;

• You can take a SELECT column alias with a DATE () filter in GROUP BY

  SELECT COUNT(id) as article_count, DATE(date) published_date GROUP BY published_date;

Hope this helps. Happy coding!

.