Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...