I have some tags that query production data from a database. I have two shifts and the data has a ‘shift’ column which contains either a 1 or 2. I can easily where clause the shift = 2, but I only need the data from the last shift which is where my trouble comes in. The shift starts at 4pm and ends 2:30 am the next day. Every query I write using things like
where shift = 2 and (DATE(t_stamp) = CURDATE() and HOUR(t_stamp) > 16 ) or (DATE(t_stamp) = DATE_ADD(CURDATE(),INTERVAL -1, DAY))
Doesn’t work because of the change in CURDATE() after midnight. Any ideas for a better way to do this?? Thanks
What did you end up doing?
I am thinking I will do like
Mytable.tstamp > 'dateadd(hour,23,dateadd(day,-1,getdate()))'
and Mytable.tstamp < 'dateadd(hour,7,getdate()))'
just not sure how to dump the minutes, or if it does it
I used a tag that holds the shift start time, then I use
where t_stamp > :shiftStart
oh and then shiftstart is like a tag?
or is there a way to do a dropdown for three shifts, but provide two different times as parameters for start and end?
For mine, I think a dropdown to pick and send the start and end times would be best for me.
I guess I can use a case, and just send 1, 2, 3 and then use static parameters for the starts and ends
I got a cannot coerce value
edit:My tag is formatted, but it has a datetime type
is there an easy way to convert this in the query?
I use the built in ignition schedules to determine the shift start and end times. I have a gateway timer script that writes to shift active boolean tags. Shift Work Schedules - Ignition - Inductive Automation Forum
Not following what you are saying.
Is this true?
Converting a DATEIME to a DATE and then back to a DATETIME again sets the time to midnight
because if so,
Mytable.tstamp >= dateadd(hour,23,dateadd(day,-1,convert(datetime,convert(date,getdate())))
and Mytable.tstamp <= dateadd(hour,7,convert(datetime,convert(date,getdate())))
Is that a query or an expression? I’m not familiar with dateadd or convert.
Usually you need shift start and end times in multiple places. It is much more ergonomic to just pass the dates as parameters to the query from a tag value, which is where you were having the type error?
I don’t want to make another tag, and I was not sure how to convert that format in sql
Mine isn’t a sliding window though. It is made of static shift times.
Update 2 weeks later, I meant to also say that I had solved the issue I had with this, and I use this technique often now.
dateadd(hour,7,convert(datetime,convert(date,getdate())))