All record from x days ago

🧩 Low-Code Development
#1

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

image
image964Ă—608 90.3 KB

(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

image
image776Ă—480 16.4 KB

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'))"

2 Likes
#2

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.

#3

Glad the post helped you!

There aren’t much docs on it.

Reference documentation:

https://docs.cortezaproject.org/corteza-docs/2023.3/integrator-guide/automation/workflows/func-reference.html#_jsenv

Example in docs:

Example from forum:

Underlying JS engine implementation:

1 Like
#4

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