AnsweredAssumed Answered

RDBMS Query, Time Variable

Question asked by TimCarmichael Champion on Nov 5, 2019
Latest reply on Nov 25, 2019 by TimCarmichael

For an RDBMS interface that pulls data from an external source, we have the option of specifying a time limit in the where clause such as "where Datetime >= getdate() -1".

However, if, for some reason, the interface was down for more that a day, this query could potential lead to a loss of data.

So, how can we define the query in such a way that the 'time limit' is the date/time of the last time of the destination tag?

For example, let's assume the last value was '01-Nov-19 00:43:47', limit the query to only value AFTER that time.

I do have a solution, but it is far from elegant.

I use an analysis to convert the last timestamp to a formatted string (yyyy-mm-dd hh:mm:ss) and store it in a tag.

Then, I pass that string tag as a parameter to the query, and, in the query, convert the source datetime to the same format and do a string comparison.

Again, not elegant... but functional.

Oh, and I'm using this approach because I have data coming from SQL Server in one interface and Oracle in another, so it is a generally 'unified' approach.

 

Love to hear your thoughts or solutions.

Outcomes