# Script for periodically changing shift time

**URL:** <https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058>\
**Category:** Ignition\
**Tags:** scripting, perspective\
**Created:** [September 17, 2021, 8:24am UTC](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058 "2021-09-17T08:24:18Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 17, 2021, 6:33pm UTC](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3 "2021-09-17T18:33:21Z")

</div>

Hi Berkay,

I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example:

```auto
create table shift_schedules (shift_id serial,
	shift_name varchar(255),
	refdate date,
	repeat int,
	tzname varchar(255),
	primary key (shift_id));

insert into shift_schedules (shift_name, refdate, repeat, tzname)
values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/New_York');

create table shift_schedule_pattern (shift_id int,
	seq int,
	pseq int,
	start time,
	shift varchar(16),
	primary key (shift_id, seq, start));

insert into shift_schedule_pattern (shift_id, seq, pseq, start, shift)
values
	(1, 0, 0, '07:30:00', 'B')
,	(1, 0, 0, '15:30:00', 'A')
,	(1, 0, 0, '23:30:00', 'C')
,	(1, 1, 1, '07:30:00', 'B')
,	(1, 1, 1, '15:30:00', 'A')
,	(1, 1, 1, '23:30:00', 'C')
,	(1, 2, 2, '07:30:00', 'B')
,	(1, 2, 2, '15:30:00', 'A')
,	(1, 2, 2, '23:30:00', 'C')
,	(1, 3, 3, '07:30:00', 'B')
,	(1, 3, 3, '15:30:00', 'A')
,	(1, 3, 3, '23:30:00', 'C')
,	(1, 4, 4, '07:30:00', 'B')
,	(1, 4, 4, '15:30:00', 'A')
,	(1, 4, 4, '23:30:00', 'C')
,	(1, 5, 5, '07:30:00', NULL)
,	(1, 6, 6, '07:30:00', NULL)
,	(1, 7, 7, '07:30:00', 'A')
,	(1, 7, 7, '15:30:00', 'C')
,	(1, 7, 7, '23:30:00', 'B')
,	(1, 8, 8, '07:30:00', 'A')
,	(1, 8, 8, '15:30:00', 'C')
,	(1, 8, 8, '23:30:00', 'B')
,	(1, 9, 9, '07:30:00', 'A')
,	(1, 9, 9, '15:30:00', 'C')
,	(1, 9, 9, '23:30:00', 'B')
,	(1, 10, 10, '07:30:00', 'A')
,	(1, 10, 10, '15:30:00', 'C')
,	(1, 10, 10, '23:30:00', 'B')
,	(1, 11, 11, '07:30:00', 'A')
,	(1, 11, 11, '15:30:00', 'C')
,	(1, 11, 11, '23:30:00', 'B')
,	(1, 12, 12, '07:30:00', NULL)
,	(1, 13, 13, '07:30:00', NULL)
,	(1, 14, 14, '07:30:00', 'C')
,	(1, 14, 14, '15:30:00', 'B')
,	(1, 14, 14, '23:30:00', 'A')
,	(1, 15, 15, '07:30:00', 'C')
,	(1, 15, 15, '15:30:00', 'B')
,	(1, 15, 15, '23:30:00', 'A')
,	(1, 16, 16, '07:30:00', 'C')
,	(1, 16, 16, '15:30:00', 'B')
,	(1, 16, 16, '23:30:00', 'A')
,	(1, 17, 17, '07:30:00', 'C')
,	(1, 17, 17, '15:30:00', 'B')
,	(1, 17, 17, '23:30:00', 'A')
,	(1, 18, 18, '07:30:00', 'C')
,	(1, 18, 18, '15:30:00', 'B')
,	(1, 18, 18, '23:30:00', 'A')
,	(1, 19, 19, '07:30:00', NULL)
,	(1, 20, 20, '07:30:00', NULL);

CREATE OR REPLACE FUNCTION production_shifts_for_range(
	sid int,
	begints timestamptz,
	endts timestamptz)
RETURNS TABLE(shift_name varchar,
	tzname varchar,
	proddate date,
	shift varchar,
	beginat timestamptz,
	endat timestamptz)
AS $$
	WITH mult_estimates AS (
		SELECT ((begints::timestamptz at time zone tzname)::date-refdate)/repeat-1 AS beginmult,
			((endts::timestamptz at time zone tzname)::date-refdate)/repeat+1 AS endmult
		FROM shift_schedules sscte
		WHERE sscte.shift_id = sid
	),
	gross_filter AS (
		SELECT shift_name, tzname, proddate, shift, boundary as beginat,
			lead(boundary) over (ORDER BY boundary) as endat
		FROM (
			SELECT ss.shift_name,
				ss.tzname,
				ss.refdate + (mult * ss.repeat) + ssp.pseq as proddate,
				ssp.shift,
				(ss.refdate + (mult * ss.repeat) + ssp.seq + ssp.start) at time zone ss.tzname as boundary
			FROM (SELECT * FROM shift_schedules WHERE shift_id = sid) ss
				INNER JOIN shift_schedule_pattern ssp ON ss.shift_id = ssp.shift_id,
				generate_series((SELECT beginmult FROM mult_estimates)::int, (SELECT endmult FROM mult_estimates)::int) as mult
		) subq
	)
	SELECT *
	FROM gross_filter gf
	WHERE gf.proddate >= (SELECT gfb.proddate FROM gross_filter gfb WHERE begints >= gfb.beginat AND begints < gfb.endat)
	  AND gf.proddate <= (SELECT gfe.proddate FROM gross_filter gfe WHERE endts >= gfe.beginat AND endts < gfe.endat)
$$ LANGUAGE SQL;

Select * From production_shifts_for_range(1, current_timestamp, current_timestamp)
Where current_timestamp between beginat and endat
ORDER BY beginat;

Select * From production_shifts_for_range(1, current_timestamp, current_timestamp)
ORDER BY beginat;

```

For that last query, you get results like this:

 ![Screenshot_20210917_142600](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b34988fc9a50cfe7ea771bac9e958e53b1105ede.png)

Note that this custom table-generating function can be use with wider timespans to give you every shift within a given month or year, _ **joinable** _ to your production records. You can group such data on shift and variations of the production date for all the stats you need to satisfy the bean counters.

---

_[View the full topic](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058)._