pgAgent to trigger action based on current timestamp

Question

Hi I started to explore pgAgent recently. Installation and testing had been done. However, there is one thing that bothers me about pgAgent. the thing will only run either SQL or batch code.

Suppose, in my database table contains different week number data. for maintenance purpose, the table should store only past 2 weeks data. Would pgAgent capable to do the automation of this task.

the workflow will be:

1)Get System timestamp
2)Calculate week Number
3)delete * from table where week= week - 3

if this can be done, Would it be on the batch or SQL kind?? I highly appreciate if you can provide some example.

Thank you so much.

CREATE OR REPLACE FUNCTION weekno() RETURNS void AS $$ 
DECLARE weekno INTEGER; 
BEGIN 
  weekno := select extract(week from (select current_timestamp)); 
  RAISE NOTICE '%', weekno; 
END; 
$$ LANGUAGE plpgsql; 
SELECT weekno();
delete * from mytable where week=weekno();

Example, Assuming select extract(week from (select current_timestamp)) returns 22, then the row containing week 19 data must be deleted.

Week          Person        Total Overtime Hours
19           Belle                    12
19           Anthony                  10
19           Boss                     0
20           Anthony                  15
20           Boss                     0
20           Belle                    5
21           Anthony                  20
21           Belle                    10
21           Boss                     0
22           Anthony                  25
22           Belle                    8
22           Boss                     0

Answer 1

Let's say week starts on Sunday, then to get rows of the week number x, you need:

t=# select now() - concat(extract(dow from now()),' days')::interval - '3 weeks'::interval;
          ?column?
-----------------------------
 2018-05-06 07:36:33.2053+00
(1 row)

Why this way, instead of week number -3? because week of the year can mean very different things:

https://www.postgresql.org/docs/current/static/functions-datetime.html

week

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.

In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It's recommended to use the isoyear field together with week to get consistent results.

Another point is that you have plenty of weeks with same number over human history - this year, year 2017, year 1970 and so on all they have same number, but very different dates. If that is meant - then indeed, but maybe not?..

And next point is delete * from table where week= week - 3 - this column week is timestamptz? I hope so, because if not - how will you define the exact edge? How do you trap summer time switch? and so on... So if it is and you just want to keep data for last three CALENDAR weeks, use:

delete * from table 
where week <= (now() - concat(extract(dow from now()),' days')::interval - '3 weeks'::interval);

UPDATE Now, when you finally posted data sample, I can advise on it without theoretical guessing:

delete * from table 
where week = extract(week from now() - '3 weeks'::interval);

once again - you need interval to for right calculations on year edge:

t=# select extract(week from '2018-01-08'::date - '3 weeks'::interval), extract(week from '2018-01-08'::date);
 date_part | date_part
-----------+-----------
        51 |         2
(1 row)

against:

t=# select extract(week from '2018-01-08'::date) - 3 , extract(week from '2018-01-08'::date);
 date_part | ?column?
-----------+----------
        -1 |       2
(1 row)