Strange DATE_ADD behavior - what's up?

[Saeven]

Hello,

Consider this table:

CREATE TABLE table_1 (
  ID int(8) unsigned NOT NULL auto_increment,
  date timestamp(8) NOT NULL,
  anniversary timestamp(8) NOT NULL,
  PRIMARY KEY  (BID)
) TYPE=MyISAM;

With this entry:

198 | 20030328 | 20030105

Where I run this query:

UPDATE table_1 SET anniversary = DATE_ADD( anniversary, INTERVAL 12 MONTH ) WHERE BID = 198;

I'd expect this result:

198 | 20030328 | 20040105

But instead I get:

198 | 20040105 | 20050105

Notice the date column was updated as well as anniversary to today's date where I hadn't specified its alteration anywhere. Why is this? What can I do to avoid this?

Thanks.
Alex

[Sxooter]

Congratulations, you've found out that MySQL defines a timestamp as an autoupdated data field, when the SQL spec and every other database in the known universe has a specific definition for timestamp that is non-autoupdating and proper. hey, just one more thing you'll have to do differently by using it.

what you want is, I think, datetime.

I'm wondering how this is gonna work out with the new maxdb, the one derived from SAPDB. Are they gonna add non-SQL spec compliant things to SAPDB to make it work like MySQL (i.e. timestamps become autoupdating) or are they gonna fix the differences between MySQL and SAPDB, thus breaking thousands of apps written for the older, broken way of doing things.

Curious.

Let us know if that helps.