Filter dates when user entered our system

Question

I am trying to filter the dates when users connect to my system.

Have a however, first need to filter so there are no duplicate users.

I’m already doing it using the argument DISTINCT in my query, with the result that my query is returning, I want to search in the column DATE, to know every day that users have connected to my site.

I’m using the following command:

SELECT
    *
FROM
    wps_history
WHERE 
    inserted
BETWEEN "2016-11-01 00:00:00" AND "2016-11-30 23:59:59"

This command brings me all the results, but this is giving more than 40,000 lines, performing the filter DISTINCT the result and filtered to 500 lines, I am using the following command:

SELECT DISTINCT
    user_id
FROM
    wps_history
WHERE
    inserted
BETWEEN
    "2016-11-01 00:00:00" AND "2016-11-30 23:59:59"

Now I’m wondering how I can use the above command and bring up the dates on which each USER_ID connected to my system.

Answer 1

Do not use the DISTINCT for this, use GROUP BYby user code:

If you want the last date:

SELECT user_id,
       MAX(inserted)
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id

If you want the first date:

SELECT user_id,
       MIN(inserted)
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id

If you want to group everything in one column:

SELECT user_id,
       GROUP_CONCAT(DATE_FORMAT(inserted, '%d-%m-%Y') separator ', ') as datas
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id