API: Provide a UTC Timestamp value with Live API responses

[9joshua]

Context

Currently the API returns 2 different times:

• server time, which is actually the timezone configured in the UI for the website being reported
• visit local time, which is the local time of the visitor

Problem

• It is difficult to convert the serverTimestamp value to a UTC/Unix Timestamp
• Local daylight saving time changes can result in overlaps of actions. i.e. a visitor can purchase a product an hour before they first entered the site when clocks are set back one hour.
• This makes uniform reporting of actions/times difficult for multiple sites using different time zones
• a serverTimestamp based on anything other than UTM is not a Unix Timestamp and this can cause confusion

Solution

Return a utcTimestamp value with API requests that currently include sever time and local time information.

[SebGay]

As requested by Matomo support, I am adding some further details to present specific issues:

actionDetails.timestamp being returned as a "localised" unix timestamp.

To me this frankly escalates beyond an issue to a straight up bug. This should be a unix timestamp on UTC, and localisation can be done with the "prettyfied" field.

To clarify, the other timestamp fields - serverTimestamp/lastActionTimestamp, and firstActionTimestamp do not suffer this issue. They are properly formulated unix timestamps operating on UTC.

Pretty fields not containing UTC offset

As outlined by @9joshua, unnecessary confusion is caused by providing the UTC offset in timestamps. Because sites are attached to regional timezone, most sites will alternate between two timezones over the year. On the timezone change, because the actionDetail.timestamp is not a proper unix timestamp, a timeline of user actions cannot be reconstructed by the provided fields (actionDetail.timestamp and actionDetail.serverTimePretty). An example of this is in real data is shown in the Matomo_daylight_savings.xlsx

serverDate always in UTC offset

This is more of a documentation point, but important none the less when importing data from Matomo Cloud into data lakes/warehouses. This question arose when asking which field the date parameter of the getLastVisitsDetails actually filtered on, with an initial guess of serverDate. After testing, I have come to the following conclusions:

1. The date parameter when applied explicitly corresponds to the site localised date of the first action.
2. serverDate is not calculated as a localised date when the site id is selected, going against this github comment.

The below table was generated by with the following request parameters and paginating:

"idSite": 1,
"period":"day",
"date": "2024-07-28",
"format":"JSON",
"filter_limit": 5000,

Our site with id=1 has timezone Switzerland, therefore during this period was UTC+2 (confirmed by the “prettyfied” fields). Below starts at row 5971 when sorted by firstActionTimestamp

Row	serverDate	firstActionTimestamp	firstActionUTC	serverDatePrettyFirstAction	serverTimePrettyFirstAction
5971	27.07.2024	1722124790	27.07.2024 23:59	Sunday, July 28, 2024	01:59:50
5972	27.07.2024	1722124790	27.07.2024 23:59	Sunday, July 28, 2024	01:59:50
5973	27.07.2024	1722124791	27.07.2024 23:59	Sunday, July 28, 2024	01:59:51
5974	27.07.2024	1722124792	27.07.2024 23:59	Sunday, July 28, 2024	01:59:52
5975	27.07.2024	1722124792	27.07.2024 23:59	Sunday, July 28, 2024	01:59:52
5976	27.07.2024	1722124792	27.07.2024 23:59	Sunday, July 28, 2024	01:59:52
5977	28.07.2024	1722124794	27.07.2024 23:59	Sunday, July 28, 2024	01:59:54
5978	27.07.2024	1722124794	27.07.2024 23:59	Sunday, July 28, 2024	01:59:54
5979	27.07.2024	1722124794	27.07.2024 23:59	Sunday, July 28, 2024	01:59:54
5980	27.07.2024	1722124795	27.07.2024 23:59	Sunday, July 28, 2024	01:59:55
5981	27.07.2024	1722124795	27.07.2024 23:59	Sunday, July 28, 2024	01:59:55
5982	27.07.2024	1722124796	27.07.2024 23:59	Sunday, July 28, 2024	01:59:56
5983	27.07.2024	1722124797	27.07.2024 23:59	Sunday, July 28, 2024	01:59:57
5984	28.07.2024	1722124798	27.07.2024 23:59	Sunday, July 28, 2024	01:59:58
5985	27.07.2024	1722124799	27.07.2024 23:59	Sunday, July 28, 2024	01:59:59
5986	28.07.2024	1722124800	28.07.2024 00:00	Sunday, July 28, 2024	02:00:00
5987	28.07.2024	1722124800	28.07.2024 00:00	Sunday, July 28, 2024	02:00:00
5988	28.07.2024	1722124800	28.07.2024 00:00	Sunday, July 28, 2024	02:00:00
5989	28.07.2024	1722124802	28.07.2024 00:00	Sunday, July 28, 2024	02:00:02
5990	28.07.2024	1722124803	28.07.2024 00:00	Sunday, July 28, 2024	02:00:03
5991	28.07.2024	1722124805	28.07.2024 00:00	Sunday, July 28, 2024	02:00:05
5992	28.07.2024	1722124805	28.07.2024 00:00	Sunday, July 28, 2024	02:00:05

This is confirmed by grouping all the returned results by serverDate

serverDate	Count
28.07.2024	55248
27.07.2024	5907

(as expected from 2/24 hours of results being on 27.07.2024 UTC)