MySQL default datetime through phpmyadmin

Question

In an existing database, I've age column (INT). Now I need to set it as dob (DATETIME).

I try doing so through PHPMyAdmin, giving CURRENT_TIMESTAMP as default value as defined by answer with 138 upvotes. However PHPMyAdmin is complaining #1067 - invalid default value for 'dob' as in attached screenshot:

Can someone please suggest why I'm getting that error and how to fix that?

Answer 1

The best way for DateTime is use a Trigger:

/************ ROLE ************/
drop table if exists `role`;
create table `role` (
    `id_role` bigint(20) unsigned not null auto_increment,
    `date_created` datetime,
    `date_deleted` datetime,
    `name` varchar(35) not null,
    `description` text,
    primary key (`id_role`)
) comment='';

drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
    on `role`
    for each row 
    set new.`date_created` = now();

Answer 2

You can't set CURRENT_TIMESTAMP as default value with DATETIME.

But you can do it with TIMESTAMP.

See the difference here.

Words from this blog

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.

Answer 3

You're getting that error because the default value current_time is not valid for the type DATETIME. That's what it says, and that's whats going on.

The only field you can use current_time on is a timestamp.

Answer 4

I don't think you can achieve that with mysql date. You have to use timestamp or try this approach..

CREATE TRIGGER table_OnInsert BEFORE INSERT ON `DB`.`table`
FOR EACH ROW SET NEW.dateColumn = IFNULL(NEW.dateColumn, NOW());

Answer 5

Set the type of the field as TIMESTAMP too.