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]
