Lindorm:Specify a time zone for a LindormTable connection

MySQL command-line tool

For connection instructions, see Use a MySQL client to connect to and use LindormTable.

The following example creates a table and inserts a row with date, time, and timestamp values, then shows how the TIMESTAMP column changes when you switch time zones.

Set up the table:

-- Create the table
CREATE TABLE tb (p1 INT, c1 DATE, c2 TIME, c3 TIMESTAMP(3), PRIMARY KEY (p1));

-- Insert a row
UPSERT INTO tb (p1, c1, c2, c3) VALUES (1, '2024-08-30', '08:11:15', '2024-08-30 08:11:15.354');

Query in UTC+8 (default):

1. Check the current time zone.

   SELECT @@time_zone;

   Expected output:

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

2. Query the table.

   SELECT * FROM tb;

   Expected output:

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

Switch to UTC and re-query:

1. Change the time zone to UTC.

   SET @@time_zone = 'UTC';

2. Confirm the change.

   SELECT @@time_zone;

   Expected output:

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

3. Query the table again.

   SELECT * FROM tb;

   Expected output:

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

The c1 (DATE) and c2 (TIME) values are unchanged because they are not time zone sensitive. The c3 (TIMESTAMP) value shifts from 08:11:15 to 00:11:15 — an 8-hour difference reflecting the conversion from UTC+8 to UTC.

Java

For connection instructions, see Use Java JDBC APIs to develop applications.

The MySQL Connector/J driver provides two parameters that work together to control the connection time zone:

Add both parameters to your connection string when your application runs outside UTC+8:

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);

// Get a connection
Connection connection = DriverManager.getConnection(url, properties);

Python

For connection instructions, see Use mysql-connector-python to develop applications.

Pass the time_zone parameter in the connection call to set the session time zone at connect time:

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

Go

For connection instructions, see Use Go to develop applications.

The Golang MySQL Driver passes session variables through the connection string. String values must be URL-encoded: wrap the value in single quotes ('), then percent-encode those quotes as %27. For example, the string 'UTC' becomes %27UTC%27.

For more information on how the driver handles system variables, see System Variables.

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'")  // produces %27UTC%27
urlString = urlString + "&time_zone=" + timeZoneValue

db, err := sql.Open("mysql", urlString)
if err != nil {
    panic(err.Error())
}