Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Table Transformer Calculations Off By 1

Adrian Avalos April 18, 2024

Hello. I setup the below formula in Table Transformer to calculate the difference between two date fields. It works for the most part, but for some reason the top piece of the formula in bold outputs a result of one additional day for some results? Would someone be able to help modify the formula in table transformer so the calculations are correct?

 

CASE
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due Date') < -1
THEN FORMATWIKI("{cell:align=center; textColor=#cc1010}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due') > -1
THEN FORMATWIKI("{cell:align=center; textColor=#008000}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
END AS 'Time Between Due and Resolved'

1 answer

1 accepted

Suggest an answer

Log in or Sign up to answer
5 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 18, 2024

Hi @Adrian Avalos ,

For which dates the formula is not correct? The DATEDIFF function is a standard SQL function that we don't somehow specifically adjust.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 20, 2024

Hi @Adrian Avalos ,

Got an idea from our developers: the DATEDIFF function may be influenced by the winter time/summer time transition. There could be a day that is 23 hours long. You may google this peculiarity regarding this SQL function.

So, you may try to use the ROUND() function before each DATEDIFF function in the query.

Adrian Avalos April 22, 2024

Hi @Stiltsoft support thank you for looking at this. Do you know how I can edit this piece to account for the 23 hour long day? I included the ROUND function but am still not getting the correct date difference.

 

ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date'))

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 23, 2024

You may try to use some workarounds: for example, check if your date period includes the day with the transition and manually correct the result difference (add or subtract 1 day from the result).

You may also create a table with test dates that are close and include your transition day (10, March? 31, March? I guess it differs for different countries), calculate the DATEDIFF using an hour interval, see what you get, correct it if required (+ or - 1 hour) and then divide by 24 hours to get days.

Like # people like this
Adrian Avalos April 23, 2024

@Stiltsoft support thanks so much! Using the hour interval calculation instead of the day calculation worked. This is the formula I used

ROUND((DATEDIFF(hour,T1.'Resolved',T1.'Due Date'))/24)

Like Stiltsoft support likes this
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 23, 2024

Glad it works now!

Meanwhile, we've also found a location where we could check the behavior of the DATEDIFF function during the winter/summer time shift and got the same result as yours (I'll leave it here for other users if they have the same issue):

Tue 9-1.png

SELECT *,
DATEDIFF(hour, 'date1', 'date2'),
ROUND(DATEDIFF(hour, 'date1', 'date2')/24)
FROM T*

So, the ROUND(DATEDIFF(hour, 'date1', 'date2')/24) is indeed a correct solution.

DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events