T_stamp in SQLite DB

I created a group transaction, save into SQLite Database, table name is TB001, with two tag , randomdouble1 & randomdouble2 .

And when I query the table, and find the t_stamp seems is integer value, not datetime.

I am trying to present the data in easy chart , so I drag a easy chart component, and add a DB pen.

Select SQLite DB and the my tag & t_stamp.
The message says" The type of time column 't_stamp' is not a date."

I tried with MSSQL DB , same step, it works fine, the t_stamp is datetime type.

How to do this in SQLite DB .
I know there is a way to convert integer to datetime like

SELECT datetime(t_stamp/1000, 'unixepoch');

But how to use this SQL query becomes another issue, seems DB pens only allow table selection, not query.

Judging by the column alignment in the screengrab of Resultset1 you are returning t_stamp as a string.
t_stamp string

I suspect that your database table is not configured correctly.

Please refer to screenshot above, t_stamp is like a integer.

It can convert to datetime with datetime(t_stamp/1000, 'unixepoch')

If the DB is not configured correctly, can you help where to check.

Try the SQLite dump command. Post the Create section to let us see the structure.

An even better solution would be to use a DB technology suited to production data. SQLite is made to hold configuration data, not production data. It is single-threaded and does not conform to modern SQL standards.

If you need free, use PostgreSQL or MariaDB.

1 Like

I query the t_stamp data type from table browser, and noticed t_stamp is text.
image

So I manually create another table TB100 , with t_stamp is datetime,create new Transaction Group and untick "Automatically create table" and select this new existing table , it works fine, I think issue is at the "Automatically create table" when create named query.

image

Anyway, I will go on this solution, thanks for your help.

And for my own curiousity , I checked tag history table which is also auto created, and the t_stamp is integer, not sure why it works fine in Easy chart, probably there is convertion Ignition backend.

image

Thanks Pturmel, I will use MSSQL for production , now I am doing test and training , thought SQLite would be easiest way to do without any futher application installation.

The two technologies are significantly different, because..."Microsoft"

You should definitely develop with MSSQL, otherwise you'll be refactoring queries during commissioning.

1 Like