How to Work with UTC Timestamps in Azure Data Factory

Question

I need assistance in writing a Data Flow expression in Azure Data Factory to generate the current UTC timestamp in the format yyyy-MM-ddTHH:mm:ss.SSS. Additionally, I would like to understand how to create expressions that allow me to both subtract and add a specific duration (e.g., days, hours, minutes, etc.) to this timestamp. Could you provide guidance or examples on how to accomplish this within Azure Data Factory's Data Flow?

Answer 1

Hi @DataCoder
Thank you for your question and using the Microsoft Q&A platform.
To generate the current UTC timestamp in the format yyyy-MM-ddTHH:mm:ss.SSS, you can use the following expression in a Derived Column transformation in Azure Data Factory's Data Flow:

toTimestamp(toUtc(now()), "yyyy-MM-dd'T'HH:mm:ss.SSS")

This expression uses the now() function to get the current UTC timestamp, then converts it to a UTC timestamp using the toUtc() function, and finally formats it using the toTimestamp() function with the specified format string.

To add or subtract a specific duration (e.g., days, hours, minutes, etc.) to this timestamp, you can use the addDuration() or subtractDuration() functions in a Derived Column transformation. Here are some examples:

To add 1 day to the current UTC timestamp:

addDuration(toTimestamp(toUtc(now()), "yyyy-MM-dd'T'HH:mm:ss.SSS"), duration('P1D'))

To subtract 1 hour from the current UTC timestamp:

subtractDuration(toTimestamp(toUtc(now()), "yyyy-MM-dd'T'HH:mm:ss.SSS"), duration('PT1H'))

These expressions will help you generate and manipulate timestamps as needed in your Data Flow.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.