Leap year scheduling problem

[marcocicalaacn]

For my project we use Postgres 12 and the pg_cron extension at version 1.6.
During the testing phase we realized that the scheduling for the days of the week does not work correctly and the cause is linked to the current year which is a leap year.
Scheduling these jobs:

jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-- | -- | -- | -- | -- | -- | -- | -- | --
22 | 40 07 * * 4 | SELECT 1 | localhost | 5,432 | dbname | username | true | WandaVisionToday
21 | 40 07 * * 5 | SELECT 1 | localhost | 5,432 | dbname | username | true | WandaVision

job 22 refers to the schedule with day of the week 5 (Friday) while the job with id 21 refers to a schedule with day of the week 4 (Thursday) or (Today 16/05/2024).

We noticed that job 21 was not executed while job 22 was executed. We expected the opposite. This made me think that one cause could be pg_cron's handling of leap years. I think it's all there.

Below is the extraction of the cron.job_run_details:

jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-- | -- | -- | -- | -- | -- | -- | -- | -- | --
21 | 7 | 43,042 | dbname| username| SELECT 1 | succeeded | SELECT 1 | 2024-05-16 09:40:00.034 +0200 | 2024-05-16 09:40:01.050 +0200

I hope it can help solve the problem.

Best regards
Marco

[marcocitus]

That's interesting, so far no luck in reproducing it.

I tried scheduling something at 8:38am on 17/5 (today) with 38 8 17 5 *, which ran without issue.

I also tried scheduling something at 8:41am on Friday (today) with 41 8 * * 5, which ran without issue.

What does SELECT now() return?

[marcocicalaacn]

SELECT now() return 2024-05-17 14:20:49.324 +0200

I set the timestamp to Europe/Rome.

[damianr13]

I am having the same issue. I have a bunch of jobs that I scheduled on Mondays, but they are running on Sundays since March 2024 (which supports the assumption that this is caused by the leap year).

This is what I get from job_run_details around the start of March

jobid|start_time|schedule
28|2024-03-03 02:05:00.004781 +00:00|25 4 * * MON
18|2024-03-03 02:00:00.460541 +00:00|0 2 * * MON
28|2024-02-26 02:05:00.004350 +00:00|25 4 * * MON
18|2024-02-26 02:00:00.723335 +00:00|0 2 * * MON
28|2024-02-19 02:05:00.005878 +00:00|25 4 * * MON
18|2024-02-19 02:00:00.534577 +00:00|0 2 * * MON
18|2024-02-12 02:00:00.770294 +00:00|0 2 * * MON
18|2024-02-05 02:00:00.084901 +00:00|0 2 * * MON

You can see here that the Monday jobs ran fine in February (5th, 12th , 19th and 26th are all Mondays) and they started running on Sunday since March 3rd.

I am running postgres on Google Cloud using Cloud SQL, with the maintenance version POSTGRES_15_5.R20240130.00_09, postgres version 15.5, pg_cron version 1.6.

SELECT NOW(); 

gives me the correct time: 2024-05-20 09:00:13.348312 +00:00.

Moreover you can see in the start_time, that pg_cron itself stores the wrong date in the column (March 3rd which is a Sunday), so it does not seem confused about the current time, but rather about which day of the week it represents.

[RP-natanaraujo]

I am having the same issue, and I found that pg_cron doesn't recognize leap years, and the bug inited after February 28.
If I want to schedule something for today (May 31), I have to put tomorrow's date (June 01).

The same happens with weekdays, and for today (Friday), I have to schedule it for tomorrow (Saturday).

Initially, the problem occurred on the PG12 cluster, but further tests shown the same error on AWS RDS pg15.