Filter data by date range

[ben-duchy]

I’ve been tasked with displaying rows based on a set of date ranges with an overall number of how many rows meet this criteria. This date range will need to be editable within the page editor but for now I can't even get a hard coded version to work!

I thought something along these lines, but no luck!

{% set table = hubdb_table_rows(12345467) %}

{% for row in table %}
    {% set plot = unixtimestamp(row.plot_date) %}
    {% set startdate1 = "01/07/2021"|strtodate("dd/MM/yyyy") %}
    {% set enddate1 = "30/09/2021"|strtodate("dd/MM/yyyy") %}
  
    {% if plot | datetimeformat('%e/%m/%Y') >= startdate1 %}
      {% if plot | datetimeformat('%e/%m/%Y') <= enddate1 %}
      Display row data
      {% endif %}
    {% endif %}
  
  {% endfor %}

To summerise, it should show:

• Between 1 July 2020 – 30 September 2020
• Unsold Plots = 10
• Row(s) information displayed
• Reserved plots = 20
• Row(s) information displayed
• Exchanged plots = 25
• Row(s) information displayed

Total plots = 55

Any help would be greatly appreciated.

[ben-duchy]

My apologies, yes of course 🙂

[Indra]

@ben-duchy can you mark this topic as solved if this solved your problem?

[Indra · Accepted solution]

Hi @ben-duchy,

Did you check your previous question?
https://community.hubspot.com/t5/CMS-Development/Finding-the-length-of-repeating-dates/td-p/457184

It's best to format your date by numbers so you can compare if it's a larger of lower number. So best thing to do it to set your date up by %Y%m%d.

Your code will look like this:

{% set rows = hubdb_table_rows(1234567, "&plot_status__in=Completed&forecast_year__in=20/21") %}

{% set items = {'amount' : 0 } %}

{% for row in rows %}

  {% set cur = row["legal_completion"]|datetimeformat('%Y%m%d') %}
  {% set from = module.from_date|datetimeformat('%Y%m%d') %}
  {% set till = module.till_date|datetimeformat('%Y%m%d') %}

  {% if from <= cur && cur <= till %}
    {% do items.update({'amount' : items.amount + 1}) %}
  {% endif %}
{% endfor %}

Items in range: {{ items.amount }}