MySQL: extract unique dates-CodePudding

I have created a table with

CREATE TABLE visits (
  user_id int,
  event_date timestamp
);

INSERT INTO visits (user_id, event_date)
VALUES
  (1, '2021-12-22 12:12:00'),
  (1, '2021-12-23 12:12:05'),
  (1, '2021-12-24 12:13:00'),
  (1, '2021-12-24 12:14:00'),
  (1, '2022-03-10 12:14:00'),
  (1, '2022-03-11 12:14:00'),
  (2, '2021-12-23 12:12:00'),
  (1, '2022-03-12 12:14:00'),
  (2, '2021-12-23 13:12:00'),
  (1, '2022-03-13 12:14:00'),
  (1, '2022-03-14 12:14:00'),
  (3, '2021-12-25 12:12:00'),
  (1, '2022-03-15 12:14:00'),
  (1, '2022-03-20 12:14:00'),
  (1, '2022-03-21 12:14:00'),
  (1, '2022-03-23 12:14:00'),
  (1, '2022-03-24 12:14:00'),
  (1, '2022-03-25 12:14:00'),
  (3, '2021-12-30 12:12:00'),
  (3, '2021-12-31 12:12:00'),
  (3, '2021-12-31 12:12:00'),
  (4, '2022-03-21 12:12:00'),
  (4, '2022-03-22 12:12:00'),
  (4, '2022-03-23 12:12:00'),
  (4, '2022-03-24 12:12:00');

And then I try to extract unique dates with

select 
  user_id,
  distinct cast(event_date as date) as event_date
from visits;

And I get

ERROR 1064 (42000) at line 111: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct cast(event_date as date) as event_date

from visits' at line 3

What did I do wrong?

CodePudding user response：

You should put the keyword distinct at the first position:

select  distinct cast(event_date as date) as event_date, user_id
from visits;

select  distinct user_id, cast(event_date as date) as event_date
from visits;

Here is a demo

CodePudding user response：

You can use GROUP BY. (The column COUNT is optional)

SELECT
count(*) "number",
user_id,
DATE(event_date) "event date"
FROM visits
GROUP BY
user_id,
DATE(event_date)
ORDER BY
user_id,
DATE(event_date);