Trading calendars

Say which days a market trades - its weekdays, how they changed over the years, and its holidays - and ask it in SQL and in timers.

Category: Time-series
Keywords: missing days, gaps, data gaps, missing bars, missing_bars, calendar, calendars, trading calendar, trading day, trading days, market days, business days, working days, holiday, holidays, era, eras, trading week, sun-thu, mon-fri, week change, NEPSE, CREATE CALENDAR, ALTER CALENDAR, DROP CALENDAR, ADD HOLIDAY, ADD TRADING DAY, is_trading_day, next_trading_day, prev_trading_day, trading_days_between, suggest_calendar_eras, kdb_calendars, trading nepse 15:05
See also: Date and time functions, Timers, Time and time zones, SAMPLE BY

A trading calendar says which days a market trades. It lives in one database, next to its tables, and SQL functions and timers read it.

A market's week can change. NEPSE traded Sunday to Thursday until 2026-04-05, then Monday to Friday from 2026-04-10. So a calendar is a list of eras: each era says from which day on which weekdays trade. On top of the eras come single days:

Part	Example	Meaning
era	`ERA FROM '2022-01-01' DAYS sun-thu`	from this day on, these weekdays trade (until the next era)
holiday	`HOLIDAY '2026-10-20' 'Dashain'`	no trading on a day the era trades
extra trading day	`TRADING DAY '2022-01-07' 'Friday session'`	a session on a day the era does not trade
time zone	`TIME ZONE 'Asia/Kathmandu'`	which clock a `TIMESTAMP` is read on (UTC when not given)

A day before the first era is not a trading day (unless it is an extra trading day).
Weekdays: sun mon tue wed thu fri sat (or the full names). A range may go past Saturday: fri-sun. A list: mon,wed,fri.
Dates are written 'yyyy-mm-dd'.

CREATE CALENDAR

Syntax:

CREATE CALENDAR [IF NOT EXISTS] name (
  [TIME ZONE 'zone',]
  ERA FROM 'date' DAYS weekdays [, ERA FROM 'date' DAYS weekdays ...]
  [, HOLIDAY 'date' ['note'] ...]
  [, TRADING DAY 'date' ['note'] ...]
)

A calendar needs at least one era. Two eras cannot start on the same day.
TIME ZONE takes any zone: 'Asia/Kathmandu', 'UTC', '+05:45', or one with summer time such as 'America/New_York' (a moment's day is on the offset in force then; summer time).
Role: ddl, like CREATE TYPE. It is saved at once, so it cannot go inside BEGIN ... COMMIT.

Example: NEPSE: Sunday to Thursday, then Monday to Friday

CREATE CALENDAR nepse (
  TIME ZONE 'Asia/Kathmandu',
  ERA FROM '2022-01-01' DAYS sun-thu,
  ERA FROM '2026-04-10' DAYS mon-fri
);
SELECT is_trading_day('nepse', '2026-04-05') AS sun_before,
       is_trading_day('nepse', '2026-04-10') AS fri_after,
       is_trading_day('nepse', '2026-04-12') AS sun_after;

Result:

CREATE CALENDAR nepse (TIME ZONE 'Asia/Kathmandu', ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

SELECT is_trading_day('nepse', '2026-04-05') AS sun_before, is_trading_day('nepse', '2026-04-10') AS

sun_before (BOOL)	fri_after (BOOL)	sun_after (BOOL)
true	true	false

(1 row)

ALTER CALENDAR

Syntax:

ALTER CALENDAR [IF EXISTS] name ADD HOLIDAY 'date' ['note'] [, 'date' ['note'] ...]
ALTER CALENDAR [IF EXISTS] name DROP HOLIDAY 'date' [, 'date' ...]
ALTER CALENDAR [IF EXISTS] name ADD TRADING DAY 'date' ['note'] [, ...]
ALTER CALENDAR [IF EXISTS] name DROP TRADING DAY 'date' [, ...]
ALTER CALENDAR [IF EXISTS] name ADD ERA FROM 'date' DAYS weekdays
ALTER CALENDAR [IF EXISTS] name DROP ERA FROM 'date'
ALTER CALENDAR [IF EXISTS] name SET TIME ZONE 'zone'

Rule	What happens
A holiday that is there already	its note is replaced
A day that is a holiday and is added as a trading day (or the other way)	refused: drop it first
A holiday on a day its era does not trade anyway (a Saturday)	kept, and the answer says so
`DROP HOLIDAY` of a day that is not a holiday	refused
`DROP ERA` of the last era	refused: a calendar needs one era
A day listed twice in one statement	refused

The next query sees the change (also a cached result).

Example: Holidays, one statement for many

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS sun-thu, ERA FROM '2026-04-10' DAYS mon-fri);
ALTER CALENDAR nepse ADD HOLIDAY '2026-10-20' 'Dashain', '2026-10-21' 'Dashain', '2026-10-22' 'Dashain';
SELECT is_trading_day('nepse', '2026-10-21') AS holiday,
       next_trading_day('nepse', '2026-10-19') AS after_dashain;

Result:

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2026-04-10' DAYS 'mon-fri')

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

ALTER CALENDAR nepse ADD HOLIDAY '2026-10-20' 'Dashain', '2026-10-21' 'Dashain', '2026-10-22' 'Dasha

calendar 'nepse' altered: 3 holiday(s) added

SELECT is_trading_day('nepse', '2026-10-21') AS holiday, next_trading_day('nepse', '2026-10-19') AS

holiday (BOOL)	after_dashain (DATE)
false	2026-10-23

(1 row)

Example: An extra Friday session in the Sunday-Thursday years

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS sun-thu, ERA FROM '2026-04-10' DAYS mon-fri);
ALTER CALENDAR nepse ADD TRADING DAY '2022-01-07' 'Friday session';
SELECT is_trading_day('nepse', '2022-01-07') AS extra, is_trading_day('nepse', '2022-01-14') AS plain_friday;

