This is a follow up to this question. Turns out the driver that I am using (can’t change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my MySQL 8.0 database as a string type. There is no native function within the software I am using to read in the PLC and write into the db that can change a string to a timestamp.
What I’m trying to figure out now, is how to set up a trigger that converts the string to a timestamp on insert. This idea was suggested to me by the help hotline of the software, but if you have any better ideas, I’m open to that too. Thus far, I have 2 columns: one where I “receive” the string which is of type VARCHAR(30), and then another one where I write the timestamp value with the trigger.
This setup feels super stupid to me because I end up with 2 columns with the same data just in different formats, and one of them is completely useless.
Thus far, my table looks like:
CREATE TABLE `test` ( `id` mediumint NOT NULL AUTO_INCREMENT, `Date_VARCHAR` varchar(30) DEFAULT NULL, `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
And the trigger is:
DELIMITER $$ CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT` BEFORE INSERT ON `db_test`.`test` FOR EACH ROW BEGIN SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ; END$$ DELIMITER ;
Is there a better way of doing this to avoid having the same data twice? I would like to make a trigger that just converts the data and writes it all in one column instead of the setup I have come up with.
Here’s a quick look at what I have going on . I have done an insert of some random values, but it’s actually all being done with the datalogger plugin of KEPServerEx.