# What is the Best way to define Shifts using db Time Stamp **URL:** \ **Category:** Ignition\ **Tags:** database, ignition79\ **Created:** [December 8, 2020, 5:18pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303 "2020-12-08T17:18:56Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\ **Post date:** [December 8, 2020, 5:18pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/1 "2020-12-08T17:18:56Z")
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. ---
**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\ **Post date:** [December 9, 2020, 1:17am UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/2 "2020-12-09T01:17:49Z")
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. ![Shift Code Expression Tag](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/d/ddd877174b54e5d42b002a68f8105024bbd45b38.png) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [December 9, 2020, 12:39pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/3 "2020-12-09T12:39:36Z")
Hi Paul, sorry, I was on the road yesterday. > [@paulm1](#): > > 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 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. ```sql 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 ``` ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/b/9b6ca65166badbbb6693bd0aec167770c8a568ab.png) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [December 9, 2020, 1:05pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/4 "2020-12-09T13:05:18Z")
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](http://forum.inductiveautomation.com/t/shift-work-schedules/19848/10) ---
**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:** [December 9, 2020, 1:24pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/5 "2020-12-09T13:24:20Z")
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. ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [March 31, 2021, 11:37pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/6 "2021-03-31T23:37:37Z")
> [@pturmel](#): > > 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. 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! ---
**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:** [April 1, 2021, 12:52am UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/7 "2021-04-01T00:52:11Z")
I’ll have to dig around in my archive… and this is a busy week. Ping me next week. (: ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [April 9, 2021, 6:05pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/8 "2021-04-09T18:05:23Z")
```auto ping pturmel.wizards.forum.inductiveautomation.com ``` ---
**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:** [April 9, 2021, 6:57pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/9 "2021-04-09T18:57:50Z")
I got stuck in the field again. ): So I’ll be drinking this weekend. Ping me again next week, Tuesday, perhaps. ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [April 9, 2021, 7:24pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/10 "2021-04-09T19:24:12Z")
Will do! Good luck! ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [April 15, 2021, 10:00pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/11 "2021-04-15T22:00:38Z")
Happy Tuesursday! Hope your week is less chaotic than mine 😄 ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [April 30, 2021, 3:23pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/12 "2021-04-30T15:23:01Z")
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. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/d/9d07abfcc6eeb0e4bf2daf8fc3a2c1af4d7d22a1.png) 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 🙂 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/b/2bee1305bd8dbc354d6c0f9b22e1142b3f6511a9.png) 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. ```auto 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. ```auto 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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/e/3e70237ac70fea20bfe07d3f06f770dde4f13f3e.png) We can also use postgres to narrow the result to the week we are actually working in, instead of the complete 3 week pattern. ```auto 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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/5/e5ab20e6884501aafafa7ce0a0075e3afa9193b1.png) 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. ```auto 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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/8/b8c8a27a418347cd7ec9dc7ef193ac5305475e22.png) Hope this solution is useful to someone in the future! ---
**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, 10:06pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/13 "2021-09-17T22:06:08Z")
Another poster prompted me to dig my solution out of my archive. Posted over there: > [@Script for periodically changing shift time](https://forum.inductiveautomation.com/t/script-for-periodically-changing-shift-time/51058/3): > > 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: 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/Ne… ---
**Author:** ![Brian](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@Brian](https://forum.inductiveautomation.com/u/Brian)\ **Post date:** [September 21, 2021, 4:15pm UTC](https://forum.inductiveautomation.com/t/what-is-the-best-way-to-define-shifts-using-db-time-stamp/41303/14 "2021-09-21T16:15:36Z")
Thanks for the follow up!