This is the “default” installation with the Sample_SQLite_Database that gets installed with Ignition 8.1.4. Why is this a text string instead of a “DATETIME” formatting as advised if one looks in the Config>Security>Auditing>Edit>Advanced Properties>Timestamp Column description?
It uses TEXT because our translator defaults need to be updated, but it should still work fine. SQLite doesn’t have any concept of datetimes as a type - there are very few actual datatypes in SQLite. Our translator currently uses TEXT, which relies on implicit string formatting (you can override the storage/retrieval format using a connection property), but at some point we’re planning to change that to INTEGER and storing epoch timestamps directly.
Ah, OK. I discovered that while making a very simple Perspective table display that wasn’t doing a column date “render” - apparently, it’s not smart enough to know how to “render” a text string that represents an Epoch timestamp. This doesn’t seem to be all that pressing though, as I don’t see any reason to want to actually store, longer-term, Epoch dates this way.
And why only this table defaults uppercase everything?
No one’s ever asked us to change it?
It’s been that way for over a decade.
But, we can certainly change that default.
Well, it sure would be ‘more normal’ to have all tables the same (lowercase).
And also you don’t need to put table names and field names inside the quotation marks in queries…
So, yes please, change it…
I don’t particularly care about the case, as long as I know what it is when things are case-sensitive. Conventions in different environments can go both ways.
I’m in development and test phase of a project with Ignition 8.1, as development phase I’m using a SQLite database to test some features and in the gateway>audit profile>view log I’m getting the following error:
java.sql.SQLException: Error parsing time stamp
There is a way to solve this without changing the database server?
@Rv.Batista for whatever it's worth, I just ran into that too. In the Designer I can view it just fine in the Database Query Browser except that the datetime isn't human readable. I'm using this for for example, the last 30 days:
SELECT datetime(EVENT_TIMESTAMP/1000, 'unixepoch'), ACTION
FROM AUDIT_EVENTS
WHERE EVENT_TIMESTAMP > (strftime('%s','now')-(86400*30))*1000
SQLite is a toy database. It has limited datatype support to focus on its primary purpose: hold configuration data. (Not to mention being single-threaded--so prone to deadlocks in a multi-threaded environment.)
Don't use SQLite. If you need something free, use PostgreSQL or MariaDB.