GitHub – sirrodgepodge/rrule_plpgsql

Pure PL/pgSQL implementation of the iCalendar RRULE (RFC 5545) for PostgreSQL. No C extensions, no compilation, works everywhere.

This library provides complete RRULE iteration calculation functionality using pure PL/pgSQL. Use this implementation for all environments (local, development, staging, production) to ensure continuity.

key features:

• No C extensions needed – Pure PL/pgSQL, works on any PostgreSQL
• Full timezone support with DST handling – Wall-clock time preserved (in DST transition (remains from 10am to 10am))
• same everywhere – Same implementation in all environments
• ready for production – Comprehensive test suite with 187 tests (including table operation integration tests)
• Compliant with RFC 5545 and RFC 7529 – Supports standard RRULE pattern plus SKIP/RSCALE
• 50-75x faster than Node.js – Excellent performance without compilation
• Works on managed services – AlloyDB, RDS, Azure Database for PostgreSQL

Calculations where your data lives Provides performance impossible with external processing:

• set-based operations: Join iteration rules against any table without events, bookings, or data transfer.
• Origin/aggregation:Filter by event dates, event count, GROUP BY – all in SQL
• batch processing: Process 100+ schedules in a single query without round trips
• Memory-Efficient Streaming:SETOF returns results sequentially, not simultaneously
• 50-75x faster than Node.js For single-schedule operations, infinitely faster for multi-schedule batch queries

See example usage for practical patterns including subscription billing, batch updates, and conflict detection.

Pure PL/pgSQL means:

• ✅ No C compiler or build tools required
• ✅ Install with a single SQL file – no configuration
• ✅ Consistent behavior across all environments (development, staging, production)

Universal Compatibility:

• google alloydb – High-performance PostgreSQL with AI integration
• Google Cloud SQL – Fully managed PostgreSQL service
• Amazon Aurora PostgreSQL – Serverless PostgreSQL with automatic scaling
• Amazon RDS PostgreSQL – Managed PostgreSQL without custom extensions
• Azure Database for PostgreSQL – Fully supported managed service
• Self-Hosted PostgreSQL – No special configuration required
• Docker PostgreSQL – Postgres standard image compatibility

npm install rrule-plpgsql

Then install into your database using your ORM/client. Look installation Guide For TypeScript/ORM integration.

# Install via psql
psql -d your_database -f src/install.sql

# Or use curl for one-line install
curl -sL https://raw.githubusercontent.com/sirrodgepodge/rrule_plpgsql/main/src/install.sql | psql -d your_database

Next Steps: See example usage for practical patterns.

✅ Production Ready (Always Enabled):

• FREQ=DAILY – Daily recurrence with date/time filter
• FREQ=WEEKLY – Weekly recurrence with selection of day of the week
• FREQ=MONTHLY – Monthly recurrence with selection of day/week of the month
• FREQ=YEARLY – Annual recurrence with month/day/week selection

Advanced (optional, disabled by default):

• FREQ=HOURLY – Hourly Repetitions (8,760/year maximum)
• FREQ=MINUTELY – Minute repetition (525,600/year maximum)
• FREQ=SECONDLY – Second iteration (31M/year max)

Look sub-day operations guide To enable these safely.

• COUNT – Limit the number of events
• UNTIL – Last date for revision
• INTERVAL – Frequency multiplier (every N days/weeks/months)
• BYDAY – Filter by day of the week (MO, TU, WE, TH, FR, SA, SU)
• BYDAY With ordinals – weekday status (2MO = 2nd Monday, -1FR = last Friday)
• BYMONTHDAY – Filter by day of the month (1-31, -1 = last day)
• BYMONTH – Filter by month (1-12)
• BYYEARDAY – Filter by day of year (1-366, negative supported)
• BYWEEKNO – Filter by ISO week number (1-53, annual only)
• BYSETPOS – Select specific posts from the generated set
• WKST – Week start day (SU, MO, TU, WE, TH, FR, SA)
• SKIP – Invalid date management (OMIT, BACKWARD, FORWARD)
• TZID – Timezone specification with automatic DST handling
• RSCALE – Calendar system (Gregorian supported)

