Sql query- Get results between two different date columns with its exact days match

Question

I have the following 2 columns in 'deals' table. - subscribed_date - expired_date

I want the SQL query that to filter the records with 2 columns difference as follows

(expired_date - subscribed_date) == 7 days

For eg:

id  |  subscribed_date      | expired_date
1   |  2015-07-04 04:13:29  | 2015-09-03 04:13:29
2   |  2015-06-03 04:13:29  | 2015-06-10 04:13:29
3   |  2015-01-05 04:13:29  | 2015-02-08 04:13:29

In the above example, the result should be id=2 because its difference is 7 days.

Is it possible through SQL query without iterations?

Thanks in advance for your help!

score 2 · Answer 1 · answered Jul 14 '15 at 10:27

2

The easiest way would be to use timestampdiff function

mysql> select timestampdiff(day,'2015-06-03 04:13:29','2015-06-10 04:13:29') as d ;
+------+
| d    |
+------+
|    7 |
+------+

So the query becomes

select * from deals
where timestampdiff(day,subscribed_date,expired_date) = 7

answered Jul 14 '15 at 10:27

Abhik Chakraborty

44,654
6
52
63

Thank you Abhik for your quick reply! However, polkovnikov.ph approach is easiest one for me. So I chosen his answer as accepted and also I voted up you :) – Asik Jul 14 '15 at 10:41

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

Why not just SELECT * FROM deals WHERE DATEDIFF(expired_date, subscribed_date) = 7?

Further info here.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 14 '15 at 10:29

polkovnikov.ph

6,256
6
44
79

1

Thank you polkovnikov.ph :) – Asik Jul 14 '15 at 10:42

Sql query- Get results between two different date columns with its exact days match

2 Answers2