Question 21562 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search