List:General Discussion« Previous MessageNext Message »
From:Patricio A. Bruna Date:October 12 2007 1:38pm
Subject:Re: Trigger
View as plain text  
Martijn,
If i define that the value for the field must be varchar data type, `fecha` varchar(25)
default NULL, the triger records the right date.
I think because is wrinting a string only. But if i define the field as a date data type,
`fecha` date default NULL, the value writen is: 1969-31-12. I suppose this is because the
table is waiting for a date data type, but the triger sends a string data type.

im correct? if im, should CAST help me?

Thanks

----- "Martijn Tonies" <m.tonies@stripped> escribió:
> 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.
> 
> Instead of "column", I guess you mean "value"?
> 
> > Any ideas?
> >
> >
> >
> > DROP TABLE IF EXISTS `visitas`;
> > CREATE TABLE `visitas` (
> > `id` int(11) NOT NULL auto_increment,
> > `date` varchar(25) default NULL,
> 
> If you want to store a date-value, use a date-datatype, not a
> character
> based datatype.
> 
> > `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 */;;
> 
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/mysql?unsub=1

Thread
TriggerPatricio A. Bruna11 Oct
  • Re: TriggerMartijn Tonies12 Oct
    • Re: TriggerPatricio A. Bruna12 Oct
      • Re: TriggerMartijn Tonies12 Oct
        • Re: TriggerPatricio A. Bruna12 Oct
          • Re: TriggerMartijn Tonies12 Oct
            • Re: TriggerPatricio A. Bruna12 Oct
              • Re: TriggerMartijn Tonies12 Oct
Re: TriggerPatricio A. Bruna11 Oct