MySQL Datetime column compare in script issue

I have a table in MySQL with two datetime columns StartDate, EndDate. I have a script where I gather the data, covert to PyDataSet, then iterate and find a match from another PyDataSet. On occasion, some of the values don’t come back as a match (typically in a set of 200, 1 or 2 will not match), but they should. Code snippet

for sqlRow in SQL:
	for dsRow in DS:
		if dsRow ['StartDate'] == sqlRow ['StartDate']:

If I use system.date.parse, as follows, it will work

for sqlRow in SQL:
     for dsRow in DS:
		if system.date.parse(dsRow ['StartDate']) == system.date.parse(sqlRow ['StartDate']):

What am I missing on this. When I print the dates off, not using system.data.parse, they look equal to me. The issue seems to be close to the hour boundary (bottom of the hour within a few minutes), not sure what that has to do with anything.

system.date.parse should parse a string to date, but these are dates. Thanks for the help.

Show your column definitions for the tables involved. JDBC supports timestamps with up to nano-second precision. Java’s datetime values are millisecond precision by default. Truncation of precision can make dates that appear identical when printed to not really be identical. Use a custom datetime format that at least show milliseconds.

Photo shows MySQL:

1

This is how I defined the DataSet:

from java.lang import Integer, String
from java.util import Date
from com.inductiveautomation.ignition.common.util import DatasetBuilder
	
pyData = system.dataset.toPyDataSet(data)
b = DatasetBuilder.newBuilder().colNames('StartDate', 'EndDate', 'Duration', 'Id', 'Devices', 'Reason', 'Notes').colTypes(Date, Date, Integer, Integer, Integer, Integer, String)
for row in pyData:
	b.addRow(row[0], row[1], row[2], row[3], row[4], row[5], row[6])
return b.build()

When you say custom datetime, are you referring to MySQL or Ignition?

Phil is suggesting printing the dates you're comparing using a string formatting that shows milliseconds.
Another option is to print someDateObject.time, which will print out the integer milliseconds - it's (sort of) easy to see a difference that way, though the actual scale of the difference can be harder to parse.

Thanks for the clarification. I did print someDateObject.time and they are identical, so I obviously have something else wrong. This was good though, prevents me from going down a rabbit hole.

1 Like

I’m getting data via system.tag.queryTagHistory. I’m then comparing some dates between the history dataset and SQL. After some more testing using someDateObject.time, the date from history is not consistent (the SQL data is correct). Some times the date has zero ms, other times it’s non-zero ms. Even those values change. Example, the same hour:minutes:seconds will have ms .123 one time, then .124 another.

Using system.date.parse will fix the issue, but it also slows the GUI down a bit since execution time is longer.

Any idea on what is going on here? v8.1.5.

Same tag? If not, this is expected.

The same data point will return a different ms result. I thought I was seeing things, but I tested for hours yesterday.

Huh. I’m stumped.

What MySQL JDBC driver version? It’s possible something’s broken in the driver, though I wouldn’t consider it likely.

No idea, tell me how to find out and I’ll let you know.

After more testing, it’s always the first row, so I’m sure this is an Ignition bug.