MySQL delete all rows added every minute but keep only 1

Question

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

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. — Keith John Hutchison, Jan 28 '19 at 08:37
Or, deleting the existing records of a currency before you insert — Peter Chaula, Jan 28 '19 at 08:41

Salman A · Accepted Answer · 2019-01-28T09:21:31.300

2

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.

edited Jan 28 '19 at 09:21

answered Jan 28 '19 at 08:57

Salman A

262,204
82
430
521

thanks man working ! Also if i want to use delete every 5 minute ? how can i use also can you add this ? – SwiftDeveloper Jan 28 '19 at 09:01
So you want to keep 1st row for each 0-5, 5-10, 10-15 minute sets? – Salman A Jan 28 '19 at 09:02
Does your datetime contain milliseconds? – Salman A Jan 28 '19 at 09:04
like this 2019-01-28 11:19:26 standard datetime – SwiftDeveloper Jan 28 '19 at 09:05
Haven't tested but `GROUP BY currency, UNIX_TIMESTAMP(date_time) - UNIX_TIMESTAMP(date_time) % (5 * 60)` might work/ – Salman A Jan 28 '19 at 09:06
ty i will test dude – SwiftDeveloper Jan 28 '19 at 09:19

Fahmi · Answer 2 · 2019-01-28T08:58:02.083

0

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

edited Jan 28 '19 at 08:58

answered Jan 28 '19 at 08:28

Fahmi

37,315
5
22
31

gives that #1064 - 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 '(SELECT @row_number:=CASE WHEN @ – SwiftDeveloper Jan 28 '19 at 08:31
trying again dude – SwiftDeveloper Jan 28 '19 at 08:51
#1064 - 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 'b join – SwiftDeveloper Jan 28 '19 at 08:52
#1064 - 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 '@currency:= currency as currency,date_time – SwiftDeveloper Jan 28 '19 at 08:55

ScaisEdge · Answer 3 · 2019-01-28T08:37:09.390

0

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
)

edited Jan 28 '19 at 08:37

answered Jan 28 '19 at 08:33

ScaisEdge

131,976
10
91
107

returns ; #1093 - You can't specify target table 'my_table' for update in FROM clause – SwiftDeveloper Jan 28 '19 at 08:35
answer updated .. using a subquery for enforce a temp table – ScaisEdge Jan 28 '19 at 08:37
i tested don't delete in all of currencies. – SwiftDeveloper Jan 28 '19 at 08:45
9351 2019-01-28 11:46:36 6.0173 GBPUSD 9353 2019-01-28 11:46:38 6.0173 GBPUSD – SwiftDeveloper Jan 28 '19 at 08:49
your query works and delete many rows but some currencies have multiple row in each minute i added before in db live output – SwiftDeveloper Jan 28 '19 at 08:50
explain better your last comment .. i don't understand .. the query should delete all the row -1 for each minute .. the min_id should be unique – ScaisEdge Jan 28 '19 at 08:51
9351 2019-01-28 11:46:36 6.0173 GBPUSD 9353 2019-01-28 11:46:38 6.0173 GBPUSD this 2 rows after i run your query. and must be delete one . but still there – SwiftDeveloper Jan 28 '19 at 08:57
@SwiftDeveloper .. for test try execute the inner subquery an check the number of rows retuned for for the 2019-01-28 11:46:36 GBPUSD ..and let me know ..also be sure your query result is correctly refreshed .. – ScaisEdge Jan 28 '19 at 09:00
`min(id)` might not match the first timestamp for that group! – Salman A Jan 28 '19 at 09:01

MySQL delete all rows added every minute but keep only 1

3 Answers3