APIs & Integrations

pger
Participant

HubDB Query to filter all dates older than today

SOLVE

Hi guys

 

I need a short hint: I have a query to filter rows by date. Currently the query is as follow:

{% set query = query + "&webinar_date__lt=" + local_dt|unixtimestamp %}

 

But the result includes all rows with today's date as well. Is there a possibility to change this query so that it only shows rows with dates in the past?

0 Upvotes
1 Accepted solution
WendyGoh
Solution
HubSpot Employee
HubSpot Employee

HubDB Query to filter all dates older than today

SOLVE

Hey @pger,

 

Referencing this community thread: Solved: HubSpot Community - Filter HubDB data based on today's date - HubSpot Community in which the user is looking to filter data based on today's + future events using something like this:

 

{% if (row['event_start_date']|datetimeformat('%j')) >= (local_dt|datetimeformat('%j')) %}

your rows like <li>...</li>

{% endif %}

 

since it's the exact opposite of your use case, we can look into changing the code to:

 

{% if (row['event_start_date']|datetimeformat('%j')) < (local_dt|datetimeformat('%j')) %}

your rows like <li>...</li>

{% endif %}

View solution in original post

0 Upvotes
3 Replies 3
WendyGoh
Solution
HubSpot Employee
HubSpot Employee

HubDB Query to filter all dates older than today

SOLVE

Hey @pger,

 

Referencing this community thread: Solved: HubSpot Community - Filter HubDB data based on today's date - HubSpot Community in which the user is looking to filter data based on today's + future events using something like this:

 

{% if (row['event_start_date']|datetimeformat('%j')) >= (local_dt|datetimeformat('%j')) %}

your rows like <li>...</li>

{% endif %}

 

since it's the exact opposite of your use case, we can look into changing the code to:

 

{% if (row['event_start_date']|datetimeformat('%j')) < (local_dt|datetimeformat('%j')) %}

your rows like <li>...</li>

{% endif %}
0 Upvotes
fawadsabri
Contributor

HubDB Query to filter all dates older than today

SOLVE

Hi @WendyGoh i am stuck with my module. So i created the code to show all of the events in future. Now in the HubDB we have events for the whole year and showing it all is just too much. I am trying to show the events for next 30 days or 40 days, basically want it to be dynamic and i want to select it based on the page and type of event how many days in future i want to show the event for. Here is code that i have for showing the events sorted by date. I tried adding "plus_time(30, 'days')" didnt really work. 

 

{% set current_dt = unixtimestamp( local_dt ) %}
{% set queryparam = queryparam + "&orderBy=date" + "&date__gt=" + local_dt|unixtimestamp %}
{% set table = hubdb_table_rows(module.hubdb, queryparam) %}

 

Could you please help me on this. 

 

Thank you, 

Fawad

0 Upvotes
pger
Participant

HubDB Query to filter all dates older than today

SOLVE

@WendyGoh 

 

Thank you for your help. I saw the solution mentioned in your answer too. My intention was to filter the query without a loop option. But your solution seems to be easier, since I have to convert the datetime to a date function. 

0 Upvotes