-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

halfer
  • 19,824
  • 17
  • 99
  • 186
SwiftDeveloper
  • 7,244
  • 14
  • 56
  • 85

3 Answers3

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
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
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
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
) 
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107