pg_cron schedule runs 1 day in advance in AWS Aurora Postgres

[hoonjoo]

DB version: AWS Aurora Posgres 13.13 pg_cron version: 1.6

Problem: For some reason, pg_cron is firing the jobs a day prior to schedule.

Description: Note the following query to examine the run details of some cron-scheduled jobs:

select d.runid,j.jobname,j.command,d.status,d.return_message,j.schedule,d.start_time,d.end_time
from cron.job_run_details d
	inner join cron.job j on j.jobid = d.jobid
where j.command ilike '%sp_task%'
order by 2 desc

Above query yields:

runid |jobname         |command                   |status   |return_message|schedule      |start_time                   |end_time                     
------+----------------+--------------------------+---------+--------------+--------------+-----------------------------+-----------------------------
254292|task.sop7938_1  |select sp_task_sop7938_1()|succeeded|SELECT 1      |30 8 17,26 1 *|2024-01-25 03:30:00.015 -0500|2024-01-25 03:30:12.359 -0500
259453|task.sop7846_3_2|select sp_task_sop7846_3()|succeeded|SELECT 1      |0 9 1,29 2 *  |2024-01-31 04:00:00.015 -0500|2024-01-31 04:00:09.826 -0500
260310|task.sop7846_2_2|select sp_task_sop7846_2()|succeeded|SELECT 1      |0 9 2,16 2 *  |2024-02-01 04:00:00.009 -0500|2024-02-01 04:00:01.745 -0500

That is, when comparing the cron expressions in the schedule (scheduled in GMT), you can clearly see that the jobs ran a full day ahead of schedule.

Would really appreciate some help in troubleshooting this odd behavior. Many thanks in advance.

[marcoslot]

There was a bug with this exact symptom that was fixed in October https://github.com/citusdata/pg_cron/blob/main/CHANGELOG.md

I'm guessing they should update pg_cron

[hoonjoo]

I see the CHANGELOG, thanks a ton.

I will reach out to AWS to see if they will be willing to update the extension version to the latest (1.6.2+) -- along with adding cron.timezone parameter for their RDS Parameter Groups. Really appreciate the help and the awesome extension.