Using the dateDifference expression or ticks to find difference between two datetimes

[wskinnermctc]

This is a review of how to find the time difference between two datetimes. The two methods I know use different expressions which will have different formats of outputs/results. 

The dateDifference() expression - Output Result string d.hh:mm:ss.fffffff

dateDifference({StartDateTime},{EndDateTime})

Using ticks() expressions- Output Result is integer whole number

Days between datetimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)

Hours between datetimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)

Minutes between datetimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)

Seconds between datetimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)

Below I will describe:

1. Expression dateDifference()
2. Time calculation using the expression ticks()
   1. Expression ticks()
   2. Converting ticks into time values
   3. Difference between two datetimes using ticks expression
   4. Note about whole number and decimal results
3. Summary

Expression dateDifference()

The use of the dateDifference() expression is straight forward with only needing 2 values in a single expression. The expression will use a start datetime and end datetime with a string output of d.hh:mm:ss.fffffff which shows the combined amount of days, hours, minutes, seconds, and fractional seconds between the two datetimes.

Link to expression info below:
https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#... 

dateDifference() expression

The expression below is what will be used:

dateDifference({start datetime},{end datetime})

Below is a flow example using a start datetime of May 1, 2023 12:00 PM and end datetime of May 4, 2023 11:30:15.45 AM which are put into separate variables (varStartTime and varEndTime). The variables are then used in an expression dateDifference(variables('varStartTime'),variables('varEndTime')) to find the time difference. The result difference output string is 2.23.30.15.4500000 where it is 2 days, 23 hours, 30 minutes, 15 seconds, and 45 hundredths of a second.

dateDifference() Expression Sample

Below are some example output strings when using the dateDifference() expression.

Start Datetime	End Datetime	dateDifference() output string	Description
May 1, 2023 12:00 PM	January 1, 2029 5:30 PM	2072.05:30:00	2072 days. 5 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	May 31, 2023 12:00 PM	30.00:00:00	30 days. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	May 31, 2023 12:00:00.1 PM	30.00:00:00.1000000	30 days. 0 hours: 0 minutes: 0 seconds. 1 tenth fractional second
May 1, 2023 12:00 PM	May 4, 2023 10:00 PM	3.10:00:00	3 days. 10 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	May 4, 2023 11:30 AM	2.23:30:00	2 days. 23 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	May 2, 2023 10:00 PM	1.10:00:00	1 day. 10 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	May 2, 2023 12:00 PM	1.00:00:00	1 day. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	May 2, 2023 11:30 AM	23:30:00	23 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	May 1, 2023 10:30 PM	10:30:00	10 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	May 1, 2023 12:30:35 PM	00:30:35	0 hours: 30 minutes: 35 seconds
May 1, 2023 12:00 PM	May 1, 2023 12:00:35 PM	00:00:35	0 hours: 0 minutes: 35 seconds
May 1, 2023 12:00 PM	May 1, 2023 12:00:00.08 PM	00:00:00.0800000	0 hours: 0 minutes: 0 seconds. 8 hundredths fractional second
May 1, 2023 12:00 PM	May 1, 2023 12:00 PM	00:00:00	0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	May 1, 2023 10:30 AM	-01:30:00	minus 1 hour: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	May 1, 2023 10:25:15 AM	-01:34:45	minus 1 hour: 34 minutes: 45 seconds
May 1, 2023 12:00 PM	May 1, 2023 10:20:05 AM	-01:39:55	minus 1 hour: 39 minutes: 55 seconds
May 1, 2023 12:00 PM	April 30, 2023 11:30 PM	-12:30:00	minus 12 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PM	April 30, 2023 12:00 PM	-1.00:00:00	minus 1 day. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PM	April 30, 2023 10:30 AM	-1.01:30:00	minus 1 day. 1 hour: 30 minutes: 0 seconds

To use individual values from the output string of dateDifference() can be difficult due to the changing format and character count that is dependent upon the initial values.

dateDifference() Output String Format	Description
d.hh:mm:ss	Equal to 24 hours OR more than 24 hours difference between start and end datetimes
d.hh:mm:ss.fffffff	Equal to 24 hours OR more than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds
hh:mm:ss	Less than 24 hours difference between start and end datetimes
hh:mm:ss.fffffff	Less than 24 hours difference between start and end datetimes; at least on of the datetimes contains fractional seconds
-hh:mm:ss	Minus less than 24 hours difference between start and end datetimes
-hh:mm:ss.fffffff	Minus less than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds
-d.hh:mm:ss	Minus more than 24 hours difference between start and end datetimes
-d.hh:mm:ss.fffffff	Minus more than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds

In the two flow results from the photo below, I have a flow that is more than 24 hours difference between start and datetime, and a separate flow that is less than 24 hours difference between start and datetime. The output string of the dateDifference() expression has two different formats.

dateDifference() Results String Format Compare

To extract a specific hour or minute value using expressions such as split() or first() that finds special characters such as '.' period or ':' colon will require knowing if the result will have a day value or fractional seconds to format the expression correctly.

