Show data in current day, week, month and year

Question

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:

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?

Answer 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:

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

Or, as @ItsMe suggested:

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

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