# HubDB and BST dates

**URL:** <https://community.hubspot.com/t/hubdb-and-bst-dates/2757>\
**Category:** CMS Development\
**Created:** [July 19, 2017, 1:16pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757 "2017-07-19T13:16:26Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![telega](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/telega/32/14784_2.png) [@telega](https://community.hubspot.com/u/telega)\
**Post date:** [July 19, 2017, 1:16pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/1 "2017-07-19T13:16:26Z")

</div>

I have created a table in hubDB to track press releases, and a listing page to display them.

The listing page filters by comparing the date in the table item (item.date|unixtimestamp) to the local time (local\_dt|unixtimestamp). As far as I can tell, the local\_dt is adjusting for BST, but the item.date from HubDB is not.

Is this correct, and does anyone know of work around ?

---

<div class="post-metadata">

**Author:** ![Jsum](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/jsum/32/20405_2.png) [@Jsum](https://community.hubspot.com/u/Jsum)\
**Post date:** [July 19, 2017, 4:13pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/2 "2017-07-19T16:13:20Z")

</div>

@telega,

Is your date and time localized? Check out the [HubL variables](http://designers.hubspot.com/docs/hubl/hubl-supported-variables), there are a lot of different options for date and time, like this:

{{ content.publish\_date\_localized }}

string

A string representing the datetime when the content was published, in the local time as defined in the HubSpot Report settings. This variable is also subject to the language and dateformat settings in **Content Settings \> Blog**.

---

<div class="post-metadata">

**Author:** ![telega](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/telega/32/14784_2.png) [@telega](https://community.hubspot.com/u/telega)\
**Post date:** [July 19, 2017, 4:23pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/3 "2017-07-19T16:23:16Z")

</div>

Yeah, its not a HubL variable though, its a field in a HubDB table.

---

<div class="post-metadata">

**Author:** ![giozua](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/giozua/32/840_2.png) [@giozua](https://community.hubspot.com/u/giozua)\
**Post date:** [July 29, 2017, 7:35am UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/4 "2017-07-29T07:35:33Z")

</div>

Try this

{{ row[“your\_date\_column\_name”]|datetimeformat(‘%A %e %B %Y’) }}

and the date will be for example

sunday 2 september 2017

Giovanni Zuanon

---

<div class="post-metadata">

**Author:** ![telega](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/telega/32/14784_2.png) [@telega](https://community.hubspot.com/u/telega)\
**Post date:** [July 31, 2017, 12:06pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/5 "2017-07-31T12:06:18Z")

</div>

I have a feeling im not explaining the problem clearly.

The problem isnt with the display formatting, its with the underlying date value.

---

<div class="post-metadata">

**Author:** ![giozua](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/giozua/32/840_2.png) [@giozua](https://community.hubspot.com/u/giozua)\
**Post date:** [July 31, 2017, 1:36pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/6 "2017-07-31T13:36:17Z")

</div>

Sorry but I don’t understand.

If you want compare two dates you can have a string like this:

{% if (row[‘your\_date\_column\_name]|datetimeformat(’%j’)) \>= (local\_dt|datetimeformat(‘%j’)) %}

(this means that your date is more recent then today date) … write your conditions

{% else % }

…alternative conditions…

{% endif %}

Note that I have in my hibdb a column of date\_event with date format (not datetime) so I’ve transform my date in timestamp with %j format

I hope this can help you

Best regards

Giovanni Zuanon

---

<div class="post-metadata">

**Author:** ![telega](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/telega/32/14784_2.png) [@telega](https://community.hubspot.com/u/telega)\
**Post date:** [July 31, 2017, 3:18pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/7 "2017-07-31T15:18:46Z")

</div>

You are correct, thank you. I did:

`{%if release['date']|datetimeformat('%Y %m %d %H') <= local_dt|datetimeformat('%Y %m %d %H')%}`

After some digging I think my issue is that the |unixtimestamp filter is not doing what I expected it to do. I should adjust the timezone of the local\_dt before filtering it.

---

<div class="post-metadata">

**Author:** ![keegan-sbm](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/keegan-sbm/32/16431_2.png) [@keegan-sbm](https://community.hubspot.com/u/keegan-sbm)\
**Post date:** [June 20, 2019, 6:01pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/8 "2019-06-20T18:01:18Z")

</div>

We recently were working through a similar issue where datetimes set in hubdb were being set in the content administrators time (CDT/CST), and we needed a way to shift those times into the correct local timezone, _as though they were set in the correct timezone to begin with_. We settled on parsing out the timezone offset from the local\_dt (which changes during the course of the year), and then using that in computing the datetimes for display purposes.

You can see how that works with the code below

```auto
"my_time" should be the timestamp you get from hubdb. In order for this code to run, I'm just using the local_dt.{% set my_time = unixtimestamp(local_dt) %}Get the offset value for the local dt:
{% set offset = datetimeformat(local_dt, '%z') %}Convert the offset value for the local dt a numerical representation of "hours to be added to a unix timestamp", eg 5.0
{% set offset_num = (((offset|float) / 100) * -1) %}Convert those hours into milliseconds and back into integer to avoid floating point errors.
{% set offset_millis = (offset_num * 60 * 60 * 1000)|int %}
Values for debugging:
{{my_time}}
{{offset}}
{{offset_num}}
{{offset_millis}}
Final shifted value (in milliseconds):
{{my_time+offset_millis}}

```

Hopefully someone will find that useful.

---

<div class="post-metadata">

**Author:** ![Guru](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/guru/32/100617_2.png) [@Guru](https://community.hubspot.com/u/Guru)\
**Post date:** [August 8, 2019, 2:40pm UTC](https://community.hubspot.com/t/hubdb-and-bst-dates/2757/9 "2019-08-08T14:40:43Z")

</div>

@keegan-sbmThis should definintely me marked as a solution. It’s the only route we could use to solve this time issue. Thanks!