Mysql COUNT按日期仅是DATETIME的一部分 [英] Mysql COUNT by date only part of DATETIME

问题描述

我有一张像下面这样的表格

I have a table like below

 +-----+--------------+---------------------+
 | id  | url          | visit_date          | 
 +-----+--------------+---------------------+
 |  1  + 60           | 2012-06-23 18:15:49 |  
 +-----+--------------+---------------------+
 |  2  + 60&ref=qr    | 2012-06-23 12:15:49 |  
 +-----+--------------+---------------------+
 |  3  + 54           | 2012-06-23 18:15:49 |  
 +-----+--------------+---------------------+
 |  4  + 5            | 2012-06-24 18:15:49 |  
 +-----+--------------+---------------------+
 |  5  + 60           | 2012-06-25 13:15:49 |  
 +-----+--------------+---------------------+
 |  6  + 60           | 2012-06-25 19:15:49 |  
 +-----+--------------+---------------------+

我只想将结果分组我的日期.所以结果是，例如我只想要 60 的 url.

I want to group results my date only. so the result would be if for example I only wanted url with 60.

  +-------------+------------------+
  |visit_date   |count(visit_date) |
  +-------------+------------------+
  |  2012-06-23 |        2         | 
  +-------------+------------------+
  |  2012-06-25 |        2         |
  +-------------+------------------+

我尝试了以下方法，但显然由于时差，我得到了每个日期.

I have tried the following but obviously I get every date due to the time difference.

  SELECT visit_time, COUNT( visit_time ) 
  FROM tracking
  WHERE query_string LIKE  '%60%'
  GROUP BY visit_time

是否可以仅通过忽略时间的日期来执行此操作?

is it possible to do this only by the date ignoring the time?

推荐答案

是的，你可以.只需使用 DATE() 函数.

Yes, you can. Just use DATE() function.

SELECT DATE(visit_time), COUNT( visit_time ) 
FROM tracking
WHERE query_string LIKE  CONCAT('%', '60' ,'%')  -- makes readable
GROUP BY DATE(visit_time)

这篇关于Mysql COUNT按日期仅是DATETIME的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！