"Lieven De Keyzer" <[EMAIL PROTECTED]> wrote on 05/18/2005 01:44:31 PM:
> I 'm writing a webapplication in Java that allows users to store bookmarks. > The system scans these pages for differences at user-selected intervals. At > another user-selected interval, the system sends notification mails about > changed bookmarks. The bookmark table provisionally looks like this: > > CREATE TABLE bookmark ( > bookmark_id INTEGER NOT NULL AUTO_INCREMENT, > bookmarkname VARCHAR (80) NOT NULL, > url VARCHAR (150) NOT NULL, > folder_id INTEGER NOT NULL, > last_scanned TIMESTAMP NOT NULL, > PRIMARY KEY (bookmark_id), > FOREIGN KEY (folder_id) REFERENCES folder(folder_id) ON DELETE CASCADE) > TYPE = InnoDB; > > I want to add another TIMESTAMP column, last_notified. But whenever I insert > a new bookmark, the first TIMESTAMP column will be set, the other will be > 0000-00-00 00:00:00. > When they get mapped by the iBatis framework to Java objects, I get an > exception that aTimestamp object can not be created with 0000-00-00 00:00:0 > > Is there a way I can set them both when the bookmark is created? I rather > not set one of them to NULL, because that would imply a lot more code to > check if a user should be notified or a bookmark should be scanned. > > In MySQL, timestamp columns are somewhat special. That is why they are not normally used to store manually updated values. To store a date+time value and not have it overwritten with the time and date of the next update to the record, you should use a DATETIME column. The exact behavior of timestamp columns depends on if they are the only one on a table and which version of MySQL you are running. Please refer to http://dev.mysql.com/doc/mysql/en/datetime.html for more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
