Stormblade wrote:
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.
I don't know. It would certainly be nice, and this seems to come up as much as, if not more than, any other feature request on this list, but, unless I missed it, I don't see this on the TODO list <http://dev.mysql.com/doc/mysql/en/TODO.html>.
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.
Exactly.
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.
Functions are evaluated by the server. The client only sends queries and receives results. So, CURDATE() and NOW() are server time, not client time.
But, you need to know that MySQL behaves differently than you expect with respect to NOT NULL columns. Because it supports non-transactional table types, every column in MySQL has a default value, even NOT NULL columns. Hence, if you leave out the date column in an insert, it will get the default value rather than throwing an error. You will only get an error if you explicitly try to set a NOT NULL column to NULL. See the manual <http://dev.mysql.com/doc/mysql/en/constraint_NOT_NULL.html> for more. You will need to have your app ensure that the date column is set, or have it automatically replace empty input with NULL to get an error.
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.
Right.
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
