Ask a database question. there is a table containing the data stored every day and how to retrieve the number of data entries per month.

Ask a database question. there is a table containing the data stored every day and how to retrieve the number of data entries per month. This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.

Reply to discussion (solution)

select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1

select FROM_UNIXTIME(iptime,'%Y%m%d'),count(0) from ec_ip_article group by FROM_UNIXTIME(iptime,'%Y%m%d')

select FROM_UNIXTIME(iptime,'%Y%m%d'),count(0) from ec_ip_article group by FROM_UNIXTIME(iptime,'%Y%m%d')

Select FROM_UNIXTIME (iptime, '% Y % M'), count (0) from ec_ip_article group by FROM_UNIXTIME (iptime,' % Y % M ')

SELECT ip_id,count(ip_id) count,FROM_UNIXTIME(iptime, '%Y%m%d') date FROM test GROUP BY FROM_UNIXTIME(iptime, '%c') ORDER BY iptime

select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1

Is % Y-% m in the timestamp?

This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.

$sql="select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1  where  user_id=392";$result=mysql_query($sql);$row=mysql_fetch_row($result);dump($row);

Why can't I retrieve it?

select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1

$sql="select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1  where  user_id=392";$result=mysql_query($sql);$row=mysql_fetch_row($result);dump($row);

Why can't I retrieve it?

Impossible! How did you write it?

Test example

mysql_connect();mysql_select_db('test');mysql_query("create temporary table ec_ip_article ( iptime int )");$a = array('2015-02-10', '2015-02-20', '2015-03-10','2015-04-10','2015-04-21',);foreach($a as $t)mysql_query(sprintf("insert into ec_ip_article values (%d)", strtotime($t)));$sql = "select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1";$rs = mysql_query($sql);while($r = mysql_fetch_assoc($rs))  echo join(' ', $r), PHP_EOL;

2015-02 22015-03 12015-04 2

Test example

mysql_connect();mysql_select_db('test');mysql_query("create temporary table ec_ip_article ( iptime int )");$a = array('2015-02-10', '2015-02-20', '2015-03-10','2015-04-10','2015-04-21',);foreach($a as $t)mysql_query(sprintf("insert into ec_ip_article values (%d)", strtotime($t)));$sql = "select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1";$rs = mysql_query($sql);while($r = mysql_fetch_assoc($rs))  echo join(' ', $r), PHP_EOL;

2015-02 22015-03 12015-04 2

In the ec_ip_article table, if there is a user ID field next to it, it is not feasible to add the where field directly when calculating the number of times of each user per month.