Named Query with where clause of datetime not working properly

[Learner_Ignition]

Hi,
I have written simple query.

I had put value as "2023-08-01" future date as I don't have any data, however output is giving all the datas available in the database.

1689306148108 = Friday, July 14, 2023 9:12:28.108 AM
1689306535620 = Friday, July 14, 2023 9:18:55.620 AM

[MMaynard]

You will need to convert your datetime parameter to match the epoch timestamp format.
Something like

WHERE t_stamp >= DATEDIFF(s, '1970-01-01 00:00:00.000', :start)

[pturmel]

I'd make the NQ parameter a bigint and convert to UTC milliseconds before the NQ.

Pssst! Why are you directly querying the historian's tables? That is a snake pit of complexity you should avoid (by using Ignition's tag history functions).

[Learner_Ignition]

Getting error
java.sql.SQLSyntaxErrorException: Incorrect parameter count in the call to native function 'DATEDIFF'

[MMaynard]

Not sure why, I just tested it and it works just fine for me with the exact same setup.
I do notice your start date is set in the future though, but I do not believe that would cause that error.

What version of SQL Server are you running?

[Tim_Carlton]

Add your filter parameter value to the return list as a sanity check. Something like this:

SELECT t_stamp, floatvalue, :start as Filt1
FROM sqlt_data_1_2023_07
WHERE t_stamp > :start
ORDER BY t_stamp

[Transistor]

You didn't respond to Phil's question about why you are querying a history table directly. The built-in Tag History binding feature will get you what you want and sort out all the table partitioning behind the scenes.

Can you explain why you are writing your own query?

[Learner_Ignition]

Sorry for the late reply,
I am using MySQL Workbench 8.0.33

For below issue I am trying to find a solution.

TagHistory and History Enabled storing duplicate values

Hi, I am reading the data from restAPI and storing the data with timestamp which is coming from API with storeTagHistory. and when last data is recieved I write to the tag, As I write to the tag the value gets stored in database with current time. But the last value I had already saved using storeTagHistory and I am having a duplicate values with current time. I am trying to restrict the last value getting saved after writing it to the tag. As this tag have read and write option I cannot p…

I was thinking of creating 2 tags for same variable, 1 for reading and 1 for writing and while saving the data in to the database I will have 2 tagID, for this reason I am trying to find a way like this.

Write to tag and set timestamp?

When reading data from API and writing to the database using storeTaghistory and at the end writing the value to tag As tag have the historian enabled so again a new value is stored in the database with current time. We cannot disabled the history as we are writing to that tag and storing in the database, only concern is when writing the last value to tag it save into the database

[Learner_Ignition]