How to specify a relative date range in Custom Chart block

Help
1

Hi

I am trying to work out how to query a data table in a custom chart block to show records created in the last 24 hours.

I haven’t been able to work out how to use the Helper expressions to create something like now - 24 hours in the query expression.

At the moment the only way I can come up with is provide a Webhook URL that performs the calculation.

Am I missing something ?

Thanks

Tim

2

Some further observations on doing this.

I have been using a workflow to extract the data.

What I had forgotten is the Data Table query is working in UTC.
This also means any queries performed and the dates presented in a Custom Chart are also in UTC.

For instance I am querying for specific data created in the last 24 hours. I am able to do this , however the data in the result set is in UTC.

It seems we need access to date manipulation functions of moment.js or helpers to correctly transform this data into viewers timezone if using dates in Custom Charts.

Given all of this how would I get the current viewers Timezone to implement conversion to local time in the the workflow?

Starting to get my head around how to use them which is good.

Cheers

Tim

3

Tim,

If I understand your question right, I think I have a solution for you that does not involve needing a workflow. In !your data table query, you can set up a condition checking your date field against an expression like:

{{subtract (currentDateTime 'x') 86400000}}

19%20AM
Screen Shot 2018-10-26 at 9.12.19 AM.png1152×460 25.3 KB

What the above is doing is using format helpers to get the current time, converting it to a number (that is the x - milliseconds since epoch) and then subtracting 86400000 (which is one day in milliseconds). The data table query understands how to convert a number back to a date automatically for query/comparison purposes.

I tested the above query (in the screenshot), and for me it returns data table rows created in the last 24 hours. Does it solve your problem?

4

Cool, will give that a go.

Will this address my later point with regards to custom charts and the date representation. The workflow method returned data from the query is in UTC or will the query within the Custom chart block be in end users timezone ?

Thanks

Tim