how to insert date in mysql table

Question

Hello everyone,

I have a mysql table called pollOfTheWeek. It has a column "pollDate" of type date. I have two questions regarding this : 1. I declared the column while creating the table as [pollDate date] What I wanted is that this column be set automatically, when the user doesnt enter any value for this column. How do i declare the column to achieve this?

1. Assuming that I have the same declaration as above, how do I enter an empty value. I mean if the column was of type varchar, I would enter empty value as " ". But since it is of type date, I get error when I enter the value as " ". How do I enter empty value for the pollDate column?

Answer 1

there is no way to automatically set a DATE column to the current date/time in mysql. TIMESTAMPs can be automatically set to the current time though. alternatively, just pass NOW() as a parameter when you insert :

INSERT INTO pollOfTheWeek (name, pollDate) VALUES ('someName', NOW());

as to your other question, you probably want to allow NULL values in the date column and set them to NULL when there is no date given.

Answer 2

You need to create a trigger:

CREATE TRIGGER triggerName
BEFORE INSERT ON yourTable
FOR EACH ROW
SET NEW.date = coalesce(NEW.date, curdate());

The empty value would be null.

Answer 3

MySQL has an extremely limited support for default values. In general, it must be a constant expression like '0' or '(none)' and functions are not allowed. The only exception I'm aware of is that you can set CURRENT_TIMESTAMP as default value for a TIMESTAMP column; however, this is not available for DATE or DATETIME columns. Unluckily, TIMESTAMP has a smaller range and contains time information.

So your options are:

1. Do it in your client application by setting NOW() as value in your inserts.
2. Write a trigger:

.

CREATE TRIGGER pollOfTheWeek_trg BEFORE INSERT ON pollOfTheWeek 
    FOR EACH ROW SET NEW.pollDate = NOW();

As about empty values, I would simply use NULL. Why use empty strings?