Issues running cron jobs at specific times in PostgreSQL using pg_cron

[ricardosikic]

I configured a cron job in PostgreSQL with pg_cron as follows:

SELECT cron.schedule('update-events', '0 0 * * *', 'CALL update_event_active_status()');
I expected this job to run every day at midnight. However, when I checked after midnight, I noticed it hadn't executed.

To test, I created a cron job with the same function but scheduled it as follows:
SELECT cron.schedule('update-events', '* * * * *', 'CALL update_event_active_status()');

Surprisingly, this job ran correctly every minute (and seemingly every second, as per my monitoring). Still, I cannot get jobs scheduled for specific times (like 0 0 or 30 0) to work properly.

The function used by the cron job is as follows:

CREATE OR REPLACE PROCEDURE update_event_active_status()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE events
  SET is_active = false
  WHERE is_active = true AND date_closing_sales <= current_date;
END;
$$;

Additionally, I configured the PostgreSQL timezone to America/Santiago with:

SET timezone = 'America/Santiago';
I verified the timezone settings with:

SHOW timezone;
SELECT NOW();

Both confirmed the server time is correct.

Could there be an issue with pg_cron or the way it handles time-based schedules in PostgreSQL? Am I missing a step to ensure jobs scheduled for specific times work properly?

Any guidance or solutions would be greatly appreciated. Thank you!

[GaryAustin1]

I’ve not had any issues with specific times. Try the new Cron UI and see what time pattern that gets.

[ricardosikic]

Hi @GaryAustin1 I am using Supabase locally. I believe the UI doesn't have cron job functionality, or it doesn't appear in my version. However, I did perform migrations for the setup.

[GaryAustin1]

Sorry I’m not in a place to check more for a couple of days. But pg_cron is an industry standard extension and I doubt a bug as it works with your other format. I’d check the Citrus docs for it to see if you find anything useful.