Hi there, could someone confirm that the timestamps for time_of_first_interaction, time_of_last_interaction, and time_of_completion in the Content Summary log CSV are provided in Coordinated Universal Time (UTC)?
For context, my company is doing an audit to make sure that the data we are pulling from that CSV is represented to our clients with datetime values reflecting the learner’s timezone. We’d like to confirm that the starting timestamps are in UTC before we develop logic to perform timestamp conversions.
Hi @LaurenMaurerAPDS - yes all the timestamps are stored in UTC, with the additional timezone information encoded alongside them. This was intentional to allow the timestamps to be sortable and comparable regardless of the timezone they were originally recorded in.
In case it is useful for your team, this is the Python code that we use to parse from the string representation to a Python datetime object: kolibri/kolibri/core/fields.py at develop · learningequality/kolibri · GitHub
In layman’s terms, the whole field consists of the UTC timestamp followed by the timezone name in parentheses.
Kind Regards,
Richard
Sorry - I failed to notice that you were asking specifically about the CSV exports - not the database. The above applies to the database, I am double checking the CSV behaviour now.
The exported timestamps in the CSV use ISO 8601 format, where the time is given in the locally recorded time, with the UTC offset following.
In this example timestamp: 2023-05-22 19:22:48.588880-07:00 this parses as at 7pm recorded as 7 hours behind UTC, so the correct UTC time would be 7 hours ahead of this.
If you want the timestamps in UTC time you would either need to parse them to apply the timezone differences, or read them directly from the database as in my comment above.
Apologies for the confusion, and I hope this helps!
Richard
On behalf of the APDS team, thank you very much for your prompt and thorough response, Richard!
Best,
Lauren
1 Like