MySQL Trigger - Update data before inserting

Question

I am trying to create a trigger that changes a date on input to a unix timestamp. When i insert a value to the table the inserted value is NULL

CREATE TRIGGER `updateDate` BEFORE INSERT ON  `tl_calendar_events` 
FOR EACH
ROW SET NEW.startDate = UNIX_TIMESTAMP(STR_TO_DATE(NEW.startDate, '%d.%m.%Y'))

The input value for 'startDate' is like 01.11.2013

Table definition:

CREATE TABLE IF NOT EXISTS `tl_calendar_events` (
...
`startDate` int(10) unsigned DEFAULT NULL,
...
PRIMARY KEY (`id`),
KEY `pid` (`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;

please post your table creation query

Fathah Rehman P
– Fathah Rehman P

2013-04-03 14:59:51 +00:00
Commented Apr 3, 2013 at 14:59 — Fathah Rehman P
– Fathah Rehman P, Commented Apr 3, 2013 at 14:59

Fathah Rehman P · Accepted Answer · 2013-04-03 15:12:48Z

1

Change the data type of startDate column to varchar, then your above query will work. Use

`startDate` VARCHAR(10) NULL DEFAULT NULL in your table creation query

OR

you can change data type to DATE or DATETIME and do the conversion to unix time in your program in that case there is no need for that trigger

answered Apr 3, 2013 at 15:12

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Álvaro González Over a year ago

@benfolds - If you end up storing dates as VARCHAR, you deserve having to deal with data like 2013-02-31 or do not know.

Devart Over a year ago

Agree, it is better to store date as is (DATE, DATETIME or INT for UNIX timestamp).

Devart · Accepted Answer · 2013-04-03 15:06:25Z

1

You cannot insert strings into INT column. It looks like STRICT server mode is off and default values are inserted (...startTime int(10) unsigned DEFAULT NULL...).

The only way is to change these values when they are being inserted, e.g. -

INSERT INTO tl_calendar_events(startDate)
  VALUES (UNIX_TIMESTAMP(STR_TO_DATE('01.11.2013', '%d.%m.%Y')));

edited Apr 3, 2013 at 15:06

answered Apr 3, 2013 at 15:00

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Álvaro González Over a year ago

If I understood it correctly, NEW.startDate is not allowed to be a string in the first place because the column is numeric, not even in a BEFORE INSERT trigger. Before the triggers runs, MySQL creates NEW.startDate as INT variable, thus the Data truncated error you get in strict mode.

Collectives™ on Stack Overflow

MySQL Trigger - Update data before inserting

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related