At 4:03 PM -0800 2000-02-25, Axley, Jason C wrote:
>According to the documentation:
>
>"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."
>
>I'm not doing any of these 3 things so the field should NOT be updating!
>The docs also say:
>
>"Illegal DATETIME, DATE or TIMESTAMP values are converted to the ``zero''
>value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00' or
>00000000000000)."
>
>So this tells me that the 'zero' value is not equivalent to the NULL value.
>
>I do want a timestamp field becaue I want it to auto-update under most
>cirucumstances. Just in this case, I want to set other fields in the same
>table without affecting the timestamp(14) field (the app is adding a new
>user profile record to the database so the user has never logged in and I'd
>like this to be reflected).
>
>-Jason
Oops, sorry, you're right. I guess I was thinking of the behavior
of AUTO_INCREMENT, where inserting NULL and 0 has the same effect.
>
>-----Original Message-----
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Friday, February 25, 2000 2:18 PM
>To: jason.c.axley@stripped; mysql@stripped
>Subject: Re: timestamp field gets updated by inserting '00000000000000'
>or '0000-00-00 00:00:00'
>
>
>At 2:03 PM -0800 2000-02-25, jason.c.axley@stripped wrote:
>> >Description:
>> In my database, I have a timestamp(14) field that I configured
>>to have a default value of NULL (I can't change this with alter
>>table--look for another bug report for that).
>
>That's not a bug. That's how TIMESTAMP columns work.
>
>> Anyhow, I am doing an INSERT from php3 using
>>the mysql_db_query() function and have tried inserting both
>>'00000000000000' and
>>'0000-00-00 00:00:00' into the timestamp(14) field, but instead of the
>value
>>being set to the zero value, the field is updated with the current
>timestamp!
>
>That's not a bug, either. That's how TIMESTAMP columns work.
>
>Perhaps you should consider using a DATETIME field instead.
>It appears from your description that you don't want TIMESTAMP
>behavior at all.
>
>
>Might be a good idea to check chapter 7 of the MySQL Reference
>Manual for a refresher on the behavior of TIMESTAMP...
>
>--
>Paul DuBois, paul@stripped
--
Paul DuBois, paul@stripped
