Basic Calendar Queries

Start here to learn the fundamentals of querying your church’s calendar and facility data. Each example builds your confidence with SQL while solving real scheduling challenges.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Calendar module live in the planning_center schema. Always prefix table names with planning_center. in your SQL. ✅ CORRECT: SELECT * FROM planning_center.calendar_events ❌ INCORRECT: SELECT * FROM calendar_events

Row Level Security (RLS)

Row Level Security automatically handles:

tenant_organization_id – isolates data to your organization
system_status – returns active records by default

Do not duplicate these filters in your queries—RLS already enforces them and extra predicates can hide data or slow execution:

❌ WHERE tenant_organization_id = 1
❌ WHERE system_status = 'active'

Let RLS govern tenant and status filtering so you can focus on scheduling logic and resource constraints.

Viewing Events
Event Instances and Scheduling
Resources and Rooms
Resource Bookings
Date and Time Queries
Tags and Categories

Viewing Events

List All Events

-- View all your church's events
SELECT
    event_id,
    name,
    TRIM(REGEXP_REPLACE(REGEXP_REPLACE(description, '<[^>]*>', '', 'g'), '\s+', ' ', 'g')) as description,
    approval_status,
    visible_in_church_center,
    created_at
FROM planning_center.calendar_events
ORDER BY created_at DESC
LIMIT 50;

Active and Approved Events

-- Only show approved events visible to the congregation
SELECT
    event_id,
    name,
    TRIM(REGEXP_REPLACE(REGEXP_REPLACE(description, '<[^>]*>', '', 'g'), '\s+', ' ', 'g')) as description,
    featured,  -- Featured events for promotion
    image_url
FROM planning_center.calendar_events
WHERE approval_status = 'A'  -- A = Approved
  AND visible_in_church_center = true
ORDER BY name;

Search Events by Name

-- Find events containing specific keywords
SELECT
    event_id,
    name,
    TRIM(REGEXP_REPLACE(REGEXP_REPLACE(description, '<[^>]*>', '', 'g'), '\s+', ' ', 'g')) as description,
    summary
FROM planning_center.calendar_events
WHERE LOWER(name) LIKE '%youth%'
   OR LOWER(REGEXP_REPLACE(description, '<[^>]*>', '', 'g')) LIKE '%youth%'
ORDER BY name;

Event Instances and Scheduling

Upcoming Event Instances

-- Get the next 20 scheduled events
SELECT
    ei.event_instance_id,
    e.name as event_name,
    ei.starts_at,
    ei.ends_at,
    ei.location,
    ei.all_day_event,
    EXTRACT(EPOCH FROM (ei.ends_at - ei.starts_at))/3600 as duration_hours
FROM planning_center.calendar_event_instances ei
JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
WHERE ei.starts_at >= CURRENT_TIMESTAMP
ORDER BY ei.starts_at
LIMIT 20;

This Week’s Schedule

-- All events for the current week
SELECT
    e.name,
    ei.starts_at,
    ei.ends_at,
    ei.location,
    CASE
        WHEN ei.all_day_event THEN 'All Day'
        ELSE TO_CHAR(ei.starts_at, 'HH12:MI AM')
    END as start_time
FROM planning_center.calendar_event_instances ei
JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
WHERE ei.starts_at >= DATE_TRUNC('week', CURRENT_DATE)
  AND ei.starts_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'
ORDER BY ei.starts_at;

Recurring Events

-- Find all recurring events and their patterns
SELECT
    e.name,
    ei.recurrence,
    ei.recurrence_description,
    COUNT(*) as instance_count,
    MIN(ei.starts_at) as first_occurrence,
    MAX(ei.starts_at) as last_occurrence
FROM planning_center.calendar_event_instances ei
JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
WHERE ei.recurrence IS NOT NULL
GROUP BY e.event_id, e.name, ei.recurrence, ei.recurrence_description
ORDER BY instance_count DESC;

Events by Day of Week

-- See which days are busiest
SELECT
    TO_CHAR(starts_at, 'Day') as day_of_week,
    EXTRACT(DOW FROM starts_at) as day_number,
    COUNT(*) as event_count
FROM planning_center.calendar_event_instances
WHERE starts_at >= CURRENT_DATE - INTERVAL '90 days'
  AND starts_at <= CURRENT_DATE + INTERVAL '30 days'
GROUP BY TO_CHAR(starts_at, 'Day'), EXTRACT(DOW FROM starts_at)
ORDER BY day_number;

Resources and Rooms

List All Resources

-- View all bookable resources
SELECT
    resource_id,
    name,
    kind,  -- Room, Equipment, etc.
    description,
    quantity,
    home_location
FROM planning_center.calendar_resources
ORDER BY kind, name;

Available Rooms

