Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SELECT a row by subtracting dates in WHERE in MySQL?
For this, use TIMESTAMPDIFF(). Let us create a table −
mysql> create table demo42 −> ( −> start_date datetime −> ); Query OK, 0 rows affected (0.77 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo42 values('2020-01-10 12:30:05');
Query OK, 1 row affected (0.11 sec)
mysql> insert into demo42 values('2019-02-24 10:40:45');
Query OK, 1 row affected (0.11 sec)
mysql> insert into demo42 values('2020-05-12 05:45:55');
Query OK, 1 row affected (0.17 sec)
mysql> insert into demo42 values('2020-05-12 05:40:55');
Query OK, 1 row affected (0.15 sec)
mysql> insert into demo42 values('2020-05-12 05:42:55');
Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −
mysql> select *from demo42;
This will produce the following output −
+---------------------+ | start_date | +---------------------+ | 2020-01-10 12:30:05 | | 2019-02-24 10:40:45 | | 2020-05-12 05:45:55 | | 2020-05-12 05:40:55 | | 2020-05-12 05:42:55 | +---------------------+ 5 rows in set (0.00 sec)
Following is the query to select a row with TIMESTAMPDIFF −
mysql> select *from demo42 −> where ABS(TIMESTAMPDIFF(MINUTE,start_date, '2020−05−12 05:40:55')) < 5;
This will produce the following output −
+---------------------+ | start_date | +---------------------+ | 2020−05−12 05:40:55 | | 2020−05−12 05:42:55 | +---------------------+ 2 rows in set (0.00 sec)
Updated on: 19-Nov-2020
95 Views
Advertisements