Stored procedure to change string to datetime in MySQL

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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&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&fiddle=08eb56a0b8846297c81c07e2703c0ab9

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply