Compare two dates using HubL

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.publish_date content.created and content.updated variables 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:

unixtimestamp(content.publish_date)

and the filter will look like:

content.publish_date|unixtimestamp

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.

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 start_date). 

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 (lte) operator.

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