I have a table named "testing_tables", let's say the current date is 2022-03-01, and 1 month = 30 days.
| ip | founded | pid |
|---|---|---|
| 192.168.1.1 | 2022-01-1 | 1111 |
| 192.168.1.2 | 2022-01-1 | 2222 |
| 192.168.1.3 | 2022-01-1 | 3333 |
| 192.168.1.1 | 2022-02-1 | 1111 |
| 192.168.1.2 | 2022-02-1 | 2222 |
| 192.168.1.3 | 2022-02-1 | 3333 |
| 192.168.1.1 | 2022-03-1 | 1111 |
| 192.168.1.2 | 2022-03-1 | 2222 |
| 192.168.1.4 | 2022-03-1 | 4444 |
I'm trying to achieve the following results:
| ip | founded | pid | aging_days |
|---|---|---|---|
| 192.168.1.1 | 2022-01-1 | 1111 | 90 days |
| 192.168.1.2 | 2022-01-1 | 2222 | 90 days |
| 192.168.1.3 | 2022-01-1 | 3333 | 60 days |
| 192.168.1.4 | 2022-03-1 | 4444 | 0 days |
Basically, I want to try counting the days that the first IP was founded, and then automate calculating the number of days. and then display only 1 IP & pid with the following aging days. Mysql version: 8.0+
New Note: I have still some issues logic regarding if IP 192.168.1.3 didn't show in march, the count of the days still continue, need to display only 60 days like the following table
You can try the following:
SELECT ip, MIN(founded) AS founded, pid, DATEDIFF(CURRENT_TIMESTAMP(), MIN(founded)) AS aging_days FROM testing_tables GROUP BY ip, pid ORDER BY ip