trigger – Stored procedure to change string to datetime in MySQL

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.