How to add 1 hour to currrent_timestamp in mysql which is the default value?

Question

I have a database with timestamp field which takes current timestamp by default, but I have problem with the time, like if I insert the row at 9:00 it will take 8 as timestamp.

So my question is how to make current_timestamp in that table add one hour by default? I know you can do it with php but I prefer pure mysql solution.

I have a problem with the server timezone but I don't want to change it, since I am afraid this might affect other databases on server, while I want to change timestamp only in one database.

It probably might be because the MySQL server is in a different timezone — hjpotter92, Mar 12 '16 at 07:39
it sounds like problem with server timezone, so check this one: http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql — mitkosoft, Mar 12 '16 at 07:40
Possible duplicate of [Should I use field 'datetime' or 'timestamp'?](http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp) — Strawberry, Mar 12 '16 at 07:46

mitkosoft · Accepted Answer · 2016-03-12T08:21:33.033

9

Simply you cannot do CURRENT_TIMESTAMP + INTERVAL 1 HOUR, but you can define a trigger instead:

CREATE TRIGGER tr_dt_table BEFORE INSERT ON your_table FOR EACH ROW BEGIN
  SET NEW.datetime_field = NOW() + INTERVAL 1 HOUR;
END

And remove any default values of that field (i.e. make it NULL by default) in order to avoid contradictions.

edited Mar 12 '16 at 08:21

answered Mar 12 '16 at 08:11

mitkosoft

5,262
1
13
31

I want to add that if it's a timestamp field it must be created with "fieldname timestamp NULL DEFAULT NULL" – Valter Ekholm Jul 08 '20 at 13:58
1

Also, I tested the trigger, it worked without the "BEGIN" – Valter Ekholm Jul 08 '20 at 14:07

score 2 · Answer 2 · answered Mar 12 '16 at 07:44

2

insert into table_name values (DATE_ADD(now() , INTERVAL 1 HOUR));

answered Mar 12 '16 at 07:44

Somil

567
5
13

2

I want to add 1 hour by default to the my timestamp field – Mathew Mar 12 '16 at 07:56

How to add 1 hour to currrent_timestamp in mysql which is the default value?

2 Answers2

Linked

Related