There are some scenarios where application do require to show datetime with regional time zone(let’s say application user wanted to show data in India IST time zone).

By default MySQL database service(MDS) has UTC time zone and in order to change the system variable of time_zone you can’t do at this moment by SQL commands and ended with below error :

mysql> set global sql_mode=”Asia/Kolkata“;

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

mysql>

However changing the time_zone system variable at session level is possible as below:

mysql> set session time_zone=”Asia/Kolkata”;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@time_zone;

+————–+

| @@time_zone  |

+————–+

| Asia/Kolkata |

+————–+

1 row in set (0.00 sec)

mysql>

 that’s by we have to modify those dynamic variable from OCI  GUI dashboard

Step 1:- Go to Hamburger MenuDB SystemsConfigurationCreate MySQL Configuration

figure 1

Step 2:-  create MySQL Configuration by choosing the right shape (which means the shape to whom you wanted to modify the time_zone)

figure 2Step 3: –  Create MySQL Configuration by selecting the system variable “time_zone”  and click on “Create”

figure 3Step 4:- Go to the running MySQL DB Instance for which you wanted to change the time_zone parameter

figure 4Step 5: –  Click on Edit as shown above and click on show advanced options change configuration as below

figure 4

figure 6Step 6:-  save changes and verify it

figure 6

Let’s verify the changes by logging through MySQL shell

Before

figure 7

After the changes into MDS as expected output 

figure 8

Now, couple of question

[Q.] does MySQL database service(MDS) will go for restart ?

[A.] No

[Q.]Does application will go for downtime for changing the configuration ?

[A.] No

Conclusion 

Changing global variable is very easy and convient with MySQL database service(MDS)  and MDS is 100% developed ,managed and supported by MySQL team.

and major business benefit is one MySQL Database service(MDS) for transactions, analytics, and machine learning (ML). Real-time, secure analytics without the complexity, latency, and cost of extract, transform, and load (ETL) duplication.

This service is available on Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.