Format output in SQL or in Ignition?

There is a best practice logic that says a database should just return raw data and the “presentation layer” (in this case Ignition) should take care of formatting it as required for output.

What are people’s thoughts on this, with respect to Ignition, as in my experience so far, often the way to achieve the thing you want is via some “clever” SQL.

I’ll give one example. I’m displaying some downtime information in a table. The information is coming from an SQL query. One of the fields is Duration which is stored in seconds, however for readability I’d like to display it as hh:mm:ss, i.e. rather than see 5465 I’d want to see 1:31:05.

So to achieve this I’ve amended my SELECT query to include:

CONVERT(varchar(6), Duration / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2), Duration % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), Duration % 60), 2)  AS DurationFmt

(actually that code is in a View in the SQL database but the principle remains the same)

I’d be interested in people’s thoughts on the example above but also the situation in general.

I actually played around with the idea of implementing Model-View-Controller design principal to one of my Ignition projects, my windows were the View and I had Python modules for my Models and Controllers. End conclusion, it doesn’t make sense. Ignition doesn’t really lend itself to implementing MVC to start and it created a lot of extra work for little or no benefit.

Regarding your example, about how downtime is converted from seconds to hh:mm:ss format, in MVC the View would receive a ‘downtime’ object or list of objects what would already have the downtime value in the required format, or the ‘downtime’ object would have both formats and it would choose which one to display.

The only thing I can say is that displaying and formatting data via SQL queries in Ignition has worked pretty well and when needed Python can be used.

I have heard that best practice logic about SQL also. And it is just that. A best practice, not the law.

All I can say is, if your SQL server is not bogging down doing this, why not use it? You aren’t changing anything in the SQL table. You are just modifying the format of the output in a query, or view in this case. I see nothing wrong with that. If this were very processor-intensive, you might want to experiment to see whether SQL or Ignition would be best for your system overall. In what you are doing here, I would suspect it is not processor-intensive, so I would go with whatever method I preferred.