List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 12 2007 8:27am
Subject:Re: Trigger
View as plain text  
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

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