# Easy Chart Database pens error: t\_stamp is not a date

**URL:** <https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502>\
**Category:** Ignition\
**Tags:** vision, ignition81\
**Created:** [September 1, 2021, 1:55am UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502 "2021-09-01T01:55:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 1, 2021, 1:55am UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/1 "2021-09-01T01:55:22Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/b/6ba3cbb4bc1d7bcbbcbf6ea70e7637ea6eecf636.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/b/abeef08eb3d8dc1c634855413cccfb66227170fd.png)

---

<div class="post-metadata">

**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:** [September 1, 2021, 11:45am UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/2 "2021-09-01T11:45:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\
**Post date:** [September 1, 2021, 1:10pm UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/3 "2021-09-01T13:10:38Z")

</div>

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):

```auto
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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [September 1, 2021, 1:35pm UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/4 "2021-09-01T13:35:22Z")

</div>

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 ☹

---

<div class="post-metadata">

**Author:** ![ena\_bj](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ena_bj/32/13318_2.png) [@ena\_bj](https://forum.inductiveautomation.com/u/ena_bj)\
**Post date:** [May 4, 2023, 8:04am UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/5 "2023-05-04T08:04:30Z")

</div>

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.

---

<div class="post-metadata">

**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:** [May 4, 2023, 12:21pm UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/6 "2023-05-04T12:21:34Z")

</div>

> [@ena\_bj](#):
>
> Running prep update where I store the date value into that column generates SQL error.

Show us?

---

<div class="post-metadata">

**Author:** ![ena\_bj](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ena_bj/32/13318_2.png) [@ena\_bj](https://forum.inductiveautomation.com/u/ena_bj)\
**Post date:** [May 4, 2023, 12:34pm UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/7 "2023-05-04T12:34:34Z")

</div>

[script console error.txt](https://forum.inductiveautomation.com/uploads/short-url/9Dk9alMQZwgKdnenipuUG9WWZ9t.txt) (10.1 KB)  
I run the script from the script console.

```auto
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

```

---

<div class="post-metadata">

**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:** [May 4, 2023, 12:49pm UTC](https://forum.inductiveautomation.com/t/easy-chart-database-pens-error-t-stamp-is-not-a-date/50502/8 "2023-05-04T12:49:53Z")

</div>

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.)