Adding microseconds to a timestamp in Azure Data Factory

Posted by on

(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 current addToTime function (https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#addToTime) in Azure Data Factory (ADF) only supports a specific set of time units ranging from Year to Seconds. Since I needed to increment a timestamp by microseconds, I had to find an alternative solution in ADF. Here are my findings on how to do this using an alternate approach.

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

Comments

Post a Comment