48 lines
1.7 KiB
PL/PgSQL
48 lines
1.7 KiB
PL/PgSQL
-- ============================================================
|
|
-- Time Tracker App - Automatic Session Closure at Midnight
|
|
-- Run this SQL in your Supabase SQL Editor
|
|
-- ============================================================
|
|
|
|
-- Check if pg_cron extension is available
|
|
CREATE EXTENSION IF NOT EXISTS pg_cron;
|
|
|
|
-- Function to close active sessions at midnight
|
|
-- This will only close sessions that are currently running (end_time IS NULL)
|
|
-- and will preserve all session history
|
|
CREATE OR REPLACE FUNCTION close_active_sessions_at_midnight()
|
|
RETURNS void AS $$
|
|
DECLARE
|
|
closed_count INTEGER;
|
|
BEGIN
|
|
-- Update all active sessions (where end_time is NULL)
|
|
-- Set end_time to today at 00:00:00 (midnight)
|
|
-- This closes only currently active sessions, preserving all history
|
|
UPDATE timers
|
|
SET end_time = DATE_TRUNC('day', NOW()),
|
|
duration_ms = EXTRACT(EPOCH FROM (DATE_TRUNC('day', NOW()) - start_time)) * 1000
|
|
WHERE end_time IS NULL
|
|
AND start_time < DATE_TRUNC('day', NOW());
|
|
|
|
-- Get the count of closed sessions for the notice
|
|
GET DIAGNOSTICS closed_count = ROW_COUNT;
|
|
|
|
RAISE NOTICE 'Closed % active sessions at midnight (history preserved)', closed_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Schedule the function to run daily at midnight (00:00)
|
|
-- This will run in the timezone of your Supabase project
|
|
SELECT cron.schedule(
|
|
'close-active-sessions-at-midnight', -- job name
|
|
'0 0 * * *', -- cron expression (at 00:00 every day)
|
|
$$SELECT close_active_sessions_at_midnight()$$
|
|
);
|
|
|
|
-- To manually test the function, you can run:
|
|
-- SELECT close_active_sessions_at_midnight();
|
|
|
|
-- To view all cron jobs:
|
|
-- SELECT * FROM cron.job;
|
|
|
|
-- To unschedule this job:
|
|
-- SELECT cron.unschedule('close-active-sessions-at-midnight'); |