Expand my Community achievements bar.

dateDifference function behavior around Daylight Savings Time and Standard Time transitions (an idea and a warning)

Avatar

3/6/24

The dateDifference(t1; t2; unit) function in Fusion allows you to compute the number of hours/days/etc. between two timestamps (t1 and t2) provided.  This behaves oddly whenever the number of hours in a day isn't exactly 24, which happens twice a year when crazy countries/localities switch between Daylight Savings Time and Standard Time.

Example: On March 10, 2024, at 2am, the clocks "jump" forward to 3am.  That is, there technically is no 2:00am to 2:59am on March 10.  This results in March 10 having only 23 hours in the day.  (The same thing happens on November 3, except we actually repeat 2:00am to 2:59am, resulting in 25 hours in the day.)

This website has more information that corroborates: https://www.ge.com/digital/documentation/csense/version2023/Blocks/Daylight_Saving_Time.htm#:~:text=....

If you're trying to use something like dateDifference(t1; t2; days) and expect something like 2024-03-09T00:00:00 to 2024-03-11T00:00:00 (both in America/Los_Angeles time zone) to return a value of "2", it will instead return a value of "1".  This occurs because dateDifference is computing a delta of 47 hours, which is less than 48 ("2 days" as per how dateDifference is written).  When dateDifference divides 47 by 48, it results in 1.979... which is then truncated to just "1".

Note that this appears unique and different than any other programming language that has similar functions.  You can test this using a Java online compiler such as https://www.programiz.com/java-programming/online-compiler/ and the code below:

 

import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoUnit;

class HelloWorld {
 public static void main(String[] args) {
  final ZoneId timeZone = ZoneId.of("America/Los_Angeles");
  final ZonedDateTime start = ZonedDateTime.of(2024, 3, 9, 0, 0, 0, 0, timeZone);
  final ZonedDateTime end = ZonedDateTime.of(2024, 3, 11, 0, 0, 0, 0, timeZone);
 
  System.out.println(ChronoUnit.DAYS.between(start, end));
 }
}

 

Java is appropriately handling the fact that the length of a "day" is actually variable and thinking more in terms of "midnight to midnight" rather than "how many 24 hour periods exist".

It is possible to workaround this using by casting every date to UTC, but this is super cumbersome, as shown by this Fusion example:

Screen Shot 2024-03-06 at 7.15.20 PM.png

 

The awareness required around this renders dateDifference as a dangerous function to use without advanced Computer Science knowledge.  For example, the help text (shown below) is fairly simple with no warnings around this, nor are the online docs (below) hinting to the need to use UTC -- though they do only show "Z" (UTC) in the examples, which is perhaps too subtle of a thing.

Screen Shot 2024-03-06 at 7.20.56 PM.png

Screen Shot 2024-03-06 at 7.21.56 PM.png

Ideally, dateDifference should internally cast to UTC if that's what's required to bring in line with the semantic notion of "hours" or "days" or other units.  For dates that are already in UTC, this would be a "no-op" and things would work fine.  For non-UTC dates, it would make things more intuitive.

While a support ticket was filed on this, they redirected to submit an Idea via the forum here, saying that the current implementation of dateDifference meets the desired behavior and indicated that it would likely only get actioned if it received enough upvotes.  So take this as both a warning in use of the function with its current behavior and an idea ask for the community to vote on.  Hope this helps someone either way!

1 Comment

Avatar

Employee

3/7/24

Upvoting for visibility. I am always in favor of ANY service (especially one I use, support, and provide directly to clients) to adhere to governing programmatic standards. Deviation from an accepted governing standard (ISO for datetime in this case) must have a detailed, precise explanation---that is publicly documented---as to why the deviation is necessary in a given case. 

 

A blanket statement of, "This function meets desired behavior," is insufficient in my mind.