Azure API Management - Timezone changes

Question

Hi,

I have an API on Azure API management that refers to a logic app. However, my Azure API management output for fields StartDateTime & EndDateTime differs from the logic app output for some reason.

For example, see the below screenshots -

--Response from my logic app--

--Response from API management service, which actually refers to the logic app response output above--

I feel I need to change my inbound policy set for the fields 'StartDateTime' and 'EndDateTime'. Unfortunately, I am unable to figure out what changes need to be made when my source input contains +01:00.

NOTE: The data from my API management output matches the logic app response output when my source input is +00:00

Answer 1

Hi Vivek Komarla Bhaskar

Thanks for reaching out on Microsoft Q&A!

As MuthuKumaranMurugaachari-MSFT correctly stated the default JSON deserialization used by the liquid template converts the dates to UTC, discarding the time zone, which invalidates your dates. So liquid unfortunately isn't an option.

However, you can solve this using JsonSerializerSettings in C# code. From there you can disable the DateParseHandling, which is causing your date invalidation. I've managed to achieve your desire by using the code below:

// Get response body as string
string responseBody = context.Response.Body.As<String>();

// Set json deserializer settings for date conversions
var jsonSettings = new JsonSerializerSettings();
jsonSettings.DateParseHandling = DateParseHandling.None;

// Convert response body to json using new settings
JArray originalItems = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject<JObject>(responseBody,jsonSettings)["ResultSets"]["Table1"];

// Create new empty array
JArray responseItems = new JArray();

// Loop through objects in array
foreach(JObject item in originalItems) {
    
    // Create new object
    JObject responseObject = new JObject();

    // Fill new object with original data
    responseObject["ProgramDate"] = DateTime.Parse(item["ProgramDate"].ToString()).ToString("dd-MM-yyyy");
    responseObject["StartDateTime"] = item["StartDateTime"];
    responseObject["EndDateTime"] = item["EndDateTime"];
    responseObject["ShowTitle"] = item["ShowTitle"];
    responseObject["ShowType"] = item["ShowType"];
    responseObject["ShowImage"] = item["ShowImage"];
    responseObject["Presenters"] = item["Presenters"];
    responseObject["Description"] = item["Description"];
    responseObject["ShowSectionId"] = item["ShowSectionId"];
    responseObject["PresentersSectionIds"] = item["PresentersSectionIds"];

    // Push new object to new array
    responseItems.Add(responseObject);

}

When pasting the code above in the set-body tags using @{ } for enabling the code block (with removing the template="liquid" part) you should be able to retrieve the dates as desired. Your code will the look as follows:

When you find my answer helpful, please click "Accept answer" and upvote it.

Feel free to drop additional queries in the comments!

Kind regards,

Sonny

Answer 2

Hi Vivek Komarla Bhaskar

Thanks for reaching out on Microsoft Q&A!

As MuthuKumaranMurugaachari-MSFT correctly stated the default JSON deserialization used by the liquid template converts the dates to UTC, discarding the time zone, which invalidates your dates. So liquid unfortunately isn't an option.

However, you can solve this using JsonSerializerSettings in C# code. From there you can disable the DateParseHandling, which is causing your date invalidation. I've managed to achieve your desire by using the code below:

// Get response body as string
string responseBody = context.Response.Body.As<String>();

// Set json deserializer settings for date conversions
var jsonSettings = new JsonSerializerSettings();
jsonSettings.DateParseHandling = DateParseHandling.None;

// Convert response body to json using new settings
JArray originalItems = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject<JObject>(responseBody,jsonSettings)["ResultSets"]["Table1"];

// Create new empty array
JArray responseItems = new JArray();

// Loop through objects in array
foreach(JObject item in originalItems) {
    
    // Create new object
    JObject responseObject = new JObject();

    // Fill new object with original data
    responseObject["ProgramDate"] = DateTime.Parse(item["ProgramDate"].ToString()).ToString("dd-MM-yyyy");
    responseObject["StartDateTime"] = item["StartDateTime"];
    responseObject["EndDateTime"] = item["EndDateTime"];
    responseObject["ShowTitle"] = item["ShowTitle"];
    responseObject["ShowType"] = item["ShowType"];
    responseObject["ShowImage"] = item["ShowImage"];
    responseObject["Presenters"] = item["Presenters"];
    responseObject["Description"] = item["Description"];
    responseObject["ShowSectionId"] = item["ShowSectionId"];
    responseObject["PresentersSectionIds"] = item["PresentersSectionIds"];

    // Push new object to new array
    responseItems.Add(responseObject);

}

When pasting the code above in the set-body tags using @{ } for enabling the code block (with removing the template="liquid" part) you should be able to retrieve the dates as desired. Your code will the look as follows:

When you find my answer helpful, please click "Accept answer" and upvote it.

Feel free to drop additional queries in the comments!

Kind regards,

