Cronjob

Schedule a query

-- Refresh an hourly rollup every 5 minutes
SELECT cron(
  'CREATE OR REPLACE TABLE hourly_rollup AS
     SELECT bucket, SUM(amount) AS total
     FROM events
     WHERE ts >= now() - INTERVAL 1 HOUR
     GROUP BY bucket',
  '0 */5 * * * *'
);
-- → 'task_0'

cron returns a job_id (e.g. task_0) — capture it if you need to cancel later. Schedules tick only while this DuckDB process is running.

Inspect what’s scheduled

-- All scheduled jobs and their last result
SELECT job_id, schedule, next_run, status, last_run, last_result
FROM cron_jobs()
ORDER BY next_run;

-- Jobs that have never run yet
SELECT job_id, schedule
FROM cron_jobs()
WHERE last_run IS NULL;

-- Recently failed runs
SELECT job_id, last_run, last_result
FROM cron_jobs()
WHERE status = 'error';

Cancel a job

SELECT cron_delete('task_0');
-- TRUE if a matching job was found

Restart of the DuckDB process also cancels every registered job — cron_delete is for un-registering during a live process.

Periodic refresh of materialized data

-- Every 5 minutes: rebuild a recent-events rollup
SELECT cron(
  'CREATE OR REPLACE TABLE recent_events AS
     SELECT * FROM events WHERE ts >= now() - INTERVAL 1 HOUR',
  '0 */5 * * * *'
);

-- Daily at 02:00: rebuild a heavy daily aggregate
SELECT cron(
  'CREATE OR REPLACE TABLE daily_summary AS
     SELECT date_trunc(''day'', ts) AS day,
            user_id,
            SUM(amount) AS total
     FROM events
     GROUP BY 1, 2',
  '0 0 2 * * *'
);

Recurring exports to disk or S3

With the httpfs extension loaded, COPY ... TO writes to S3 / GCS just like any local path:

-- Local Parquet snapshot every hour
SELECT cron(
  'COPY (SELECT * FROM events WHERE ts >= now() - INTERVAL 1 HOUR)
   TO ''/var/exports/events_'' || strftime(now(), ''%Y%m%d_%H'') || ''.parquet''
   (FORMAT PARQUET)',
  '0 0 * * * *'
);

-- Daily export to S3 at 03:00
SELECT cron(
  'COPY (SELECT * FROM daily_summary)
   TO ''s3://my-bucket/exports/daily/'' || strftime(now(), ''%Y-%m-%d'') || ''.parquet''
   (FORMAT PARQUET)',
  '0 0 3 * * *'
);

Periodic Slack / webhook alerts

Pair with the http_client extension to fire a webhook when a metric crosses a threshold:

-- Every minute: post to Slack if error rate is high
SELECT cron(
  'WITH r AS (
     SELECT 100.0 * SUM(CASE WHEN status >= 500 THEN 1 END) / COUNT(*) AS pct
     FROM requests WHERE ts >= now() - INTERVAL 5 MINUTE
   )
   SELECT http_post(
     ''https://hooks.slack.com/services/XXX/YYY/ZZZ'',
     ''{"text":"Error rate '' || round(pct, 2) || ''%"}'',
     MAP {''Content-Type'': ''application/json''}
   )
   FROM r WHERE pct > 5',
  '0 * * * * *'
);

The query writes nothing to disk — http_post is a side effect that fires only when the WHERE filter passes.

Maintenance: ANALYZE and pruning

-- Nightly ANALYZE at 01:30
SELECT cron('ANALYZE', '0 30 1 * * *');

-- Prune log rows older than 30 days, weekly on Sunday at 04:00
SELECT cron(
  'DELETE FROM audit_log WHERE ts < now() - INTERVAL 30 DAY',
  '0 0 4 ? * SUN'
);

Six-field cron quick reference

┌────────── second  (0–59)
│ ┌──────── minute  (0–59)
│ │ ┌────── hour    (0–23)
│ │ │ ┌──── day of month (1–31)
│ │ │ │ ┌── month   (1–12)
│ │ │ │ │ ┌ day of week (0–6, Sun..Sat, or MON–SUN)
* * * * * *

Operators: * (any), ? (no specific value), , (list), - (range), / (step). Sanity-check on crontab.guru — note crontab.guru is five-field POSIX, so drop the leading seconds field when pasting.

Restart-safe scheduling: external orchestrator

When jobs must survive process restarts, drive DuckDB from system cron(8) instead of using this extension. Example crontab entry:

*/5 * * * * /usr/local/bin/duckdb /var/data/analytics.db -c "CREATE OR REPLACE TABLE hourly_rollup AS SELECT ..."

Or a systemd timer, or a Kubernetes CronJob. Use the Cronjob extension only when DuckDB is already a long-running service and the schedule’s lifetime can equal the process’s.