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.
Actually, in MySQL 4.1.2, you'll be able to do this. Currently, the first TIMESTAMP column is set to the current timestamp when a record is created by default *and* updated when any other column is changed. In 4.1.2, you'll be able to decouple this (for one TIMESTAMP column in the table), so that the column can be set to the current timestamp for record creation, but not changed afterward unless you change it explicitly.
For example:
mysql> create table t (ts timestamp default current_timestamp, i int); Query OK, 0 rows affected (0.00 sec)
mysql> insert into t (i) values(1); Query OK, 1 row affected (0.18 sec)
mysql> select * from t; +---------------------+------+ | ts | i | +---------------------+------+ | 2004-04-18 14:15:08 | 1 | +---------------------+------+ 1 row in set (0.04 sec)
mysql> update t set i = i + 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t; +---------------------+------+ | ts | i | +---------------------+------+ | 2004-04-18 14:15:08 | 2 | +---------------------+------+ 1 row in set (0.00 sec)
Note that the ts column did not change when the i column was updated.
This isn't documented in the manual yet, because I am still working on it.
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
