Robert_Foote | 2023-02-26 22:53:54 UTC | #1
Hi, we currently extract WFM Adherence data for our agents into Google BigQuery/power BI for reporting using this API endpoint: /api/v2/workforcemanagement/adherence/historical. We are having a few issues which we think is related to timezones and daylight saving being on and off.
We have currently set our API ingestion into reporting to only use the Australia/Sydney timezone so we can use a single API call to ingest the data. We have agents configured in WFM business units working in Australia/Adelaide and Australia/Brisbane time zones however.
The Sydney timezone is usually UTC+10 during non daylight savings and becomes UTC+11 during daylight savings (which is currently on). We have set our UTC day time boundaries in the API query as 14:00 to 14:00 which equates to UTC+10 midnight to midnight. This query works fine when daylight savings in off for the Sydney timezone but when it's on (UTC +11) then the API result is returning 2 days results as we have an extra hour that has crossed the day boundary now.
The current API extraction is done using Sydney timezone. For example using these parameters. { "startDate": "2023-01-02T14:00:00", "endDate": "2023-01-03T13:59:59", "timeZone": "Australia/Sydney", "userIds": ["41b58579-6d3a-4f17-95c9-4e5a353b7497"], "includeExceptions": false }
The Result is below showing we get 2 day metrics: {"entityId":"6ce23abf-cb94-469b-bd80-50cb65310da8","data":[{"userId":"41b58579-6d3a-4f17-95c9-4e5a353b7497","managementUnitId":"d196a7db-f65b-4a97-8693-86d19682a4d1","startDate":"2023-01-02T14:00:00.000Z","endDate":"2023-01-03T13:59:59.000Z","adherencePercentage":79.48,"conformancePercentage":67.95,"impact":"Negative","exceptionInfo":[],"dayMetrics":[{"dayStartOffsetSecs":0,"adherenceScheduleSecs":28080,"conformanceScheduleSecs":15600,"conformanceActualSecs":10600,"exceptionCount":16,"exceptionDurationSecs":5762,"impactSeconds":-4990,"scheduleLengthSecs":28800,"actualLengthSecs":82800,"adherencePercentage":79.48,"conformancePercentage":67.95},{"dayStartOffsetSecs":82800,"adherenceScheduleSecs":0,"conformanceScheduleSecs":0,"conformanceActualSecs":0,"exceptionCount":0,"exceptionDurationSecs":0,"impactSeconds":0,"scheduleLengthSecs":0,"actualLengthSecs":3599}],"actualsEndDate":"2023-01-03T13:59:59.000Z","actuals":[{"actualActivityCategory":"Unavailable","startOffsetSeconds":0,"endOffsetSeconds":22295},{"actualActivityCategory":"OffQueueWork","startOffsetSeconds":22295,"endOffsetSeconds":22319},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"5","startOffsetSeconds":22319,"endOffsetSeconds":25216},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":25216,"endOffsetSeconds":27817},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":27817,"endOffsetSeconds":28198},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":28198,"endOffsetSeconds":28407},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":28407,"endOffsetSeconds":28834},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":28834,"endOffsetSeconds":29706},{"actualActivityCategory":"Break","actualSecondaryPresenceLookupId":"2","startOffsetSeconds":29706,"endOffsetSeconds":30321},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":30321,"endOffsetSeconds":30684},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":30684,"endOffsetSeconds":31367},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":31367,"endOffsetSeconds":31986},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":31986,"endOffsetSeconds":33645},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":33645,"endOffsetSeconds":34221},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":34221,"endOffsetSeconds":35173},{"actualActivityCategory":"OffQueueWork","actualSecondaryPresenceLookupId":"3","startOffsetSeconds":35173,"endOffsetSeconds":35238},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":35238,"endOffsetSeconds":36165},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":36165,"endOffsetSeconds":36475},{"actualActivityCategory":"Break","actualSecondaryPresenceLookupId":"1","startOffsetSeconds":36475,"endOffsetSeconds":37370},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":37370,"endOffsetSeconds":38170},{"actualActivityCategory":"Meal","actualSecondaryPresenceLookupId":"4","startOffsetSeconds":38170,"endOffsetSeconds":40006},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":40006,"endOffsetSeconds":40901},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":40901,"endOffsetSeconds":40980},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":40980,"endOffsetSeconds":41412},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":41412,"endOffsetSeconds":42643},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":42643,"endOffsetSeconds":43213},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":43213,"endOffsetSeconds":50761},{"actualActivityCategory":"Unavailable","startOffsetSeconds":50761,"endOffsetSeconds":86399}]}],"lookupIdToSecondaryPresenceId":{"0":"bd0e6462-3bc5-4d73-b87c-480a6586cf24","1":"480203d6-cbc5-4b00-9390-12487d806dd5","2":"597fd842-cd2d-4867-8f5a-3161ef167c22","3":"034b92ca-cc0a-45d2-a0e8-4f12fbef7921","4":"1288f9a4-c55f-43c8-b61c-c690ac7e2354","5":"895b292a-e9c2-47db-b62c-849db35b36c7","6":"adc0ebb4-df0c-4fe5-9c1f-6a1df5b29947","7":"fccfabbb-41f8-43b3-8690-8bf67a7b2283","8":"d2ae04dc-4183-409e-b3f7-afd00223ff29","9":"fdbd5d11-681d-4aaf-b432-fce6dcd15028","10":"03518c49-cc78-4096-8aa4-6ca3ad947c57","11":"a4239d9f-7b18-4f07-9f01-3389235500d0"},"version":1}
As daylight savings is now on at the moment in Sydney (UTC+11) we can adjust the UTC day boundaries to be 13:00 to 13:00 to match up to midnight to midnight in UTC+11 timezone. Query becomes: { "startDate": "2023-01-02T13:00:00", "endDate": "2023-01-03T12:59:59", "timeZone": "Australia/Sydney", "userIds": ["41b58579-6d3a-4f17-95c9-4e5a353b7497"], "includeExceptions": false }
And the result is below showing a single day metric as times match up with timezone: {"entityId":"d0d699b9-1181-4665-bae4-c75997c4a757","data":[{"userId":"41b58579-6d3a-4f17-95c9-4e5a353b7497","managementUnitId":"d196a7db-f65b-4a97-8693-86d19682a4d1","startDate":"2023-01-02T13:00:00.000Z","endDate":"2023-01-03T12:59:59.000Z","adherencePercentage":79.48,"conformancePercentage":67.95,"impact":"Negative","exceptionInfo":[],"dayMetrics":[{"dayStartOffsetSecs":0,"adherenceScheduleSecs":28080,"conformanceScheduleSecs":15600,"conformanceActualSecs":10600,"exceptionCount":16,"exceptionDurationSecs":5762,"impactSeconds":-4990,"scheduleLengthSecs":28800,"actualLengthSecs":86399,"adherencePercentage":79.48,"conformancePercentage":67.95}],"actualsEndDate":"2023-01-03T12:59:59.000Z","actuals":[{"actualActivityCategory":"Unavailable","startOffsetSeconds":0,"endOffsetSeconds":1800},{"actualActivityCategory":"Unavailable","startOffsetSeconds":1800,"endOffsetSeconds":25895},{"actualActivityCategory":"OffQueueWork","startOffsetSeconds":25895,"endOffsetSeconds":25919},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"5","startOffsetSeconds":25919,"endOffsetSeconds":28816},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":28816,"endOffsetSeconds":31417},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":31417,"endOffsetSeconds":31798},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":31798,"endOffsetSeconds":32007},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":32007,"endOffsetSeconds":32434},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":32434,"endOffsetSeconds":33306},{"actualActivityCategory":"Break","actualSecondaryPresenceLookupId":"2","startOffsetSeconds":33306,"endOffsetSeconds":33921},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":33921,"endOffsetSeconds":34284},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":34284,"endOffsetSeconds":34967},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":34967,"endOffsetSeconds":35586},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":35586,"endOffsetSeconds":37245},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":37245,"endOffsetSeconds":37821},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":37821,"endOffsetSeconds":38773},{"actualActivityCategory":"OffQueueWork","actualSecondaryPresenceLookupId":"3","startOffsetSeconds":38773,"endOffsetSeconds":38838},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":38838,"endOffsetSeconds":39765},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":39765,"endOffsetSeconds":40075},{"actualActivityCategory":"Break","actualSecondaryPresenceLookupId":"1","startOffsetSeconds":40075,"endOffsetSeconds":40970},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":40970,"endOffsetSeconds":41770},{"actualActivityCategory":"Meal","actualSecondaryPresenceLookupId":"4","startOffsetSeconds":41770,"endOffsetSeconds":43606},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":43606,"endOffsetSeconds":44501},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":44501,"endOffsetSeconds":44580},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":44580,"endOffsetSeconds":45012},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":45012,"endOffsetSeconds":46243},{"actualActivityCategory":"OnQueueWork","startOffsetSeconds":46243,"endOffsetSeconds":46813},{"actualActivityCategory":"Meeting","actualSecondaryPresenceLookupId":"0","startOffsetSeconds":46813,"endOffsetSeconds":54361},{"actualActivityCategory":"Unavailable","startOffsetSeconds":54361,"endOffsetSeconds":86399}]}],"lookupIdToSecondaryPresenceId":{"0":"bd0e6462-3bc5-4d73-b87c-480a6586cf24","1":"480203d6-cbc5-4b00-9390-12487d806dd5","2":"597fd842-cd2d-4867-8f5a-3161ef167c22","3":"034b92ca-cc0a-45d2-a0e8-4f12fbef7921","4":"1288f9a4-c55f-43c8-b61c-c690ac7e2354","5":"895b292a-e9c2-47db-b62c-849db35b36c7","6":"adc0ebb4-df0c-4fe5-9c1f-6a1df5b29947","7":"fccfabbb-41f8-43b3-8690-8bf67a7b2283","8":"d2ae04dc-4183-409e-b3f7-afd00223ff29","9":"fdbd5d11-681d-4aaf-b432-fce6dcd15028","10":"03518c49-cc78-4096-8aa4-6ca3ad947c57","11":"a4239d9f-7b18-4f07-9f01-3389235500d0"},"version":1}
Question: Do we have to adjust our API queries every 6 months to change the start/end times to match the daylight saving on or off or do we leave the query UTC day boundaries always set at 14:00 to 14:00 and just ignore the 2nd day metrics returned when daylight savings is enabled? Is there any advice you can provide on best way to approach using this API and timezones/daylight savings on/off?
Dan_Hoffman | 2023-02-27 17:16:03 UTC | #2
Hi Robert,
First of all, I will say that your approach that would throw out the second day of metrics is a bad idea. This is because the data you throw out will not show up on the query for the next day. For example if during DST you requested from 2023-01-02T14:00:00 to 2023-01-03T13:59:59 and threw out the second dayMetrics, the query you made the next day from 2023-01-03T14:00:00 to 2023-01-04T13:59:59 would not include the data that you threw out from the first query, losing data. Its important to remember you always get exactly everything requested between the start and end dates.
It is hard to recommend an approach without knowing your use case. The easiest way would probably be to always use the same start and end times and timezone, and store everything in UTC (which all results are returned in). That way your data store does not need to worry about DST. Then only when consuming data from your data store do you filter out and convert the retrieved data based on the desired timezone.
dale.olcorn | 2023-02-28 22:37:04 UTC | #3
Hi Dan,
Thanks for coming back to us on this (I've been working with Rob to understand what's going on)
What we're looking to achieve with WFM API is to ingest historical adherence based on the "timeZone": "Australia/Sydney".
We've been using the following body in the request to this API https://api.{{environment}}/api/v2/workforcemanagement/adherence/historical
{
- "startDate": "2023-01-02T14:00:00",*
- "endDate": "2023-01-03T14:00:00",*
- "timeZone": "Australia/Sydney",*
- "userIds": ["41b58579-6d3a-4f17-95c9-4e5a353b7497"],*
- "includeExceptions": false*
- }*
Based on Sydney currently being in Daylight savings the is splitting the day metrics in the response into multiple records with the second having an offset of 82800 seconds (23 hours)
So in order to be returning a single day metrics records whilst in daylight savings the input parameters need to be:
{
- "startDate": "2023-01-02T13:00:00",*
- "endDate": "2023-01-03T13:00:00",*
- "timeZone": "Australia/Sydney",*
- "userIds": ["41b58579-6d3a-4f17-95c9-4e5a353b7497"],*
- "includeExceptions": false*
- }*
However when Sydney comes out of Daylight savings on the 2nd of April, the request parameters would need to be changed back to the 14 hours offset, rather than the 13 hours.
Based on this, ideally, we should be able to set in ingestion parameters up and not have to change these as we transition in and out of daylight savings.
I guess this leads me to the thought, that we are missing something in the way we are requesting the data.
brian.trezise | 2023-03-01 15:19:35 UTC | #4
I think the fundamental misunderstanding here may be that the timestamp you're sending in your request is an absolute value relative to UTC (UTC, as you know, does not observe DST).
The timeZone value is used to define what a "day" is in the returned data but has no effect on the "period" you are querying.
Since the request passes an absolute time relative to UTC for your start/end, you do have to adjust that time when the offset of the particular timezone shifts for DST.
system | 2023-04-01 15:19:38 UTC | #5
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.
This post was migrated from the old Developer Forum.
ref: 18597