ApsaraDB for SelectDB:Variable management

Set a session variable

Session variables take effect immediately and apply only to the current connection:

SET exec_mem_limit = 137438953472;
SET forward_to_master = true;
SET time_zone = "Asia/Shanghai";

Variables also accept constant expressions:

SET exec_mem_limit = 10 * 1024 * 1024 * 1024;
SET forward_to_master = concat('tr', 'u', 'e');

Set a global variable

Global variables take effect for the current session and all subsequent sessions:

SET GLOBAL exec_mem_limit = 137438953472;

The following variables support both session and global scope:

• time_zone

• wait_timeout

• sql_mode

• enable_profile

• query_timeout

• insert_timeout

• exec_mem_limit

• batch_size

• allow_partition_column_nullable

• insert_visible_timeout_ms

• enable_fold_constant_by_be

The following variables support global scope only:

• default_rowset_type

• default_password_lifetime

• password_history

• validate_password_policy

Override a variable for a single query

Use the SET_VAR hint to set a session variable for a single statement without changing the session state:

SELECT /*+ SET_VAR(exec_mem_limit = 8589934592) */ name FROM people ORDER BY name;
SELECT /*+ SET_VAR(query_timeout = 1, enable_partition_cache=true) */ sleep(3);

Comments must start with /*+ and follow the SELECT statement.

Query and set time zone variables

To view both time zone variables:

SHOW VARIABLES LIKE '%time_zone%';

To set the session time zone:

SET time_zone = 'Asia/Shanghai';

To set the cluster time zone for all subsequent sessions:

SET GLOBAL time_zone = 'Asia/Shanghai';

Supported time zone formats

Some data import methods may accept additional time zone formats not listed above. Do not rely on non-standard formats in production — ApsaraDB for SelectDB may change how it handles them at any time.

How time zone settings affect functions and data

Affected functions

The following functions return results based on the current time_zone setting:

SHOW LOAD and SHOW BACKENDS output also reflects the configured time zone.

Not affected by time zone settings:

• The less than values of time-type partition key columns in CREATE TABLE

• Values stored as DATE or DATETIME type (the stored value itself does not change)

Time type data during import

When importing data of the DATE, DATEV2, DATETIME, or DATETIMEV2 type, ApsaraDB for SelectDB handles time zone conversion as follows:

• Data with a time zone suffix (e.g., 2020-12-12 12:12:12+08:00): Converted to the cluster's current time_zone. For example, if time_zone is +00:00, the stored value becomes 2020-12-12 04:12:12.

• Data without a time zone suffix (e.g., 2020-12-12 12:12:12): Treated as absolute time — imported as-is with no conversion.

Daylight saving time

Daylight saving time adjustments are embedded in named time zones. For example, America/Los_Angeles shifts from -08:00 to -07:00 in March and back in November.

To avoid daylight saving time shifts, set time_zone to a fixed UTC offset (e.g., -08:00) instead of a named zone like America/Los_Angeles.

Time zone data sources

On each backend (BE) node, ApsaraDB for SelectDB searches the following locations for time zone data, in order:

1. The directory returned by the TZDIR command

2. /usr/share/zoneinfo

3. The zoneinfo directory in the BE deployment directory

If no time zone data is found in any location, the BE node fails to start.

Time zone-sensitive data

Before importing data, decide how to handle time zone information consistently. Data of time types such as DATETIME does not store a time zone — ApsaraDB for SelectDB distinguishes two categories:

Absolute time: No time zone in the data, no time zone configured at import time. Example: 2023-12-12 08:00:00. Stored and queried as-is, unaffected by time_zone.

Time in a specific time zone: Data includes a time zone suffix. Example: 2020-12-12 12:12:12+02:00. The import method determines which time zone setting applies:

• INSERT INTO: Governed by the time_zone session variable.

• Stream Load or Broker Load: Governed by the timezone request header. If the timezone header conflicts with the time_zone session variable, the timezone header takes precedence.

ApsaraDB for SelectDB converts the literal time zone in the incoming data to the time zone configured by time_zone at import time.

Recommended approach: Set time_zone once before importing, then keep it consistent. Changing time_zone after importing timezone-aware data causes the stored values to be misinterpreted in subsequent queries.

The following example shows what happens when time_zone is changed mid-session:

-- Current time zone: Asia/Hong_Kong
SELECT @@time_zone;
-- +----------------+
-- | @@time_zone    |
-- +----------------+
-- | Asia/Hong_Kong |
-- +----------------+

-- Import data with literal time zone +02:00
-- Stored as 2020-12-12 18:12:12 (converted to Asia/Hong_Kong)
INSERT INTO dtv23 VALUES('2020-12-12 12:12:12+02:00');

SELECT * FROM dtv23;
-- +-------------------------+
-- | k0                      |
-- +-------------------------+
-- | 2020-12-12 18:12:12.000 |
-- +-------------------------+

-- Changing time_zone does not update stored values
SET time_zone = 'America/Los_Angeles';

-- A second import now uses America/Los_Angeles for conversion
INSERT INTO dtv23 VALUES('2020-12-12 12:12:12+02:00');

SELECT * FROM dtv23;
-- Rows now carry inconsistent time zone semantics
-- +-------------------------+
-- | k0                      |
-- +-------------------------+
-- | 2020-12-12 02:12:12.000 |  -- imported under America/Los_Angeles
-- | 2020-12-12 18:12:12.000 |  -- imported under Asia/Hong_Kong
-- +-------------------------+

Summary of time zone best practices:

• Confirm the cluster's time_zone before importing data and do not change it afterward.

• When using Stream Load or Broker Load, set the timezone header to the same value as the time_zone session variable.

• For absolute time data, import without a time zone suffix. For time zone-aware data, include the time zone suffix and let ApsaraDB for SelectDB handle the conversion.

Daylight saving time accuracy

Named time zone data (including daylight saving time start and end dates) is maintained by the Internet Assigned Numbers Authority (IANA). The data stored on each BE node may not reflect the most recent changes confirmed by IANA for the current year.

If your workload requires the latest daylight saving time schedule, make sure the time zone data source on each BE node contains the latest IANA tz database release. For the latest data, see The tz-announce Archives.

Supported variables