CMS Development

SvenVanbrabant
投稿者

Filter HubDB data based on today's date

解決

I am using HubDB to display a list of webinars.

I use the following columns:

event_start_date = start date of the event

event_end_date = end date of the event

landing_page_url = desination URL

 

However, I would not like to display past webinars.

So I need to retreive today's date and make sure that only future events are displayed.

Can I filter these in my overview?

 

My current code is below.

 

 

 

{% set queryparam = "&orderBy=event_start_date" %}

{% set table = hubdb_table_rows(671918, queryparam ) %}
{% if table == [] %}
     <p class='lead'>Sorry, no webinars available.</p>
{% else %}
     <ul> 
          {% for row in table %}
          <li>
               <h2><a href="{{ row.landing_page_url }}">{{ row.hs_name }}</a</h2>                      
          </li>
          {% endfor %}
     </ul>
{% endif %}

 

2件の承認済みベストアンサー
giozua
解決策
投稿者 | Platinum Partner
投稿者 | Platinum Partner

Filter HubDB data based on today's date

解決

Hi Sven,

you must put inside your for cycle this strings:

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

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

{% endif %}

note that %j is needed to convert my date (in date format and not datetime) in a timestamp so it's possible to compare two dates

This string hide the past events

I hope this can help you

Best regards

Giovanni

 

元の投稿で解決策を見る

jasonvanpelt
解決策
参加者

Filter HubDB data based on today's date

解決

Rather than filtering by date in your for loop, it is better to filter the query itself

 

{% set queryparam = "&orderBy=start_date" %}
{% set queryparam = queryparam + "&event_start_date__gt=" + local_dt|unixtimestamp %}
{% set table = hubdb_table_rows(XXXXXX, queryparam) %}

 

This helps in scenarios where you might only want to show a limited # of results >= today's date -- say a list of the next 3 upcoming on your homepage, not ALL upcoming.

元の投稿で解決策を見る

9件の返信
jasonvanpelt
解決策
参加者

Filter HubDB data based on today's date

解決

Rather than filtering by date in your for loop, it is better to filter the query itself

 

{% set queryparam = "&orderBy=start_date" %}
{% set queryparam = queryparam + "&event_start_date__gt=" + local_dt|unixtimestamp %}
{% set table = hubdb_table_rows(XXXXXX, queryparam) %}

 

This helps in scenarios where you might only want to show a limited # of results >= today's date -- say a list of the next 3 upcoming on your homepage, not ALL upcoming.

fawadsabri
投稿者

Filter HubDB data based on today's date

解決

Hi @jasonvanpelt, so i tried the cod and it works perfectkly. However i am not able to show the events for the next 30 days. How can i add that? Thanks in advance.

0 いいね!
giozua
解決策
投稿者 | Platinum Partner
投稿者 | Platinum Partner

Filter HubDB data based on today's date

解決

Hi Sven,

you must put inside your for cycle this strings:

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

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

{% endif %}

note that %j is needed to convert my date (in date format and not datetime) in a timestamp so it's possible to compare two dates

This string hide the past events

I hope this can help you

Best regards

Giovanni

 

Subinbajra
参加者

Filter HubDB data based on today's date

解決

Hi @giozua ,

This solution work perfect when we use '%j' in a timestamp but the thing is this condition doesnot show up the events for next year. I mean this condition only shows the events till Dec 31, 2020. If we set the event date for Jan 01, 2021 then, the events are not being displayed in the site. I suppose you get what's the issue I mean.

It would be really a great help if there is any solutions regarding the issue. Thanks in advance! 

leckerman
トップ投稿者

Filter HubDB data based on today's date

解決

@giozua - It appears that string of code works if the event dates are within the same year (ex. past 2018 events don't show up), but any events with dates in the year 2017 are showing up in the listing (and shouldn't be). Any idea how to get around this or why this is happening?

 

This is my string of code...

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

leckerman
トップ投稿者

Filter HubDB data based on today's date

解決

Can anyone help with this? It appears that new 2019 event dates are not showing up either with that string of code. Please help!

0 いいね!
jennysowyrda
コミュニティーマネージャー
コミュニティーマネージャー

Filter HubDB data based on today's date

解決

Hi @leckerman,

 

I want to see if other HubDB users are experiencing this issue @Jlamb1 and @stefan are you experiencing the same thing? Do you have any tips for @leckerman?

 

Thanks,

Jenny

0 いいね!
leckerman
トップ投稿者

Filter HubDB data based on today's date

解決

@jennysowyrda - I was able to find help in the HubSpot slack group on Friday. Key was to add "&datetime_column__gt=0s" to the query and not use an if statement. Hope that can help someone else!

 

https://developers.hubspot.com/docs/methods/hubdb/v2/get_table_rows

giozua
投稿者 | Platinum Partner
投稿者 | Platinum Partner

Filter HubDB data based on today's date

解決

Try this

{% if (row['event_date_start']|unixtimestamp)>=(local_dt|unixtimestamp)%}

It should work

 

Giovanni