SQL query with date range

Ignition
1

I’m very new to Ignition and have very limited experience with any kind of scripting. I’m working on a very simple report that has an XY plot on it that trends a temperature over a measured length. I’m able to query the temperature and length data, but I’m not sure how to incorporate my StartDate and EndDate parameters into the query so it pulls the data I’m looking for.

This is the code I have so far:

SELECT R105_GelStar_Trend.GelStar_ActTemp,
R105_GelStar_Trend.GelStar_Location
FROM R105_GelStar_Trend

When I test the query in the database query browser, it pulls data from a random date a few years ago. Any help would be appreciated.

2

That depends on what other columns you have and what data type they are. Do you have a column that contains a SQL date, or maybe a UTC time stamp column?

3

Welcome!

7.9 or 8.0+?

You say report, using the report module?

https://inductiveuniversity.com/videos/sql-query/7.9

4

I can add a time stamp column, but the dates are way off:

image
image944×735 48.3 KB

5 
SELECT R105_GelStar_Trend.GelStar_ActTemp,
R105_GelStar_Trend.GelStar_Location
FROM R105_GelStar_Trend
where tstamp BETWEEN ? and ?

For the ? and ? link to StartDate and EndDate, that looks like a 79 IDE refer to my linked video

6

Hm, now I’m getting a “query could not be executed” message on the query browser. I’ve already watched that video a few times, and have looked in the online user manual. Neither were very helpful to me.

7

You can’t run ? in the query browser, for the query browser you need to set a specific date.

What DB are you using?

8

That would make sense then. I’m just using the Universal database.

9

Try this in the DB query browser (by universal database I assume you mean SqlLite… (MS)):

SELECT R105_GelStar_Trend.GelStar_ActTemp,
R105_GelStar_Trend.GelStar_Location
FROM R105_GelStar_Trend
WHERE tstamp > DATE_ADD(NOW(), INTERVAL -1 DAY)
10

No that doesn’t seem to be working. It’s getting late here so I’ll need to start fresh in the morning. Thanks for your help so far.

11

OK. I don’t know what you’re referring to when you say “universal database”.

Go into your gateway tomorrow, config, and check the JDBC connector used by the DB, let us know.

12

Sorry what I put earlier was MySQL. If it is MS, try this:

where tstamp > DATEADD(day,-1,GETDATE())
13

Looks like the JDBC connector used is Microsoft SQLServer.

When I was saying Universal, I meant this:
image

It looks like that code worked!

Which now brings me to me next issue… I only need the last 2 minutes of data. Also, the data don’t seem to be plotted on my XY chart in the report module when I add the data sources to the chart.
image

I should probably add, the reason I’m asking so many questions is that my company really doesn’t have an Ignition expert. So this is my main resource for any issues I’m running into.

14

That Universal is the syntax you’re telling query builder but it does not necessarily reflect what your actual db connection is. Go to your gateway->status->database connections and you should be able to tell there.

15

OP has already stated JDBC is MS SQL

DateADD(mi, -2,GETDATE())

However, this is only for query browser.

For your final report, just leave the EndDate as now and change the StartDate to:

dateArithmetic(now(), -2, "min")
16

Run it in the query browser first to ensure you have data returned for the last 2 minutes, remember the query/report can’t give data that isn’t in the DB!

17

I don't think you have your keys configured correctly.

There are IU videos covering this.

I should probably add, the reason I’m asking so many questions is that my company really doesn’t have an Ignition expert. So this is my main resource for any issues I’m running into.

We're here to help, but consider going through IU also.

18

I've watched the IU video and it helped me figure out the data key issue.

My SQL query is as follows:

SELECT R107_GelStar_Trend.GelStar_ActTemp,
R107_GelStar_Trend.GelStar_Location,
R107_GelStar_Trend.t_stamp
FROM R107_GelStar_Trend
WHERE R107_GelStar_Trend.t_stamp BETWEEN ? AND ?

Where ? are my start date and end date parameters.

I'm confused why I'm not seeing any data plotted on the preview of my XY chart though. It seems like the preview is only using 100 points of raw data when there's far more than that in reality based on my query.

19

By default, the Preview tab of the report module is limited.

You can change how many rows are returned or remove the limit entierly by unchecking the checkbox.

image

20

THANK YOU!!! My report is showing exactly what I want now. Phew.

1 Like