#StackBounty: #mysql #query #select #time How get info about duration of activity in time from MySQL table?

Bounty: 50

I have MySQL table "sessions" with columns bellow. In this table I have information about start and end every session which was iniciated from specific IP. In some cases for technical reasons end time could not be determined so in these cases value is "NULL". Example is bellow. I need get information about duration of activity for each "ip". For example 1.1.1.1 was active for 3 days, 2.2.2.2 for 7 days and etc. Is possible get these information with any SQL query please? Thank you for your help.

Table sessions

| sessions | CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `sensor` int(4) NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `termsize` varchar(7) DEFAULT NULL,
  `client` int(4) DEFAULT NULL,
  `asnid` int(10) DEFAULT NULL,
  `redirect` varchar(5) DEFAULT NULL,
  `ipid` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `starttime` (`starttime`,`sensor`),
  KEY `asnid` (`asnid`),
  KEY `fk_grade_id` (`ipid`),
  KEY `idx_sesip` (`ip`),
  KEY `idx_sescl` (`client`),
  CONSTRAINT `fk_grade_id` FOREIGN KEY (`ipid`) REFERENCES `ipinfo` (`id`),
  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`asnid`) REFERENCES `asinfo` (`asnid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Data example:

| id           | starttime           | endtime             | sensor | ip              | termsize | client | asnid | redirect | ipid |
+--------------+---------------------+---------------------+--------+-----------------+----------+--------+-------+----------+------+
| 000002cf3795 | 2018-06-25 13:24:35 | 2018-06-25 13:24:38 |      4 | 1.1.1.1         | NULL     |     89 |  4167 | NULL     | NULL |
| 000007057b0e | 2017-08-12 13:49:36 | 2017-08-12 13:49:45 |      2 | 2.2.2.2         | NULL     |     53 |   851 | NULL     | NULL |
| 000012420d7e | 2019-01-08 19:58:18 | 2019-01-08 19:58:23 |      6 | 3.3.3.3         | NULL     |     53 | 41735 | NULL     | NULL |
| 0000198bf2d6 | 2018-03-22 15:55:59 | NULL                |      4 | 1.1.1.1         | NULL     |   NULL | 18671 | NULL     | NULL |
| 000019ec3c02 | 2017-08-16 21:37:31 | 2017-08-16 21:37:36 |      5 | 2.2.2.2         | NULL     |     90 |   169 | NULL     | NULL |

On the server I use mysql Ver 14.14 Distrib 5.5.62, but I have full export of database on my local system, where I have the latest version of MariaDB.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.