Hi Ferindo>The intended effect is to get a timestamp field that inserts the current system >time on inserts and continues to update the field with the current timestamp >on updates without the application or use needing to specify it.... are you
>saying that the timestamp attribute alone will do that?
Yep. Try... create table ts(i int,ts timestamp); insert into ts values(1,null); select * from ts; +------+---------------------+ | i | ts | +------+---------------------+ | 1 | 2005-12-04 17:21:01 | +------+---------------------+ update ts set i=2; select * from ts; +------+---------------------+ | i | ts | +------+---------------------+ | 2 | 2005-12-04 17:21:13 | +------+---------------------+ PB http://www.artfulsoftware.com ----- Ferindo Middleton Jr wrote:
Thanks Peter. I did originally use this table in a Postgresql db. Thanks for you advice. Your suggestions below allowed me to create this table and I learned a thing t two about proper usage of the TIMESTAMP data type. The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it.... are you saying that the timestamp attribute alone will do that?Ferindo Peter Brawley wrote:Ferindo One problem is:employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL,NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,(i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just writelast_updated TIMESTAMP,Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL?PB ----- Ferindo Middleton Jr wrote:I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement:CREATE TABLE registration_and_attendance ( id SERIAL NOT NULL UNIQUE, firstname VARCHAR(256) NOT NULL, middlename TEXT, lastname VARCHAR(256), suffix TEXT,sf182_received BOOLEAN NOT NULL DEFAULT TRUE,registrant_email_address TEXT, cc_email_list TEXT,bureau_id INTEGER REFERENCES bureaus(id),office TEXT,class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id),start_date DATE, end_date DATE, enrolled BOOLEAN, attended BOOLEAN, completed BOOLEAN, cancelled BOOLEAN DEFAULT FALSE, cancelled_comments TEXT, comments TEXT,email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL,last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrant BOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT,funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date));I get the following error message with the above statement but I can't figure out what the problem is:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
