Exchange calendars

import { EnterpriseNote } from "@site/src/components/EnterpriseNote"

Exchange calendars extend TICK interval syntax with real exchange trading schedules. Instead of manually specifying trading hours, day filters, and holidays, reference an exchange by its ISO 10383 MIC code:

-- NYSE regular trading hours for January, holidays excluded automatically
SELECT * FROM trades
WHERE ts IN '2025-01-[01..31]#XNYS';

This single expression generates interval scans for every trading session in January, automatically handling weekends, holidays (New Year's Day, MLK Day), and the exact trading hours including DST transitions.

Syntax

Exchange calendars use the # filter position in TICK expressions:

date [T time] [@timezone] [#EXCHANGE] [;duration]

The exchange code replaces the day-of-week filter (#workday, #Mon,Wed,Fri). You cannot combine an exchange calendar with a day-of-week filter in the same position.

-- Single day
WHERE ts IN '2025-01-24#XNYS'

-- Date range
WHERE ts IN '2025-01-[06..10]#XNYS'

-- Full month
WHERE ts IN '2025-03#XNYS'

-- Full year
WHERE ts IN '2025#XNYS'

Exchange codes are case-insensitive: #XNYS, #xnys, and #Xnys are all equivalent.

What the calendar provides

An exchange calendar defines, for each trading day of the year:

• Session open and close times in UTC
• Holiday closures (the day is skipped entirely)
• Early closes (shortened trading hours)
• Multiple sessions per day (e.g., morning and afternoon with a lunch break)
• DST transitions (UTC times shift when the exchange's local timezone changes clocks)

Single-session exchanges

Most exchanges have one continuous trading session per day. For example, NYSE (XNYS) trades from 9:30 AM to 4:00 PM Eastern Time:

WHERE ts IN '2025-01-24#XNYS'
-- Winter (EST, UTC-5): 14:30 - 21:00 UTC
-- Summer (EDT, UTC-4): 13:30 - 20:00 UTC

Multi-session exchanges

Some exchanges have a lunch break, producing two intervals per trading day. Hong Kong (XHKG) has morning and afternoon sessions:

WHERE ts IN '2025-02-03#XHKG'
-- Morning:   01:30 - 04:00 UTC  (09:30 - 12:00 HKT)
-- Afternoon: 05:00 - 08:00 UTC  (13:00 - 16:00 HKT)

Holidays and early closes

Exchange calendars automatically exclude holidays and apply early closes.

Holiday closure

The day is completely removed from results:

-- April 14-18, 2025: Good Friday (Apr 18) is a NYSE holiday
WHERE ts IN '2025-04-[14..18]#XNYS'
-- Returns intervals for Mon-Thu only; Friday is skipped

Early close

The session ends earlier than usual:

-- July 2-7, 2025: July 3 is an early close (1:00 PM ET), July 4 is closed
WHERE ts IN '2025-07-[02..07]#XNYS'
-- Jul 2 (Wed): 13:30 - 20:00 UTC  (normal)
-- Jul 3 (Thu): 13:30 - 17:00 UTC  (early close, 3h shorter)
-- Jul 4 (Fri): closed
-- Jul 7 (Mon): 13:30 - 20:00 UTC  (normal)

Multi-session early close

For exchanges with multiple sessions, an early close may mean only the morning session runs:

-- XHKG around Lunar New Year 2025
WHERE ts IN '2025-01-[27..31]#XHKG'
-- Jan 27 (Mon): morning 01:30-04:00 + afternoon 05:00-08:00 (normal)
-- Jan 28 (Tue): morning 01:30-04:00 only (LNY Eve, no afternoon session)
-- Jan 29-31:   closed (Lunar New Year holidays)

Interaction with TICK features

Exchange calendars combine with all standard TICK features. The behaviors specific to exchange calendars are described below.

Duration

The ;duration suffix extends the close of each trading session:

WHERE ts IN '2025-01-24#XNYS;1h'
-- Without duration: 14:30 - 21:00 UTC
-- With ;1h:         14:30 - 22:00 UTC

For multi-session exchanges, each session is extended independently. If extended sessions overlap, they merge into a single continuous interval:

WHERE ts IN '2025-01-24#XHKG;1h'
-- Morning:   01:30 - 05:00 UTC  (extended from 04:00)
-- Afternoon: 05:00 - 09:00 UTC  (extended from 08:00)

Non-trading days remain excluded even with a duration.

Timezone

The @timezone resolves the date to a UTC range first, then the exchange schedule intersects that range:

WHERE ts IN '2025-01-24@-05:00#XNYS'
-- Jan 24 in EST = 05:00Z Jan 24 to 05:00Z Jan 25
-- Intersected with NYSE hours: 14:30 - 21:00 UTC on Jan 24

This matters when timezone offsets shift a date across midnight UTC. A large positive offset like @+14:00 causes the UTC range to span two calendar days, so trading sessions from both days may appear.

Time suffix

A T time suffix is intersected with trading sessions. Times outside trading hours produce an empty result:

-- 15:00 UTC is within NYSE hours
WHERE ts IN '2025-01-24T15:00#XNYS'
-- Result: 15:00 - 15:59:59.999999 UTC

-- 04:30 UTC falls in the XHKG lunch break
WHERE ts IN '2025-02-03T04:30#XHKG'
-- Result: [] (empty)

Per-element filters

Each element in a date list can specify its own exchange. A per-element filter takes precedence over a global filter for that element:

-- Different exchanges per date
WHERE ts IN '[2025-01-24#XNYS, 2025-02-03#XHKG]'

-- Per-element overrides global
WHERE ts IN '[2025-01-24#XNYS, 2025-02-03]#XHKG'
-- Jan 24 uses XNYS; Feb 3 uses XHKG

Custom calendars

Built-in schedules

QuestDB Enterprise ships with a Parquet file inside the JAR containing pre-configured schedules for major exchanges. On every startup, this file is extracted to:

<dbRoot>/import/.questdb-internal/tick_calendars.parquet

The file is overwritten on each restart, so any manual edits to it are lost. To customize schedules, use the _tick_calendars_custom table described below. Custom entries are merged with the built-in data at query time, and take precedence when both define the same session.

Setup

Call reload_tick_calendars() to create the table where you'll put your custom calendar data:

SELECT reload_tick_calendars();

This function creates the _tick_calendars_custom table if it does not exist. It requires system admin privileges. You'll use the same function to reload the calendars after you make changes to this table.

Custom table schema

The _tick_calendars_custom table has the following columns:

The session column is the merge key. When a custom row has the same exchange and session as a built-in entry, the custom row takes precedence.

Add a session

Insert a row with the exchange, session date, and UTC timestamps:

-- Add a Saturday trading session to NYSE
INSERT INTO _tick_calendars_custom
    (exchange, session, open, close)
VALUES
    ('XNYS', '2025-01-25',
     '2025-01-25T10:00:00.000000Z', '2025-01-25T14:00:00.000000Z');

SELECT reload_tick_calendars();

After reloading, 2025-01-25#XNYS returns a 10:00-14:00 UTC session instead of being empty.

Override a built-in session

Insert a custom row with the same session key to replace it:

-- Override NYSE Jan 27: late open at 16:00 instead of 14:30
INSERT INTO _tick_calendars_custom
    (exchange, session, open, close)
VALUES
    ('XNYS', '2025-01-27',
     '2025-01-27T16:00:00.000000Z', '2025-01-27T21:00:00.000000Z');

SELECT reload_tick_calendars();

Remove a built-in session

Insert a row with all four timestamp columns left as NULL:

-- Close NYSE on Jan 27 (remove the built-in session entirely)
INSERT INTO _tick_calendars_custom
    (exchange, session)
VALUES
    ('XNYS', '2025-01-27');

SELECT reload_tick_calendars();

Define a custom exchange

You can define entirely new exchange codes not present in the built-in data:

-- Define a custom exchange with a lunch break
INSERT INTO _tick_calendars_custom
    (exchange, session, open, break_start, break_end, close)
VALUES
    ('MINE', '2025-03-03',
     '2025-03-03T09:00:00.000000Z', '2025-03-03T12:00:00.000000Z',
     '2025-03-03T13:00:00.000000Z', '2025-03-03T17:00:00.000000Z'),
    ('MINE', '2025-03-04',
     '2025-03-04T09:00:00.000000Z', '2025-03-04T12:00:00.000000Z',
     '2025-03-04T13:00:00.000000Z', '2025-03-04T17:00:00.000000Z');

SELECT reload_tick_calendars();

Then use it like any other exchange:

SELECT * FROM trades WHERE ts IN '2025-03-[03..04]#MINE';

Undo a custom override

QuestDB does not support DELETE. Instead, the deleted column provides soft-delete semantics. To restore a built-in session after overriding it, mark the custom row as deleted:

UPDATE _tick_calendars_custom
SET deleted = true
WHERE exchange = 'XNYS' AND session = '2025-01-27';

SELECT reload_tick_calendars();

The built-in session is restored because deleted rows are excluded from the merge.

Inspect effective schedules

Use tick_calendars() to view the merged result of built-in and custom data:

-- All effective sessions for NYSE
SELECT * FROM tick_calendars() WHERE exchange = 'XNYS';

-- Check a specific session
SELECT * FROM tick_calendars()
WHERE exchange = 'XNYS' AND session = '2025-01-27';

The function returns one row per session with columns: exchange, session, open, break_start, break_end, close.

Validation rules

Custom rows are validated on load. Invalid rows are skipped with a log warning:

• open and close must both be NULL (removal) or both non-NULL
• break_start and break_end must both be NULL or both non-NULL
• When non-NULL: open < break_start < break_end < close (or open < close without a break)
• Session duration must be less than 24 hours
• If multiple non-deleted rows share the same exchange and session, the last row wins

:::warning Changes require reload

Custom calendar changes are not applied automatically. You must call reload_tick_calendars() after modifying the _tick_calendars_custom table. Until then, queries continue using the cached schedules.

:::

Processing order

Within a TICK expression, components are applied in this order:

1. Date and time are parsed as local time (or UTC if no timezone is given)
2. Timezone (@) converts local time intervals to UTC
3. Exchange calendar (#EXCHANGE) intersects with the UTC trading sessions
4. Duration (;) extends the close of each resulting interval
5. Interval merging combines any overlapping intervals

:::note Exchange calendars vs day-of-week filters

Day-of-week filters like #workday apply to the local date (before timezone conversion), so "Monday" means Monday in the specified timezone. Exchange calendars apply after conversion to UTC, because exchange schedules are defined in UTC.

:::

See also

• TICK interval syntax — Full TICK syntax reference
• Interval scan — How QuestDB optimizes time queries