MySQL log data with start and stop times

[Phrog30]

I was wondering if anyone had a good way of logging data to DB with start and stop times.  I can easily log data with a timestamp, but that just states when the event occurred, not when it was cleared.  Currently, I do it by searching the DB for any NULL fields in the stop column and then enter the current time.  This is very clunky and I'm sure not best practice.  What I have is a DINT in the PLC (CLX) that when non zero logs a name and start time to MySQL.  When the value changes to zero, or another value it will write the current time into stop.  Only one items is active at a time.  Any ideas or pointers on best practice for this?

James

[Archie]

Are you using ADO.NET, Data Source, or Entity Framework?

The way I would go about it to create a Data Source. Within that DataSource would be the table with the Start and Stop columns. When the value changes from 0, create a row and add it to the table and set the Start Time. Once the value goes back to 0, set the Stop Time to the row in the table. Call Update with a Table Adapter to send the changes to the DB, then clear the table so it is record for the next event.

[Phrog30]

Archie, I think I see what you are saying... something I didn't consider.  Thank you.

Just to clarify, the table inside the .NET app would just have the one row, correct?  Then after completion and I update the DB it would have zero rows... rinse and repeat?

James

[Archie]

Just to clarify, the table inside the .NET app would just have the one row, correct?  Then after completion and I update the DB it would have zero rows... rinse and repeat?

Exactly. Basically the in-memory data table would hold only a single row, which would be the active one.