HubDB and BST dates

[telega]

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 ? 

[keegan-sbm]

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.

[Guru]

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

[Jsum]

@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:

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

[telega]

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

[giozua]

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

[telega]

I have a feeling im not explaining the problem clearly. 

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

[giozua · Accepted solution]

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

[telega]

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.