topic Re: HubDB and BST dates in CMS Development

HubDB and BST dates

telega — Wed, 19 Jul 2017 13:16:26 GMT

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 ?

Re: HubDB and BST dates

Jsum — Wed, 19 Jul 2017 16:13:20 GMT

@telega,

Is your date and time localized? Check out the HubL variables, there are a lot of different options for date and time, like this:

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.

Re: HubDB and BST dates

telega — Wed, 19 Jul 2017 16:23:16 GMT

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

Re: HubDB and BST dates

giozua — Sat, 29 Jul 2017 07:35:33 GMT

Try this

and the date will be for example

sunday 2 september 2017

Giovanni Zuanon

Re: HubDB and BST dates

telega — Mon, 31 Jul 2017 12:06:18 GMT

I have a feeling im not explaining the problem clearly.

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

Re: HubDB and BST dates

giozua — Mon, 31 Jul 2017 13:46:48 GMT

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

Re: HubDB and BST dates

telega — Mon, 31 Jul 2017 15:18:46 GMT

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.

Re: HubDB and BST dates

keegan-sbm — Thu, 20 Jun 2019 18:32:52 GMT

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

"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.

Re: HubDB and BST dates

Guru — Thu, 08 Aug 2019 14:42:03 GMT

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