Sonny

Answer 3

Hi Vivek Komarla Bhaskar

Thanks for reaching out on Microsoft Q&A!

As MuthuKumaranMurugaachari-MSFT correctly stated the default JSON deserialization used by the liquid template converts the dates to UTC, discarding the time zone, which invalidates your dates. So liquid unfortunately isn't an option.

However, you can solve this using JsonSerializerSettings in C# code. From there you can disable the DateParseHandling, which is causing your date invalidation. I've managed to achieve your desire by using the code below:

// Get response body as string
string responseBody = context.Response.Body.As<String>();

// Set json deserializer settings for date conversions
var jsonSettings = new JsonSerializerSettings();
jsonSettings.DateParseHandling = DateParseHandling.None;

// Convert response body to json using new settings
JArray originalItems = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject<JObject>(responseBody,jsonSettings)["ResultSets"]["Table1"];

// Create new empty array
JArray responseItems = new JArray();

// Loop through objects in array
foreach(JObject item in originalItems) {
    
    // Create new object
    JObject responseObject = new JObject();

    // Fill new object with original data
    responseObject["ProgramDate"] = DateTime.Parse(item["ProgramDate"].ToString()).ToString("dd-MM-yyyy");
    responseObject["StartDateTime"] = item["StartDateTime"];
    responseObject["EndDateTime"] = item["EndDateTime"];
    responseObject["ShowTitle"] = item["ShowTitle"];
    responseObject["ShowType"] = item["ShowType"];
    responseObject["ShowImage"] = item["ShowImage"];
    responseObject["Presenters"] = item["Presenters"];
    responseObject["Description"] = item["Description"];
    responseObject["ShowSectionId"] = item["ShowSectionId"];
    responseObject["PresentersSectionIds"] = item["PresentersSectionIds"];

    // Push new object to new array
    responseItems.Add(responseObject);

}

When pasting the code above in the set-body tags using @{ } for enabling the code block (with removing the template="liquid" part) you should be able to retrieve the dates as desired. Your code will the look as follows:

When you find my answer helpful, please click "Accept answer" and upvote it.

Feel free to drop additional queries in the comments!

Kind regards,

Sonny

Answer 4

I don't believe this has anything to do with your output format but rather what your logic app is returning as the output. Both the values you posted are the same value. The 23:00+01:00 is UTC + 1 hour. 22:00:00+00:00 is just UTC. They are the same, just different time zones. In your format string you're using K which means return the timezone information. And since that is 0 that would mean it is returning datetime values in UTC. Therefore I suspect your logic app is getting the time in whatever format, but does it's calculations and returns the time in UTC, hence you get the different timezone response.

To resolve this ideally the logical app should not convert the value to UTC. But if you need to normalize the data but still want to return the final value in the original timezone then you'll need to capture the offset before you convert to UTC. Once you have the result that you want to pass back then convert from UTC to that timezone and return the resulting value. It's messy.

Note that it really shouldn't matter what timezone the input/output is in. The whole reason for including the timezone information is so code can correctly convert to another timezone. So really the logic app isn't doing anything wrong. Any code that consumes the output is still going to have to handle arbitrary timezones and therefore should be able to work with whatever timezone is returned, including UTC.

Answer 5

Vivek Komarla Bhaskar Thank you for posting your question in Microsoft Q&A. Currently, when APIM parses JSON, it converts the local time to UTC and the offset is getting discarded. Based on your description, you would like to keep the original date time zone but unfortunately, this is not possible right now.

I have passed your feedback to our product team internally, and we are adding a backlog item to support this feature. Once this is implemented, we will publish it in our doc: Set body. Meanwhile, you can save JSON body in set-variable with DateParseHandling as None and then use that in the liquid template.

Sample request body:

{
	"results": [
	{ "StartDateTime": "2022-03-26T23:00:00+01:00", "EndDateTime": "2022-03-26T23:59:00+01:00" },
	{ "StartDateTime": "2022-03-25T23:00:00+01:00", "EndDateTime": "2022-03-25T23:59:00+01:00" }
	]
}

Sample Policy Snippet:

<set-variable name="varBody" value="@{
            JObject inBody = context.Request.Body.AsJObject(true, new JsonSerializerSettings() { DateParseHandling = DateParseHandling.None } ); 
            return (JObject)inBody;
        }" />
        <set-body template="liquid"> [
            {% JSONArrayFor item in context.Variables["varBody"].results %}
            { "StartDate": "{{item.StartDateTime}}", "EndDate": "{{item['EndDateTime']}}" }
            {% endJSONArrayFor %}
        ]
        </set-body>

Note, this is for reference, and you can customize the policy based on your need. I hope this helps with your questions and let me know if any.

---

If you found the answer to your question helpful, please take a moment to mark it as "Yes" for others to benefit from your experience. Or simply add a comment tagging me and would be happy to answer your questions.