How to select minimum and maximum time per for each day and userid using mysql query?-CodePudding

I have mysql 5.7.40 db table with simple layout as

| userid | datetime         | ipaddress |
|--------|------------------|-----------|
| 1      | 25-11-2022 05:30 | 10.15.1.1 |
| 1      | 21-11-2022 05:30 | 10.15.1.1 |
| 2      | 22-11-2022 11:30 | 10.15.1.1 |
| 1      | 23-11-2022 22:30 | 10.15.1.1 |
| 2      | 25-11-2022 14:30 | 10.15.1.1 |
| 3      | 20-11-2022 18:30 | 10.15.1.1 |
| 3      | 23-11-2022 05:30 | 10.15.1.1 |
| 2      | 11-11-2022 09:30 | 10.15.1.1 |
| 1      | 22-11-2022 11:30 | 10.15.1.1 |
| 2      | 23-11-2022 13:30 | 10.15.1.1 |
| 2      | 08-11-2022 12:30 | 10.15.1.1 |
| 1      | 04-03-2022 11:30 | 10.15.1.1 |
| 3      | 25-11-2022 10:30 | 10.15.1.1 |
| 3      | 23-11-2022 15:30 | 10.15.1.1 |

I want to have the output per each userid and each day in one record, showing start time and start ipaddress and end time and end ipaddress, using mysql query.

| userid | date       | start ipaddress | start time | end ipaddress | end time |
|--------|------------|-----------------|------------|---------------|----------|
| 1      | 25-11-2022 | 10.15.1.1       | 05:30      | 10.15.1.1     | 14:30    |
| 1      | 26-11-2022 | 10.15.1.2       | 06:20      | 10.15.1.2     | 16:30    |
| 2      | 22-11-2022 | 10.15.1.4       | 10:21      | 10.15.1.3     | 12:30    |
| 3      | 23-11-2022 | 10.15.1.6       | 12:30      | 10.15.1.5     | 16:30    |
| 2      | 25-11-2022 | 10.15.1.3       | 07:45      | 10.15.1.3     | 15:30    |
| 3      | 21-11-2022 | 10.15.1.1       | 04:33      | 10.15.1.1     | 19:30    |

I've tried to use self join and inner join with no success, hope someone can help with a straight forward solution. Also I am concerned about the query timing, since this table has millions of records.

Please note that the data used in sample above is not consistent, just to deliver the idea of what I want to achieve.

Your help is appreciated, Thanks.

CodePudding user response：

Assuming datetime is actually a datetime column something like this should work:

SELECT v.userid,
    v._date,
    ul1.ipaddress AS start_ip,
    TIME_FORMAT(v._start, '%H %i') AS start_time,
    ul2.ipaddress AS end_ip,
    TIME_FORMAT(v._end, '%H %i') AS end_time
FROM (
    SELECT userid, min(datetime) AS _start, max(datetime) AS _end, DATE(datetime) AS _date
    FROM userlogin
    GROUP BY userid, DATE(datetime)
) v
    JOIN userlogin ul1 ON ul1.datetime = v._start AND ul1.userid = v.userid
    JOIN userlogin ul2 ON ul2.datetime = v._end AND ul2.userid = v.userid

The idea is to create join the table to a view of itself which is already grouped by date and with the start and end times. You can read more here about various approaches: https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

Unfortunately it appears you might be storing datetime as varchar as mentioned in the comments which makes this a bit trickier. You can probably implement the above approach with a bit of data manipulation though using str_to_date().

It's always best to include schema and sample data in your question. A dbfiddle or the like would be even better.