Manage time zone configuration#

The server time zone for a set of current and future database instances can be set using the configuration groups feature of Cloud Databases. Suppose you want to set the default time zone for a set of database instances.

The server time zone is set in MySQL in the default_time_zone parameter, and the default value is ‘SYSTEM’. You can set the time zone in MySQL using either:

  • named time zones

  • UTC (Coordinated Universal Time) offsets

To set the time zone using named time zones, use the appropriate name for your zone, such as “Europe/London” or “US/Eastern” to set the default_time_zone, for example:

  • {"default_time_zone":"Europe/London"}

  • {"default_time_zone":"US/Eastern"}

For more information about named time zones, see the MySQL documentation .

To set the default_time_zone using offsets of the time zone from UTC (Coordinated Universal Time) use the appropriate offset value for your zone. For example the CST (Central Standard Time) time zone would be “-6:00”:

{"default_time_zone":"-6:00”}

If you lived in the AEST (Australian Eastern Standard Time) time zone on the other hand, the offset would be “+10:00”.

You can set default_time_zone in a configuration group that can be applied to your database instance. You can create a new configuration group just for the time zone management, or add the time zone parameter to an existing configuration group.

After you update time zone settings on a database instance, you can check the server time to verify the change.

Following are two methods for using a configuration group to set the default time zone:

Managing time zones by using the trove client#

This section shows how to use trove client commands to manage the time zones for a database instance. You’ll learn how to create the configuration group, apply it to the database, and also how to add custom time zone information to an existing configuration group.

Note

After updating the settings, check the server time to verify that updates were successful.

Create a configuration group#

The following example creates a configuration group named TimeConfig that sets the time zone to CST (Central Standard Time).

Run the following configuration-create trove client command with group name and time zone values:

$ trove configuration-create TimeConfig '{"default_time_zone":"-6:00"}' --datastore MySQL

The command returns the id and name for the configuration group among the other information:

+----------------------+--------------------------------------+
|       Property       |                Value                 |
+----------------------+--------------------------------------+
| datastore_version_id | 20000000-0000-0000-0000-000000000002 |
|     description      |                 None                 |
|          id          | 00f6070d-27f1-4af2-8388-4cae33899c0c |
|         name         |              TimeConfig              |
|        values        |    {"default_time_zone": "-6:00"}    |
+----------------------+--------------------------------------+

As an alternative, you could use a named time zone instead of the UTC offset.

In the response, the id value is a unique identifier assigned to the configuration group. You need to include this value in configuration group client commands that require it, for example the configuration-attach command used to apply the configuration group to a database instance.

Apply the time zone configuration group to an existing database instance#

You can apply a configuration group to an existing database instance. You need to include the ID values for the database instance and configuration group in the command.

Run the following configuration-attach trove client command to apply the configuration group.

$ trove configuration-attach <config_id> <instance_id>

Remember to replace the variables in the example above with their actual respective values:

The configuration-attach command does not return any output.

Note

You need to restart the Cloud Databases instance to apply the new configuration settings.

Add custom time zone information to an existing configuration group#

You can update settings in an existing configuration group by using the configuration-patch command. You need to include the ID value for the configuration group in the command.

The following command shows how to update the default_time_zone setting to add the IST (India Standard Time, +6:00) time zone to a configuration group with id <config_id>:

$ trove configuration-patch <config_id> '{"default_time_zone":"+6:00"}'

This command does not return any output.

Managing the time zone with cURL#

This section shows how to use cURL to manage the time zones for a database instance. You’ll learn how to create the configuration group, apply it to the database, and also how to add custom time zone information to an existing configuration group.

Note

After updating the settings, checking the server time zone to verify that updates were successful.

Create a configuration group (cURL)#

You can create a configuration group to manage time zones by using the create configuration operation.

The following example shows the cURL request to create a configuration group named TimeConfig that sets the time zone to CST (Central Standard Time).

Example: Create Configuration Group Request cURL request

 curl -s -d \
 '{
   "configuration": {
     "datastore": {
       "type": "10000000-0000-0000-0000-000000000001",
       "version": "20000000-0000-0000-0000-000000000002"
     },
     "description": "Test config",
     "name": "test-configuration",
     "values": {
       "default_time_zone": "-6:00"
    }
   }
 }' \
-H "X-Auth-Token: $AUTH_TOKEN" \
-H "Content-Type: application/json" \
 $API_ENDPOINT/configurations  | python -m json.tool

