How to alter column to set default CURRENT_TIMESTAMP in MySQL

Question

I have created a table later I want to add a default value for the column as CURRENT_TIMESTAMP

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `order_date` date DEFAULT NULL,  -- this column...
  `order_status` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

my alter query is like

ALTER TABLE `orders` CHANGE `order_date` `order_date` DATE NOT NULL DEFAULT CURRENT_TIMESTAMP;

and I got error like

 Query error:
#1067 - Invalid default value for 'order_date'

You can also check this: https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value — rinz1er, Jun 27 '20 at 06:50
You can't assign a `DATE` datatype column with a `CURRENT_TIMESTAMP` default value. Just go with Jim's solution and do your query to something like this `WHERE order_date LIKE '2020-06-27%'` or `WHERE DATE(order_date)='2020-06-27'`. — FanoFN, Jun 27 '20 at 07:43

score 3 · Answer 1 · edited Jun 28 '20 at 14:58

3

Please use this syntax:

ALTER TABLE `table` 
    MODIFY column_1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

In your case the statement would be as below,

ALTER TABLE `orders` 
    MODIFY `order_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

edited Jun 28 '20 at 14:58

marc_s

732,580
175
1,330
1,459

answered Jun 27 '20 at 07:00

Jim Macaulay

4,709
4
28
53

1

The first line changes the type, which may or may not be acceptable, and the second line won't work. – Frank Jun 27 '20 at 07:02

Frank · Answer 2 · 2020-06-27T07:08:45.057

0

Your column needs to be of type DATETIME or TIMESTAMP. Docs. Aside from changing the column type, which you may not want to do, you could write an INSERT/UPDATE trigger.

edited Jun 27 '20 at 07:08

answered Jun 27 '20 at 06:53

Frank

459
2
9

How to alter column to set default CURRENT_TIMESTAMP in MySQL

2 Answers2