Interpreting Timestamps in Repository Log Tables
Administering BI Office often requires querying the repository to understand certain operations and events. This typically involves querying the various log tables. In Pyramid 2018, it looks as if the different log tables (such as the [dbo].[server_log_transaction] table) use timestamp columns that are [bigint] data types versus some other standard date/time data types. How do we interpret the values found there. For example, while looking at a transaction log entry via the Logs admin page, a transaction had a Start Time of 11/21/2017 7:24:43. The corresponding row in the [dbo].[server_log_transaction] table shows a value of 1511267083752 in the [start_time] column. How does that [bigint] value translate to the displayed date and time?
2 replies
-
The time stamp saved in the logs tables represents an epoch time in UTC.
an epoch time is the number of milliseconds that have elapsed since January 1, 1970
in order to translate this value from a long back into a date time you need to add it to January 1, 1970
so for example in order to see the date time representation of "1511267083752" , you can run the following query in SQL Server
"select DATEADD(s, 1511267083752/1000, '1970-01-01 00:00:00')"
please note that the time stamp value saved in the logs is in UTC ,running the above query will show a date time result which is represented in UTC ("2017-11-21 12:24:43.000").
-
Thank you! Very helpful :-)