There may be times in which you want to calculate the difference between two date fields in HubSpot, such as:
- Checking if one date happens before or after another
- Displaying past vs upcoming events (using HubDB, or a custom field on blog posts)
- Calculating the duration of an event
Ensure dates are in unix formatting
Before comparing the two dates, you'll want to make sure that both are in unix format, which may already be the case depending on where you're pulling the date from. Below are some examples of dates you might be using and their default formats:
- Page or Blog created/published/updated dates. The
content.updatedvariables are in UTC time and will need to be converted to unix timestamps.
- HubDB date/date and time columns. When calling a column of either of these types, the date is already output as a unix timestamp.
- Module date/date and time fields. When using a field of these types from a custom module, the date is output in UTC time and will need to be converted to a unix timestamp.
To convert a date to its unix timestamp in HubL, you can either use the
unixtimestamp function or the
unixtimestamp filter. To convert a blog post's publish date to unix formatting, the function will look like:
and the filter will look like:
Use a calculation to compare the two dates
Once you've ensured that your dates are in unix format, you can calculate the difference between them.
For example, in a module with two date fields "Start Date" (variable name
start_date) and "End Date" (variable name
end_date), your calculation might look like this:
You can use then use the "difference" variable as needed.
Examples and Use Cases
Add a warning for outdated content
In my blog, you'll notice a warning at the top of posts that haven't been updated for over a year. This is done by calculating the difference between the current date using the
local_dt variable and the post's last updated date, like this:
The number 31556926000 is the amount of miliseconds there are in a year.
plus_timefilters to add and subtract an amount of time to a datetime object.
Distinguish between upcoming and past events from a HubDB table
You may have a HubDB table for your events and want to show upcoming events in one section and past events in another.
In the example above, we have a HubDB table that has a "Start Date" date type column (variable name
To pull upcoming events, we create a variable
upcoming_events_queryparam where we get the current datetime using the
local_dt variable (and format it using the
unixtimestamp function). Then we use the greater than (
gt) operator to compare the two. We get the HubDB rows for upcoming events using the
hubdb_table_rows function and our query.
Past events are pulled in the same way but using the less than or equal to (
Calculate the duration of an event
Another reason we may want to find the difference between two dates is to calculate the duration of an event.
To do this, we'll take our simple calculation from above where we get the difference between the Start Date and the End Date fields in a module. The "duration" output here will be a unix timestamp with the number of miliseconds.
To convert that timestamp to hours, for example, we could use a calculation (there are 3,600,000 miliseconds in an hour):
← Previous ArticleTimezones on HubSpot's "Date" and "Date and Time" module fields
Next Article →Customize the embedded video in a HubSpot Video module