INSERT current date or time into MySQL

Question

If I create a table with an entity that is suppose to be DATE and when I Insert and leave that column blank shouldn't it display the current date? Same with time?

For example...

CREATE TABLE Register
(
Name CHAR(20) NOT NULL,
Date DATE,
Time TIME
);

Then I Insert:

INSERT INTO Register (Name)
VALUES ('Howard');

I want it to display on the table:

Howard | 5/6/2014 | 8:30 PM

But instead it displays:

Howard | NULL | NULL

Is this incorrect and if so what am I suppose to Insert to allow the current date and time of insert to display?

Answer 1

Firstly, you should have a PRIMARY KEY in your table.

Secondly, you have not set default values for columns Date and Time. Also, you can't set them separately for the DATE and TIME types – you should use TIMESTAMP type and DEFAULT CURRENT_TIMESTAMP like :

 CREATE TABLE Register (
    Name CHAR(20) PRIMARY KEY NOT NULL,
    Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Thirdly, if you want to use exactly two columns for date storing, you can set a trigger on INSERT event for this table, like it is shown below :

 CREATE TRIGGER default_date_time
 BEFORE INSERT ON my_table_name
 FOR EACH ROW
 BEGIN
    SET NEW.Date = CURDATE();
    SET NEW.Time = CURTIME();
 END;
 $$

Answer 2

You need to set a default. So you might think you could do this:

CREATE TABLE Register
(
Name CHAR(20) NOT NULL,
Date DATE DEFAULT CURRENT_DATE,
Time TIME DEFAULT CURRENT_TIME
);

But that won’t work. You need to use CURRENT_TIMESTAMP and change your DB structure to use the combined TIMESTAMP format:

CREATE TABLE Register
(
Name CHAR(20) NOT NULL,
Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The reason being is there is no MySQL DEFAULT value for DATE or TIME alone. Some clues to that behavior here:

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

Answer 3

Here are two options:

1. Get rid of Date and Time columns and add time stamp

   INSERT INTO Register (Name,Ctime) VALUES ('Howard',CURRENT_TIMESTAMP);

2. If you want to continue with your table structure

   INSERT INTO Register (Name,Date,Time) VALUES ('Howard',CURDATE(), CURTIME());

Also Note that date and time are reserved words of MySQL and hence should be quoted with backticks to avoid conflicting with reserved words. Or just rename it according to a table name format.