-- Find all rooms and their details
SELECT
    resource_id,
    name,
    description,
    home_location,
    quantity as capacity,
    path_name  -- Location hierarchy
FROM planning_center.calendar_resources
WHERE kind = 'Room'
ORDER BY name;

Equipment Inventory

-- List all equipment resources
SELECT
    resource_id,
    name,
    description,
    quantity,
    serial_number,
    home_location
FROM planning_center.calendar_resources
WHERE kind = 'Equipment'
   OR kind = 'Resource'
ORDER BY name;

Resources Expiring Soon

-- Resources with expiration dates (like rentals or leases)
SELECT
    name,
    kind,
    expires_at,
    CASE
        WHEN expires_at < CURRENT_DATE THEN 'Expired'
        WHEN expires_at < CURRENT_DATE + INTERVAL '30 days' THEN 'Expiring Soon'
        ELSE 'Active'
    END as status
FROM planning_center.calendar_resources
WHERE expires_at IS NOT NULL
ORDER BY expires_at;

Resource Bookings

Current Bookings

-- See what's booked right now
SELECT
    r.name as resource_name,
    r.kind as resource_type,
    rb.starts_at,
    rb.ends_at,
    rb.quantity as quantity_booked
FROM planning_center.calendar_resource_bookings rb
JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
WHERE rb.starts_at <= CURRENT_TIMESTAMP
  AND rb.ends_at >= CURRENT_TIMESTAMP
ORDER BY r.name;

Room Schedule for Specific Date

-- Check room bookings for a specific date
SELECT
    r.name as room_name,
    rb.starts_at::time as start_time,
    rb.ends_at::time as end_time,
    ei.location,
    e.name as event_name
FROM planning_center.calendar_resource_bookings rb
JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
JOIN planning_center.calendar_event_instances ei ON rb.event_instance_id = ei.event_instance_id
JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
WHERE r.kind = 'Room'
  AND DATE(rb.starts_at) = '2024-01-15'  -- Change to your date
ORDER BY r.name, rb.starts_at;

Find Available Resources

-- Find resources NOT booked during a specific time
SELECT
    r.resource_id,
    r.name,
    r.kind,
    r.quantity
FROM planning_center.calendar_resources r
WHERE r.kind = 'Room'  -- Change to desired resource type
  AND NOT EXISTS (
    SELECT 1
    FROM planning_center.calendar_resource_bookings rb
    WHERE rb.resource_id = r.resource_id
      AND rb.starts_at < '2024-01-15 14:00:00'::timestamp  -- Your end time
      AND rb.ends_at > '2024-01-15 12:00:00'::timestamp     -- Your start time
  )
ORDER BY r.name;

Resource Utilization Summary

-- See how often each resource is used
SELECT
    r.name as resource_name,
    r.kind as resource_type,
    COUNT(rb.resource_booking_id) as total_bookings,
    SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours_booked,
    MIN(rb.starts_at) as first_booking,
    MAX(rb.ends_at) as last_booking
FROM planning_center.calendar_resources r
LEFT JOIN planning_center.calendar_resource_bookings rb ON r.resource_id = rb.resource_id
GROUP BY r.resource_id, r.name, r.kind
ORDER BY total_bookings DESC;

Date and Time Queries

Today’s Events

-- All events happening today
SELECT
    e.name,
    CASE
        WHEN ei.all_day_event THEN 'All Day'
        ELSE TO_CHAR(ei.starts_at, 'HH12:MI AM')
    END as start_time,
    ei.location
FROM planning_center.calendar_events e
JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
WHERE (DATE(ei.starts_at) = CURRENT_DATE
       OR (ei.all_day_event = true
           AND DATE(ei.starts_at) <= CURRENT_DATE
           AND DATE(ei.ends_at) >= CURRENT_DATE))
ORDER BY ei.all_day_event DESC, ei.starts_at;

This Month’s Events

-- All events in the current month
SELECT
    e.name,
    ei.starts_at,
    ei.location,
    ei.all_day_event
FROM planning_center.calendar_events e
JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
WHERE ei.starts_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND ei.starts_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
ORDER BY ei.starts_at;

Weekend Events

-- Events on Saturdays and Sundays
SELECT
    e.name,
    ei.starts_at,
    TO_CHAR(ei.starts_at, 'Day') as day_name,
    ei.location
FROM planning_center.calendar_events e
JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
WHERE EXTRACT(DOW FROM ei.starts_at) IN (0, 6)  -- 0 = Sunday, 6 = Saturday
  AND ei.starts_at >= CURRENT_DATE
  AND ei.starts_at < CURRENT_DATE + INTERVAL '30 days'
ORDER BY ei.starts_at;

Events by Time of Day

