List:General Discussion« Previous MessageNext Message »
From:Patricio A. Bruna Date:October 11 2007 7:00pm
Subject:Re: Trigger
View as plain text  
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@stripped> 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@stripped> 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@stripped
> 
>  - All models are wrong.  Some models are useful.

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