SETTINGS Clause

The SETTINGS clause configures specific settings that influence the execution behavior of the SQL statement it precedes. To view the available settings in TiDB Cloud Lake and their values, use SHOW SETTINGS.

See also: SET

Syntax

SETTINGS ( <setting> = <value> [, <setting> = <value>, ...] ) <statement>

Supported Statements

The SETTINGS clause can be used with the following SQL statements:

• SELECT
• INSERT
• INSERT (multi-table)
• MERGE
• COPY INTO <table>
• COPY INTO <location>
• UPDATE
• DELETE
• CREATE TABLE
• EXPLAIN

Examples

This example demonstrates how the SETTINGS clause can be used to adjust the timezone parameter in a SELECT query, impacting the displayed result of now():

-- When no timezone is set, TiDB Cloud Lake defaults to UTC, so now() returns the current UTC timestamp
SELECT timezone(), now();

┌─────────────────────────────────────────┐
│ timezone() │            now()           │
│   String   │          Timestamp         │
├────────────┼────────────────────────────┤
│ UTC        │ 2024-11-04 19:42:28.424925 │
└─────────────────────────────────────────┘

-- By setting the timezone to Asia/Shanghai, the now() function returns the local time in Shanghai, which is 8 hours ahead of UTC.
SETTINGS (timezone = 'Asia/Shanghai') SELECT timezone(), now();

┌────────────────────────────────────────────┐
│   timezone()  │            now()           │
├───────────────┼────────────────────────────┤
│ Asia/Shanghai │ 2024-11-05 03:42:42.209404 │
└────────────────────────────────────────────┘

--  Setting the timezone to America/Toronto adjusts the now() output to the local time in Toronto, reflecting the Eastern Time Zone (UTC-5 or UTC-4 during daylight saving time).
SETTINGS (timezone = 'America/Toronto') SELECT timezone(), now();

┌──────────────────────────────────────────────┐
│    timezone()   │            now()           │
│      String     │          Timestamp         │
├─────────────────┼────────────────────────────┤
│ America/Toronto │ 2024-11-04 14:42:48.353577 │
└──────────────────────────────────────────────┘

This example demonstrates how to use the date_format_style setting to switch between MySQL and Oracle date formatting styles:

-- Default MySQL style date formatting
SELECT to_string('2024-04-05'::DATE, '%b');

┌────────────────────────────────┐
│ to_string('2024-04-05', '%b')  │
├────────────────────────────────┤
│ Apr                            │
└────────────────────────────────┘

-- Oracle style date formatting
SETTINGS (date_format_style = 'Oracle') SELECT to_string('2024-04-05'::DATE, 'MON');

┌────────────────────────────────┐
│ to_string('2024-04-05', 'MON') │
├────────────────────────────────┤
│ Apr                            │
└────────────────────────────────┘

This example shows how the week_start setting affects week-related date functions:

-- Default week_start = 1 (Monday as first day of week)
SELECT date_trunc(WEEK, to_date('2024-04-03'));  -- Wednesday

┌────────────────────────────────────────┐
│ date_trunc(WEEK, to_date('2024-04-03')) │
├────────────────────────────────────────┤
│ 2024-04-01                             │
└────────────────────────────────────────┘

-- Setting week_start = 0 (Sunday as first day of week)
SETTINGS (week_start = 0) SELECT date_trunc(WEEK, to_date('2024-04-03'));  -- Wednesday

┌────────────────────────────────────────┐
│ date_trunc(WEEK, to_date('2024-04-03')) │
├────────────────────────────────────────┤
│ 2024-03-31                             │
└────────────────────────────────────────┘

This example allows the COPY INTO operation to utilize up to 100 threads for parallel processing:

SETTINGS (max_threads = 100) COPY INTO ...