EVENT_TIMESTAMP in AUDIT_EVENTS is text, not datetime

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?

24

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?

1 Like

No one’s ever asked us to change it?
image
It’s been that way for over a decade.

But, we can certainly change that default.

1 Like

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.