> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic queries

# 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.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  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.
</Card>

## 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.

## Table of Contents

* [Viewing Events](#viewing-events)
* [Event Instances and Scheduling](#event-instances-and-scheduling)
* [Resources and Rooms](#resources-and-rooms)
* [Resource Bookings](#resource-bookings)
* [Date and Time Queries](#date-and-time-queries)
* [Tags and Categories](#tags-and-categories)

## Viewing Events

### List All Events

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- Get event duration in hours
EXTRACT(EPOCH FROM (ends_at - starts_at))/3600 as hours
```

### 4. Check Resource Availability

```sql theme={null}
-- 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

```sql theme={null}
-- 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](/planning-center/calendar/advanced-queries) - Conflict detection, optimization, and analytics
* [Reporting Examples](/planning-center/calendar/reporting-examples) - Complete reports you can use immediately