Hello, I am currently working on a system that keeps track of CIP data by logging it into SQL. I am using the system.date.now() command to get the time before I log data into the SQL server. My problem is that my time isn’t always getting entered correctly. I will get several good entries but then I will get one where the time is completely off. Is there a better way to make sure my time is always correct?
Can you give us an example of one of the incorrect times? Is there anything else going in here with your script? Is there a chance your system’s clock is actually changing?
So for instance we will get lets say 4 good entries and the times are reading correctly, then on the fifth one it will be two hours off. The system’s clock hasn’t changed. Is there any better option for getting time then system.date.now()?
system.date.now() is literally just new Date() in Java. There only way that’s wrong is if your system clock is reporting an incorrect value.
Can you print each date out before you insert it and then compare the printed date in the logs to the one that got inserted in the database next time you notice it?
So we have been trying that. In Ignition the times are correct, it just seems that when they get transferred to the SQL that they change
We just entered in a new time log and then when I check the memory tag it showed an hour ahead
How are you doing the inserts? With any script function of the form system.db.*Prep*(), where data is parameterized? Or the same features with named queries? Or are you constructing your queries from strings?
The latter is bad.
I am using mem tags to hold the data until it is ready to be put into SQL, and then running a named query to insert that data into the db
Are the memory tags date types, or strings? Could you show your named query? What brand of DB? What column type for the dates?
Some are strings, but for the times I am using date time. I am using microsoft SQL 2017. The column types are date time for the dates. Here is the script which runs the named query:
CIP_Start = system.tag.read(‘Controller:Global/CIP 1/CIP1_Start_SQL’).value
CIP_CircuitSel = system.tag.read(‘Controller:Global/CIP 1/CIP1_CircuitSel’).value
T_Stamp = system.tag.read(‘Controller:Global/CIP 1/CIP1_TStamp’).value
CIP_End = system.tag.read(‘Controller:Global/CIP 1/CIP1_End_SQL’).value
UniqueID = system.tag.read(‘Controller:Global/CIP 1/CIP1 UniqueID’).value
CIP_Number = system.tag.read(‘Controller:Global/CIP 1/CIP1_Number’).value
if(system.tag.read(‘Controller:Global/EB/EB3-1’).value) == 1:
system.db.runNamedQuery(“Saputo_Fraser_RawPast”, “Insert Data”, {“CIP_End”:CIP_End, “CIP_CircuitSel”:CIP_CircuitSel, “T_Stamp”:T_Stamp, “UniqueID”:UniqueID, “CIP_Start”:CIP_Start, “CIP_Number”:CIP_Number})
value = 0
system.tag.write('Controller:Global/EB/EB3-1', value)You left this out.
Also, is your DB in a different time zone from your gateway?
What version of Ignition are you using?
Here is the named query:
INSERT INTO CIP_Data (CIP_Start, CIP_CircuitSel, T_Stamp, CIP_End, UniqueID, CIP_Number) VALUES (:CIP_Start, :CIP_CircuitSel, :T_Stamp, :CIP_End, :UniqueID, :CIP_Number)
The gateway and the db are in the same time zone
Hmm. I’m stumped. I don’t see any obvious reason for the discrepancies.
I am on 7.9, I am using the system.tag.write function. Basically when they start the process it loads those memory tags with the data, at the end of the process the end time is loaded into the end time memory tag and named query runs and inserts the data into SQL
7.9 what? If you’re not on 7.9.13 I’d upgrade to that.
You should also make sure all your system.tag.write calls are actually system.tag.writeSynchronous.
Do these 2 things and see if this keeps happening.
I am on 7.9.12, I can’t update at the current time but I can try the system.tag.wrtieSynchronus and see if it fixes the issue
So I changed my system.tag.write calls but still the time was off when it wrote. It was supposed to be 12:53pm and instead wrote in 2:53pm
Are you writing from a client that is on different time zone from the servers?