2

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?

the row marked as x is the one I am describing above.

@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

2

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)
  • sorry I did not mention the version im using. its mysql 5.7 – Driggs Alonzo Feb 7 at 10:03
  • For 5.7 you can move pseudotables from WITH clause to FROM clause. The recursive CTE can be converted using statically-generated numbers list. Or you can try to use a variable, of course, but this technique sometimes can give unpredictably strange result while applying in subquery - I do not recommend this. – Akina Feb 7 at 10:31
  • @DriggsAlonzo Is there any maximum possible difference between starttime and endtime (counting in days)? Some days count which guarantee the difference will never be longer? – Akina Feb 7 at 10:35
1

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`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.