显示当前日，周，月和年的数据

I have table user, it contains id_user, username and user_time. And user_time is the time a user registered, the properties is timestamp so the output is like this:

我有表用户,它包含id_user,username和user_time。 user_time是用户注册的时间,属性是时间戳,因此输出如下:

User A | user_time > 2014-12-22 10:10:10

Then I use this query to display data per year

然后我使用此查询来显示每年的数据

SELECT * FROM user WHERE user_time = YEAR(NOW());

The query works but just returns an empty row. It's not only the year, but also day, week, and month return empty rows. Why is this query not returning any data?

查询有效,但只返回一个空行。它不仅是年份,而且是日,周和月返回空行。为什么此查询不返回任何数据?

2 个解决方案

#1

Problem is that you doesn't do anything with your data:

问题是你没有对你的数据做任何事情:

SELECT * FROM user WHERE user_time=YEAR(NOW())

Try to get the YEAR from the user_time too, like this, so you do compare the numbers, not timestamp and a number:

尝试从user_time获取YEAR,就像这样,所以你要比较数字,而不是时间戳和数字:

SELECT * FROM user WHERE YEAR(user_time) = YEAR(NOW())

Or, as @ItsMe suggested:

或者,正如@ItsMe建议:

EXPLAIN SELECT * FROM user WHERE YEAR(user_time) = YEAR(NOW())

And please, avoid the * in your queries, this is a bad practice.

请在你的查询中避免使用*,这是一种不好的做法。

#2

YEAR returns a number like 2014. That will never be equal to a time. You need to test whether the year of user_time is the same as the given year:

YEAR返回的数字与2014年相同。这永远不会等于时间。您需要测试user_time的年份是否与给定年份相同:

SELECT * FROM user
WHERE YEAR(user_time) = YEAR(NOW())

#1