All record from x days ago

Is there a simple way of fetching all records “whose date column y is from x days ago”?

e.g. All contacts whose “LastContacted” column is less than 7 days ago.

My current process is:

  1. Run javascript in JS Env to get the unixtime of 7 days ago
  2. Set variable of type date to unix timestamp
  3. format a query using timestamp and date functions in the query language to run the timestamp comparisons (I got the idea from the way the frontend does it)

See below:

Run JS Env

(code in plain text below for those of you who want to use this strategy)

function getUnixTimestamp(numberOfDaysAgo) {
  const today = new Date();
  const daysAgo = new Date(today);
  daysAgo.setDate(today.getDate() - numberOfDaysAgo);
  return Math.floor(daysAgo.getTime() / 1000);
return getUnixTimestamp(input)

Set Variable

Create this monstrosity of a query

format("MainRepId_calc=%v ",rep.ID)+" AND (TIMESTAMP(DATE_FORMAT(ApprovalDate, '%Y-%m-%dT%H:%i:00.%f+00:00')) > TIMESTAMP(DATE_FORMAT('"+strftime(daysAgo, "%Y-%m-%dT%H:%M:%S")+"', '%Y-%m-%dT%H%i'))"

1 Like

This doesn’t answer your question at all but I had no idea you could use JavaScript in a function like that. I need to go find where I missed that in the docs lol

Thank you for sharing. Now I’m off to test and play with this.

Glad the post helped you!

There aren’t much docs on it.

Reference documentation:

Example in docs:

Example from forum:

Underlying JS engine implementation:

1 Like

might have a look at this one: Prefilter records / SQL dialect - #3 by grisgruis but might be insufficient because you cannot easily subtract days in this manner