Adding microseconds to a timestamp in Azure Data Factory

(2023-Apr-25) It’s not hard to see that many different teams were involved in building Azure Data Factory (ADF) product at Microsoft. It doesn’t have conformed constraints for naming the objects inside of the product, some of them would support a big variety of characters and symbols, and other components would raise a “name with invalid characters” error message, saying that “only alphanumeric characters are supported”.

The functions for control flows and data flows in Azure Data Factory are different too. While this is expected, I need to confirm whether a specific value expression logic can be used in both control and data flows, or if there are alternative solutions available.

The first step is to get a microsecond portion of your incoming timestamp and then increase this value to 1,000,001 (use case of increasing my timestamp by 1 microsecond, your use case may vary).

It is a crucial sub-step to add 0 seconds to your timestamp before proceeding with any further transformations. This step is important as it cleans and prepares the timestamp value to be in the format of yyyy-MM-ddTHH:mm:ss.fffffffK, which supports timestamps with incomplete seconds or mere dates.

or

 

@string(

    add(

        int(

            substring(addSeconds(pipeline().parameters.par_incoming_date, 0), 20, 6)

            )

    , 1000001)

    )

Following the initial step, we perform a check to see if the result of the first equation is equal to 2,000,000. If it is, we add a whole second to the incoming timestamp value. If it is not equal to 2,000,000, we replace the last 6 digits of the incoming value with the result generated in the first step.

or

@if(

    equals(variables('var_micro_seconds'), '2000000')

    , replace(addSeconds(pipeline().parameters.par_incoming_date, 1), substring(pipeline().parameters.par_incoming_date, 20, 6), '000000')

    , replace(addSeconds(pipeline().parameters.par_incoming_date, 0), substring(addSeconds(pipeline().parameters.par_incoming_date, 0), 20, 6), substring(variables('var_micro_seconds'), 1,6))

    )

As a result, we can support incoming timestamp values in various time formats that can still be increased by 1 microsecond:

Incoming Timestamp value	Increase Timestamp value
2022-11-11T09:11:57.280548	2022-11-11T09:11:57.280549
2022-11-11T09:11:57.280549	2022-11-11T09:11:57.28055
2022-11-11T09:11:57	2022-11-11T09:11:57.000001
2022-11-11T09:11:57.999999	2022-11-11T09:11:58
2022-11-11T23:59:59.999999	2022-11-12T00:00:00