Check if date from HubDB date and time field has passed today's (current) date
SOLVE
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?
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 %}
Check if date from HubDB date and time field has passed today's (current) date
SOLVE
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?
Check if date from HubDB date and time field has passed today's (current) date
SOLVE
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 %}
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!
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 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 %}
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 %}