CODEWITHSUNDEEP
mysqlsql
OJ102
OJ102

Reputation: 49

Adding a column to MySQL to record the Date the entry was made

I am setting up a database and wanting to record when each entry was added, date only.

ALTER TABLE form ADD COLUMN created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

I am using the above code but it also stores the time which I don't want. There is an option for DATE alone, but I can't work out how to have it auto input it like the above does.

Anyone know the query by chance? I've tried:

ALTER TABLE form ADD COLUMN created DATE NOT NULL DEFAULT CURDATE

But it didn't like that and I've not found any other commands to try.

Upvotes: 1

Views: 192

Answers (3)

Jehad Keriaki
Jehad Keriaki

Reputation: 545

If triggers are considered, this trigger should be fine: 

CREATE trigger def_date before INSERT ON form
FOR each
ROW SET NEW.date = CURDATE( )

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Your second ALTER statement don't work cause you can't use function/expression as default. from MySQL documentation CREATE TABLE

The DEFAULT clause specifies 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

So essentially, you can only use CURRENT_TIMESTAMP as default.

Upvotes: 0

VMai
VMai

Reputation: 10336

No, only CURRENT_TIMESTAMP will work. That said, you can get easily extract the date portion of this column with the DATE function:

SELECT
    DATE(created) as created_date
FROM
    form;

Here the relevant part of the MySQL manual:

The DEFAULT clause specifies 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 or (as of MySQL 5.6.5) DATETIME column.

Upvotes: 1

Related Questions