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

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

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@upscene.com> 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=pbruna@it-linux.cl

>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.

When you send an INSERT statement to the server, what value are you passing
for
the date column?

If you want to store a DATE, send a date! Don't send a "unixtime", or a
"varchar" in
some format, send a date.

Why on earth would you go sending a "unixtime" to the server and then using
a trigger
to convert it into a "real date"?!

>im correct? if im, should CAST help me?

No, sending the proper value, that's what you should be doing.

And storing a date-value in a DATE datatype will only be more convenient
later on.

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

> > 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 */;;

The aplication which is writing to the database is sending the date in unixformat.
I can't no change that, so i suposse using a triger will help.

The application pass the 'xxxxxx.xx' value when is inserting to the table.

----- "Martijn Tonies" <m.tonies@upscene.com> escribió:
> >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.
>
> When you send an INSERT statement to the server, what value are you
> passing
> for
> the date column?
>
> If you want to store a DATE, send a date! Don't send a "unixtime", or
> a
> "varchar" in
> some format, send a date.
>
> Why on earth would you go sending a "unixtime" to the server and then
> using
> a trigger
> to convert it into a "real date"?!
>
> >im correct? if im, should CAST help me?
>
> No, sending the proper value, that's what you should be doing.
>
> And storing a date-value in a DATE datatype will only be more
> convenient
> later on.
>
> 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
>
> > > 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 */;;
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=pbruna@it-linux.cl