CODEWITHSUNDEEP
mysql
freddy888
freddy888

Reputation: 1010

MYSQL replace part of table

I have the following data that grows too large too quickly:

c1    c2       timestamp
5      1       2019-01-31 15:35:36.055000
6      2       2019-01-31 15:35:36.055000
7      3       2019-01-31 15:35:35.055000
8      4       2019-01-31 15:35:35.055000
7      3       2019-01-30 15:35:36.055000
8      4       2019-01-30 15:35:36.055000
7      3       2019-01-30 15:35:35.055000
8      4       2019-01-30 15:35:35.055000

I want to replace part of the table by ceiling the timestamps to 10 second intervals where timestamp is older than 2019-01-31 00:00:00:000000 The result should be this and the bottom two columns from the aforementioned table should NOT be in the new table:

c1    c2       timestamp
5      1       2019-01-31 15:35:36.055000
6      2       2019-01-31 15:35:36.055000
7      3       2019-01-31 15:35:35.055000
8      4       2019-01-31 15:35:35.055000
7      3       2019-01-30 15:35:40.000000
8      4       2019-01-30 15:35:40.000000

I did the following query to ceil the timestamp, but I dont know how to replace that certain part of the table.

SELECT c1, c2, FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as datetime 
FROM mytable
WHERE timestamp in (SELECT MAX(timestamp) FROM mytable
GROUP BY FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10)) 
GROUP BY datetime

Upvotes: 0

Views: 58

Answers (2)

Santi
Santi

Reputation: 1812

A query like this will ceil the timestamp value for all rows

UPDATE mytable SET timestamp=FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10);

Add a WHERE clause if you want to apply it to only some rows

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

(Note: If c1 or c2 can be null, this solution will probably not work for you without significant modification.)

If you just want to keep the most recent from each 10 second window, something like this should work:

DELETE 
FROM mytable
WHERE timestamp < '2019-01-31 00:00:00:000000'
    AND (c1, c2, timestamp) 
        NOT IN (
           SELECT ends.c1, ends.c2, ends.windowEnd
           FROM (  SELECT c1, c2
                      , FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as dtWindow
                      , MAX(timestamp) AS windowEnd
                   FROM mytable
                   WHERE timestamp < '2019-01-31 00:00:00:000000'
                   GROUP BY c1, c2, dtWindow
                ) AS ends
        )
;

Note: sometimes MySQL is a little bit finicky about selecting from the table you are deleting from in the same query, but I think there are enough levels of indirection in that subquery that it should not be an issue.

Bonus: If you want to double check the records that will be deleted before running such a query, you can move the subselect into a joining select to preview things.

SELECT t.*
, CASE WHEN ends.rowPresent IS NOT NULL THEN 'keep' ELSE 'to delete' END AS `plan`
FROM mytable AS t 
LEFT JOIN (
   SELECT c1, c2
      , FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as dtWindow
      , MAX(timestamp) AS windowEnd
      , 1 AS rowPresent -- You could just use c1 or c2 in the above CASE, but if they could naturally be null that would complicate things
   FROM mytable
   WHERE timestamp < '2019-01-31 00:00:00:000000'
   GROUP BY c1, c2, dtWindow
) AS ends ON t.c1 = ends.c1 AND t.c2 = ends.c2 AND t.timestamp = ends.windowEnd
WHERE t.timestamp < '2019-01-31 00:00:00:000000'
;

Upvotes: 1

Related Questions