List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 26 2000 12:14am
Subject:RE: timestamp field gets updated by inserting '00000000000000' or
'0000-00-00 00:00:00'
View as plain text  
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
Thread
timestamp field gets updated by inserting '00000000000000' or '0000-00-00 00:00:00'jason.c.axley26 Feb
  • Re: timestamp field gets updated by inserting '00000000000000' or'0000-00-00 00:00:00'Paul DuBois26 Feb
RE: timestamp field gets updated by inserting '00000000000000' or'0000-00-00 00:00:00'Jason C Axley26 Feb
RE: timestamp field gets updated by inserting '00000000000000' or'0000-00-00 00:00:00'Paul DuBois26 Feb