How to get audit event timestamp in date and time

Ignition
,
1

Hi,

I'm facing the problem that my timestamps are not converted to date and time in my Named query. Can someone help me to convert it to a date and time instead of the timestamp value? I can't figure it out :frowning:
My namedquery is as follows:

SELECT
ae.event_timestamp AS Time,
ae.actor AS User,
ae.action AS Action,
ae.action_value AS Value,
case when ae.action='tag write' then
(select oae.action_value from AUDIT_EVENTS oae where oae.action=ae.action and oae.action_target=ae.action_target and oae.EVENT_TIMESTAMP<ae.EVENT_TIMESTAMP order by oae.EVENT_TIMESTAMP desc LIMIT 1)
else '' end as oldValue,
ae.action_target AS Target,
ae.actor_host AS Host,
ae.status_code AS 'Status Code',
ae.originating_system AS 'System',
ae.originating_context
FROM AUDIT_EVENTS ae
ORDER BY
ae.event_timestamp DESC

image
image909×693 148 KB

The timestamps in this table must be in date and time.

2

You will need to convert the value from unix epoch to get date and time.

Which DB are you using? I use this for MSSQL:
DATEADD(s, t_stamp/1000, '1970-01-01')

3

Thanks it helped me to get an idea, it now works with the following:

DATETIME(ROUND(ae.event_timestamp / 1000), 'unixepoch') AS Time

I use SQLite

4

Eww! You really should not. It is not designed for production, just to hold configuration data. If you need something free, use MariaDB or PostgreSQL.

5 Likes
5

Hi Stef,

I am having the same issue as you had.
Could you tell me where and how you inserted the code.
I am using SQlite as a database for and audit trail function.
My event time stamp is in milliseconds
I would appreciate any help.