Re: Trigger
This is a discussion on Re: Trigger within the MySQL General forum forums, part of the MySQL category; --> Michael, im converting the unixtime to "normal" time with from_unixtime. So after i did the convertion i write the ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 07:01 AM
| ||||
| ||||
 Re: Trigger Michael, im converting the unixtime to "normal" time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column ----- "Michael Dykman" <mdykman@gmail.com> escribió: > That is a common symptom of some conversion error... A Unix > timestamp > of '0' in fact always resolved to that specific date. In some > earlier, buggier versions of MySQL, negative values would be > inteterpreted as "seconds-before-the-unix-epoch"... now they all are > treated as invalid, and hence, guive up the date you see. > > the thing here is, if your column type is Date and you try to pass it > a unix timestamp as in > > UPDATE visitas SET `date` = `1192109927' > > you will get an unpredictable result, because that string is not a > date. You are trying to transport 2 distinct type (Date and > timestamp) in the same typed variable, and that suimply is not going > to work. IF your app is supplying the time as a timestamp, have it > update a seperate int field, then use the trigger to grab that value > and translate it for your Date field. > > Caveat: timestamps have a short lifespan.. they can not represent > dates reliable before Jan 1, 1970.. if you only need it to stamp > 'current' events, then go ahead, but if you are trying to track > anything historic (like birth dates), they fall apart as soon as a > 37-year-old signs up. > > BTW: you might want to adjust your schema so that you don't use any > MySQL keywords as column names (or table names or any other user > object)... the example that pops out at me is your field 'Date', > which, not surprisingly, is a keyword. this can only lead to greif > sooner or later. > > - michael dykman > > > On 10/11/07, Patricio A. Bruna <pbruna@it-linux.cl> wrote: > > Hi, > > I have a problem with a trigger which should conver a unix timestamp > to a MySQL date datatype. > > The trigger works if the column is varchar, but when the column is > date type, it write the date of 1969-31-12. > > Any ideas? > > > > > > > > DROP TABLE IF EXISTS `visitas`; > > CREATE TABLE `visitas` ( > > `id` int(11) NOT NULL auto_increment, > > `date` varchar(25) default NULL, > > `elapsed` int default NULL, > > `src_ip` varchar(15) default NULL, > > `result_code` varchar(25) default NULL, > > `http_status` TINYINT default NULL, > > `bytes` int default NULL, > > `request` varchar(50) default NULL, > > `authname` varchar(10) default NULL, > > `type` varchar(20) default NULL, > > PRIMARY KEY (`id`) > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > > > /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; > > DELIMITER ;; > > /*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES" */;; > > /*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 > TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW > begin > > set New.date=date(from_unixtime(New.date)); > > end */;; > > > > When > > > > > > > -- > - michael dykman > - mdykman@gmail.com > > - All models are wrong. Some models are useful.      |
Linear Mode