-- Categorize events by time of day
SELECT
    CASE
        WHEN EXTRACT(HOUR FROM starts_at) < 12 THEN 'Morning'
        WHEN EXTRACT(HOUR FROM starts_at) < 17 THEN 'Afternoon'
        ELSE 'Evening'
    END as time_of_day,
    COUNT(*) as event_count
FROM planning_center.calendar_event_instances
WHERE starts_at >= CURRENT_DATE - INTERVAL '30 days'
  AND all_day_event = false
GROUP BY time_of_day
ORDER BY
    CASE time_of_day
        WHEN 'Morning' THEN 1
        WHEN 'Afternoon' THEN 2
        WHEN 'Evening' THEN 3
    END;

Tags and Categories

Events with Tags

-- Find events by their tags (using relationship tables)
SELECT DISTINCT
    e.name as event_name,
    t.name as tag_name,
    tg.name as tag_group
FROM planning_center.calendar_events e
JOIN planning_center.calendar_events_relationships er
    ON e.event_id = er.event_id
    AND er.relationship_type = 'Tag'
JOIN planning_center.calendar_tags t
    ON er.relationship_id = t.tag_id
LEFT JOIN planning_center.calendar_tag_groups tg
    ON t.tag_group_id = tg.tag_group_id
ORDER BY e.name, tg.name, t.name;

Popular Tags

-- See which tags are used most
SELECT
    t.name as tag_name,
    tg.name as tag_group,
    COUNT(DISTINCT er.event_id) as event_count
FROM planning_center.calendar_tags t
LEFT JOIN planning_center.calendar_tag_groups tg
    ON t.tag_group_id = tg.tag_group_id
LEFT JOIN planning_center.calendar_events_relationships er
    ON t.tag_id = er.relationship_id
    AND er.relationship_type = 'Tag'
GROUP BY t.tag_id, t.name, tg.name
ORDER BY event_count DESC;

Tips for Writing Calendar Queries

1. Handle All-Day Events

-- All-day events span from start date to end date
WHERE all_day_event = true
  AND DATE(starts_at) <= '2024-01-15'
  AND DATE(ends_at) >= '2024-01-15'

2. Work with Timestamps

-- Extract useful parts from timestamps
SELECT
    DATE(starts_at) as event_date,
    starts_at::time as start_time,
    TO_CHAR(starts_at, 'Day') as day_name,
    EXTRACT(HOUR FROM starts_at) as hour_of_day

3. Calculate Duration

-- Get event duration in hours
EXTRACT(EPOCH FROM (ends_at - starts_at))/3600 as hours

4. Check Resource Availability

-- Ensure no overlapping bookings
WHERE NOT EXISTS (
    SELECT 1 FROM calendar_resource_bookings rb
    WHERE rb.resource_id = r.resource_id
      AND rb.starts_at < [your_end_time]
      AND rb.ends_at > [your_start_time]
)

5. Format Times for Display

-- User-friendly time formats
TO_CHAR(starts_at, 'HH12:MI AM') as time_12hr,  -- 02:30 PM
TO_CHAR(starts_at, 'HH24:MI') as time_24hr,     -- 14:30
TO_CHAR(starts_at, 'Mon DD, YYYY') as date_str  -- Jan 15, 2024

Common Issues & Solutions

Issue: Events appearing multiple times

Solution: You might be joining to event instances - use DISTINCT or GROUP BY.

Issue: Missing recurring events

Solution: Check the date range includes future instances, not just the master event.

Issue: Resources showing as available when booked

Solution: Ensure your time comparison includes both start AND end times for overlaps.

Issue: All-day events not showing

Solution: Remember to check both the date AND the all_day_event flag.

Next Steps

Ready for more complex queries? Check out:

Advanced Queries - Conflict detection, optimization, and analytics
Reporting Examples - Complete reports you can use immediately

​Basic Calendar Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Viewing Events

​List All Events

​Active and Approved Events

​Search Events by Name

​Event Instances and Scheduling

​Upcoming Event Instances

​This Week’s Schedule

​Recurring Events

​Events by Day of Week

​Resources and Rooms

​List All Resources

​Available Rooms

​Equipment Inventory

​Resources Expiring Soon

​Resource Bookings

​Current Bookings

​Room Schedule for Specific Date

​Find Available Resources

​Resource Utilization Summary

​Date and Time Queries

​Today’s Events

​This Month’s Events

​Weekend Events

​Events by Time of Day

​Tags and Categories

​Events with Tags

​Popular Tags

​Tips for Writing Calendar Queries

​1. Handle All-Day Events

​2. Work with Timestamps

​3. Calculate Duration

​4. Check Resource Availability

​5. Format Times for Display

​Common Issues & Solutions

​Issue: Events appearing multiple times

​Issue: Missing recurring events

​Issue: Resources showing as available when booked

​Issue: All-day events not showing

​Next Steps