SQL query for time

When running a query from SQL database, a DateTime field in the database has seconds recorded down to three decimals, but the returned results are only down to the second. Anyone else run into this issue?

Example: 2019-01-10 05:15:00.123 becomes 2019-01-10 05:15:00 when the result is returned into the dataset in Ignition.

When I later want to update something related to that time entry, I can’t use the returned information to modify that same line because the time doesn’t “match”.

It depends on the JDBC driver. The java.util.Date type that Ignition uses will hold milliseconds if the JDBC driver passes them through. Ignition won’t display them by default, though you can force it to through various date formatting functions. PostgreSQL actually returns java.sql.Timestamp, a subclass of java.util.Date, which can hold nanoseconds.

If you set a table column’s date format to include milliseconds, you might discover you actually have them. Unfortunately, Ignitiion’s builtin to-CSV conversions truncate to seconds, whether going to the clipboard or invoked via script.