We were working on having a field calculate the number of days (to the nearest hundredth, for example, 2.25 days) of difference between two dates. However, we noticed that the date difference can be off by approximately an hour, if the two dates fall before and after a time change, such as daylight savings. Is there a way to account for the hour that was lost? Here is the method in how we calculate the duration in days:
- 1) Convert the dates to milliseconds i.e. store the date in number field
- 2) Subtract the dates i.e. c=a-b
- 3) Convert to minutes i.e. d=c/60000
- 4) Convert to days i.e. d/1440
- 5) Round two decimal points.
As a temporary fix, a manual calculation would be necessary. The existing logic to calculate the date to date duration was not handling daylight saving time (DST), and the calculations being performed do not appear to be necessary. Subtracting the EPOCH start and EPOCH end date values passed into the method will give an accurate duration calculation, on top of the fact they already account for DST.