How can i change timezone in the Managed MYSQL Database in DigitalOcean?

Question

I brought new Managed MYSQL Database from DigitalOCean. Now I am unable to change Global timeZone. When I am trying to change it error Occurred and it says there isn't Privileges.

*I'm working with Spring Boot Project.

Is there any solution to resolve that?

Answer 1

Still, there is no way to change it from SQL or digitalocean's dashboard. there is a way when you create the connection. the main user also hasn't root privileges to change the global variable. so we have one only option. only we can play with the session. When you create the connection you cant set SESSION timezone.

If you are using Spring boot and Hikari (pool), I'll put the configuration. Todo this we can use connection-init-sql

spring.datasource.hikari.connection-init-sql=SET SESSION time_zone='Asia/Colombo'

now your session timezone will be as you want.

or you can pass the timezone [serverTimezone] with the connection URL like below,

jdbc:mysql://localhost:3006?serverTimezone=Asia/Colombo