alarma / events-query.sql

-- Query Evolution Data Server calendar cache for events
-- Hardcoded date range: 2025-12-25 to 2026-03-31 (extended to catch recurring events)
-- This query works against ~/.cache/evolution/calendar/{hash}/cache.db

SELECT 
    ECacheUID,
    summary,
    -- Use occur_start if available (for recurring events), 
    -- otherwise parse DTSTART from the iCalendar object
    CASE 
        WHEN occur_start != '' THEN occur_start
        ELSE substr(
            substr(ECacheOBJ, instr(ECacheOBJ, 'DTSTART')),
            instr(substr(ECacheOBJ, instr(ECacheOBJ, 'DTSTART')), ':') + 1,
            15
        )
    END as event_start,
    -- Extract location from iCalendar object if present
    CASE 
        WHEN instr(ECacheOBJ, 'LOCATION:') > 0 THEN
            rtrim(substr(
                substr(ECacheOBJ, instr(ECacheOBJ, 'LOCATION:')),
                10,
                instr(substr(ECacheOBJ, instr(ECacheOBJ, 'LOCATION:')), char(10)) - 10
            ))
        ELSE ''
    END as location
FROM ECacheObjects
WHERE substr(event_start, 1, 8) BETWEEN '20251225' AND '20260115'
    AND event_start != ''
ORDER BY event_start;