citusdata / pg_cron

Run periodic jobs in PostgreSQL
PostgreSQL License
2.9k stars 195 forks source link

[New Feature] #343

Open melquelima opened 3 months ago

melquelima commented 3 months ago

would be great to have a column called next_runtime or a way to calculate next run time like

select cron.next_runtime(' *')

cwang9208 commented 2 weeks ago

+1

melquelima commented 2 weeks ago

@cwang9208 ive created a code that does that for me check this out

next run time

import pycron
from datetime import datetime,timedelta
import pytz

def next_runtime(s:str)
  dt = datetime.now()

  while True:
      if pycron.is_now(s, dt):
          return dt.replace(second=0, microsecond=0)
      dt += timedelta(minutes=1)

last run time

import pycron
from datetime import datetime,timedelta
import pytz

def last_runtime(s:str)
  dt = datetime.now()

  while True:
      if pycron.is_now(s, dt):
          return dt.replace(second=0, microsecond=0)
      dt -= timedelta(minutes=1)

POSTGRES


// ================================ PYTHON + PYCRON
CREATE EXTENSION IF NOT EXISTS plpython3u

CREATE EXTENSION IF NOT EXISTS pg_cron;

CREATE or replace function  get_last_runtime (s varchar)
  RETURNS VARCHAR
AS $$

import pycron
from datetime import datetime,timedelta
import pytz

dt = datetime.now()

while True:
    if pycron.is_now(s, dt):
        return dt.replace(second=0, microsecond=0)
    dt -= timedelta(minutes=1)

$$ LANGUAGE plpython3u;

CREATE or replace function  get_next_runtime (s varchar)
  RETURNS VARCHAR
AS $$

import pycron
from datetime import datetime,timedelta
import pytz

dt = datetime.now()

while True:
    if pycron.is_now(s, dt):
        return dt.replace(second=0, microsecond=0)
    dt += timedelta(minutes=1)

$$ LANGUAGE plpython3u;
cwang9208 commented 2 weeks ago

@melquelima https://github.com/cybertec-postgresql/pg_timetable/blob/master/internal/pgengine/sql/cron.sql this might be useful.

cwang9208 commented 2 weeks ago

@marcoslot @marcocitus Is it possible to calculate the next run date using the entry data structure in pg_cron's source code? Could you please help.