Need help with writing a dataflow expression

Question

Need help with writing a dataflow expression to generate UTC timestamp in yyyy-MM-ddTHH:mm:ss.SSS and subtract and be able to add to the current time.

Answer 1

Hi @AJ, Mithun - Admin Account

Thanks for the question and using MS Q&A platform.

To generate a UTC timestamp in the format of yyyy-MM-dd'T'HH:mm:ss.SSS, you can use either of the following expression that might give you the desired output format with the T separator.

concat(
    toString(toUTC(currentTimestamp(), 'yyyy-MM-dd')),
    'T',
    toString(toUTC(currentTimestamp(), 'HH:mm:ss.SSS'))
)

Adding and Subtracting Time: For adding or subtracting time, you can still use the addToTime() and subtractFromTime() functions.

Subtract 1 day:

concat(
    toString(toUTC(subtractFromTime(currentTimestamp(), 1, 'Day'), 'yyyy-MM-dd')),
    'T',
    toString(toUTC(subtractFromTime(currentTimestamp(), 1, 'Day'), 'HH:mm:ss.SSS'))
)

Add 1 hour:

concat(
    toString(toUTC(addToTime(currentTimestamp(), 1, 'Hour'), 'yyyy-MM-dd')),
    'T',
    toString(toUTC(addToTime(currentTimestamp(), 1, 'Hour'), 'HH:mm:ss.SSS'))
)

These expressions should help you achieve the desired format with the T character included.

I hope this information helps.

---

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.

Answer 2

Hello AJ, Mithun - Admin Account

To generate a UTC timestamp in the format of yyyy-MM-ddTHH:mm:ss.SSS, you can use the toUTC() function in the mapping data flow. Here's an example expression:

toUTC(currentTimestamp(), 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')

This will generate a UTC timestamp in the specified format. To subtract or add time to the current timestamp, you can use the add() and minus() functions.

Here's an example expression to subtract 1 day from the current timestamp: minus(currentTimestamp(), 'P1D')

And here's an example expression to add 1 hour to the current timestamp:

add(currentTimestamp(), 'PT1H')

Note that the add() and minus() functions take an ISO 8601 duration as the second argument. In the examples above, P1D represents a duration of 1 day, and PT1H represents a duration of 1 hour. You can modify these values to subtract or add different amounts of time.

This should get you started.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.