How do I find out if a DateTime object in one table falls between 2 DateTime objects in another table

Ask for Help
1

I have a Trades table that contains a TradeDateTime object for the trade time.

image

I have a News table that contains a time window for news.

image

I need to check each TradeDateTime in the Trades table to see if it falls between NewsStopTime and NewsResumeTime.

the concept is simple…
bool pauseTrading = if (and(TradeDateTime > NewsStopTime, TradeDateTime < NewsResumeTime))

but I don’t know how to do it with Grist tables

help please :wink:

2

I guess I should google more before I ask… here is my answer. Please let me know if these is an easier way.

rows = News.lookupRecords(sort_by = 'Date')

pause = False
for r in rows:
  if(
      AND(
        $TradeDateTime >= r.NewsStopTime,
        $TradeDateTime <= r.NewsResumeTime
        )
    ): 
      pause = True
      break
         
    
return pause
3

Same idea but shorter:

tdt = $TradeDateTime
any(r.NewsStopTime <= tdt <= r.NewsResumeTime for r in News.all)

However, note that both versions are inefficient, since they have to scan potentially the full News table for each cell where the formula is evaluated.

There is a proposal in the works that could make it much more efficient, if the intervals are non-overlapping. But if you may have arbitrary overlaps, then I am not sure if it’s possible for this to be efficient.

4

thanks. I updated my formula with your simpler version. There isn’t any overlap between the dates so the proposed idea should work for me