Ignition Min and Max Date From Dataset

Ignition
1

Does anyone know an easy way to get the min and max date values from a dataset with multiple columns, one column is datetime? I’m using Ignition version 7.8.5.

2

Have you tried the minDate and maxDate expression functions?

3

Yeah, I get:
“NameError: name ‘minDate’ is not defined.”

Do I need to import anything before using it? Or is it not available on the version I’m using (7.8.5)?

4

These are expression functions, to be used in a binding. If you are scripting, iterate through the dataset to build a list from the timestamp column, then use python’s min and max functions. Something like this:

timestamps = [ds.getValueAt(r, 't_stamp') for r in range(ds.rowCount)]
print min(timestamps), max(timestamps)
1 Like
5

That worked, thank you!

6

Curious if you had a time zone conversion problem when using this script. It seems like the actual value stored in SQL in other scripts is in UDT not my gateway or session time zone. When I read back from the database often the local time zone will be applied, but this script does not adjust for the gateway/session time zone. Any guess on an easy way to fix this, or do I need to expand the script to get time zone and then add/subtract that value from the returned date/time?

7

If by SQL you mean MS SQL Server, then yes, you are [expletive]. Its timezone-aware column type, DATETIME2, is not generically supported by JDBC. So you must use DATETIME, which uses local time. Pretty much all other databases have a timezone-compatible column type that stores UTC under the hood and converts to and from the connection timezone on the fly. This behavior perfectly matches Java’s date/time functionality and “just works” with Ignition.

If you’ve ever wondered why Ignition’s Tag Historian uses a long integer (UTC milliseconds) for t_stamp instead of a proper timestamp column, now you know.

So:

  • Use a timezone compatible column type in a competent DB (like timestamptz in PostgreSQL), or
  • Run your MS SQL Server and your Ignition gateway in UTC–Java will do the rest.
8

Like said from [pturmel] first of all you need to verified your dateTime format to know what are you
working on. Different DBMS work with different dateTime format.

9

DateTime formats should not matter, because datetimes should only go to databases as parameters, not as strings. (Value params in Named Queries or ‘?’ substitutions in “Prep” queries.)

10

Thanks, that explains the problem I’m running into.
Earlier in troubleshooting I was able to get the datetime tag to display well because the gateway and database were on UTC, but the reports were not displaying in local time as those run on the gateway.
This gives me some things to think about, this early in the project it might not be too difficult to change databases.