Solved

DateDifferenceCalcuator produces different output to SQL Server DateDiff function

  • 14 September 2018
  • 4 replies
  • 13 views
tim_wood
Rank
Badge +7

I was using the DateDifferenceCalculator to work out the difference in days between a completed date (contains date and time) and now. This is to find out if the completed date is in the past 30 days. I have now converted this to a SQL query using the DateDiff function: SELECT DATEDIFF(day, completed_date, CURRENT_TIMESTAMP) from ...

The SQL function returns different results to the DateDifferenceCalculator, often 1 day less. For example DateDifferenceCalulator says the difference between today (14th September) and a completed date of 17th August is 29, but SQL Server (and Excel) say 28. To be precise, the completed date is 17/08/2018 00:32:00 and the comparison was done at about 14/09/2018 17:20:00. Excel give the difference as 28.7 which I would round down to 28 because it's not yet been 29 days.

Looking at the embedded transformer, it appears that DateDifferenceCalculator is rounding up the output value if the decimal point value is greater or equal to .5. SQL Server DateDiff function is a bit more complicated, because as this StackOverflow topic says, "DATEDIFF uses the boundary of the date part. So for "day", 23:59 today is one whole day before 00:01 tomorrow".

https://stackoverflow.com/questions/5131958/datediff-rounding

For example:

select DATEDIFF(DAY, '2018-09-14 23:59:00', '2018-09-15 00:01:00')

= 1

To get a more precise value, you have to get the hours, then divide by 24:

 

select (convert(numeric(10,3), DATEDIFF(HOUR, '2018-09-14 23:59:00', '2018-09-15 00:01:00')))/24

= 0.04166 then you can round as you see fit.

DateDifferenceCalulator returns zero for this comparison which I would agree with.

So it seems neither method is completely perfect. Any thoughts?

icon

Best answer by paulnalos 15 September 2018, 02:56

View original

4 replies

tim_wood
Rank
Badge +7
For certainty, I'm going to use SELECT FLOOR((CONVERT(numeric(10,3), DATEDIFF(HOUR, CompletedDate, CURRENT_TIMESTAMP)))/24) until a better solution presents itself.

 

P

About what you're seeing...

 

 

The DateDifferenceCalculator is an older transformer available via FME Hub from FME Workbench 2017.0 or before. (You can type "DateDifferenceCalculator" on the canvas and it will automatically download.) From FME 2017.1 onward, attempts to add this transformer *on a system where it hasn't been used before* will add a DateTimeCalculator instead (our recommended alternative). The DateDifferenceCalculator works by converting the start and end date/time into "seconds since epoch - midnight Jan 1, 1970" dividing by 86,400 seconds per day, and yes - rounding that result. This results in the behavior you see.

My recommendation going forward...

From FME 2017.1, you can use the new DateTimeCalculator do the same calculation. Like Excel, you can ask for this difference in days and it will return 28.7.

Or, if you want, you can take the difference between just the date portion, like SQL Server. To do this, before the DateTimeCalculator, turn the datetimes into dates, e.g. with @DateTimeCast(@Value(myattr),date) in an AttributeCreator.date-differences.fmw

If any of the above is unclear, please let me know and I'll take a look. Regards, Paul.

tim_wood
Rank
Badge +7

About what you're seeing...

 

 

The DateDifferenceCalculator is an older transformer available via FME Hub from FME Workbench 2017.0 or before. (You can type "DateDifferenceCalculator" on the canvas and it will automatically download.) From FME 2017.1 onward, attempts to add this transformer *on a system where it hasn't been used before* will add a DateTimeCalculator instead (our recommended alternative). The DateDifferenceCalculator works by converting the start and end date/time into "seconds since epoch - midnight Jan 1, 1970" dividing by 86,400 seconds per day, and yes - rounding that result. This results in the behavior you see.

My recommendation going forward...

From FME 2017.1, you can use the new DateTimeCalculator do the same calculation. Like Excel, you can ask for this difference in days and it will return 28.7.

Or, if you want, you can take the difference between just the date portion, like SQL Server. To do this, before the DateTimeCalculator, turn the datetimes into dates, e.g. with @DateTimeCast(@Value(myattr),date) in an AttributeCreator.date-differences.fmw

If any of the above is unclear, please let me know and I'll take a look. Regards, Paul.

Thanks @paulnalos that's really helpful. When I'm next editing any Workspaces that use DateDifferenceCalulator, I'll try and remember to update them to DateTimeCalculator. It would be nice if it showed up as an upgradeable transformer but maybe there's not a clear upgrade path...

 

The example that prompted this post came about because I'm re-engineering a process and was looking for a way to do the date difference calculation in SQL rather than using transformers. Unless there's a better SQL statement, I'm going stick with what I've got because it's much quicker than reading the data in, running it through some transformers and writing it back out to the same SQL table.

 

P
Thanks @paulnalos that's really helpful. When I'm next editing any Workspaces that use DateDifferenceCalulator, I'll try and remember to update them to DateTimeCalculator. It would be nice if it showed up as an upgradeable transformer but maybe there's not a clear upgrade path...

 

The example that prompted this post came about because I'm re-engineering a process and was looking for a way to do the date difference calculation in SQL rather than using transformers. Unless there's a better SQL statement, I'm going stick with what I've got because it's much quicker than reading the data in, running it through some transformers and writing it back out to the same SQL table.

 

I fully see the value in keeping things in SQL when convenient - and now you know it's possible to get the same answers in FME if/when needed.

 

 

I asked our Desktop GUI lead about the possibility of upgrading from one transformer to the other in Workbench, and it turns out we don't currently have the infrastructure to do that for custom to built-in transformers. I absolutely get why that would help, though.

 

 

Thanks very much for your report and feedback here! -Paul

Reply


Community Stats

28,724
Posts
109,516
Replies
36,561
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.