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
CodePudding user response：
Try this
SELECT
ip, MIN(founded) founded, pid, DATEDIFF(CURRENT_TIMESTAMP(), MIN(founded)) aging_days
FROM testing_tables
GROUP BY ip, pid
CodePudding user response：
select ip, min(founded) as founded, pid, DATEDIFF(CURRENT_DATE,min(founded)) as aging_days
from testing_tables
group by pid
order by pid;
CodePudding user response：
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