Move the entire range of keys

Question

I am working on time series data for which the key column is a timestamp: Time. There are also many “value” columns for each row.

I am going to transfer a number of my data for several hours (due to daylight saving time problems). To do this, I update the key from several lines, and this can lead to duplication of keys. I would like duplicate keys at the edge of the date range to be ignored. I want the shifted range to overlap the old.

I plan to do something like:

UPDATE IGNORE time_series_table SET time=time-<some_shift> WHERE <time in a date-range> 

Here is the result of describe <table> for the time key:

 Field Type Null Key Default Extra TimeMeas datetime NO PRI NULL 

My question is: will he shift all the keys at once or try to shift each row one by one, which will lead to massive duplicate keys in the most shifted range?

Do you have a better way to do this in your mind? thanks in advance

Answer 1

Does it move all the keys at the same time or will it try to shift each row one at a time.

He will immediately shift all the keys.

leads to massive duplicate keys in the most offset range?

This is simply a failure if any primary key is duplicated.
With an update ignore it just scans.

This is my approach to fixing this.

 /* create a temporary table to store matches records*/ create table tmp_table select time-<some_shift>, etc_cols.... from time_series_table where <time in a date-range>; 

then

 /* delete the matches in the original table */ delete from time_series_table where <time in a date-range>; delete from time_series_table where <time in a date-range - some_shift>; 

finally,

 /* at this point, there won't be any duplicate data */ /* so, insert back into original table */ insert into time_series_table select * from tmp_table; optmize table time_series_table;