Stormblade (and anyone watching this thread), Just watch yourself when deciding between DATETIME and TIMESTAMP; the two datatypes support very different ranges of values!
According to the manual, DATETIME can store any value between '1000-01-01 00:00:00' and '9999-12-31 23:59:59' but TIMESTAMP can only handle the range '1970-01-01 00:00:00 ' and "sometime in the year 2037". Therefore, even if you really want to use TIMESTAMP but you have to store a timestamp earlier than 1970 or after 2037, you're going to be out of luck. Be sure you think about what values you'll have to store before you make a final choice of the datatype. Rhino ----- Original Message ----- From: "Stormblade" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, April 18, 2004 1:30 PM Subject: Re: Datetime Default Value > On Sun, 18 Apr 2004 12:17:00 -0400, Michael Stassen wrote: > > > Stormblade wrote: > > > >> Hey all, > >> > >> I'm currently converting a SQLServer 2000 database over to MySQL. I have a > >> web application that currently uses SQLServer but will be using MySQL soon > >> as I can get this done. > >> > >> I was able to match data types but so far I have not found a way to let the > >> database handle setting the default value of a datetime field to the > >> current date/time. Is this possible? > > > > Defaults must be constants, not functions, in MySQL. So, except for the > > TIMESTAMP type, the answer is no. > > I suspected as much. I wonder if they plan to add this in the future or if > there is some technical reason that they did not allow for this. > > > > >> In my research I found 2 main suggestions: > >> > >> 1. Use timestamp. While this suggestion is good for my modified fields it > >> is not really suitable for the others which basically will get set once and > >> that's it. > > > > You can preserve the value of a timestamp column by explicitly setting it to > > itself in an UPDATE. Something like this: > > > > UPDATE yourtable SET somecol='newvalue', timestampcol=timestampcol > > WHERE ... > > > > Yea I saw that but then I'd have to guarantee that any SQL that updated did > this. I had visions of lots of pain in this case. It would only take a > single mistake to permanently remove the creation date. > > >> 2. Use datetime but specify the date/time in your SQL. This is also not > >> very suitable for two reasons. One is that it would require me to go > >> through the web application and change all the SQL which I can do but > >> rather not. The second reason is that this approach is dangerous. There is > >> no guarantee that the database is on the same system as the web > >> application. May not even be in the same timezone. So I prefer a more > >> loosely coupled design. > > > > This need not be dangerous. You can use the CURDATE() and NOW() functions > > to let the server define the date and time. Something like this: > > > > INSERT INTO yourtable (datecol, datetimecol, othercols...) > > VALUES (CURDATE(), NOW(), othervals...); > > > > See <http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html> for more. > > Ok, I knew about the functions but what do you mean allow the server to to > define the date/time. If I use an SQL like you have above does the database > itself resolve those function then? If so then that's a good alternative. > Sure I'll have to modify some SQL but I can set the date field not to allow > null so that when they create a record they HAVE to supply a date. > > Now on updates if I remember my SQL I don't have to specify the date and > it'll just leave it alone. So that means rather than changing all the SQL I > only really have to modify the Inserts. > > > > >> If I can't find any other way I will have to go with the second option but > >> I'd really like to find out a better way if one exists. > > > > So, you have two choices: Use timestamp and change your code to preserve the > > timestamp in updates, or use datetime and change your code to set it to > > NOW() on insert. Depending on your application, one of those may be easier > > to do (require fewer changes). All else being equal, I'd recommend using > > datetime, as it is intuitively closer to what you want (self-documenting). > > Yup that's what I think I will do. I will use timestamp only for when I > need a last modified type value and datetime for all else. > > > > > Michael > > Thanks much. If the curdate and now functions use the system date according > to where the MySQL database is then that's the solution for me. > -- > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > Stormblade (Shaolin Code Warrior) > Software Developer (15+ Years Programming exp.) > > My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