Result:

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2026-04-10' DAYS 'mon-fri')

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

ALTER CALENDAR nepse ADD TRADING DAY '2022-01-07' 'Friday session'

calendar 'nepse' altered: 1 extra trading day(s) added

SELECT is_trading_day('nepse', '2022-01-07') AS extra, is_trading_day('nepse', '2022-01-14') AS plai

extra (BOOL)	plain_friday (BOOL)
true	false

(1 row)

Example: A holiday cannot also be a trading day (shows an error)

Setup:

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS sun-thu);
ALTER CALENDAR nepse ADD HOLIDAY '2026-10-20';

ALTER CALENDAR nepse ADD TRADING DAY '2026-10-20';

Result:

Error: '2026-10-20' is a holiday of calendar nepse; DROP HOLIDAY '2026-10-20' first

DROP CALENDAR

Syntax:

DROP CALENDAR [IF EXISTS] name [, name ...]

A timer with a trading schedule on a dropped calendar fails every day and says why, until you make the calendar again or change the schedule.

The functions

The calendar is named as text. It may come first or last: is_trading_day('nepse', ts) and is_trading_day(ts, 'nepse') are the same.

Function	Returns	Meaning
`is_trading_day(cal, d)`	`BOOL`	`d` is a trading day
`next_trading_day(cal, d)`	`DATE`	the first trading day after `d`
`prev_trading_day(cal, d)`	`DATE`	the last trading day before `d`; `NULL` before the first one
`trading_days_between(cal, from, to)`	`LONG`	trading days `d` with `from <= d < to`; negative when `to` is before `from` (the rule of NumPy's `busday_count`)

Which day: a DATE or '2026-04-10' is that day. A TIMESTAMP is the day it falls on in the calendar's time zone: 2026-04-10 20:00 UTC is Saturday 01:45 in Kathmandu.
NULL: any NULL argument gives NULL.
Errors: a calendar that does not exist is an error, never a quiet false.
is_trading_day(t) with one argument is the old fixed Sunday-to-Thursday week on the UTC date, with no holidays (date functions).
Speed: the calendar is looked up once per query and thread; each row is a few comparisons. trading_days_between counts whole weeks at once, so a range of ten years costs no more than a week.

Example: Around the week change

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS sun-thu, ERA FROM '2026-04-10' DAYS mon-fri);
SELECT next_trading_day('nepse', '2026-04-02') AS after_thu,
       next_trading_day('nepse', '2026-04-10') AS after_fri,
       prev_trading_day('nepse', '2026-04-13') AS before_mon,
       trading_days_between('nepse', '2026-04-01', '2026-04-15') AS days_1_to_14;

Result:

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2026-04-10' DAYS 'mon-fri')

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

SELECT next_trading_day('nepse', '2026-04-02') AS after_thu, next_trading_day('nepse', '2026-04-10')

after_thu (DATE)	after_fri (DATE)	before_mon (DATE)	days_1_to_14 (LONG)
2026-04-05	2026-04-13	2026-04-10	10

(1 row)

Example: Rows that fall on a day the market was closed

