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:

Error screenshot

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

Wondering how date of birth can have current time as default value? — Mithun Sreedharan, Oct 25 '12 at 07:58
[DATETIME Vs TIMESTAMP](http://stackoverflow.com/questions/409286/datetime-vs-timestamp) — Mithun Sreedharan, Oct 25 '12 at 08:00
@Mithun. Just temporary value. later will change with other values. — Kapil Sharma, Oct 25 '12 at 08:16

score 30 · Accepted Answer · edited May 23 '17 at 10:31

30

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.

edited May 23 '17 at 10:31

Community

1
1

answered Oct 25 '12 at 08:04

Mithun Sreedharan

49,883
70
181
236

Thanks. I used null as temporary value. Initially I was trying to go through `not null` and using current date as default value but seems it is not possible. Allowed null temporarily and it worked. – Kapil Sharma Oct 25 '12 at 08:26
1

For future readers: note that this is no longer true. `DATETIME` can have `CURRENT_TIMESTAMP` as a default value since MySQL 5.6.5. https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html – CmdrSharp Sep 03 '21 at 08:05

score 4 · Answer 2 · answered Jul 25 '17 at 15:26

4

Set the type of the field as TIMESTAMP too.

answered Jul 25 '17 at 15:26

Ashutosh Bajpai

73
5

score 3 · Answer 3 · answered Oct 25 '12 at 08:26

3

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());

answered Oct 25 '12 at 08:26

user1518659

2,198
9
29
40

score 2 · Answer 4 · answered Oct 25 '12 at 08:02

2

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.

answered Oct 25 '12 at 08:02

Nanne

64,065
16
119
163

score 1 · Answer 5 · answered Feb 19 '15 at 13:39

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();

MySQL default datetime through phpmyadmin

5 Answers5

Linked