CODEWITHSUNDEEP
mysqldatabasetimestamp
Mathew
Mathew

Reputation: 137

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.

Upvotes: 4

Views: 13677

Answers (3)

jebarpg
jebarpg

Reputation: 73

This actually works just fine:

CREATE TABLE IF NOT EXISTS  password_rest_tokens (
  token char(5) DEFAULT NULL,
  expires datetime DEFAULT (CURRENT_TIMESTAMP + INTERVAL 1 HOUR)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

if you run an insert

INSERT INTO password_rest_tokens (token ) VALUES (12345);

You'll see the expires is set to 1 hours ahead upon insertion.

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

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.

Upvotes: 9

Somil
Somil

Reputation: 587

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

Upvotes: 2

Related Questions