CREATE CALENDAR nepse (TIME ZONE 'Asia/Kathmandu', ERA FROM '2022-01-01' DAYS sun-thu, ERA FROM '2026-04-10' DAYS mon-fri);
CREATE TABLE ticks (ts TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESTAMP(ts) PARTITION BY DAY;
INSERT INTO ticks VALUES ('2026-04-09T05:15:00Z', 'CITY', 510), ('2026-04-10T05:15:00Z', 'CITY', 512),
                         ('2026-04-11T05:15:00Z', 'CITY', 511), ('2026-04-12T05:15:00Z', 'CITY', 513);
SELECT ts, price FROM ticks WHERE NOT is_trading_day('nepse', ts) ORDER BY ts;

Result:

CREATE CALENDAR nepse (TIME ZONE 'Asia/Kathmandu', ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

CREATE TABLE ticks (

table 'ticks' created

INSERT INTO ticks VALUES ('2026-04-09T05:15:00Z', 'CITY', 510), ('2026-04-10T05:15:00Z', 'CITY', 512

4 row(s) inserted

SELECT ts, price FROM ticks WHERE NOT is_trading_day('nepse', ts) ORDER BY ts

ts (TIMESTAMP)	price (DOUBLE)
2026-04-11T05:15:00.000Z	511
2026-04-12T05:15:00.000Z	513

(2 rows)

Find missing days

A trading day with no row is a gap in your data (a feed that stopped, a day not loaded). List the calendar's trading days between a symbol's first and last row with generate_series, and keep those without a row:

Example: Trading days of CITY with no daily bar

CREATE CALENDAR nepse (TIME ZONE 'Asia/Kathmandu', ERA FROM '2026-04-10' DAYS mon-fri, HOLIDAY '2026-09-24' 'holiday');
CREATE TABLE day_bars (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO day_bars VALUES ('2026-09-21', 'CITY', 510), ('2026-09-23', 'CITY', 512), ('2026-09-25', 'CITY', 515);
SELECT d AS missing_day
FROM generate_series((SELECT min(time) FROM day_bars WHERE symbol = 'CITY')::DATE,
                     (SELECT max(time) FROM day_bars WHERE symbol = 'CITY')::DATE) AS d
WHERE is_trading_day('nepse', d)
  AND d NOT IN (SELECT time::DATE FROM day_bars WHERE symbol = 'CITY');

Result:

CREATE CALENDAR nepse (TIME ZONE 'Asia/Kathmandu', ERA FROM '2026-04-10' DAYS 'mon-fri', HOLIDAY '20

calendar 'nepse' created: 1 era(s), 1 holiday(s), 0 extra trading day(s)

CREATE TABLE day_bars (

table 'day_bars' created

INSERT INTO day_bars VALUES ('2026-09-21', 'CITY', 510), ('2026-09-23', 'CITY', 512), ('2026-09-25',

3 row(s) inserted

SELECT d AS missing_day FROM generate_series(CAST((SELECT min(time) FROM day_bars WHERE symbol = 'CI

missing_day (DATE)
2026-09-22

(1 row)

22 September is missing; 24 September is a holiday, so it is not.

See the calendars: kdb_calendars

SELECT * FROM kdb_calendars lists every calendar of the database: one row per era, holiday and extra trading day.

Column	Meaning
`calendar`	the calendar's name
`time_zone`	its zone (`UTC` when none was given)
`kind`	`era`, `holiday` or `trading day`
`day`	the era's first day, or the day
`days`	an era's weekdays (`sun-thu`)
`note`	the note of a holiday or trading day

Example: What the calendar holds

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS sun-thu, ERA FROM '2026-04-10' DAYS mon-fri);
ALTER CALENDAR nepse ADD HOLIDAY '2026-10-20' 'Dashain';
SELECT kind, day, days, note FROM kdb_calendars WHERE calendar = 'nepse' ORDER BY day;

Result:

CREATE CALENDAR nepse (ERA FROM '2022-01-01' DAYS 'sun-thu', ERA FROM '2026-04-10' DAYS 'mon-fri')

calendar 'nepse' created: 2 era(s), 0 holiday(s), 0 extra trading day(s)

ALTER CALENDAR nepse ADD HOLIDAY '2026-10-20' 'Dashain'

calendar 'nepse' altered: 1 holiday(s) added

SELECT kind, day, days, note FROM kdb_calendars WHERE calendar = 'nepse' ORDER BY day

kind (STRING)	day (DATE)	days (STRING)	note (STRING)
era	2022-01-01	sun-thu	NULL
era	2026-04-10	mon-fri	NULL
holiday	2026-10-20	NULL	Dashain

(3 rows)

Find the eras in your data: suggest_calendar_eras

Syntax:

suggest_calendar_eras(time [, 'zone'])

An aggregate: it looks at the days that have rows and writes a report as text:

how many days of each weekday have data, per year, and how many of the era's weekdays have no data (holidays, or gaps in your data);
the eras it found, and the days with data outside their era's weekdays (extra sessions);
a CREATE CALENDAR statement to start from (and ADD TRADING DAY for the extra sessions).

How it decides: each week gets the weekdays that have data in most of the weeks around it (4 before, 4 after), so one holiday or one extra session does not count. Weeks in a row with the same weekdays are an era; an era is at least 8 weeks. Where two eras meet, the new era starts on the day that leaves the fewest days outside their era.

Give the zone of the market when the times are UTC and a session could cross midnight UTC. The day is the local day in that zone.
Check the days without data before you add them as holidays: they may be gaps in your data.

Example: The weekdays of the sample candles (sample data: sample)

SELECT suggest_calendar_eras(time, 'Asia/Kathmandu') AS report FROM ochl_day;

Result:

report (STRING)
year sun mon tue wed thu fri sat no_data 2025 1 1 1 1 0 0 0 0 2026 3 3 3 3 3 0 0 166 eras: 2025-12-28 sun-thu era ...

(1 row)

Timers on trading days

A timer with the schedule trading nepse 15:05 runs at 15:05 on the trading days of calendar nepse of its database only. It follows the calendar as it changes: a holiday added in the morning is skipped that afternoon. When the calendar is dropped, each run fails and says so.

How it is stored

Calendars are part of the database's catalog, like tables and ENUM types: in every backup, kept across restarts, and replayed from the log after a crash. A database with a calendar needs a KDB from 2026-09-25 or newer (feature calendars, see versions).