Hi!
>>>>> "J" == J Random Hacker <archiver@stripped> writes:
J> This message was sent from Geocrawler.com by "J. Random Hacker"
> <tyen_0@stripped>
J> Be sure to reply to that address.
J> I have a table with a timestamp(14) field that I
J> am trying to insert a value of '00000000000000' or
J> '0000-00-00
J> 00:00:00' into but instead what happens is the
J> field is updated to the current timestamp as if I
J> inserted NULL!
J> I'm running mysql-3.22.32 (and this hasn't worked
J> as far back as 3.22.25--don't know about versions
J> before
J> that).
I just fixed a similar bug for MySQL 3.23.12.
In MySQL 3.22.32, it appears to work for me:
mysql> create table t2 (t timestamp default '00000000000000');
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values (0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+----------------+
| t |
+----------------+
| 00000000000000 |
+----------------+
1 row in set (0.00 sec)
J> I think that part of the problem may be that the
J> default type for the timestamp(14) column in
J> question is NULL
J> (not what I want). However, when I explicitly try
J> to set a value as above, this shouldn't come into
J> play. Anyhow,
J> another problem I've had is trying to change this
J> column's default value from NULL to '0000-00-00
J> 00:00:00'.
J> I've used "ALTER TABLE table_name CHANGE
J> column_name column_name TIMESTAMP(14) DEFAULT
J> '0000-00-00 00:00:00'" and get a confirmation
J> message:
J> Query OK, 10 rows affected (0.44 sec)
J> Records: 10 Duplicates: 0 Warnings: 0
J> However, nothing changes! The default is still
J> NULL.
The DEFAULT value for a timestamp is always NULL (= the current time)
If you don't like this, you should use DATE or DATETIME instead.
Regards,
Monty
