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

  • Server cannot return the data which is absent. You must generate pseudotable which contains all dates within checking period, then join your data to it by including. And where does you find the reason for DISTINCT applying? – Akina Feb 7 at 8:51
  • Create a calendar table c and join against that. select t.username, c.dt, count(*) from t join calendar c on c.dt between date(t.starttime) and date(t.endtime) group bt t.username, c.dt – Lennart Feb 7 at 8:55
  • Assume there are 2 records with the starttime and endtime within the same date for same user. Does the value in `count(*)` column must be 2 for this date and user? – Akina Feb 7 at 10:39
  • @Akina nope, it should only be 1 record. the goal is to check the count of unique username per day. – Driggs Alonzo Feb 10 at 15:13
  • @Akina nope, it should only be 1 record. the goal is to check the count of unique username per day. – Driggs Alonzo Feb 10 at 15:14
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
  • Last date in dayes should be: SELECT MAX(DATE(endtime) – Lennart Feb 7 at 11:56
  • 1
    @Lennart Misprint. Great thanks. Edited. – Akina Feb 7 at 13:29
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`;
  • kindly see my response above @Mr.Brownstone – Driggs Alonzo Feb 7 at 8:27
  • @DriggsAlonzo I apologize I do not understand the difference. If there is 2019-02-07 in your data, it will be returned in the result set. Could you elaborate? – Mr.Brownstone Feb 7 at 8:30
  • yes I know. that is the tricky part. since the username 445425760933 starttime was 2019-02-05 and endtime was 2019-02-07. It means that it was used from 2019-02-05 - 2019-02-07 – Driggs Alonzo Feb 7 at 8:47
  • OK - now I understand! – Mr.Brownstone Feb 7 at 8:54
  • thanks for the inputs! this is the answer that I needed. – Driggs Alonzo Feb 7 at 10:08

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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