Question 21520 in Mysql Question posted in
The official MySQL documentation can be found here.

How can i query the dates between 2 columns with same username in mysql

I have a mysql table which has ‘username’, ‘acctstarttime’ and ‘acctstoptime'(and an ‘id’) datetime columns. I need to get records that its acctstarttime or acctstoptime between other ‘acctstarttime’ and ‘acctstoptime’ records with same username. For example ‘John’ has 3 records.

id username acctstarttime acctstoptime
1 John 29.12.2022 01.01.2023
2 John 30.12.2022 03.03.2023
3 John 12.12.2022 14.12.2022

Second rows acctstarttime is between first rows acctstarttime and acctstoptime and first rows acctstoptime is between second rows acctstarttime and acctstoptime. I want to query that 2 records.

I can do it with php but it takes about 3-4 days or more for 1 million records. I have very inefficient function.

How can i reduce this time in mysql or php(just speed up)?

I downloaded each record to different files based on username. I wrote this laravel code but it works too slow. It can be confusing.

    function findRecordsBetweenSameHours()
    {
        $directory = storage_path() . "/app/";
        $files = Storage::files('usernames');
        foreach ($files as $file) {
            $records =json_decode(file_get_contents($directory . $file), true);
            foreach ($records as $record) {
                $record["acctstarttime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record["acctstarttime"]);
                $record["acctstoptime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record["acctstoptime"]);
                foreach ($records as $record2) {
                    $record2["acctstarttime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record2["acctstarttime"]);
                    $record2["acctstoptime"] = Carbon::createFromFormat('Y-m-d H:i:s', $record2["acctstoptime"]);
                    if (
                        ($record2["acctstoptime"]->between($record["acctstarttime"], $record["acctstoptime"], false)
                            || $record2["acctstarttime"]->between($record["acctstarttime"], $record["acctstoptime"], false)
                        )
                        && $record2["acctsessionid"] != $record["acctsessionid"]
                ) {
                        Storage::append('x.log',
                            $record["acctsessionid"] . " - " . $record2["acctsessionid"] . " - " . $record["username"]
                        );

                    }
                }
            }
        }
        Storage::append('x.log',
            "finish"
        );
    }
Tags:

2

Answers


  1. 0

    You can write query like this

    SELECT 
    t1.*
FROM
    table_name t1
        JOIN
    table_name t2 ON t1.username = t2.username
WHERE
    (t1.acctstarttime > t2.acctstarttime
        AND t1.acctstarttime < t2.acctstoptime)
        OR (t1.acctstoptime > t2.acctstarttime
        AND t1.acctstoptime < t2.acctstoptime);
    Login or Signup to reply.
  2. 0

    As I understand your question, you want records for which another record exists with the same user name and an overlapping period of time.

    I would recommend exists and a few inequality conditions:

    select *
from mytable t1
where exists (
    select 1
    from mytable t2
    where t1.id != t.id
      and t1.username = t2.username
      and t1.acctstarttime <= t2.acctstoptime 
      and t2.acctstarttime <= t1.acctstoptime
)

    For each row, the subquery searches for an "overlapping" record of the same user, and filters in rows that match. The inequality conditions on the start and end time qualify the date range overlap.

    An index on (id, acctstarttime, acctstoptime) might help performance.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search