Return data from a specific time period

Queries and Resources
, ,
1

Hello there. I've got PSQL query that returns what I want but I need to narrow it down to a specific time period, say 1 month ago, 2 months ago or year to date. And I need to do it in a way so it's practical and there isn't too much crowded queries. (say there's only 10 queries but I need to narrow it down to 5 different time periods. that would mean 50 more queries just to get the data for these time periods alone). Transformers look like they could definitely work but I couldn't figure them out, I need help

How do I do it? This returns all the data:

select count(*) from issuances where network = 'xyz' and status = 'completed'

And this one returns the data from a month ago (between current time and a month ago)

select count(*) from issuances where network = 'xyz' and issued_at > now() - interval '1 month' and status = 'completed';

Doing this second query for all would be inefficient. How could I do it practically?

2

Maybe pass in a date component value {{date1.value}} where it is equal to
something like the following as a default or selected value by the user....
{{ moment().subtract(1,'Month').format('YYYY-MM-DD')}}

3

I don't quite get it. I tried to implement what you said but I still get the original value that returns all data:

2022-08-02 10_40_05-murat_kpi_metrics _ Editor _ Retool
2022-08-02 10_40_05-murat_kpi_metrics _ Editor _ Retool1075×380 29 KB

2022-08-02 10_40_29-murat_kpi_metrics _ Editor _ Retool
2022-08-02 10_40_29-murat_kpi_metrics _ Editor _ Retool872×380 20.1 KB

4

btw I've managed to figure out how to get data for the current month by doing this, returns the count of all issuances since the start of this month:

SELECT count(*) as ethereum_issuance_m0  FROM issuances_extended WHERE issued_at >= date_trunc('month',current_date) and network = 'ethereum' and status = 'completed'

and this one, returns data since the start of this year:

SELECT count(*) as ethereum_issuance_ytd FROM issuances_extended WHERE issued_at >= date_trunc('year',current_date) and network = 'ethereum' and status = 'completed'

but there 7 different time periods and 24 different data I want to get, that would mean a lot of queries, very inefficient. Looking for a way to do it by scripting retool.

5

Hey @caysle!

To add on to what Scott has said, you might also try using additional scope here. if you rewrite your JS query as follows

const query = timeout;
const intervals = [moment().subtract(1, 'month').format('YYYY-MM-DD')];
const results = intervals.map(interval => query.trigger({additionalScope: {interval}}));
return Promise.all(results);

You can then reference the {{interval}} variable directly in your query, e.g. with something like

select count(*) from issuances where network = 'xyz' and issued_at > {{interval}} and status = 'completed';

If you'd like, you can also reference date_transformer.value when defining your intervals in the JS query (note that JS queries don't require {{}} to reference objects in your app):

const intervals = [moment().subtract(1, 'month').format('YYYY-MM-DD'), date_transformer.value];

Also note that .map and Promise.all are used here so that the JS query will actually return the collected results of each query trigger, see this post for more details there.

1 Like
6

Thanks @Kabirdas, I've managed to write it in Postgres only but now I need something else. currently the SQL query I'm using brings data between now and any chosen time. For example, when I say

issued_at > now() - interval '2 month'

I can get data from between 11 august (today) and 11 june. However, when I say data from 2 months ago I need this to bring the data from 1 to 30 june. How do I do it? Is using retool transformers a better option?

7

Awesome, good find!

You can try using `date_trunc('month', current_date)` to get the first of the month before subtracting your interval, e.g.

issued_at > date_trunc('month', current_date) - interval '2 month' as two_months_ago

Does that work? Or have you found another solution in the interim? :sweat_smile:

1 Like
8

well yes I have, this brings me the data from since start and end of june. it's two months ago from august 2022 so when it's september it will be updated to july, two months ago from september. list goes on

issued_at >= date_trunc('month', current_date) - interval '2 month'
and issued_at < date_trunc('month', current_date) - interval '1 month'
9

there is also this, however stupid it may look, gets me data since start and end of july, so one month ago and also will be updated as months pass by

issued_at >= date_trunc('month', current_date) - interval '1 month'
and issued_at < date_trunc('month', current_date) - interval '0 month'

I also need help with another subject, can you check?