Azure APIM - incorrect date format

Question

Hi,
Iam trying to retrive data from database via api, below is the code .We have a column whose type is timestamp_ntz which is like 2019-07-02T00:00:00Z . But when i get data via api it is like 1653651822.253000000. Please help me on what changes needs to be done in the below code .

<set-body template="liquid">{    
                            "statement": "select  * from xxxxxxxx;",
                            "timeout": 180,
                            "resultSetMetaData": {
                                "format": "json"
                            },
                            "database": "xxxxx",
                            "schema": "xxxx",
                            "warehouse": "xxxxx",
                            "role": "xxxx"
                            
                   
 }</set-body>

Answer 1

Edem, Sai Thanks for posting your question in Microsoft Q&A. Based on my understanding, your backend API is returning data as 1653651822.253000000 (epoch timestamp) and you would like to convert it to date format using liquid template in APIM.

APIM uses dotliquid library (.NET implementation of Ruby liquid) for its liquid implementation and unfortunately, this is not supported. Here is reference: Unix epoch string support and we have similar thread discussion: Convert Unix timestamp to human-readable date using Liquid in APIM policy. Alternatively, you can use policy expression in C# 7 and should be able to convert epoch timestamp to date format with expression like below:

<set-variable name="xxx" value="@(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddSeconds(1653651822.253000000).ToString("o"))" />

Reference: https://www.epochconverter.com/

I hope this helps with your question and let me know if you have any other.

---

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.