Check if date from HubDB date and time field has passed today's (current) date

[Amit_95]

I have a table in HubDB called events.

Within this table, I have a column of the type date and time. For reference, this is the data I have for that field:

When the event is live, I want to display a banner across the site stating "this event is live, watch now". This banner should only appear on the event date and when past the event start date. So using the above image as an example, the banner should show only when it's 7th Sept 2020 at 12:00.

To achieve the above, I'm trying to utilize HuBL.

I've looped through the table and obtained the fields values:

{% set table_id = 2663275 %}

{% set table_query = hubdb_table_rows(table_id) %}

{% for data in table_query %}
  {% set event_date         = data.event_date|datetimeformat('%B %e, %Y') %}
  {% set event_time         = data.event_date|datetimeformat('%H %M') %}
{% endfor %}

Now, in HuBL, I'm trying to run an if statement on my banner module that is equivelent to the following psuedo:

if {{ event_date }} and {{ event_time}} is past {{ today's date and time }} then show module else, don't show

Can't see a way to get todays date in HuBL? And also, is my datetimeformat for such an approach?

[Chris-M]

The field event_date should give you already the timestap of date and time of the event.

You need to check it with the timestap of right now.

Basicly you need something like this

{% for data in table_query %}
  {% if (data['event_date'] >= unixtimestamp(local_dt) %}
     DO THIS
  {% else %}
     IF NOT THEN THIS
  {% endif %}
{% endfor %}

i hope i got your fields right, please check the naming of the fields, maybe i missed something.

- Chris

[Amit_95]

Hi Chris, 

Thanks, but it's producing the same results for me, here's my appraoch in full:

{% set table_data_dict = {} %}

{% set table_id = 2663275 %}
{% set table_query = hubdb_table_rows(table_id) %}

{% for data in table_query %}
     {% set event_time_raw     = data.event_date %}
     {% set dict_event_time_raw = table_data_dict.update({'event_time_raw' : event_time_unix}) %}
{% endfor %}

{% set event_date_and_time = table_data_dict.event_time_raw %}
{% set current_time_and_date = local_dt|unixtimestamp %}

{% if current_time_and_date >= event_date_and_time %}
  event is live
{% else %}
  event is not live
{% endif %}

With the above, here are my test results

Test 1:

• Date set in DB: 8th August 2020 12:00pm
• Current date: 5th August 2020 13:42pm
• Results: Prints "event is not live" - Correct

Test 2:

• Date set in DB: 3rd August 2020 12:00pm
• Current date: 5th August 2020 13:43pm
• Results: Prints "event is live" - Correct

Test 3:

• Date set in DB: 5th August 2020 13:50pm
• Current date: 5th August 2020 13:50pm
• Results: Prints "event is not live" - incorrect

Test 4:

• Date set in DB: 5th August 2020 13:50pm
• Current date: 5th August 2020 13:51pm
• Results: Prints "event is not live" - incorrect

Ideas on why tests 3 and 4 say it's not live, when it's past or on the time?

[Chris-M]

Lets see if i get you right 😄

We are just having, is date_time before or after the actual time.

We are getting timestaps which is in seconds, so basicly if 'Test 3'

Date set in DB: 5th August 2020 13:50pm
Current date: 5th August 2020 13:50pm

It's actually over already since the timestamp of "5th August 2020 13:50pm" is the first second of this value, every second after you are in the past time.

So this is why it says event is over / not live. If you want to give the event a timeframe, you need a variable or another hubdb field with event end time.

If you know every event is scheduled for 2 hours, you can make an elif statement with event_date_time and add like "7200" for (2h).

Maybe this is more suiting (this example would work with an extra field for the end date)

{% for data in table_query %}
  {% if (data['event_date'] >= unixtimestamp(local_dt) %}
     Event is in the future
  {% elif  (data['event_date'] >= unixtimestamp(local_dt) and (data['END_event_date'] <= unixtimestamp(local_dt) %}
  Event is live until it hits the End Date
  {% else %}
     Event is in the past
  {% endif %}
{% endfor %}

i hope i got everything right!

Possible results:

Event is not live

Event is currently live

Event is in the past

- Chris

[Amit_95]

Hi @Chris-M , 

Thanks for explaining the timestamp stuff, makes sense.

I've implemented an end datee field in the database table now and tried your approach, still seeing incorrect results. Here's my current approach:

Database table: 

Here is how my database date and time fields look, alongside their column names.

Code:

{% set table_id = 2663275 %}
{% set table_query = hubdb_table_rows(table_id) %}

{% for data in table_query %}
  {% if data['event_start_date'] >= unixtimestamp(local_dt) %}
     Event is in the future
  {% elif  data['event_start_date'] >= unixtimestamp(local_dt) and data['event_end_date'] <= unixtimestamp(local_dt) %}
     Event is live until it hits the End Date
  {% else %}
     Event is in the past
  {% endif %}
{% endfor %}

Results: 

Test 1:

• Start date in DB set to: 5/08/2020 @ 12:00pm
• End date in DB set to: 6/08/2020 @ 20:00pm
• Result should be: "Event is live until it hits the End Date"
• Actual result: "Event is in the past"

Test 2:

• Start date in DB set to: 1/08/2020 @ 12:00pm
• End date in DB set to: 2/08/2020 @ 20:00pm
• Result should be: "Event is in the past"
• Actual result: "Event is in the past"

Test 3:

• Start date in DB set to: 24/08/2020 @ 12:00pm
• End date in DB set to: 24/08/2020 @ 20:00pm
• Result should be: "Event is in the future"
• Actual result: "Event is in the future"

Test 4:

• Start date in DB set to: 6/08/2020 @ 08:00am
• End date in DB set to: 6/08/2020 @ 18:00pm:
• Result should be: "Event is live until it hits the End Date"
• Actual result: "Event is live until it hits the End Date"

Seems to me like the elif statement isn't working currently? We look to be nearly there though!

[Chris-M]

Hi @Amit_95,

I've seen a mistake on your code, you called the event_end_date -> end_end_date

Your code:

{% set table_id = 2663275 %}
{% set table_query = hubdb_table_rows(table_id) %}

{% for data in table_query %}
  {% if data['event_start_date'] >= unixtimestamp(local_dt) %}
     Event is in the future
  {% elif  data['event_start_date'] >= unixtimestamp(local_dt) and data['end_end_date'] <= unixtimestamp(local_dt) %}
     Event is live until it hits the End Date
  {% else %}
     Event is in the past
  {% endif %}
{% endfor %}

 i think this should be event_end_date.

- Chris

[Amit_95]

Hi Chris,

Yeah, I saw that as soon as I posted that reply. Thought I edited the post with the correct field name but guess it didn't update.

To clarify, I'm seeing the same results with event_end_date 

[Chris-M · Accepted solution]

Hey @Amit_95,

i made a HubDB and adjusted the code.  This should work now!

{% set table_query = hubdb_table_rows(table_id) %}

{% for data in table_query %}
  {% if  unixtimestamp(local_dt) > data['event_start_date']   and  unixtimestamp(local_dt) < data['event_end_date'] %}
     Event is live till End Date
  {% elif  data['event_start_date'] >= unixtimestamp(local_dt)  %}
     Event is in the future
  {% else %}
     Event is in the past
  {% endif %}
{% endfor %}

This gave me the results i wanted.

I hope this will work for you.

- Chris

[ACBlack]

no longer working.
this interogation does work: 
{% if local_dt|unixtimestamp|between_times(row.your_date_time_field, 'seconds') > 1 %}

in the future
{% endif %}

[Kevin-C]

Hey @Amit_95 

When comparing date/time data I always avoid mutating the data until its needed to print.

Maybe try something like this:

{% for row in table %}

  {% if local_dt|unixtimestamp|between_times(row.date_time, 'seconds') > 1 %}
   Show banner
  {% else %}
    Do not show banner
  {% endif %}

{% endfor %}