Jtdylktuy

my title may seem a bit confusing but here's the context.

I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user connects today and disconnects after 2days. So in the table it only has 1 record containing starttime which is today and endtime that contains 2days after today.

Now I want to extract a report of user connection per day. In this case when I group by on the reference code per day, it will only count as 1 for 3 days. but i want it to be counted as 1 per day. What are some ways that I can do this?

@MrBrownstone
The result of that query would be

username                 starttime         count(*)

445425760933             2019-02-05            1

445425760934             2019-02-05            1

445425760934             2019-02-06            1

This is not the result that I am looking for. Below is the result that I need:

username                 starttime         count(*)

445425760933             2019-02-05            1

445425760933             2019-02-06            1

445425760933             2019-02-07            1

445425760934             2019-02-05            1

445425760934             2019-02-06            1

Since the code 445425760933 was used until 2019-02-07

For MySQL 8+:

WITH RECURSIVE dates AS (

SELECT MIN(DATE(starttime)) `date` 

  FROM table1

UNION ALL

SELECT `date` + INTERVAL 1 DAY 

  FROM dates 

  WHERE `date` < ( SELECT MAX(DATE(endtime)) 

                   FROM table1 )

),

users AS (

SELECT DISTINCT username 

  FROM table1

)

SELECT /* DISTINCT */ users.username, dates.`date`, 1 `count(*)`

FROM users

JOIN dates

JOIN table1 ON table1.username = users.username

           AND dates.`date` BETWEEN DATE(table1.starttime) 

                                AND DATE(table1.endtime)

If I understand your needs correctly, you could use the DATE function to group by just the date and ignore the time:

SELECT username, DATE(starttime) AS start_date, COUNT(*) as `count`

  FROM my_table

GROUP BY username, DATE(starttime)

ORDER BY username, DATE(starttime);

You can find more information regarding date and time functions below:

Date and Time Functions (MySQL Reference)

EDIT

Based on the additional information provided you will need an ancillary table that contains just dates in order to achieve what you want to do:

CREATE TABLE `dates`

(

    `Date` DATE NOT NULL PRIMARY KEY

);



SELECT DISTINCT username, `Date`, COUNT(*) as `count`

  FROM my_table AS t

  JOIN dates AS d

    ON d.`Date` BETWEEN DATE(t.startime) AND DATE(t.endtime)

 GROUP BY username, `Date`

 ORDER BY username, `Date`;