As an alternative, you could use a named time zone instead of the UTC offset.

The following example shows the response for Create Configuration Group Request:

JSON Response

 {
     "configuration": {
        "datastore_version_id": "20000000-0000-0000-0000-000000000002",
         "description": "Test config",
         "id": "63c6e164-2324-4eaa-a3d8-d79528a26e7d",
        "name": "test-configuration",
         "values": {
             "default_time_zone": "-6:00"
        }
    }
}

In the response, the id value is a unique identifier assigned to the configuration group. You need to include this value in configuration group commands that require it, for example the configuration-attach command used to apply the configuration group to a database instance.

Apply the time zone configuration group to an existing database instance (cURL)#

You can apply a configuration group to an existing database instance. You need to include the ID values for the database instance and configuration group in the command.

The following example shows the cURL request to add an existing configuration group to a specific database instance:

Example: Apply configuration group to a specified database instance cURL request

 curl -i -d \
 '{
   "instance": {
       "configuration": "<config_id>"
   }
}' \
 -X PUT \
 -H "X-Auth-Token: $AUTH_TOKEN" \
 -H "Content-Type: application/json" \
 $API_ENDPOINT/instances/<instance_id>

Remember to replace the following variables in the example above with their actual respective values:

If successful, the Rackspace Cloud Databases API returns an HTTP/1.1 202 Accepted response header to confirm that the settings have been accepted. The operation does not return a request body.

JSON response header

HTTP/1.1 202 Accepted
Content-Type: application/json
Via: 1.1 Repose (Repose/2.12)
Content-Length: 0
Date: Fri, 02 May 2014 15:18:56 GMT
Server: Jetty(8.0.y.z-SNAPSHOT)

Note

You need to restart the Cloud Databases instance to apply the new configuration settings.

Add custom time zone information to an existing configuration group by using cURL#

You can update settings in an existing configuration group by using the update configuration operation. You need to include the ID value for the configuration group in the command.

The following command shows how to update the default_time_zone parameter on an existing configuration group with with id <config_id> from CST (Central Standard Time, -6:00) to IST (India Standard Time, +6:00).

Example: Update default time zone in existing configuration group cURL request

 curl -i -d \
 '{
     "configuration": {
       "values": {
           "default_time_zone": "+6:00"
       }
    }
}' \
-X PATCH \
-H "X-Auth-Token: $AUTH_TOKEN" \
 -H "Content-Type: application/json" \
 $API_ENDPOINT/configurations/<config_id>

If successful, the Rackspace Cloud Databases API returns an HTTP/1.1 202 Accepted response header to confirm that the settings have been accepted. The operation does not return a request body.

JSON response header

HTTP/1.1 200 OK
Content-Type: application/json
Via: 1.1 Repose (Repose/2.12)
Content-Length: 0
Date: Fri, 02 May 2014 15:44:43 GMT
Server: Jetty(8.0.y.z-SNAPSHOT)

After updating the settings, checking the server time zone to verify that updates were successful.

Checking the server time zone#

You can check the current time zone setting for an instance by logging in to the MySQL console (see the KC article Connect to a Cloud Database instance) and querying the value of global.time_zone:

SELECT @@global.time_zone;

The returned value will show the instance’s current time zone setting, for example:

+--------------------+ | @@global.time_zone | +--------------------+ | +06:00  |

If the time zone does not reflect what you set in the configuration group attached to the instance, the instance may need to be restarted in order for the change to take effect.

This concludes the Getting Started Guide.