# Best way to query value at a certain time in a report **URL:** \ **Category:** Ignition\ **Tags:** reporting\ **Created:** [January 8, 2025, 2:14pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588 "2025-01-08T14:14:41Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Nicolas\_Glienke](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@Nicolas\_Glienke](https://forum.inductiveautomation.com/u/Nicolas_Glienke)\ **Post date:** [January 8, 2025, 2:14pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/1 "2025-01-08T14:14:41Z")
Hello, in a report, I would like to query the value of a tag/parameter "temperature" at a certain time "timestamp". What would be the easiest way to do this? From what I understand, I could use scripting, SQL query or an expression, but since I am inexperienced with all 3 I can't find a solution on my own. SQL seemed the most intuitive, so I got as far as: ```python SELECT column FROM table WHERE t_stamp = timestamp ``` but this raises the questions: In which table are the values saved? In which column is the tagname saved? How can I enter the timestamp that is saved in Ignition in this format, so that SQL can read it? `dateFormat({[default]temperature},"dd.MM.yyyy HH:mm:ss")` Thanks in advance to anyone reading. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [January 8, 2025, 2:43pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/2 "2025-01-08T14:43:34Z")
The first question, is "How is the temperature being stored?". That will provide context to the question of how to query the data. ---
**Author:** ![Nicolas\_Glienke](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@Nicolas\_Glienke](https://forum.inductiveautomation.com/u/Nicolas_Glienke)\ **Post date:** [January 8, 2025, 2:56pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/3 "2025-01-08T14:56:03Z")
Originally I have them stored as memory tags with tag history in a database. I also have them available as parameters in my report. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [January 8, 2025, 3:02pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/4 "2025-01-08T15:02:55Z")
Look here: > **[Tag Historian Query | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/tag-historian-query)** > > The Tag Historian Query provides a simple way to query data from Tag Historians. In the Tag History Query you can read data from Historical tags for specific date ranges, apply aggregates, and specify the sample size. It functions the same as a Tag... Essentially, you need to query the historian for the data, and you can allow the user to select the time they are interested in. You can also do this through a Script Data Source if the Tag Historian Data Source doesn't meet your exact needs. You can read about that at these links: > **[Scripting Data Source | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/scripting-data-source)** > > The Script data source allows you to use scripting to add data into a report, or modify existing data. With this data source, you can pull in data from the database and then modify it using a script before pushing it out as a data key for use in the... > **[system.tag.queryTagHistory | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-tag/system-tag-queryTagHistory)** > > Issues a query to the Tag Historian. > **[system.tag.queryTagCalculations | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-tag/system-tag-queryTagCalculations)** > > Queries various calculations (aggregations) for a set of tags over a specified range. Technically, you can query the Tag History Tables directly, but that is not recommended because they are generally partitioned and using the built in functions takes care of the gory details of crossing partition boundaries for you. ---
**Author:** ![Nicolas\_Glienke](https://avatars.discourse-cdn.com/v4/letter/n/f475e1/32.png) [@Nicolas\_Glienke](https://forum.inductiveautomation.com/u/Nicolas_Glienke)\ **Post date:** [January 9, 2025, 7:13am UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/5 "2025-01-09T07:13:55Z")
Hello, I have tried the tag historian query yesterday but I got the error "ERROR: class java.lang.String cannot be cast to class java.util.Date (java.lang.String and java.util.Date are in module java.base of loader 'bootstrap')". I found out that this was because I formatted my start and end time with dateFormat(). ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/971fb10f2778dc5f6b8ec5b10ab3136991436dec.png) However, when leaving the date without format and entering in the query, there are no errors ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/6/b60c1fe904fe4ec0a1ee3413ef0ab27d9cc0c573.png) Thank you very much for your help! ---
**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:** [January 9, 2025, 12:22pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/6 "2025-01-09T12:22:58Z")
> [@Nicolas\_Glienke](#): > > leaving the date without format That's because formatting changes the date into a string. It isn't a date anymore when formatted. ---
**Author:** ![AUSDIV](https://avatars.discourse-cdn.com/v4/letter/a/e47c2d/32.png) [@AUSDIV](https://forum.inductiveautomation.com/u/AUSDIV)\ **Post date:** [January 9, 2025, 3:04pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/7 "2025-01-09T15:04:10Z")
I am rather new to ignition but I have been enjoying using the named Queries. Below is an example of a query, that uses the Parameter to put in the time value to go backwards and query from. I called it Minutes back. You can also allow for 2 inputs of time ranges, and use a BETWEEN. I also like to convert the time from T\_stamp because I like to be able to read it. Now the parameter for the named query just needs linked to a value in your project! One thing to note with named queries, is I have the database set to not create new tables every month. If you use named queries when the database creates a new table for the new month, or week or whatever... default setting is month, it breaks the named queries. I then wrote some of my Queries to index the data how I want and keep my database clean. > SELECT > Dateandtime, > ROUND([15]/100,2) AS 'T4A1 PH' > FROM > ( > Select > CAST(DATEADD(SECOND, (t\_stamp/1000) - 18000,'1970/1/1') AS DateTime) DateandTime, > tagid, floatvalue > From Ignition\_Server.dbo.sqlth\_1\_data > > ) as Source > Pivot > ( > AVG(floatvalue) FOR tagid IN ([15]) > )as PVt > WHERE dateandtime \>= DATEADD(minute, :MinutesBack ,GETDATE()) > AND [15] is not null > AND [Dateandtime] is not null > Order by DateandTime asc; ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/8/f80cf93129fcb839013a11012d07a80c56acfd14.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/6/6622cd45e44c6b52011f47e53b4bbd21de02fb4b.png) ---
**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:** [January 9, 2025, 3:13pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/8 "2025-01-09T15:13:12Z")
> [@AUSDIV](#): > > been enjoying using the named Queries. Whether you enjoy them or not, they are a terrible choice for retrieving from the historian's automatic tables. It is very difficult to reliably get the right `_data` table names, nor to properly UNION partitions together based on timestamps, nor to properly convert long timestamps to Java datetime values in a DB-brand agnostic way. The historian's native queries can deliver the last value in a time frame simply by providing that timeframe, choosing result size == 1, and choosing last value as the aggregation mode. Please do not recommend direct queries of historian data here unless **you** are prepared to help the OP in navigating all of the nuances that apply to their situation. ---
**Author:** ![AUSDIV](https://avatars.discourse-cdn.com/v4/letter/a/e47c2d/32.png) [@AUSDIV](https://forum.inductiveautomation.com/u/AUSDIV)\ **Post date:** [January 9, 2025, 3:13pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/9 "2025-01-09T15:13:42Z")
> [@AUSDIV](#): > > WHERE dateandtime \>= DATEADD(minute, :MinutesBack ,GETDATE()) So if you used this method, you would alter this code below from: WHERE dateandtime \>= DATEADD(minute, :MinutesBack ,GETDATE()) to Where dateandtime = MinutesBack but presumably you could use a variable named 'TimeSelection' or something of that nature, and rather than storing numbers like -10 as I did, you would use the DateTime variable in the parameter group. Then, you can use the Date and time picker perspective component. ---
**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:** [January 9, 2025, 3:16pm UTC](https://forum.inductiveautomation.com/t/best-way-to-query-value-at-a-certain-time-in-a-report/100588/10 "2025-01-09T15:16:58Z")
@AUSDIV, you're posting **code** as **quotations**. Don't! Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).