problem with setting up a default value for a datetime variable

[Ted]

OK, I am using MySQL Administrator 1.1.9 and MySQL 5.0.19 on Windows XP
Pro.

I am setting up a table which I will routinely populate by uploading
the data from a CSV text file. What I will actually do is load the
data into memory tables, and do my processing using these. And before
I process the new data (which will come in daily), I intend to use the
form of the INSERT statement that is designed for quickly copying data
from one table to another. The destination tables are archives,
ultimately to be used for queries supporting historical questions or
analyses, and these, unlike the originals, need time stamps so we know
the date and time at which the data were retrieved and processed.

So, I have a couple extra columns relative to what is in the file, both
intended to have a default value. The first is an integer to be used
as an index, and it s to be autoincremented. That I have set up
without difficulty.

The column that is giving trouble is a date/time stamp, and it ought to
have a default value of NOW(). I have tried using NOW() as a default
value, as well as CURRENT_TIMESTAMP, and in both cases I get an error
claiming that these values are invalid default values. According to my
references dealing with SQL, as well as both the manual and a book I
have on MySQL, I should be able to use either NOW() or
CURRENT_TIMESTAMP as a default for a date or time variable. So what is
wrong?

Any ideas on what I can do to use ANSI standard SQL to set up and use
my time variable with a default value of NOW()?

Thanks,

Ted

[Bill Karwin]

Ted wrote:

> Any ideas on what I can do to use ANSI standard SQL to set up and use
> my time variable with a default value of NOW()?

MySQL seems to support DEFAULT CURRENT_TIMESTAMP as a special behavior
of the TIMESTAMP datatype only. This behavior doesn't work with the
DATETIME datatype, nor does it work with DATE, TIME, or YEAR datatypes.

As far as standard SQL solutions, I'd recommend either of the following:

- Specify the value when you copy data from the origin table:

INSERT INTO real_table (a, b, c, d)
SELECT a, b, c, CURRENT_TIMESTAMP()
FROM in_memory_table;

- Write a BEFORE INSERT trigger on your table and write logic to
substitute the value returned by CURRENT_TIMESTAMP() when the datetime
column has a NULL state.

DELIMITER |
CREATE TRIGGER setdefault_ts BEFORE INSERT ON real_table
FOR EACH ROW BEGIN
SET NEW.d = COALESCE(NEW.d, CURRENT_TIMESTAMP());
END

Regards,
Bill K.