SQL and millisecond timestamps

Ignition
1

I assume that Ignition does not have a built in way to work around the fact that MySQL does not support timestamps with less than a second resolution. viewtopic.php?f=23&t=3419&hilit=millisecond

Are there any other SQL issues like this that would drive a user to one database over another when used with Ignition?

Have you noticed any performance and/or CPU loading differences between MS SQL and PostgreSQL with Ignition?

Thanks,

2

For the most part, your assumption is true, with the exception of SQLTags Historian. It uses 8-byte integers to store timestamps rather than the database’s date/time column type, which assures millisecond resolution. But for other features, like the Transaction Groups, we are at the mercy of the database’s timestamp resolution.

Performance is a fairly complex question, so I’m going to paint with a broad brush here. I’d say that on Windows, MS SQL Server tends to out-perform MySQL, but only by a slim margin. Oracle also offers very good performance. MySQL tends to perform better on Linux.

We do not recommend using PostgreSQL unless you are already extremely familiar with PostgreSQL tuning. We’ve had some bad experiences with PostgreSQL. That said, I know of a few customers who are PostgreSQL experts, and they’re having great results.

All-in-all, much of the performance questions tend to be most greatly influenced not by choice of database, but by schema/table design and query tuning. As in, its not the tool, but how you use it, that is most important.

3

So, the Tag Historian stores timestams to the millisecond?

How do I get the table component to display timestamps to the millisecond?

It must default to displaying to the second.

Thanks

Dennis

Found the answer…

Column Attributes Data

dateFormat string needs a .SSS appended to the ss.

Thanks

Dennis