# Named Query with where clause of datetime not working properly **URL:** \ **Category:** Ignition\ **Created:** [July 14, 2023, 11:20am UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371 "2023-07-14T11:20:29Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\ **Post date:** [July 14, 2023, 11:20am UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/1 "2023-07-14T11:20:30Z")
Hi, I have written simple query. ![Query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/e/de51ab8cdea7f584a57a11f90c747f7a06b217b3.jpeg) 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 ![output](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/2/124e7f14a17c603075a2a7db02a3a28121dbde8e.jpeg) ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [July 14, 2023, 12:08pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/2 "2023-07-14T12:08:07Z")
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)` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 14, 2023, 12:14pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/3 "2023-07-14T12:14:18Z")
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). ---
**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\ **Post date:** [July 14, 2023, 12:27pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/4 "2023-07-14T12:27:22Z")
Getting error java.sql.SQLSyntaxErrorException: Incorrect parameter count in the call to native function 'DATEDIFF' ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [July 14, 2023, 1:35pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/5 "2023-07-14T13:35:44Z")
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? ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [July 14, 2023, 2:04pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/6 "2023-07-14T14:04:30Z")
Add your filter parameter value to the return list as a sanity check. Something like this: ```auto SELECT t_stamp, floatvalue, :start as Filt1 FROM sqlt_data_1_2023_07 WHERE t_stamp > :start ORDER BY t_stamp ``` ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [July 14, 2023, 2:37pm UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/7 "2023-07-14T14:37:19Z")
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? ---
**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\ **Post date:** [July 16, 2023, 10:21am UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/8 "2023-07-16T10:21:48Z")
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](https://forum.inductiveautomation.com/t/taghistory-and-history-enabled-storing-duplicate-values/76301/1): > > 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?](https://forum.inductiveautomation.com/t/write-to-tag-and-set-timestamp/60100/22): > > 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 ---
**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\ **Post date:** [July 17, 2023, 3:23am UTC](https://forum.inductiveautomation.com/t/named-query-with-where-clause-of-datetime-not-working-properly/76371/9 "2023-07-17T03:23:59Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/e/de03982578436bbf4b8e9befa8870b5ae3127c9f.png)