You have 4 options for storing datetime values: You can use an UNSIGNED INTEGER column to store the # of seconds since EPOCH You can use a CHAR or VARCHAR column to store any arbitrary string (including a string that looks like a formatted date) You can use a DATETIME field You can use a TIMESTAMP field.
However, since you said that you didn't want to have the date changed each time the record was updated that eliminates the TIMESTAMP as a possible column type. Since it's quite frustrating to decipher formatted strings (especially when you want to determine differences between two datetime values) unless you write your own conversion functions that eliminates CHAR or VARCHAR columns. It can be a royal pain in the butt to use an UNSIGNED INTEGER as each time to need to read or write from it you need to use FROM_UNIXTIMESTAMP() or UNIXTIMESTAMP() to convert the numerical value into an actual datetime value before you can use it. That leaves only DATETIME as the logical storage type for your needs. Please note: how the database __stores__ a datetime value is totally OBLIVIOUS to your formatting needs. That's why there is an entire page of functions that can be used to convert and format DATETIME values in MySQL. Some combination of these will help you to achieve your desired output in YYYYMMDDhhmmss format. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html You cannot confuse STORAGE with PRESENTATION. You can't reprogram how the database stores a datetime value unless you change the source code. However you have many options of how to convert or format a datetime value as you retrieve it from storage. Read how to specify datetime values here: http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html That should help you to understand how to write your UPDATE statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine news <[EMAIL PROTECTED]> wrote on 12/17/2004 01:29:05 PM: > I'm using MySQL 4.1.7 and need to create a column to store date and > time. Also, I need to be able to update the column with something like: > update machines set update3_status='y', update3_time=SOMETHING; > > I don't want update3_time to be automatically changed any time another > column changes. What is the correct column type to use to get > update3_time to contain day and time in the form YYYYMMDDhhmmss? Is > there a fuction or something to use in place of SOMETHING in the query? > > Jason Joines > ================================= > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
