0

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.

8
  • You do not need in trigger - generated column may do the same. From the other side if you provide correct string-type datetime value for DATETIME or TIMESTAMP column then MySQL automatically casts the value to column's datatype. – Akina May 11 at 8:56
  • @Akina I'm not familiar with generated columns, can you explain how I would go about doing that please? – E.Aigle May 11 at 9:08
  • What version of MySQL are you running? – Vérace May 11 at 9:40
  • @Vérace MySQL 8.0 – E.Aigle May 11 at 9:41
  • Please provide a few lines of sample input and your table design - use dbfiddle.uk! MySQL 8.0 is perfect! SHOW CREATE TABLE test\G! – Vérace May 11 at 9:41
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

1
  • Awesome, super helpful, thank you. I used the trigger solution with a little modification: SET NEW.Date_VARCHAR = CONVERT(NEW.Date_VARCHAR, DATETIME); because I really needed the column to be a time type and it works perfectly now. – E.Aigle May 11 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.