CODEWITHSUNDEEP
mysqlsql
Shai
Shai

Reputation: 91

sql group by date without time

I am new to sql, and I would like to create a query that will count all my article id's for each day. but the problem the date column is also containing time.. so how I can make the query to group by just by date without time?

for example: 

id|article_id|date (timestamp)

1|22|2014-01-10 13:30:10 
1|23|2014-02-10 12:30:10

Thanks Shai

Upvotes: 9

Views: 24356

Answers (6)

Channaveer Hakari
Channaveer Hakari

Reputation: 2927

I am too late in the race. Hope it may help some one in future.

There are 2 solution's for this

  1. You can take DATE as filter option on your date timestamp in GROUP BY as follows

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

  2. You can take the alias of the SELECT column with is filtered by DATE() in GROUP BY

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

Hope it helped. Happy Coding!

.

Upvotes: 5

dev
dev

Reputation: 979

this 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')

Upvotes: 0

Nestor
Nestor

Reputation: 61

Try this:

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

Upvotes: 6

Ronak Shah
Ronak Shah

Reputation: 1549

Try this:

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

Upvotes: 9

Raghaven 3534
Raghaven 3534

Reputation: 296

To group by date without time => use the following

NOTE: myDateTime is in date time format

GROUP BY CAST(myDateTime AS DATE)

Upvotes: 0

gr3g
gr3g

Reputation: 2914

$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;

 }

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

Upvotes: 2

Related Questions