The dateDifference() expression is a good tool for getting the difference between two datetimes into a readable string.

Expression ticks()

The use of the ticks() expression will convert a datetime into ticks. Ticks are are 100-nanosecond intervals, since January 1, 0001 12:00:00 midnight to the datetime. Basically the number of nanoseconds from January 1, 0001 to the datetime divided by 100 is the number of ticks. The ticks can be converted into days, hours, minutes, seconds, and fractional seconds by using the appropriate conversion number to divide by.

A tick is equal to 100 nanoseconds, or one ten-millionth of a second, which is seven decimal places after a second (00.0000001).
https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#... 

Ticks Expression

The expression is written as: 

ticks({datetime})

The example flow below shows the ticks() expression used on a datetime of May 1, 2023 12:00 PM. The output result is the number of ticks that are between January 1, 0001 to May 1, 2023 12:00 PM. (This number of ticks would be the same as getting the number of nanoseconds between January 1, 0001 to May 1, 2023 12:00 PM and dividing that total amount of nanoseconds by 100.)

May 1, 2023 12:00 PM = 638,185,392,000,000,000 ticks 

There are 638 quadrillion, 185 trillion, 392 billion ticks between January 1, 0001 to May 1, 2023

Ticks Expression Example

Convert ticks into time values:

Knowing the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks doesn't immediately help us much. The number is incredibly large and not in a normal term we would use. To get a better usage of the ticks number, we can convert it to something more understandable such as Days, Hours, Minutes, or Seconds by using a set conversion amount. (There is not a conversion amount in terms of years and months, both of which have a variable number of days.)

The table below shows how many ticks are within one of the corresponding time values. There are 864 billion ticks in a single day. There are 36 billion ticks in a single hour. If we consider there are 24 hours in a day and multiply the 36,000,000,000 ticks in a single hour by 24, the result is 864,000,000,000 which is the number of ticks in a single day.

Similarly, if we consider there are 60 minutes in an hour, we can divide the 36,000,000,000 ticks in a single hour by 60 the result is 600,000,000 which is the number of ticks in a single minute.

Time Value	Total Ticks Number	Total Ticks as Integer	Date Time Format d.hh:mm:ss.fffffff
1 Day = 864 billion ticks	864,000,000,000	864000000000	1.hh:mm:ss.fffffff
1 Hour = 36 billion ticks	36,000,000,000	36000000000	d.01:mm:ss.fffffff
1 Minute = 600 million ticks	600,000,000	600000000	d.hh:01:ss.fffffff
1 Second = 10 million ticks	10,000,000	10000000	d.hh:mm:01.fffffff
1 Decisecond = 1 million ticks	1,000,000	1000000	d.hh:mm:00.1
1 Centisecond = 100 thousand ticks	100,000	100000	d.hh:mm:00.01
1 Millisecond = 10 thousand ticks	10,000	10000	d.hh:mm:00.001
1 Microsecond = 10 ticks	10	10	d.hh:mm:00.000001
100 Nanoseconds = 1 tick	1	1	d.hh:mm:00.0000001
1 Nanosecond = 1/100 tick	1/100 = 0.01	0.01	d.hh:mm:00.000000001

In power automate you will use the div() expression to divide the number of ticks({DateTime}) by the conversion amount.

To Convert Ticks to Days divide by 864 billion:

div({ticks},864000000000)

To Convert Ticks to Hours divide by 36 billion:

div({ticks},36000000000)

To Convert Ticks to Minutes divide by 600 million:

div({ticks},600000000)

To Convert Ticks to Seconds divide by 10 million:

div({ticks},10000000)

Using the previous example to find the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks. We can convert that tick quantity into different time values such as days, hours, minutes, or seconds. Remember this will be the total time difference between January 01, 0001 and May 1, 2023 12:00 PM. 

Below is an example of converting ticks to Days, Hours, Minutes, and Seconds.

Example Flow Ticks to Time ValueExample Flow Ticks to Time Value Results

The output results show that the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks. This number converted is the amount of time between May 1, 2023 12:00 PM and January 1, 0001 as days, hours, minutes, and seconds.

• Days: There are 738,640 days between May 1, 2023 and January 1, 0001
• Hours: There are 17,727,372 hours between May 1, 2023 and January 1, 0001
• Minutes: There are 1,063,642,320 minutes between May 1, 2023 and January 1, 0001
• Seconds: There are 63,818,539,200 seconds between May 1, 2023 and January 1, 0001

Time Difference Between Two DateTimes Using Ticks()

Most practical uses of ticks() will be to find the time difference between two datetimes. For example a request for an item is sent on May 1, 2023 at 12:00 PM and it was finally approved on May 4, 2023 at 11:30 AM and we want to know the amount of time the request was in process. We would convert both datetimes to ticks() and then subtract the Send (Start DateTime) from the Approval (End DateTime) to get the total difference of ticks between the two datetimes; then divide the difference in ticks by the conversion amount to get the total time difference as days, hours, or minutes.

