Maybe, it has to do with the TIMESTAMP behavior: whenever you insert a TIMESTAMP value MySQL converts it to UTC and converts it back to local time (or the time you entered).
To prevent this behavior you can - change TIMESTAMP into DATETIME or - "disable" it by adding this setting to the MY.INI configuration file: default-time-zone=+00:00 It both cases your existing values have been not stored as you expect so you have to fix them accordingly (things are rather difficult if you are dealing with data acquired in different TZ and/or with/without daylight saving mode. However if the data is stored and fetched always in the same time-zone (it was not my case, as we had to move databases between systems), you are safe as you are now. David -----Original Message----- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Alessandro Agostini Sent: giovedì 24 maggio 2007 14.28 To: mysql@lists.mysql.com Subject: problem with timestamp data type Hi to all, I have a strange error on using of timestamp data type. See the follow table definition and insert command. DROP TABLE IF EXISTS Storicocorrenti; CREATE TABLE Storicocorrenti ( ID_LINEA mediumint(6) unsigned zerofill NOT NULL default '000000', DATAORA timestamp NOT NULL default '0000-00-00 00:00:00', DFASEABS smallint(3) default NULL, TENSIONE decimal(9,2) default NULL, POTATT decimal(7,3) default NULL, POTREA decimal(7,3) default NULL, DFASEVI smallint(3) default NULL, CORRENTE decimal(6,2) default NULL, PRIMARY KEY (ID_LINEA,DATAORA) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 01:00:00', 0, '400000.00', '0.000', '0.000', 0, '0.00'); INSERT INTO Storicocorrenti VALUES (032100, '2005-03-27 02:00:00', 0, '400000.00', '0.000', '0.000', 0, '0.00'); Watching the table content, I see second record as: 032100, '2005-03-27 03:00:00', 0, '400000.00', '0.000', '0.000', 0, '0.00' MySql change the time information of DATAORA field from "02:00:00" to "03:00:00". This error is reproducible on various version of mysql. I have tried it on 3.23, 4.1, 5.0 On Postgres DB all is ok. I have similar record at same time but other date and in these case the problem is not present. Can you help me to resolve it? Thanks in advance Alessandro Agostini IFAC - CNR Italy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
