What is the Best way to define Shifts using db Time Stamp

What is a good way to define production Shifts using the Time Stamp from MySQL db entries?

Our company has just changed its shift schedule to the following.
Shift A is Monday thru Thursday 06:00 to 16:00
Shift B is Monday thru Thursday 16:00 to 04:00
Shift C is Friday, Saturday and Sunday 06:00 to 18:00
Shift D is Friday, Saturday and Sunday 18:00 to 06:00

I tried making an Expression tag and then inserting it into these tables when the transaction groups fire.
But what I really need is to be able to add the ability to define the shift based off the start and stop time stamp columns that already exist in all our existing db tables. Maybe by changing our SELECT statements that populate the reports datasets?

I would love to see any and all recommendations.

I ended up creating an expression tag within this db table’s transaction group.
See the pic below.
I was hoping someone would have a better solution.

Hi Paul, sorry, I was on the road yesterday.

If you’re still looking for an SQL solution, this example may help. The WEEKDAY() function starts on Monday, unlike DAY_OF_WEEK(), which starts on Sunday.

SELECT now() AS now,
	WEEKDAY(now()) AS dow, 
	HOUR(now()) AS hr,
	(CASE 
		WHEN WEEKDAY(now()) < 4 AND HOUR(now()) BETWEEN 6 AND 15 THEN 'A' 
		WHEN WEEKDAY(now()) < 4 AND HOUR(now()) NOT BETWEEN 6 AND 15 THEN 'B' 
		WHEN WEEKDAY(now()) > 4 AND HOUR(now()) BETWEEN 6 AND 17 THEN 'C' 
		WHEN WEEKDAY(now()) > 4 AND HOUR(now()) NOT BETWEEN 6 AND 17 THEN 'D' 
		ELSE 'Error'
	END) AS shift

2 Likes

I added a shift column in my database, which the transaction group logs to along with work data. That way I can use Ignition schedule management tool. I create a “user” for each shift, then run a timer script to check if they are currently scheduled or not, then write to a bool memory tag. It may seem a little hacky, but it has worked without fail for me.

Shift Work Schedules - Ignition - Inductive Automation Forum

2 Likes

I generally use PostgreSQL’s table-generating functions and a table with one complete repeat cycle for all of the shifts, with actual timestamps for that one cycle. The function replicates the sample shift cycle into a desired time frame and uses lead() to yield virtual rows with shift label, start timestamp, and end timestamp covering the entire time frame. This is joinable with your raw data from that same time frame to attach the correct shift to each raw row.

This approach allows you to support many different shift rotation patterns for many facilities, or even departments within a facility, simply by including the appropriate sample cycles in the definitions table.

1 Like

Phil,
I have a project that I’m working on that this approach sounds like it might be usable, but I’m having trouble understanding the details. Could you flesh out the concept a little? Maybe just an example of the table, and a function call example.

In my case, I’m looking to compare a shift start time with an actual start time timestamp. I’m thinking I can use your approach to at least generate the expected start times and modify from there.

Thanks!

I’ll have to dig around in my archive… and this is a busy week. Ping me next week. (:

ping pturmel.wizards.forum.inductiveautomation.com 

I got stuck in the field again. ): So I’ll be drinking this weekend. Ping me again next week, Tuesday, perhaps.

1 Like

Will do! Good luck!

Happy Tuesursday! Hope your week is less chaotic than mine :smile:

So, I finally circled back to this yesterday, and I think I have a workable solution for my situation. It’s a little simpler than I thought it would be, actually. My needs were strictly week based, but I don’t think it would be too hard to increase the flexibility of the code to handle other units of time.

First, I made a table to store info about the schedule that included the schedule name, the origin date, and the length of the cycle in weeks.

Next, a table to store the actual shift data. The time ranges are the actual shift time ranges for the week of the origin date and the x weeks of the initial cycle. The id column ties it to the named schedule. The sample data I have in the table has just three days, Monday of the first week, Tuesday of the second week, and Wednesday of the third week. Makes it easier to see patterns for now :slight_smile:

To use the system, first we need to determine the offset in weeks from the origin date to the start of the current cycle. For my system, the origin date of a schedule, and the first date of the current week will always be Mondays (the beginning of our weekly pay cycle). Postgresql can give us this info via query. Substitute the first date with the current week’s Monday, and the second date with the origin date of the schedule you are working with. The final /3 is the cycle length in weeks of the schedule. These substitutions could be by subquery, or variables provided by scripting.

select ((extract(epoch from '2021-4-26'::date) - extract(epoch from '2021-3-1'::date))/604800)::integer/3

result = 2

So, two complete cycles have occurred since the origin date. We can have postgres add an offset to the original shift patterns to project the shifts to the current date. This query will generate the current shift pattern (also queries the original data so I can see it to compare). The offset applied is the number of cycles X the length of the cycle. 2 cycles X 3 weeks = 6 weeks.

SELECT *, tstzrange(lower(scheduledshift) + '6 weeks'::interval, upper(scheduledshift)+'6 weeks'::interval) as shift 
FROM employee_schedule_data
WHERE id = 1
ORDER BY lower(scheduledshift)

Result:

We can also use postgres to narrow the result to the week we are actually working in, instead of the complete 3 week pattern.

SELECT *, tstzrange(lower(scheduledshift) + '6 weeks'::interval, upper(scheduledshift)+'6 weeks'::interval) as shift 
FROM employee_schedule_data
WHERE id = 1 and scheduledshift && tstzrange('2021-4-26'::date - '6 weeks'::interval, '2021-5-3'::date - '6 weeks'::interval)
ORDER BY lower(scheduledshift)

Result:

Going forward, I’m planning on wrapping this into a CTE and comparing the standard shift to actual time worked.

Edit to add another code snippet, this one to generate the repeating schedule across multiple cycles. Useful for displaying on a calendar, for example. The only variables to provide are the three items for the generate_series function. The first two are the min and max (cycle*weeks) to generate, and the third is the number of weeks in a cycle.

WITH offsets as (
	select generate_series(3,9,3) as weekoffset
	)
SELECT *, 
	tstzrange(lower(scheduledshift)+ (weekoffset* '1 week'::interval),upper(scheduledshift)+ (weekoffset* '1 week'::interval))  FROM employee_schedule_data 
CROSS JOIN offsets
WHERE id = 1

Result:

Hope this solution is useful to someone in the future!

1 Like

Another poster prompted me to dig my solution out of my archive. Posted over there:

Thanks for the follow up!