# Filter Hubdb by year

**URL:** <https://community.hubspot.com/t/filter-hubdb-by-year/58140>\
**Category:** CMS Development\
**Tags:** hubdb\
**Created:** [June 1, 2022, 1:54pm UTC](https://community.hubspot.com/t/filter-hubdb-by-year/58140 "2022-06-01T13:54:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sanntn](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/sanntn/32/99782_2.png) [@sanntn](https://community.hubspot.com/u/sanntn)\
**Post date:** [June 1, 2022, 1:54pm UTC](https://community.hubspot.com/t/filter-hubdb-by-year/58140/1 "2022-06-01T13:54:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![piersg](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/piersg/32/99782_2.png) [@piersg](https://community.hubspot.com/u/piersg)\
**Post date:** [June 1, 2022, 4:22pm UTC](https://community.hubspot.com/t/filter-hubdb-by-year/58140/2 "2022-06-01T16:22:54Z")

</div>

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

```lia-code-sample
{% 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:

```lia-code-sample
{% 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 %}

```

---

<div class="post-metadata">

**Author:** ![sanntn](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/sanntn/32/99782_2.png) [@sanntn](https://community.hubspot.com/u/sanntn)\
**Post date:** [June 2, 2022, 7:22am UTC](https://community.hubspot.com/t/filter-hubdb-by-year/58140/3 "2022-06-02T07:22:53Z")

</div>

This was exactly what I needed, thanks!

---

<div class="post-metadata">

**Author:** ![VDave](https://avatars.discourse-cdn.com/v4/letter/v/7bcc69/32.png) [@VDave](https://community.hubspot.com/u/VDave)\
**Post date:** [October 19, 2022, 12:22pm UTC](https://community.hubspot.com/t/filter-hubdb-by-year/58140/4 "2022-10-19T12:22:10Z")

</div>

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 🙂