无法从数据库获取当前月份和年份
问题描述：
I have a query to my database that holds interviews with the dates they were done, I need to be able to pull just the interviews for the current month and year only and count them then group them with the person that did the interview.
PHP
$time = time();
QUERY
select count(i.interv_id) as cnt, u.user_name, year(from_unixtime($time)) as year, month(from_unixtime($time)) as month
from support.support_interviews as i
left join support.support_logs as l on l.log_id = i.log_id
left join support.support_users as u on u.user_id = l.user_id
group by u.user_name, month, year
order by cnt desc
This query works fine and and I get what I want but I it counts all the data in the table.
TABLE STRUCTURE
CREATE TABLE support_interviews (
interv_id int(11) NOT NULL AUTO_INCREMENT,
log_id int(11) NOT NULL,
interv_date int(11) NOT NULL,
interv_url varchar(255) NOT NULL,
PRIMARY KEY (interv_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;
DATA
INSERT INTO `support_interviews` VALUES(1, 1, 1413849800, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(2, 1, 1413849800, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(3, 1, 1413849800, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(4, 2, 1413936200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(5, 2, 1413936200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(6, 3, 1414973000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(7, 3, 1414973000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(8, 3, 1414973000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(9, 3, 1414973000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(10, 4, 1415750600, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(11, 4, 1415750600, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(12, 5, 1415837000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(13, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(14, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(15, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(16, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(17, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(18, 6, 1416096200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(19, 7, 1416701000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(20, 7, 1416701000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(21, 7, 1416701000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(22, 7, 1416701000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(23, 7, 1416701000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(24, 8, 1417392200, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(25, 9, 1418342600, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(26, 9, 1418342600, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(27, 9, 1418342600, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(28, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(29, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(30, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(31, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(32, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(33, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(34, 10, 1418429000, 'http://somesite.com/interviewlink');
INSERT INTO `support_interviews` VALUES(35, 10, 1418429000, 'http://somesite.com/interviewlink');
答
If you want to get only the interviews for the current month and year, a solution is to add a WHERE clause in your SQL statement
SELECT *
FROM table
WHERE date_format(from_unixtime(interv_date),'%Y-%m')=date_format(now(), '%Y-%m')
答
You are using from_unixtime on now() and then applying year function. This will return null
mysql> select year(from_unixtime(now()));
+----------------------------+
| year(from_unixtime(now())) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.00 sec)
If you are trying to find year and month on now then you can directly use the function on now()
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2015 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)