How can i change a field's value while using select insert in mysql

Question

The sql statement is

INSERT INTO t_source_type_transfer(logdate,time, Host, CountryCode, RegionCode,City, SourceIP, Hits, cdn_id, CityName ,type) 
   select b.logdate,b.time,b.Host,b.CountryCode, b.RegionCode,b.City, b.SourceIP, b.Hits, b.cdn_id, b.CityName ,b.type  
   from t_source_type as b 
ON DUPLICATE KEY UPDATE Hits=t_source_type.Hits+b.Hits;

The time field in t_source_type is 14:35:21

How can I change it to 14:35:00 while using that statement?

Thanks!

So, you're asking for a way to truncate the seconds off `b.time` before inserting it into `t_source_type_transfer.time`? — underscore_d, Oct 26 '17 at 09:33
What's the rule that governs the change? Do you want to subtract 21 seconds, or round the time down to the nearest minute, 5 minutes, etc.? What do you want? — Strawberry, Oct 26 '17 at 09:33
yeah, like @underscore_d said, i want a way to truncate the seconds off b.time before inserting it into t_source_type_transfer.time — Alan Fu, Oct 26 '17 at 09:53
Great, please mark this as a duplicate of that one, if it answered your question. — underscore_d, Oct 26 '17 at 10:11

score 0 · Answer 1 · answered Oct 26 '17 at 10:51

I think using maketime() is a clearer way to round down to the nearest minute:

INSERT INTO t_source_type_transfer(logdate, time, Host, CountryCode, RegionCode, City, SourceIP, Hits, cdn_id, CityName, type) 
   select b.logdate,
          maketime(hour(b.time), minute(b.time), 0),
          b.Host, b.CountryCode, b.RegionCode, b.City, b.SourceIP, b.Hits, b.cdn_id, b.CityName ,b.type  
   from t_source_type b 
ON DUPLICATE KEY UPDATE Hits=t_source_type.Hits+b.Hits;

Small variations on this also work for many multiples of minutes -- such as 5 minutes or 30 minutes.

How can i change a field's value while using select insert in mysql

1 Answers1