Filter the records of current day, month and year in MySQL?

Let’s say you have a table with UserLoginTime column wherein we have stored some values for sample. This is the login time of users and we want to filter all these records on the basis of current day, month and year i.e. the current date. We will be

Let us now create the table we discussed above

mysql> create table userLoginInformation
   - > (
   - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > UserName varchar(20),
   - > UserLoginTime datetime
   - > );
Query OK, 0 rows affected (0.79 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into userLoginInformation(UserName,UserLoginTime) values('John','2016-02-12');
Query OK, 1 row affected (0.18 sec)
mysql> insert into userLoginInformation(UserName,UserLoginTime) values('Carol','2019-01-31');
Query OK, 1 row affected (0.16 sec)
mysql> insert into userLoginInformation(UserName,UserLoginTime) values('Bob','2019-02-19');
Query OK, 1 row affected (0.12 sec)
mysql> insert into userLoginInformation(UserName,UserLoginTime) values('Sam','2018-02-19');
Query OK, 1 row affected (0.16 sec)
mysql> insert into userLoginInformation(UserName,UserLoginTime) values('Larry','2017-04-18');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from userLoginInformation;

The following is the output

+--------+----------+---------------------+
| UserId | UserName | UserLoginTime       |
+--------+----------+---------------------+
|      1 | John     | 2016-02-12 00:00:00 |
|      2 | Carol    | 2019-01-31 00:00:00 |
|      3 | Bob      | 2019-02-19 00:00:00 |
|      4 | Sam      | 2018-02-19 00:00:00 |
|      5 | Larry    | 2017-04-18 00:00:00 |
+--------+----------+---------------------+
5 rows in set (0.00 sec)

Now, we will filter records on the basis of current date using the YEAR() and now(). Here, we are using now() to get the current date

mysql> select *from userLoginInformation where YEAR(UserLoginTime)=YEAR(now());

The following is the output

+--------+----------+---------------------+
| UserId | UserName | UserLoginTime       |
+--------+----------+---------------------+
|      2 | Carol    | 2019-01-31 00:00:00 |
|      3 | Bob      | 2019-02-19 00:00:00 |
+--------+----------+---------------------+
2 rows in set (0.00 sec)