-1

In Azure Data Factory, what can I do to transform "hh:mm:ss" to total seconds.

In "Add dynamic content" part, I want to use "@activity('dataflow').Duration" in Azure Data Factory to get the duration of my current activity. However, I found that "@activity('dataflow').Duration" is in "hh:mm:ss" format, and I want to transform it to the total seconds.

For example, using "@activity('dataflow').Duration", I can get "00:01:02". What can I do to transform it to the total seconds 62?

"00:01:22" -> "82"

"00:00:30" -> "30"

"01:00:00" -> "3600"

Thanks very much!!!!!

enter image description here

lalalalaalalallll
  • 1
  • 2
  • ADF has a capable expression language for this sort of thing a combination of `formatDateTime`, `add`, `mul` (for multiply) and `int` and `string` for conversion should get you there. Read through the docs [here](https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions) and give it a try. Post back your attempts when you are ready and any error messages you get! – wBob Dec 01 '21 at 15:21

2 Answers2

0

As Wbob mentioned, you can try to use an expression like below -

In my case, i am having a parameter with a hh:mm:ss value. I am using it in the expression as given below. you can replace the parameter portion with the Data flow duration.

@string(
add(
add(
mul(mul(int(split(pipeline().parameters.time,':')[0]),60),60),
mul(int(split(pipeline().parameters.time,':')[1]),60)),
int(split(pipeline().parameters.time,':')[2])
))

Basically i am splitting the time portion based on : and working on each part of the time.

hh: [0]
mm: [1]
ss: [2]

hh conversion: hh6060 mm conversion: mm*60

finally add up everything.

All About BI
  • 493
  • 3
  • 6
0

I came up with the following expression:

@string(
add(
add(
mul(int(formatDateTime(item(), 'HH')), 3600 ),
mul(int(formatDateTime(item(), 'mm')), 60 )
), int(formatDateTime(item(), 'ss'))
)
)

It is using the following functions:

  • formatDateTime - return a timestamp in the specified format
  • int - return the integer version of a string
  • mul - return the product of two numbers (also known as multiply)
  • add - add two numbers together
  • string - return the string version of a value

You should spend some time with the following article and do some practice. I often use the Set Variable activity to debug complex expressions and build them up, normally from the inside out:

Expressions and functions in Azure Data Factory and Azure Synapse Analytics https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions

wBob
  • 13,710
  • 3
  • 20
  • 37