MySQL delete all rows added every minute but keep only 1

I have MySQL table named datas and column like this:

id int(11)
currency varchar(16)
date_time (datetime)

Example data:

id         currency      date_time
---------  ------------  ---------------------
12         EURUSD        2019-01-28 11:19:26
13         EURUSD        2019-01-28 11:19:29
14         EURUSD        2019-01-28 11:19:34
15         EURUSD        2019-01-28 11:19:38
16         EURUSD        2019-01-28 11:19:49
17         GBPUSD        2019-01-28 11:19:23
18         GBPUSD        2019-01-28 11:19:27
19         GBPUSD        2019-01-28 11:19:39
20         GBPUSD        2019-01-28 11:19:45
21         GBPUSD        2019-01-28 11:19:57

Must be like this (delete another in one minute only one row in each currency):

12         EURUSD        2019-01-28 11:19:26
17         GBPUSD        2019-01-28 11:19:23

And my program inserting rows about every 3-6 seconds. but I want to keep only 1 currency price in 1 minute, and I want to delete others.

How can I do it with a MySQL query? I'm using PHP.

My cron jobs will works every 15 minutes. with php will work this query.

MySQL version = 5.7.25

You can use this:

DELETE FROM t
WHERE (currency, date_time) NOT IN (
    SELECT * FROM (
        SELECT currency, MIN(date_time)
        FROM t
        GROUP BY currency, UNIX_TIMESTAMP(date_time) - UNIX_TIMESTAMP(date_time) % (1 * 60)
    ) AS x
)

The expression UNIX_TIMESTAMP(datetime) - UNIX_TIMESTAMP(datetime) % (n * 60) will floor the datetime value to n * 60 second boundary as described here. You can group by this expression to find currency, MIN(datetime) pairs within each group.

To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get better performance. For a table that has a foreign key constraint, when you delete rows from the parent table, the rows in the child table will be deleted automatically by using the ON DELETE CASCADE option.

You could use a NOT IN the min id ( or max if you prefer ) group by minute and currency

delete from my_table  
where id NOT IN  (
    select  my_id 
    from (
    select  min(id) my_id
    from  my_table 
    GROUP BY currency
        , YEAR(date_time)
        , month(date_time)
        , day(date_time)
        , HOUR(date_time)
        , MINUTE(date_time)
    ) T
) 

To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date. Let us first create a table − mysql> create table DemoTable -> ( -> UserMessage text, -> UserMessageSentDate date -> ); Query OK, 0 rows affected (0.59 sec)

You can try below -

delete  b from datas b join
(
SELECT 
    @row_number:=CASE
        WHEN @currency = currency THEN @row_number + 1
        ELSE 1
    END AS num,id
    @currency:=currency as currency,date_time
FROM
    datas,(SELECT @currency:=0,@row_number:=0) as t
ORDER BY currency
)A on a.id=b.id where num<>1

The DELETE statement deletes rows from table_name and returns the number of deleted rows. You can use ROW_COUNT () function to check the number of deleted rows. The conditions in the WHERE clause (optional) identify which rows to delete. Without WHERE clause, all rows are deleted.

I am using MySQL to store data for an online high score chart for a computer game. I only want to keep the top 100 scores --- everything else can be deleted. It would be a huge waste of storage space to keep every score ever submitted when I only need the top 100, so I'm trying to figure out how to delete all but the top 100 scores.

Hi, Suppose I have a table called temp_data. I want to delete row 10 minutes after it's creation. I know this can be done in php via corn(job/tab). But I would like it to be done in mysql itself

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2. The preceding examples use INNER JOIN, but multiple-table DELETE statements can use other types of join permitted in SELECT statements, such as LEFT JOIN. For example, to delete rows that exist in t1 that have no match

Comments
  • what is your mysql version
  • @fa06 mysql version 5.7.25
  • datas sounds like a bad table name
  • What have you done so far?
  • Have you considered doing an insert for each currency using a left join on currency and yyyy-mm-dd HH:mm where there is no match? Then you'd only ever have one record per currency per minute.
  • thanks man working ! Also if i want to use delete every 5 minute ? how can i use also can you add this ?
  • So you want to keep 1st row for each 0-5, 5-10, 10-15 minute sets?
  • Does your datetime contain milliseconds?
  • like this 2019-01-28 11:19:26 standard datetime
  • Haven't tested but GROUP BY currency, UNIX_TIMESTAMP(date_time) - UNIX_TIMESTAMP(date_time) % (5 * 60) might work/
  • returns ; #1093 - You can't specify target table 'my_table' for update in FROM clause