Lindorm:Specify a time zone for a LindormTable connection

MySQL command-line tool

For information about how to use the MySQL command-line tool to connect to LindormTable, see Use a MySQL client to connect to and use LindormTable.

In this example, a table named tb is created and populated by executing the following statements:

-- Create the table.
CREATE TABLE tb(p1 int, c1 date, c2 time, c3 timestamp(3), PRIMARY KEY(p1));

-- Insert data into the table.
UPSERT INTO tb(p1,c1,c2,c3) VALUES(1, '2024-08-30', '08:11:15', '2024-08-30 08:11:15.354');

You can query and change the time zone.

1. Query the time zone .

   SELECT @@time_zone;

   Sample result:

   +-------------+
   | @@time_zone |
   +-------------+
   | +08:00      |
   +-------------+

   The result indicates that the current time zone is UTC+8.

2. Query the time data format in the current time zone.

   SELECT * FROM tb;

   Sample result:

   +------+------------+----------+-------------------------+
   | p1   | c1         | c2       | c3                      |
   +------+------------+----------+-------------------------+
   |    1 | 2024-08-30 | 08:11:15 | 2024-08-30 08:11:15.354 |
   +------+------------+----------+-------------------------+

3. Change the time zone to UTC.

   SET @@time_zone='UTC';

4. Check whether the time zone is changed as expected.

   SELECT @@time_zone;

   Sample result:

   +-------------+
   | @@time_zone |
   +-------------+
   | UTC         |
   +-------------+

5. Recheck the time data format in the current time zone to check whether the time zone is changed as expected.

   SELECT * FROM tb;

   Sample result:

   +------+------------+----------+-------------------------+
   | p1   | c1         | c2       | c3                      |
   +------+------------+----------+-------------------------+
   |    1 | 2024-08-30 | 08:11:15 | 2024-08-30 00:11:15.354 |
   +------+------------+----------+-------------------------+

   The data formats of the c1 and c2 columns of the DATE and TIME types remain unchanged because they are unrelated to time zones. The data format in the c3 column of the TIMESTAMP type is changed to the date and time in the UTC time zone.

Java

For information about how to use Java Database Connectivity (JDBC) to connect to LindormTable, see Use Java JDBC APIs to develop applications.

If your business runs in a region that does not use the UTC+8 time zone, we recommend that you add the forceConnectionTimeZoneToSession=true configuration to the connection string to specify connection time zone.

Sample connection code

String url = "jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/" + database + "?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000&forceConnectionTimeZoneToSession=true";
Properties properties = new Properties();
properties.put("user", username);
properties.put("password", password);

//Obtain the established connection.
Connection connection = DriverManager.getConnection(url, properties);

Python

For information about how to use the mysql-connector-python driver to connect to LindormTable, see Use mysql-connector-python to develop applications.

If your business runs in a region that uses the UTC time zone, you must add the time_zone parameter to the connection string to change the connection time zone. For example, add the time_zone='UTC' string to the connection string.

Sample code:

connection = mysql.connector.connect(host='<LindormTable endpoint for MySQL>', port=33060, user='<Username>', passwd='<Password>', database='<Database name>', time_zone='<Time zone>')

Go

For information about how to use the Golang MySQL Driver to connect to LindormTable, see Use Go to develop applications.

If your business runs in a region that uses the UTC time zone, you must add the loc=Locall&time_zone=%27UTC%27 configuration to the connection string.

For more information, see System Variables.

Sample code:

urlString := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?timeout=%s&parseTime=true&loc=Local", user, password, host, port, database, connectTimeout)
timeZoneValue := url.QueryEscape("'UTC'")
urlString = urlString + "&time_zone=" + timeZoneValue
db, err := sql.Open("mysql", urlString)
if err != nil {
	panic(err.Error())
}