SQL Filter rows on date difference

You can try to use group by with DATE(date_buy) and client instead of DISTINCT. Then get MIN(date_buy) be your date_buy

Schema (MySQL v5.7)

CREATE TABLE tickets(
  client varchar(50),
  date_buy datetime
);


insert into tickets values ('0027847524333', '2018-06-13 16:03:43');
insert into tickets values ('0027847524333', '2018-06-13 16:13:01');
insert into tickets values ('0027847524333', '2018-06-18 22:03:01');
insert into tickets values ('0033652356025', '2018-06-16 17:22:56');
insert into tickets values ('00353857861869','2018-08-13 17:37:56');

Query #1

SELECT client, MIN(date_buy) date_buy
FROM tickets
GROUP BY DATE(date_buy),client
ORDER BY client, MIN(date_buy);

| client         | date_buy            |
| -------------- | ------------------- |
| 0027847524333  | 2018-06-13 16:03:43 |
| 0027847524333  | 2018-06-18 22:03:01 |
| 0033652356025  | 2018-06-16 17:22:56 |
| 00353857861869 | 2018-08-13 17:37:56 |

View on DB Fiddle

you can do this :

    SELECT * 
FROM tickets t_1, tickets t_2
where t_1.client = t_2.client
and DATEDIFF(hour, t_1.date_buy, t_2.date_buy) < 24

It seems that the date_buy column is of smalldatetime type (if not, you can easily parse it to be).

Here's one way you could implement the query. Start by creating a table of all later clients where it has been less than or equal to 24 hours since a previous order:

CREATE TABLE helper AS
SELECT DISTINCT b.client AS cl
FROM tickets AS a, tickets AS b
WHERE DATEDIFF(hour, a.date_buy, b.date_buy) <= 24 AND a.client = b.client

Then, select all other clients from tickets:

SELECT DISTINCT client, date_buy
FROM tickets, helper
WHERE client <> cl
ORDER BY client, date_buy

You can obtain the same result by deleting entries from tickets, though this would mutate the original table. This solution takes into account hour differences across the day change.

Here is a solution using LAG

SELECT client, date_buy
FROM (SELECT client, date_buy, 
        DATE_ADD(IFNULL(LAG(date_buy, 1) OVER(PARTITION BY client ORDER BY client, date_buy ASC), '1900-01-01 00:00:00'), INTERVAL 24 HOUR) prev_date
      FROM tickets) sub
WHERE date_buy > prev_date