How to obtain a group membership time evolution from login date / logout date table in mysql

Question

a newcomer to mysql here: I have a members table with Name, login_date and logout_date columns.

Name	login_date	logout_date
John	2004-01-08 00:00:00	2004-01-10 00:00:00
Mary	2004-01-09 00:00:00	2005-05-31 00:00:00
Frank	2004-01-12 00:00:00	2005-11-08 00:00:00
Nancy	2004-01-12 00:00:00	2007-10-13 00:00:00
Louise	2004-01-16 00:00:00	2011-09-30 00:00:00

I want to know the evolution of membership over a period of time so using 2 dates (first_date and last_date) as limits to get the number of members in the group each day in the range (first_date,last_date)

Can it be done in mysql? Any clue will be very much appreciated

Cheers.

The following query gets the number of members in a particular date (@mydate)

SELECT count(*) FROM members WHERE login_date <= @mydate and logout_date>= @mydate

I want to have something like this given 2 dates: @myfirstdate = '2004-01-08 00:00:00' @mylastdate = '2004-01-16 00:00:00'

Date	Members #
2004-01-08 00:00:00	1
2004-01-09 00:00:00	2
2004-01-10 00:00:00	2
2004-01-11 00:00:00	1
2004-01-12 00:00:00	3
2004-01-13 00:00:00	3
2004-01-14 00:00:00	3
2004-01-15 00:00:00	3
2004-01-16 00:00:00	4

But I don't know how to do it in mysql if it is at all possible.

This will generate a series of dates: https://stackoverflow.com/questions/2157282/generate-days-from-date-range?noredirect=1&lq=1. — JHH, Dec 28 '22 at 20:18

score 1 · Answer 1 · answered Dec 28 '22 at 09:23

You can use WITH RECURSIVE to generate days between two dates:

    WITH RECURSIVE days AS (
        SELECT '2004-01-08' AS login_date
        UNION ALL
        SELECT DATE_ADD(login_date, INTERVAL 1 DAY) AS value
        FROM days
        WHERE days.login_date < '2004-01-16'
    )
    SELECT *
    FROM days;

Then join it with your table using inner join.

This is the query :

   WITH RECURSIVE days AS (
        SELECT '2004-01-08' AS login_date 
        UNION ALL
        SELECT DATE_ADD(login_date , INTERVAL 1 DAY) AS login_date 
        FROM days
        WHERE days.login_date  < '2004-01-16'
    )
    SELECT d.login_date, count(1)
    FROM days d
    inner join member_name mn on d.login_date between mn.login_date and mn.logout_date
    group by d.login_date;

Thanks for your answer, but this version of mysql (5.5.62) doesn't support WITH RECURSIVE. Do you think there is another way to do it? — OPERACIONES, Dec 28 '22 at 11:39

SelVazi · Answer 2 · 2022-12-28T13:24:35.973

-1

I choose to Create an other query for anyone using mariadb.

We can use seq_8_to_16 to generate dates we wants,

This select seq from seq_8_to_16; will generate numbers from 8 to 16. For example, to generate the sequence of integers from 1 to 100, do this :

select seq from seq_1_to_100;

So our query will be as follows :

select d.login_date, count(1)
from member_name mn
inner join (
    SELECT concat('2004-01-', if(seq<10, concat('0',seq) ,seq)) as login_date
    FROM seq_8_to_16 d
) as d on d.login_date between mn.login_date and mn.logout_date
group by d.login_date;

edited Dec 28 '22 at 13:24

answered Dec 28 '22 at 12:50

SelVazi

10,028
2
13
29

1

There is no `seq_8_to_16` in MySql. – forpas Dec 28 '22 at 12:59
You are right Its only on mariadb, I made a mistake, I will edit my answer – SelVazi Dec 28 '22 at 13:22
1

@SelVazi Thanks for sharing MariaDB Sequence Storage Engine information. – JHH Dec 28 '22 at 19:52

How to obtain a group membership time evolution from login date / logout date table in mysql

2 Answers2