FSQL datalogging question

Archived Topics
1

I am wondering if it were possible in FSQL to keep a backup of the time stamp. I am hoping I am using the correct terms here. but in the event I am not here is what I would like to accomplish.

When an event occurs at the PLC it triggers a write of information to the PLC. What I would like to do is use the system time/date stamp. Which I know is already written if you check the box to do so.

When an event occurs we have it currently writing start time and end time. This information is currently comming from the PLC. I would like to change that so it comes from the SQL server which from what I take it is where the t-stamp comes from.

So this is how I envision it would work. When a bit goes active, from 0 to a 1 the current value of t_stamp is written in the end time column, if this is an initial log in the same value will be written to the start time column. After the data has been written this bit goes in active from a 1 to a 0 at this point I want to move the value from end time into start time, then the next time this bit goes active the current time is written into end time. This would then show a span of time. I hope this make sence. if this is not easily possible that is fine just wanted to toss the idea around a little.

2

There are many schemes that can accomplish this sort of thing. Most will probably involve Action Items that are SQL queries.

I assume this means that the PLC controls and clears the trigger. Otherwise, please specify how this works.

Here’s an approach that will use 2 groups on the same table:

  1. Create a “start” group. This group will have your trigger condition (read only, meaning it doesn’t need to be stored in the db) set to trigger on a 1. It only has 1 Action Item that runs a query to get the current time and write it back to the start_time column. You can write OPC items here, but they’re bound to get overwritten by the next step.
  2. The “end” group will store all of your OPC items to the same table that the “start” group used. The trigger is set to the same condition as the other group, except it triggers on a 0. This group should be set to OPC->DB, INSERT new record.

Both groups should “Only execute once when trigger is active”, and neither should reset the trigger since the PLC is doing it.

3

I know that I am not the most educated person, especially when it comes to the english language. So let me try to clear up a few points then you can tell me if this is still the best solution.

Currrently I have a bit called ‘Start Collect’ When this bit goes active, it is set to a read only, it activates a group. When it does this it reads the group and stores the information in my history DB. Currently in the PLC I take the time and date and manipulate them so as they contain the / and the : then I read these as 20 character strings. However not all the PLC’s are set the identical time. And because of wire time, responce time, and all the other accumulate time that occurs I dont forsee the ability without some major scripting and some major math to sync all the clocks to be exactly identical.

Our data base engineers have developed a program using fox pro to summarize these history reports every hour on the hour. I also in FSQL have a time watch that every hour on the hour it kicks off and enables this start collect bit. I have argued with them that instead of using the start and/or end times which are reported to the DB from the PLC why not use the t-stamp to do your manipulations. That is still being voted on.

So I thought being as I am using the SQL time to kick off the event every hour on the hour, instead of the PLC sending the start and end times why not have the SQL time do it. This is a generalization of what the end raw report would look like:

26 0192834582 08/23/2007 06:15:23 08/23/2003 06:30:10 20 200 …

First is the machine number, then the job id, then the start date/time, then the end date time, then all the relevant information for that period of time. This would occur one the bit goes active, from 0 to 1. Upon going inactive, from 1 to a 0 the end date/time 08/23/2003 06:30:10 would be moved into an area where it would now become the start time. Then the next time the start collect bit kicks off the current time would become the end time and the process would repeat itself where the end time would move into the start time. The only condition I have to make arrangments for is when the person is first logging on. Because One shift ends say at 4am and the other starts at 6:30am so I dont want that but there is another bit that I might be able to monitor that would allow me to write some conditional statement to take care of this.

So would this scenario you reprent here still be the way to go and the easiest and most reliable? Or is there a better approach?

[quote=“nathan”]There are many schemes that can accomplish this sort of thing. Most will probably involve Action Items that are SQL queries.

I assume this means that the PLC controls and clears the trigger. Otherwise, please specify how this works.

Here’s an approach that will use 2 groups on the same table:

  1. Create a “start” group. This group will have your trigger condition (read only, meaning it doesn’t need to be stored in the db) set to trigger on a 1. It only has 1 Action Item that runs a query to get the current time and write it back to the start_time column. You can write OPC items here, but they’re bound to get overwritten by the next step.
  2. The “end” group will store all of your OPC items to the same table that the “start” group used. The trigger is set to the same condition as the other group, except it triggers on a 0. This group should be set to OPC->DB, INSERT new record.

Both groups should “Only execute once when trigger is active”, and neither should reset the trigger since the PLC is doing it.[/quote]

4

A few points:

  1. PLC clock synchronization really isn’t difficult with FactorySQL. If you do it every hour or so your PLCs had better not drift much. I explained that here and in other posts on this forum. That said, I would use the technique recommended below (and in my earlier response) for your situation.

  2. Given your clarification, I still think that my response post should satisfy your requirement. I still don’t see the advantage of moving the end date_time to the start date_time. It uses the DB clock for both times.

Here’s a higher level summary of what I intended.

  1. PLC trigger goes from 0->1
  2. A new record is written with now() as the start_time. The end_time is meaningless so far.
  3. PLC trigger goes from 1->0
  4. A second group updates all the relevant columns of the newest record in that same table. t_stamp now represents the end_time of your process. You could easily rename the column or create another one.

Please let me know if you need any additional clarification.