CODEWITHSUNDEEP
mysqlsql
yacho
yacho

Reputation: 45

How do I change timezone in mysql in 'default' and 'on update' current_timestamp only?

I'm currently struggling on to insert default value to the table.

I just want to insert default datetime value to 'created_at', and default 'on update' to 'updated_at' column. But because schema's timezone is different, so when I change column it only shows UTC time.

alter table table1
    change created_at created_at datetime not null default date_add(current_timestamp, interval 9 hour);

alter table table1
    change updated_at updated_at datetime not NULL default current_timestamp on update date_add(current_timestamp, interval 9 hour);

So I tried this but it doesn't work. I found this answer: MySQL set default value for DATE column using DATE_ADD?

So I understood what the problem is, but I just want to make sure I have to save created_at and updated_at based on UTC + 9 hours not changing default timezone. How do I do that?

Thanks.

Upvotes: 0

Views: 5340

Answers (2)

Sonu Chohan
Sonu Chohan

Reputation: 273

Setting time_zone in my.cnf options file ( In MySQL:8.0.26 and CentOS 7 )

sudo vi /etc/my.cnf

And add this line: ( I have set the timezone as per India - Asia/Kolkata )
default-time-zone = "+5:30"

Then save the file and restart MySQL server
sudo systemctl restart mysqld

Then check the MySQL server status
sudo systemctl status mysqld

Upvotes: 0

Md.Shams Imran Shuvo
Md.Shams Imran Shuvo

Reputation: 96

sudo nano /etc/mysql/my.cnf Scroll down to the [mysqld] section, and find the default-time-zone = "+00:00" line. Change the +00:00 value (the GMT value) to +09:00 (the time zone you want). Save the file and exit.

if not found add default-time-zone = "+09:00"

then restart the mysql sudo service mysql restart

Another option to use query sudo mysql -e "SET GLOBAL time_zone = '+9:00';" then check with sudo mysql -e "SELECT @@global.time_zone;"

Upvotes: 0

Related Questions