In power automate we use the sub() subtract expression to subtract the number of ticks of the start datetime from the end date time. The End DateTime will have more ticks than the Start DateTime because it is further away from January 01, 0001. So the End DateTime should be the first (minuend) of the subtraction and the Start DateTime should be the second (subtrahend) of the subtraction EndDateTime - StartDateTime. Then divide this difference of ticks by the conversion time.

Subtraction Expression sub()Divide Expression div()

Days Between DateTimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)

Days Time Difference Using Variables

Hours Between DateTimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)

Hours Time Difference Using Variables

Minutes Between DateTimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)

Minutes Time Difference Using Variables

Seconds Between DateTimes using ticks:

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)

Seconds Time Difference Using Variables

Don't let the ticks() and nanoseconds references confuse you, this is basically simple Subtraction and Division.

Thought Example:

• If Steve has 330 apples and John has 200 apples, to find out how many more apples Steve has than John you would subtract 330-200=130 apples. This is easy Subtraction.
• If you had 130 minutes and wanted to convert it to hours. Since there are 60 minutes in an hour, you would divide the number of minutes 130 by 60 to get the total number of hours (Ex. 130 minutes / 60 = 2.16 hours). This is easy Division.

Now pretend the apples are actually datetime ticks(). Steve's apples will be ticks(End DateTime) and John's apples are ticks(Start DateTime). You subtract the two values to get the difference just like with the apples, except the values are ticks.

ticks(End DateTime) - ticks(Start DateTime) = TicksDifference

Then convert the TicksDifference into hours. Just like the easy division of converting minutes into hours, which is 60 minutes for 1 hour so Minutes/60 = Hours, you would use the number of ticks in 1 hour which is 36,000,000,000. So it will be TicksDifference/36,000,000,000 = Hours

Below is a flow example getting the total time values difference between two datetimes using ticks. The initialized variable varStartTime is the datetime May 1, 2023 12:00 PM. And the initialized variable varEndTime is May 4, 2023 11:30:15.45 AM.

Example Flow Time Difference Using Ticks

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Days: Result is 2

Days Time Difference Example

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Hours: Result is 71

Hours Time Difference Example

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Minutes: Result is 4290

Minutes Time Difference Example

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Seconds: Result is 257415

Seconds Time Difference Example

The output results show the total time values between May 1, 2023 12:00 PM and May 4, 2023 11:30:15.45 AM are as follows:

• Total Days: 2
• Total Hours: 71
• Total Minutes: 4,290
• Total Seconds: 257,415

Note About Whole Number Results and Decimal Results: 

The results of the previous expressions are whole number values and are not rounded. We know that the difference between the two datetimes is greater than 2 days, but since power automate div() expression does not add decimals if only using whole numbers, the output is only 2. To get a decimal value one or both of the factors in the div() expression needs to be a decimal which requires the use of the float() expression.

If we wrap each of the ticks({datetime}) values in a float() expression, it will return the results of each as a decimal value. That decimal value is then used in subtraction sub() expression which will also be decimal, and finally the division div() expression.

Days between DateTimes using ticks with decimal value output:

div(sub(float(ticks({EndDateTime})),float(ticks({StartDateTime}))),864000000000) 

Hours between DateTimes using ticks with decimal value output:

div(sub(float(ticks({EndDateTime})),float(ticks({StartDateTime}))),36000000000)

Here are the same result of days and hours as the above but including the float() expression.

Time Difference Ticks Decimal Results

The decimal value output results show the total time values between May 1, 2023 12:00 PM and May 4, 2023 11:30:15.45 AM are as follows:

• Total Days: 2.9793454860740742
• Total Hours: 71.50429166577777

These outputs can be formatted further by using formatNumber() expression to determine a set amount of decimal places to return.

Summary

I know of two methods that will return the time value differences between two datetime values. The dateDifference() expression is a good tool that will return a string that is easier to read and understand. We can easily understand the dateDifference() string result of 2.23.30.15.45 as 2 days, 23 hours, 30 minutes, 15 seconds, and 45 hundredths of a second. However, this result is not as helpful if the time difference is needed in other steps to be added or calculated.

The use of the ticks() expression can seem complicated at first, but it is really just a way to get datetimes into a factor that can be calculated. When finding the difference between datetimes using the ticks() expression, the output will be a total time value that is either days, hours, minutes, or seconds as an integer. This value is easier to use in other steps where the time might need to be added or calculated with another value.

I hope this information helps or can be a reference, I will reply below with a way to extract values from the dateDifference() string.

[v-qiaqi-msft · Accepted solution]

Hi @wskinnermctc,

So detailed tutorial.👍

Thanks for your sharing.

Later, I will mark here to close this case so that other users who may have the similar issue could reach here dieectly.

Thanks for your cooperation.

[Holly_CMS]

So AWESOME!!!!

Thank you!!!!

[gokhanarda]

great work! 

many thanks