CODEWITHSUNDEEP
mysqldatabasebash
nyitguy
nyitguy

Reputation: 608

MYSQL delete all records except first and last for a given date

This gives me all the records for a particular switch and port. I would like to delete all except the first and last one for each day.

/mysql -sN --user=root --password=notmypassword -e 
"SELECT * FROM collector.fibre 
WHERE fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  AND (time > '2016-05-31 00:00:00' AND time < '2016-05-31 23:59:59') "

Upvotes: 0

Views: 1898

Answers (4)

Charif DZ
Charif DZ

Reputation: 14721

I don't know how much time will it take to delete all this records but i think minutes of works just wait for it :

delete fb from collector.fibre fb 
INNER JOIN 
(
SELECT 
  distinct 
  f.id -- extract only the ids 
FROM 
  collector.fibre  f
  INNER JOIN (
    -- create a special table   day   min_time  max_time 
    select 
      date(time) as 'day',
      min(time) as 'min_time',
      max(time) as 'max_time'
    from 
      collector.fibre
     Where
        -- must be the same set of records 
        -- because we look for a dates of this switch
       fabric_switch_name = 'switch-1' 
       AND port = '0' 
       AND datatype = 'TxElements'
    group by date(time)
  )  fd on date(f.time) = fd.day  -- join the two table to have a special table see example down  
WHERE 
  fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  -- extract record witch the time is not
  -- the min or the max of that date 
  AND (f.time <> fd.min_time and f.time <> fd.max_time) 
) recs on fb.id = recs.id -- join the table and the result of query by the id now you will remove the records that exist in the result of the query

Upvotes: 2

DarbyM
DarbyM

Reputation: 1203

Give this a try:

DELETE 
FROM collector.fibre  
WHERE fabric_switch_name = 'switch-1'
AND port = '0' 
AND datatype = 'TxElements'
AND time BETWEEN '2016-05-31 00:00:00' AND '2016-05-31 23:59:59' 
AND time NOT IN (
    SELECT MIN(time), MAX(time)
    FROM collector.fibre  
    WHERE fabric_switch_name = 'switch-1'
    AND port = '0' 
    AND datatype = 'TxElements'
    AND time BETWEEN '2016-05-31 00:00:00' AND '2016-05-31 23:59:59' 
)

Upvotes: 0

Martin
Martin

Reputation: 22760

by "first and last" I will assume you mean earliest and latest, so:

  • Select criteria in the given date and then deselect the minimum date and maximum date values.

It is assumed your time is stored in a DATETIME column.

    DELETE FROM collector.fibre 
         WHERE fabric_switch_name = 'switch-1' 
         AND port = '0' 
         AND datatype = 'TxElements' 
         AND (TIME(time) != 
             (
             SELECT MAX(TIME(time))
             FROM `collector`.`fibre` WHERE fabric_switch_name = 'switch-1' 
             AND port = '0' 
             AND datatype = 'TxElements'
             AND DATE(time) = '2016-05-30'
             )
             AND
             TIME(time) != (
             SELECT MIN(TIME(time))
             FROM `collector`.`fibre` WHERE fabric_switch_name = 'switch-1' 
             AND port = '0' 
             AND datatype = 'TxElements'
             AND DATE(time) = '2016-05-30'
             )
         ) 
         AND DATE(time) = '2016-05-30'

This is probably poorly written and does unfortunately use criteria repetition for the subqueries but the concept works as:

SubSelect by criteria and Not rows where the time column is MAX or MIN time values fitting said criteria

Reference : https://stackoverflow.com/a/7836444/3536236

So you can then input each day and remove non-earliest and non-latest values. If you want to cycle through the whole table in one fell swoop I would figure you want to create a Procedure but I don't know enough to help you much here. However your title says MYSQL delete all records except first and last for a given date which implies you will manually run it one date at a time as needed.

Example:

Example Set:

   fabric_switch_name |   datatype  |  port  |        time
 -------------------------------------------------------------------
       switch-1       |  TxElements |    2   |  2016-05-31 15:00:00
       switch-1       |  TxElements |    1   |  2016-05-30 22:10:00
       switch-2       |  TxElements |    0   |  2016-05-30 15:00:00
       switch-1       |  TxElements |    0   |  2016-05-29 10:20:10
       switch-1       |  TxElements |    0   |  2016-05-29 05:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 19:50:00
       switch-1       |  TxElements |    5   |  2016-05-29 21:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 20:11:40

Result Set:

   fabric_switch_name |   datatype  |  port  |        time
 -------------------------------------------------------------------
       switch-1       |  TxElements |    2   |  2016-05-31 15:00:00
       switch-1       |  TxElements |    1   |  2016-05-30 22:10:00
       switch-1       |  TxElements |    0   |  2016-05-29 05:50:00
       switch-2       |  TxElements |    0   |  2016-05-30 15:00:00
       switch-1       |  TxElements |    5   |  2016-05-29 21:50:00
       switch-1       |  TxElements |    0   |  2016-05-29 20:11:40

Upvotes: 1

Charif DZ
Charif DZ

Reputation: 14721

this query will extract all the record that should be deleted :

SELECT 
  * 
FROM 
  collector.fibre  f
  INNER JOIN (
    -- create a special table   day   min_time  max_time 
    select 
      date(time) as 'day',
      min(time) as 'min_time',
      max(time) as 'max_time'
    from 
      collector.fibre
     Where fabric_switch_name = 'switch-1'
    group by date(time)
  )  fd on date(f.time) = fd.day  -- join the two table to have a special table see example down  
WHERE 
  fabric_switch_name = 'switch-1' 
  AND port = '0' 
  AND datatype = 'TxElements' 
  -- extract record witch the time is not
  -- the min or the max of that date 
  AND (f.time <> fd.min_time and f.time <> fd.max_time)

let suppose this is your date 

    1           2016-05-31 23:00:00 
    2           2016-05-31 01:00:00
    3           2016-05-31 00:00:00
    4           2016-05-30 10:00:12
    5           2016-05-30 01:00:00
    6           2016-05-30 00:05:00

the result of join :

    1           2016-05-31 23:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00  -- this will not be selected in the result
    2           2016-05-31 01:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00  -- this will be selected because it's not min or max of that day
    3           2016-05-31 00:00:00         2016-05-31   2016-05-31 00:00:00  2016-05-31 23:00:00 
    4           2016-05-30 10:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12
    5           2016-05-30 01:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12
    6           2016-05-30 00:00:00         2016-05-30   2016-05-30 00:05:00  2016-05-30 10:00:12

Upvotes: 0

Related Questions