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

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'

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

CodePudding user response：

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;

CodePudding user response：

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;