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 &quot;receive&quot; 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.
Based on the fiddle you may apply
ALTER TABLE test
DROP COLUMN Date_TIMESTAMP,
ADD COLUMN Date_TIMESTAMP TIMESTAMP AS (STR_TO_DATE(Date_VARCHAR, '%Y-%m-%dT%h-%i-%s'));

https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=104e72ae4730b2c8c30f84f61bf392d7
The column Date_TIMESTAMP may be defined as STORED (occupies disk space, fast access) or VIRTUAL (calculated each time its value is retrieved, does not occupy disk space).

Alternatively you may use BEFORE INSERT and BEFORE UPDATE triggers which will convert inserted data into correct datetime literals (but the column will stay VARCHAR nevertheless). In BEFORE UPDATE you may check current data format to avoid double convertion which will cause an error.
CREATE TRIGGER trigger_name  
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.Date_VARCHAR = STR_TO_DATE(NEW.Date_VARCHAR, '%Y-%m-%dT%h-%i-%s');

https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=08eb56a0b8846297c81c07e2703c0ab9