Marco, > mysql> update T_ORDH set STATUS=2 where PK_ID=26272; > ERSTELL_DATUM is set to the current date. I know that a timestamp > takes the current time, if set it to NULL, but since I'm not touching > it, it shouldn't change, should it? > > A quick workaround is > mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM > -> where PK_ID=26272; > > The big question: Is it a bug or a feature?
The small answer is: It's a feature, and it's documented as well: Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the TIMESTAMP column to NULL. http://www.mysql.com/doc/en/DATETIME.html BTW: In tables like yours I always have _two_ timestamp columns, like: letzteAenderung TIMESTAMP erstellDatum TIMESTAMP On INSERT, I set erstellDatum to now(). letzteAenderung will automatically be set to the same value. On UPDATE, erstellDatum will be left untouched (because it's not the first TIMESTAMP column), and letzteAenderung will be set to NOW() without me having to think of it. A very convenient feature, indeed :) Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Marco Deppe" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 31, 2003 12:17 PM Subject: TIMESTAMP field is updated unintentionally > Hi, > > I was already questioning my sanity, but the problem below is > reproduceable: > > This is how my table looks: > mysql> describe T_ORDH; > --------------+----------------------+-----+----+--------+-------- > Field |Type |Null |Key |Default |Extra > --------------+----------------------+-----+----+--------+-------- > PK_ID |int(10) unsigned | |PRI |NULL |auto_inc > ERSTELL_DATUM |timestamp(14) |YES | |NULL | > STATUS |smallint(5) unsigned | | |0 | > > If I do > mysql> update T_ORDH set STATUS=2 where PK_ID=26272; > ERSTELL_DATUM is set to the current date. I know that a timestamp > takes the current time, if set it to NULL, but since I'm not touching > it, it shouldn't change, should it? > > A quick workaround is > mysql> update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM > -> where PK_ID=26272; > > The big question: Is it a bug or a feature? > (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) > > > > -- > Best regards, > Marco > > mailto:[EMAIL PROTECTED] > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
