Extract of hours online per day

Question

I need to extract from the freeradius-powered RADACCT table, Radius server with Mysql, the connection records of a given user, for each day of the month, to determine how many hours that day the user was effectively online.

The difficulty I encountered referred to the date and time (datetime) starting on one day and to the date and time (datetime) ending on the other day or several days after the beginning of the session. With the calculations and codes I’ve made, it’s always minutes and hours!

The table below has been shortened, and this only with the fields I need for the calculations!

Below is an example of the table:

id | username | starttime           | stoptime            | totalsessiontime(use gmdate to return total)
1  | test     | 2019-03-31 16:28:43 | 2019-04-02 19:34:19 | 183935
2  | test     | 2019-04-02 19:35:17 | 2019-04-04 16:28:43 | 183935

The result I need is like this:

Day        | Total
2019-03-31 | 07:31:17
2019-04-01 | 24:00:00
2019-04-02 | 23:59:02
2019-04-03 | 24:00:00
2019-04-04 | 16:28:43

The results may not be correct, I made an example in my head to be as faithful as possible.

I’ve done several searches, but the cases I find are very specific, like mine, and I just can’t interpret to implement a QUERY.

Query used:

    SELECT SUM(acctsessiontime) as sessiontime,date(acctstarttime) as data_inicio, date(acctstoptime) as data_fim,time(acctstarttime) as time_inicio, time(acctstoptime) as time_fim FROM radacct WHERE username = '$usuario' AND (date(acctstarttime) = '$ano-$mes-$dia' AND (date(acctstoptime) = '$ano-$mes-$dia' OR date(acctstoptime) = '$ano-$mes-$dia_seguinte')

Controller

    function consumo() {

        $this->load->model('radius_model');

        $tempo_online = $this->radius_model->tempo_online('usuario@test', '04', '2019');

        foreach ($tempo_online as $dia => $value) {
            $dias[' ' . $dia . '-04-2019 '] = gmdate("H:i:s", (int) $tempo_online[$dia]['sessiontime']);
        }

        echo "<pre>";
        print_r($dias); }

Model

    public function tempo_mensal_online($usuario, $dia, $mes, $ano) {

        $sql = "SELECT SUM(acctsessiontime) as sessiontime,date(acctstarttime) as data_inicio, date(acctstoptime) as data_fim,time(acctstarttime) as time_inicio, time(acctstoptime) as time_fim FROM radacct WHERE username = '$usuario' AND (date(acctstarttime) = '$ano-$mes-$dia' AND (date(acctstoptime) = '$ano-$mes-$dia' OR date(acctstoptime) = '$ano-$mes-$dia_seguinte')";
        return $this->db->query($sql)->result_array();
    }

Answer 1

_{^{answer prepared by the author of the question, transcribed here}}

---

After reading the Mysql documentation sent by @ngueno, I was able to arrive at the expected result by modifying my Query and treating the result with date calculations.

It follows below as they remained the codes:

New Query

    SELECT acctsessiontime, acctstarttime, acctstoptime FROM radacct WHERE username = '$usuario' AND ('$ano-$mes-$dia' BETWEEN date(acctstarttime) AND date(acctstoptime) OR '$ano-$mes-$dia' BETWEEN date(acctstoptime) AND date(acctstarttime))

The result was only satisfactory using the BETWEEN operator(Reference), I was able to extract the correct records and so make calculations that gave the result I need.

New Controller

function consumo() {
        $this->load->model('radius_model');

        $usuario = 'usuario@test';
        $mes = '04';
        $ano = '2019';
        $ultimo_dia_mes = date("t", mktime(0, 0, 0, $mes, '01', $ano));


        for ($dia = 01; $dia <= $ultimo_dia_mes; $dia++) {
            strlen($dia) === 1 ? $dia = '0' . $dia : ''; // add 0 aos dias 1-9

            /*  Buscando registros diarios do usuário */
            $registros_diario = $this->radius_model->tempo_online($usuario, $dia, $mes, $ano);

            if (count($registros_diario) > 0) {

                /*  Descobrindo o primeiro e ultimo item da array de registros */
                $primeiro = reset($registros_diario);
                $ultimo = end($registros_diario);

                $primeiro_acct = strtotime($primeiro['acctstarttime']);
                $sessao_inicio = strtotime("$ano-$mes-$dia 00:00:00");
                $primeira_diferenca = $sessao_inicio - $primeiro_acct;

                $sessao_fim = strtotime("$ano-$mes-$dia 24:00:00");
                $ultimo_acct = strtotime($ultimo['acctstoptime']);
                $segunda_diferenca = $ultimo_acct - $sessao_fim;

                /*  Descobrindo o tempo online total */
                $soma = 0;
                foreach ($registros_diario as $registro) {
                    $soma += $registro['acctsessiontime'];
                }

                $soma_final = $soma - ($primeira_diferenca + $segunda_diferenca);

                $dtF = new \DateTime('@0');
                $dtT = new \DateTime("@$soma_final");
                $horas_online = $dtF->diff($dtT)->format('%h:%i:%s');
                if ($dtF->diff($dtT)->format('%a') > 0) {
                    $horas_online = '24:00:00';
                }

                $registros[$dia] = $horas_online;
            }
        }

        echo "<pre>";
        print_r($registros);
    }

Nova Model

public function tempo_online($usuario, $dia, $mes, $ano) {

        $this->db->select("acctsessiontime, acctstarttime, acctstoptime");

        $this->db->where("username = '$usuario' AND ('$ano-$mes-$dia' BETWEEN date(acctstarttime) AND date(acctstoptime) OR '$ano-$mes-$dia' BETWEEN date(acctstoptime) AND date(acctstarttime))");

        return $this->db->get('radacct')->result_array();
    }