"Lieven De Keyzer" <[EMAIL PROTECTED]> wrote on 05/18/2005 03:21:02 PM:
> > > >From: [EMAIL PROTECTED] > >To: "Lieven De Keyzer" <[EMAIL PROTECTED]> > >CC: mysql@lists.mysql.com > >Subject: Re: Different TIMESTAMP columns > >Date: Wed, 18 May 2005 15:05:17 -0400 > > > >"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. > > Yes, I have already considered using the DATETIME, but there is no Java > class for this database type? > > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > I am not sure which Java package you are using (or I would look at its documentation to make sure of what I am about to say) but it makes sense to me that an object of the Java "timestamp" class would map to a MySQL "datetime" field. What happens when you try that kind of match? You see, the name "timestamp" cannot not mean exactly the same thing under all circumstances or across different languages. This may be the case where what Java calls "timestamp", MySQL calls "datetime". Same data, different name. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
