# SQLite Audit log date time not able to render

**URL:** <https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [November 9, 2023, 7:18am UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697 "2023-11-09T07:18:46Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\
**Post date:** [November 9, 2023, 7:18am UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/1 "2023-11-09T07:18:46Z")

</div>

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

---

<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:** [November 9, 2023, 12:55pm UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/2 "2023-11-09T12:55:13Z")

</div>

> [@Learner\_Ignition](#):
>
> Any suggestions

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.

---

<div class="post-metadata">

**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\
**Post date:** [November 10, 2023, 3:16am UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/3 "2023-11-10T03:16:43Z")

</div>

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

---

<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:** [November 10, 2023, 12:03pm UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/4 "2023-11-10T12:03:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Learner\_Ignition](https://avatars.discourse-cdn.com/v4/letter/l/b3f665/32.png) [@Learner\_Ignition](https://forum.inductiveautomation.com/u/Learner_Ignition)\
**Post date:** [November 16, 2023, 11:16am UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/5 "2023-11-16T11:16:47Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 11:28am UTC](https://forum.inductiveautomation.com/t/sqlite-audit-log-date-time-not-able-to-render/81697/6 "2023-11-16T11:28:44Z")

</div>

> [@Learner\_Ignition](#):
>
> Below query worked for me.

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