Audit log gives numbers instead of datetime

Support
1

hi @yanokwa, can you help me with this audit log, my audit log file give me numbers for the start and end instead of date.
below is my audit log file.
image

i used ODK collect v1.14.0 and ODK Briefcase v1.10.0

thank you.

2

@Fabla This is because of how you are importing. You can confirm by looking at the CSV with a good text editor (e.g., https://notepad-plus-plus.org).

I don't know what version of Excel you are using, but you'll need to go to File, Import, then specify the columns as Text instead of General.

3

Please take a look at the audit timestamp section at https://github.com/opendatakit/docs/pull/649/files.

2 Likes
4

Thanks @LN, you are right (and I am wrong)! The import is fine, but it's a UNIX timestamp so it shows up as a big number.

5

Hi Yaw, I have opened it using notepad ++ and imported it using excel 2010 and 2013 and the results are still the same.

6

Time is stored as a unix timestamp which is milliseconds elapsed since Jan 1, 1970. This makes it really easy to calculate how long a question took to answer -- you add a column that computes end - start.

If you do want to see a human-friendly date/time, see the link I posted above.

7

https://docs.opendatakit.org/form-audit-log has improved docs. Thanks for writing this up, @LN!

8

Just in case anyone else comes across this looking for a shortcut to converting unix time to LibreOffice (and probably other spreadsheet programs, including the dominant commercial one) time format:

=IF(D2<>"",((D2/86400000)+25569),"")

And then set the cell (or column) format to the time format of your choice and you should get the expected human-readable date-time format. The conditional accounts for blank entries (end times aren't always filled in the audit log) and prevents those being filled with 1970.