Clever Geek Handbook

MySQL - how to choose an id where min / max difference in dates is more than 3 years

I have a table like this:

| id |           date     |        user_id         |
----------------------------------------------------
| 1  |     2008-01-01     |          10            |
| 2  |     2009-03-20     |          15            |
| 3  |     2008-06-11     |          10            |
| 4  |     2009-01-21     |          15            |
| 5  |     2010-01-01     |          10            |
| 6  |     2011-06-01     |          10            |
| 7  |     2012-01-01     |          10            |
| 8  |     2008-05-01     |          15            |

I'm looking for a solution how to choose user_id where the difference between MIN and MAX dates is more than 3 years. For the above data, I should get:

|      user_id        |
-----------------------
|         10          |

Anyone can help?

+3


source to share


4 answers 


SELECT   user_id 
FROM     mytable 
GROUP BY user_id
HAVING   MAX(`date`) > (MIN(`date`) + INTERVAL '3' YEAR);


Tested here: http://sqlize.com/MC0618Yg58

+5


source


Similar to bernie's method, I would keep the date format. I probably also probably enumerated MAX first to avoid the call ABS

(the protection of a positive number always returns).

SELECT   user_id
FROM     my_table
WHERE    DATEDIFF(MAX(date),MIN(date)) > 365


DATEDIFF

just returns the delta (in days) between two given date fields.

+2


source 


SELECT user_id
 FROM (SELECT user_id, MIN(date) m0, MAX(date) m1
       FROM table
       GROUP by user_id)
HAVING EXTRACT(YEAR FROM m1) - EXTRACT(YEAR FROM m0) > 3
-1


source 


SELECT A.USER_ID FROM TABLE AS A
JOIN TABLE AS B 
ON A.USER_ID = B.USER_ID
WHERE DATEDIFF(A.DATE,B.DATE) > 365
-1


source






More articles:


All Articles