MySQL add some hours when set default column datetime

Question

I want to add some hours when alter table datetime to default current_timestamp

here the sql queries were I tried :

 ALTER TABLE [table_name] MODIFY COLUMN created_date TIMESTAMP NOT NULL DEFAULT SELECT DATE_ADD(NOW(), INTERVAL 7 HOUR);

 ALTER TABLE [table_name] MODIFY COLUMN created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL 7 HOUR;

 ALTER TABLE [table_name] MODIFY COLUMN created_date TIMESTAMP NOT NULL DEFAULT NOW() + INTERVAL 7 HOUR;

but none of them work, please share me if you know how to solved this case.

great idea, actually if it possible, I want to know how it can be done via alter query. — mrfizh, Apr 05 '18 at 04:03
I didn't know this, but you can't add hours to a default value in MySQL. You'll have to use a trigger, q.v. the duplicate link. — Tim Biegeleisen, Apr 05 '18 at 04:15
hmm..,so it not possible to add hours to a default value in MySQL...it means my case can be applied only by trigger..okay thanks @TimBiegeleisen — mrfizh, Apr 05 '18 at 04:24

score 2 · Accepted Answer · edited Apr 05 '18 at 06:27

2

For Trigger Try below:

DELIMITER $$

CREATE TRIGGER [trigger_name]
BEFORE INSERT
ON [table_name] FOR EACH ROW

BEGIN

SET NEW.created_date = CURRENT_TIMESTAMP + INTERVAL 7 HOUR;

END$$ 
DELIMITER ;

And then try to insert data into your table.

edited Apr 05 '18 at 06:27

mrfizh

121
1
9

answered Apr 05 '18 at 04:08

Rehan Azher

1,340
1
9
17

Hi @Rehan, thanks for your trigger query :) – mrfizh Apr 05 '18 at 04:25
Did it work? if yes please accept the answer. – Rehan Azher Apr 05 '18 at 04:25
okay but maybe it's need some editing, since it not possible to add hours via default value.., your answer is right for my case. thank you – mrfizh Apr 05 '18 at 04:35

MySQL add some hours when set default column datetime

1 Answers1