I get temperature data from sensors that report midnight as 00:00:00. I also get data from the State of California CIMIS system and they report midnight as 24:00:00. If I want to merge my data with the state data it doesn’t work. Plus datetime in MySQL isn’t happy about time being 24:00:00.
So to get around that I put the data into my tables with the time portion of the datetime field as 23:59:59. I use BBEdit to edit the raw CSV data so that it can be used in a MySql INSERT statement and it’s just one more find and replace to make it work.
For most purposes that is fine, but there are some calculations that I do on an hourly basis and the last reading of the day is yesterday in the CIMIS data compared to my sensors so I get some slightly different answers. Also, if I am missing data for my sensors, it is difficult to add the missing data.
The solution is obvious, just add a second to the CIMIS data. I couldn’t figure out a way to do that in BBEdit or in LibreOffice.
But after the CIMIS data is in the table you can convert dates with 23:59:59 by adding one second to them with a simple MySQL command.
UPDATE `values_Temperature_1` SET `datetime_temp` = ADDTIME(`datetime`, '0:0:01') WHERE SECOND(`datetime`) = '59'