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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
