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.
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 ...
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.
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).
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
