Table: Get Rows - How do I query for all rows created within last 7 days?

Help
1

I’ve been trying to create a Table Get Rows query that only includes the last 7 days of data.

I’ve tried the > operator, but it expects a date object. This works with the current {{ time }} object, but not with any other date object I put on the payload – they’re converted to string literals :frowning:

So how do you request just the last 7 days of data?

1 Like
2

Hi Steve,

I believe your issue is that you are not casting the time to a date object like this {{ formatDate time }}. Here is some documentation on formatting payload templates.

To request the last seven days of data, I would use a math node before calling the Losant API node to export the rows. In the math node I would use the expression {{ formatDate time 'x' }} - (7 * 24 * 60 * 60 * 1000) and put it on your payload at data.sevenDaysAgo. Then on the API node, you can build a query like the following:

{
"createdAt": { "$gte":  {{ data.sevenDaysAgo }} }
}

Hope that helps,
Erin

3

Thanks Erin,

The API node query worked :slight_smile: But I’m still curious why a simple > operator didn’t work in the Table Get Rows

NB. For others reading this, I used a function block rather than handlerbars markup to calculate the start of the seven day period:

// Calculate start of reporting period in ms since epoch UTC.
// Report includes all samples entered for the previous 7 days
// ending at midnight today (00:00 UTC).

let now = Date.now();
let endOfReport = new Date(now - now % (24 * 60 * 60 * 1000));
let startOfReport = new Date(endOfReport.valueOf() - 7 * 24 * 60 * 60 * 1000);
console.log("Start of Reporting Period: " + startOfReport.toISOString());
payload.data.startOfReport_ms = startOfReport.valueOf(); // payload only supports JSON
1 Like