Joseph's suggestion works beautifully when I do an insert statement from the MySQL monitor. Now how can I avoid getting the string of zeros that shows up when I use the LOAD DATA INFILE command?
To clarify: I have three columns in my table: Column1, Column2, Column3. Column3 is my TIMESTAMP column. I've tried to put the contents of the text file that I'm using for my LOAD DATA INFILE operation as: value1, value2, null and I have also tried leaving out any value for Column3 (the Timestamp column) as so: value1, value2 Either way, I get all zeros for my TIMESTAMP column. My LOAD DATA INFILE line reads as follows: load data infile "/home/mike/data.txt" into table my_table fields terminated by "," enclosed by "\""; The MySQL manual section on LOAD DATA INFILE (http://www.mysql.com/doc/en/LOAD_DATA.html) explains as follows, but maybe someone can help me understand where I am going wrong: "An empty field value is interpreted differently than if the field value is missing: For string types, the column is set to the empty string. For numeric types, the column is set to 0. For date and time types, the column is set to the appropriate ``zero'' value for the type. See section 6.2.2 Date and Time Types. Note that these are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement. TIMESTAMP columns are only set to the current date and time if there is a NULL value for the column, or (for the first TIMESTAMP column only) if the TIMESTAMP column is left out from the field list when a field list is specified." Thanks, Mike --- Joseph Bueno <[EMAIL PROTECTED]> wrote: > According to the manual, 'timestamp' does exactly > what you are asking > for: > > "The TIMESTAMP column type provides a type that you > can use to > automatically mark INSERT or UPDATE operations with > the current date and > time. If you have multiple TIMESTAMP columns, only > the first one is > updated automatically. > > Automatic updating of the first TIMESTAMP column > occurs under any of the > following conditions: > > * The column is not specified explicitly in an > INSERT or LOAD DATA > INFILE statement. > * The column is not specified explicitly in an > UPDATE statement and > some other column changes value. (Note that an > UPDATE that sets a column > to the value it already has will not cause the > TIMESTAMP column to be > updated, because if you set a column to its current > value, MySQL ignores > the update for efficiency.) > * You explicitly set the TIMESTAMP column to > NULL. " > > (from http://www.mysql.com/doc/en/DATETIME.html) > > Or am I missing something ? > > Regards, > Joseph Bueno > > [EMAIL PROTECTED] wrote: > > I've created a table that contains a column called > > "recordLastModified" and that column uses > "timestamp" > > as the data type. Is there a way in MySQL to > > automatically update timestamp fields as records > are > > modified? I want it to be nearly automatic in the > > same way that using "auto_increment" increases int > > data types, since that is faster than remembering > to > > type numbers each time you add a new record. If > not, > > how can I structure my insert or > > update statements to automatically capture the > > server's sytem time into the timestamp field, or > am I > > required to manually include the date/time when I > > update a record? > > > > Thanks. > > > > __________________________________________________ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up > now. > > http://mailplus.yahoo.com > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
