# How to get audit event timestamp in date and time

**URL:** <https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836>\
**Category:** Ignition\
**Tags:** ignition81, vision\
**Created:** [January 5, 2024, 10:36am UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836 "2024-01-05T10:36:03Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Stef\_Velers](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@Stef\_Velers](https://forum.inductiveautomation.com/u/Stef_Velers)\
**Post date:** [January 5, 2024, 10:36am UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/1 "2024-01-05T10:36:03Z")

</div>

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 ☹  
My namedquery is as follows:

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/3/13fa3a69c79b07fc606afcdb6429c042c837e106.png)  
The timestamps in this table must be in date and time.

---

<div class="post-metadata">

**Author:** ![schenk](https://avatars.discourse-cdn.com/v4/letter/s/6bbea6/32.png) [@schenk](https://forum.inductiveautomation.com/u/schenk)\
**Post date:** [January 5, 2024, 11:58am UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/2 "2024-01-05T11:58:55Z")

</div>

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')`

---

<div class="post-metadata">

**Author:** ![Stef\_Velers](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@Stef\_Velers](https://forum.inductiveautomation.com/u/Stef_Velers)\
**Post date:** [January 5, 2024, 12:22pm UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/3 "2024-01-05T12:22:08Z")

</div>

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

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

```

I use SQLite

---

<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:** [January 5, 2024, 2:07pm UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/4 "2024-01-05T14:07:08Z")

</div>

> [@Stef\_Velers](#):
>
> I use SQLite

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.

---

<div class="post-metadata">

**Author:** ![colin\_doyle](https://avatars.discourse-cdn.com/v4/letter/c/b4bc9f/32.png) [@colin\_doyle](https://forum.inductiveautomation.com/u/colin_doyle)\
**Post date:** [April 30, 2024, 2:09pm UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/5 "2024-04-30T14:09:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Hiren\_Kathiriya](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@Hiren\_Kathiriya](https://forum.inductiveautomation.com/u/Hiren_Kathiriya)\
**Post date:** [August 1, 2024, 12:39pm UTC](https://forum.inductiveautomation.com/t/how-to-get-audit-event-timestamp-in-date-and-time/83836/6 "2024-08-01T12:39:24Z")

</div>

Hi Stef,

I am having the same issue as you had.  
Could you tell me where and how you inserted the code.  
I would appreciate any help.  
Thank You.