How to compare timestamps in MySQL?

To compare timestamps in MySQL, you can use DATE(). Let us first create a table−

mysql> create table comparingTimestampDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> AdmissionDate timestamp
   -> );
Query OK, 0 rows affected (0.54 sec)

Following is the query to insert records in the table using insert command −

mysql> insert into comparingTimestampDemo(AdmissionDate) values('2019-03-31');
Query OK, 1 row affected (0.13 sec)
mysql> insert into comparingTimestampDemo(AdmissionDate) values('2019-04-10');
Query OK, 1 row affected (0.12 sec)
mysql> insert into comparingTimestampDemo(AdmissionDate) values('2019-04-15');
Query OK, 1 row affected (0.17 sec)
mysql> insert into comparingTimestampDemo(AdmissionDate) values('2019-03-29');
Query OK, 1 row affected (0.15 sec)
mysql> insert into comparingTimestampDemo(AdmissionDate) values('2019-04-07');
Query OK, 1 row affected (0.18 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from comparingTimestampDemo;

This will produce the following output −

+----+---------------------+
| Id | AdmissionDate       |
+----+---------------------+
| 1  | 2019-03-31 00:00:00 |
| 2  | 2019-04-10 00:00:00 |
| 3  | 2019-04-15 00:00:00 |
| 4  | 2019-03-29 00:00:00 |
| 5  | 2019-04-07 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)

Let us now compare timestamps in MySQL −

mysql> SELECT DATE(`AdmissionDate`) FROM comparingTimestampDemo WHERE
DATE(`AdmissionDate`) < CURDATE() - INTERVAL 3 DAY;

This will produce the following output −

+-----------------------+
| DATE(`AdmissionDate`) |
+-----------------------+
| 2019-03-31            |
| 2019-03-29            |
+-----------------------+
2 rows in set (0.00 sec)

Nishtha Thakur

Published on 16-Apr-2019 13:42:09

Previous Page Print Page