See RFC specific compliance for complete feature support metrics and examples.

Full RFC 5545 timezone support with automatic DST handling:

-- Meeting stays at 10 AM wall-clock time across DST boundary
SELECT * FROM rrule.all(
    'FREQ=DAILY;COUNT=3;TZID=America/New_York',
    '2025-03-08 10:00:00'::TIMESTAMP
);
-- Returns: March 8 (EST), March 9 (EDT), March 10 (EDT)
-- All at 10:00 AM wall-clock time

See the API reference for TIMESTAMPTZ API details.

psql -d your_database -f src/install.sql

These include:

• Daily, Weekly, Monthly, Yearly Frequencies
• All standard modifiers and filters
• Timezone support with DST handling
• Secure by default (no DoS vectors)

psql -d your_database -f src/install_with_subday.sql

Standard Plus includes:

• Hourly, Minute, Second Frequency
• Safety warnings during installation
• Requires application-level verification

Refer to the sub-day operations guide before using this installation.

Error: “Invalid rule: FREQ parameter is required”

• Each rule must start with FREQ
• fix: add FREQ=DAILY (or weekly/monthly/yearly)

Error: “COUNT and UNTIL are mutually exclusive”

• Both COUNT and UNTIL cannot be used in the same RRULE
• Fix: Use one of these COUNT=10 Or UNTIL=20251231T235959

Error: “BYWEEKNO can only be used with FREQ=YEARLY”

• BYWEEKNO requires annual frequency
• Fix: Change to FREQ=YEARLY;BYWEEKNO=10

See validation rules for complete error context.

All functions support both TIMESTAMP And TIMESTAMPTZ With automatic timezone management:

-- Generate occurrences
rrule.all(rrule, dtstart) → SETOF TIMESTAMP
rrule.between(rrule, dtstart, start, end) → SETOF TIMESTAMP

-- Query occurrences
rrule.after(rrule, dtstart, after_date) → TIMESTAMP
rrule.before(rrule, dtstart, before_date) → TIMESTAMP
rrule.next(rrule, dtstart) → TIMESTAMP
rrule.most_recent(rrule, dtstart) → TIMESTAMP

-- Utilities
rrule.count(rrule, dtstart) → INTEGER
rrule.overlaps(dtstart, dtend, rrule, mindate, maxdate) → BOOLEAN

-- Every Monday for 4 weeks
SELECT * FROM rrule.all(
    'FREQ=WEEKLY;BYDAY=MO;COUNT=4',
    '2025-01-06 10:00:00'::TIMESTAMP
);

-- With timezone support
SELECT * FROM rrule.all(
    'FREQ=DAILY;COUNT=5;TZID=America/New_York',
    '2025-03-08 10:00:00'::TIMESTAMP  -- DST handled automatically
);

See the API reference for complete function signatures, timezone management details, and advanced examples.

Contributions welcome! Please:

1. Run all tests (all 10 test suites must pass)
2. Add test coverage for new features
3. Update Document
4. Follow RFC 5545/7529 specifications
5. Submit a pull request

See the development guide for contribution guidelines and test suite details.

Single Schedule: Node.js 50-75x faster than rrule.js
Multi-Schedule Question: Completely eliminates application-database round trips

Scalability Benefits:

• Set-based batch operations: query 100+ schedules in parallel without loops
• Streaming results: SETOF returns sequentially, not all at once (persistent memory)
• Query planner optimization: PostgreSQL optimizes joins and filters with event expansion
• Quick-exit optimization: stops counting when COUNT/UNTIL limit is reached

See the Performance Guide for optimization strategies and the Development Guide for benchmarking details.

• PostgreSQL 12 or higher
• No C extensions needed
• no external dependencies
• Works on all PostgreSQL-compatible platforms

See the Development Guide for development setup instructions.