DateRange end is off by one second when using Date picker UI

I have a “Reporting Period” DB whose entities define Date ranges, and they collectively comprise a complete range of time, separated into mutually-exclusive, non-overlapping ranges that abut each other; so any given timestamp is guaranteed to “belong to” no more than one Period.

Thus each Period’s date range must begin exactly where the previous Period’s range ends. Successive ranges can’t overlap, and they can’t have any gap between them.

Then I have:

someDB → links to a Period entity

otherDB → contains a Timestamp Date field

In the context of a particular someDB entity, associated with a specific date range, I have a Formula to find/filter the otherDB entities whose timestamp falls within this date-range:

    [Timestamp] >= [Step 1 someDB].[Period].Start() and
    [Timestamp] <  [Step 1 someDB].[Period].End()

However, when manually setting a Date Range field, the Fibery Date picker UI apparently adds one second to the user-specified end time, resulting in a 1-second overlap between these Periods that need to be mutually exclusive:

This makes it impossible to manually create a set of Periods/Date Ranges that comprehensively cover a range of time and do not overlap.

Scripts apparently do not suffer from this issue.

1 Like

1 Like