SQLite Audit log date time not able to render

Ignition
,
1

Hi,
I had a MySQL database for storing Audit logs and to display on perspective table I have used named query and bind it to the table and render the Event date & time column to YYYY-MM-DD HH:mm:ss and it works fine.

then I changed the database connection to SQLite and event starts storing in SQLlite database. However event date & time column displaying date & time in milliseconds and I tried changing render and dateformat properties of table but nothings works.

columns

[
{
"field": "EVENT_TIMESTAMP",
"visible": true,
"editable": false,
"render": "date",
"justify": "left",
"align": "center",
"resizable": false,
"sortable": false,
"sort": "descending",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"width": 75,
"strictWidth": false,
"header": {
"title": "Event Time ",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"style": {
"classes": ""
}
},
{
"field": "Action",
"visible": true,
"editable": false,
"render": "auto",
"justify": "left",
"align": "center",
"resizable": false,
"sortable": false,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": 225,
"strictWidth": true,
"header": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"style": {
"classes": ""
}
},
{
"field": "Action_Target",
"visible": true,
"editable": false,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": false,
"sortable": false,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": 225,
"strictWidth": true,
"header": {
"title": "Action Target",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"style": {
"classes": ""
}
},
{
"field": "Action_Value",
"visible": true,
"editable": false,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": false,
"sortable": false,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": 400,
"strictWidth": false,
"header": {
"title": "Action Value",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"style": {
"classes": ""
}
},
{
"field": "Actor",
"visible": true,
"editable": false,
"render": "auto",
"justify": "auto",
"align": "center",
"resizable": false,
"sortable": false,
"sort": "none",
"filter": {
"enabled": false,
"visible": "on-hover",
"string": {
"condition": "",
"value": ""
},
"number": {
"condition": "",
"value": ""
},
"boolean": {
"condition": ""
},
"date": {
"condition": "",
"value": ""
}
},
"viewPath": "",
"viewParams": {},
"boolean": "checkbox",
"number": "value",
"progressBar": {
"max": 100,
"min": 0,
"bar": {
"color": "",
"style": {
"classes": ""
}
},
"track": {
"color": "",
"style": {
"classes": ""
}
},
"value": {
"enabled": true,
"format": "0,0.##",
"justify": "center",
"style": {
"classes": ""
}
}
},
"toggleSwitch": {
"color": {
"selected": "",
"unselected": ""
}
},
"nullFormat": {
"includeNullStrings": false,
"strict": false,
"nullFormatValue": ""
},
"numberFormat": "0,0.##",
"dateFormat": "MM/DD/YYYY",
"width": 125,
"strictWidth": true,
"header": {
"title": "User",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"footer": {
"title": "",
"justify": "left",
"align": "center",
"style": {
"classes": ""
}
},
"style": {
"classes": ""
}
}
]

Any suggestions. Thanks

2

Change back to MySQL/MariaDB. SQLite isn't designed to be a production database. It is designed to be a configuration database, with a read-mostly, light-duty workload. It takes many shortcuts with SQL to fit that purpose.

3

Historian module is enough (whether using MySQL / SQLite) no additional cost or module needs to purchase right.

4

Database connection capability is part of the platform. Not even the historian is required for this.

1 Like
5

with SQLite database I have updated the named query to below query and it works fine for me.

SELECT strftime("%Y-%m-%d %H:%M:%S",datetime(EVENT_TIMESTAMP/1000,'unixepoch','localtime')) as EVENT_TIMESTAMP

6

In which database? Edit your post to make it clear and I'll delete this one.