Filter Hubdb by year

[sanntn]

Hi,

I have a Hubdb table on which one column is a date and time field. Is it possible to filter the table to fetch rows by year so for example fetch rows where the year is 2018 in the date and time field?

Thanks.

[piersg · Accepted solution]

Hi @sanntn , you'd do it like this:

{% set start = "2018-01-01T00:00:00+0000"|strtotime("yyyy-MM-dd'T'HH:mm:ssZ")|unixtimestamp %}
{% set end = "2018-12-31T23:59:59+0000"|strtotime("yyyy-MM-dd'T'HH:mm:ssZ")|unixtimestamp %}
{% set query = query ~ "&date_time__gte=" ~ start ~ "&date_time__lte=" ~ end ~ "&orderBy=date_time" %} {# assuming here you may have other queries #}
{% set table = hubdb_table_rows(table_id, query) %}
{% for row in table %}
	{{row}}
{% endfor %}

Or you could loop over your table and use an if statement to check for year, which is a bit more readable. Like this:

{% set table = hubdb_table_rows(table_id, query) %} {# assuming here you may have other queries #}
{% for row in table|sort(False, False, 'date_time') %}
  {% if row.date_time|datetimeformat('%Y') == '2018' %}
    {{row}}
  {% endif %}
{% endfor %}

[VDave]

Hi @piersg 

I have a one question about hubdb filter by month and year.

Task Description : 

I created a db (ss attached)

I done the data print part and navigation of data Now the main task is filter the data by dropdown of months and years 

My Code is Below : 

<h1>DB Filter Demo</h1>

{% set batch_num = 5 %}

{% if not request.query_dict.page_num %}
{% set page_num = 1 %}
{% set offset_num = 0 %}
{% elif request.query_dict.page_num %}
{% set page_num = request.query_dict.page_num %}
{% set offset_num = page_num|add(-1) * batch_num %}
{% endif %}

{% set query = "limit=" ~ batch_num ~ "&offset=" ~ offset_num %}

{% set rows = hubdb_table_rows('5533038',query,queryparam) %}
<ul class="item-list">
{% for row in rows %}
<li class="list-item">
<h2>{{ row.name }}</h2>
<p> {{ row.description }}</p>
<p>{{ row.date|datetimeformat('%B') }}</p>
<p>{{ row.date|datetimeformat('%Y') }}</p>
</li>
{% endfor %}
</ul>

{% set nav = hubdb_table_rows('5533038') %}

{% set total_pages = nav|length|divide(batch_num) %}

{% set more_pages = total_pages - page_num %}

<nav>
{% if page_num > 1 %}
<a class="previous-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}" title="Next">Previous</a>
{% endif %}

{% if more_pages == 0 %}
{% if page_num|add(-4) >= 1 -%} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-4) }}">{{ page_num|add(-4) }}</a>{%- endif %}
{% endif %}

{% if more_pages <= 1 %}
{% if page_num|add(-3) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-3) }}">{{ page_num|add(-3) }}</a>{% endif %}
{% endif %}

{% if page_num|add(-2) >= 1 %} <a href="{{ content.absolute_url }}?page_num={{ page_num|add(-2) }}">{{ page_num|add(-2) }}</a>{% endif %}

{% if page_num|add(-1) >= 1 %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(-1) }}">{{ page_num|add(-1) }}</a>{% endif %}

<a class="active" href="{{ content.absolute_url }}?page_num={{ page_num }}">{{ page_num }}</a>

{% if page_num|add(1) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}">{{ page_num|add(1) }}</a>{% endif %}

{% if page_num|add(2) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(2) }}">{{ page_num|add(2) }}</a>{% endif %}

{% if page_num <= 2 %}
{% if page_num|add(3) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(3) }}">{{ page_num|add(3) }}</a>{% endif %}
{% endif %}

{% if page_num == 1 %}
{% if page_num|add(4) <= total_pages %}<a href="{{ content.absolute_url }}?page_num={{ page_num|add(4) }}">{{ page_num|add(4) }}</a>{% endif %}
{% endif %}

{% if total_pages > page_num %}
<a class="next-posts-link" href="{{ content.absolute_url }}?page_num={{ page_num|add(1) }}" title="Older Posts">Next</a>
{% endif %}
</nav>


Thanks in advance 🙂

[sanntn]

This was exactly what I needed, thanks!