Easy Chart Database pens error: t_stamp is not a date

Ignition
,
1

v8.1.5

I’m getting an error when trying to create a database pen on the Easy Chart directly from one of the standard tag history tables (sqlt_data_x_yyyy_mm). How should I be doing this when using the database pens with unix epoch timestamps?

image

image

2

You’ll need to create a view of each sqlt table that has the milliseconds UTC converted into a proper timestamptz or similar.

3

I’m just curious, any particular reason you’re using a DB pen instead of a Tag Pen?

Also, it’s a bit wonky, but any of those DB fields you can add additional query logic and it will execute. You could type into the Time Column field (I’m using MySQL syntax):

FROM_UNIXTIME(t_stamp / 1000)

Note: If I remember correctly, t_stamp typically has an index defined on it. So if it’s a really large table, this will likely kill performance due to the query optimizer not being able to utilize the index.

4

It was more as a test. I was testing using the tag history splitter for a local and remote database, where local is short-term storage and remote is long-term, and testing the query cutoff point. It wasn’t working so I wanted to query the tables directly (could have just used a table as well or the individual history providers). I was more curious if I was actually able to use the epoch t_stamp field more than anything when I ran into the issue. Bit sad that I can’t nicely :frowning:

5

Could you please elaborate on that?

I'm importing data from a third party server into ignition DB. I've created a table with id, t_stamp, name, value1 and value2 columns. I'd like to chart value1 and value 2 in regards to t_stamp using Easy Chart.

Raw data contains timestamp as a string. Using the script, I've converted this string using type casting, getDate, setTime and toMillis functions to integer and stored it in t_stamp column. When selecting t_stamp as a Time Column, I get the exact same error.

Or better question would be, in what format should I store timestamp in t_stamp column, so Easy Chart recognizes it as a valid date column?

UPDATE: I found that Easy Chart recognizes database table column type DATETIME as valid for Time Column. However, now I can't figure out how to store the date (that I got from getDate and setTime functions) into that column. Running prep update where I store the date value into that column generates SQL error.

6

Show us?

1 Like
7

script console error.txt (10.1 KB)
I run the script from the script console.

def storeDataInDB(loggerNum, loggerName, data):
	tableQuery = 'CREATE TABLE IF NOT EXISTS logger_' + loggerNum + '(t_stamp DATETIME NOT NULL, name VARCHAR(255) NOT NULL, flow FLOAT(23) NOT NULL, pressure FLOAT(20) NOT NULL, PRIMARY KEY (t_stamp))'
	system.db.runUpdateQuery(tableQuery)
	
	for i in range(len(data)):
		timestampStr = data[i][0]
		date = system.date.setTime(system.date.getDate(int(timestampStr[0:4]), int(timestampStr[5:7])-1, int(timestampStr[8:10])), int(timestampStr[11:13]), int(timestampStr[14:16]), int(timestampStr[17:]))
		system.db.runPrepUpdate("INSERT INTO logger_? (t_stamp, name, flow, pressure) VALUES (?,?,?,?)", [loggerNum,date,loggerName,round(data[i][1],4),round(data[i][2],4)])
	return 0
8

You are trying to use a question mark replacement parameter as part of a table name. Not permitted. (By JDBC--parameters are only for values, not for SQL structure.)

2 Likes