Hi all, keen to get feedback on how people have used the date field timestamp in any of their reporting (CSV export or API data feed).

From our understanding:

• if a field in a template is Date/Time and has both Date and Time checked, that data is stored as the UTC equivalent of the actual inputted date/time (for eg, 2023-10-31T11:00:00Z)
• if a field is Date/Time and has only Date checked, that data is stored as 2023-10-31T00:00:00Z using the previous example (ie the 12am equivalent of the date)
• if a field is Date/Time and has only Time checked, that data is stored as 2024-09-02T11:00:00Z (ie UTC equivalent of the entry date, but using the Time reference)

We have the challenge of metrics reporting. Using the above understanding of timestamps, if an audit is timestamped in NZ at 1am on the 1/2/2024, this would equate to 12pm on the 31/1/2024 (UTC equivalent). If my SC profile is set to Sydney Australia, I would see this timestamp as 11pm on the 31/1/2024.

Extracting this data via the data feed API (or CSV), which stores it as the UTC equivalent, can lead to potential misrepresentation of the monthly audit counts. Ideally we would like that audit performed on the 1/2/2024 at 1am in NZ be recognised as a Feb audit, but it is stored as a Jan audit with a timestamp of 2024-01-31T12:00:00Z, even if we convert to Sydney time zone (for reporting purposes).

As possible way around this would be if the User Profile API also includes the User time zone. We could then convert any timestamp used in reporting/metrics in the template to the actual local date/time. Another option would be if the back end also stores the actual local date/times.

Keen to hear what others have come across and/or have solved similar challenges (or whether this should be added to the Ideas area ).

Cheers

Rob