/v3 Search API Does not handle date bounding

CDownard · ‎Feb 23, 2022

Howdy folks,

I am currently working on building an automated data pull for our sales team that will access their call logs in HubSpot. To do so, I have chosen to use the search API with the following URL:

https://api.hubapi.com/crm/v3/objects/calls/search?hapikey=xxx

In setting up for testing, I have found that filter groups regarding date fields do not work as intended. For example:

{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "hs_timestamp",
          "operator": "GTE",
          "value": "1643698800"
        },
        {
          "propertyName": "hs_timestamp",
          "operator": "LT",
          "value": "1646117999"
        }
      ]
    }
  ],
  "sorts": [
    {
      "propertyName": "hs_createdate",
      "direction": "DESCENDING"
    }
  ],
  "properties": [
    "createdate",
    "hs_call_body",
    "hubspot_owner_id",
    "hs_call_title",
    "hs_call_status",
    "hs_call_duration",
    "hs_call_from_number",
    "hs_call_recording_url"
  ],
  "limit": 100,
  "after": 0
}

This set of filters will in theory perform a date boundary between February 1, 2022 and Feb 28, 2022 (end of day). However, the result set returned is 0 result.

This set of filters:

{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "hs_timestamp",
          "operator": "GTE",
          "value": "1643698800"
        }
      ]
    }
  ],
  "sorts": [
    {
      "propertyName": "hs_createdate",
      "direction": "DESCENDING"
    }
  ],
  "properties": [
    "createdate",
    "hs_call_body",
    "hubspot_owner_id",
    "hs_call_title",
    "hs_call_status",
    "hs_call_duration",
    "hs_call_from_number",
    "hs_call_recording_url"
  ],
  "limit": 100,
  "after": 0
}

will return all calls in the system (for us about 600k). If I change the sort order, the first one that comes back is in August of 2017 despite being filtered for GTE Feb 1, 2022.

The set of filters:

{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "hs_timestamp",
          "operator": "LT",
          "value": "1646117999"
        }
      ]
    }
  ],
  "sorts": [
    {
      "propertyName": "hs_createdate",
      "direction": "DESCENDING"
    }
  ],
  "properties": [
    "createdate",
    "hs_call_body",
    "hubspot_owner_id",
    "hs_call_title",
    "hs_call_status",
    "hs_call_duration",
    "hs_call_from_number",
    "hs_call_recording_url"
  ],
  "limit": 100,
  "after": 0
}

has a different problem. Here we are filtering and asking for everything before Feb 28, 2022 (end of day) and instead receive a result set of 0.

I have found other threads on the forum indicating that these unix timestamp fields don't handle their operators properly, but none that I had seen were looking for date bounding. Ultimately, I am hoping to run a task daily to fetch the previous day's call logs, however the only solution I can see right now is to reverse sort and provide no filters, then parse through the result sets and continue paging until I reach a date before my targeted time frame.

Here is a link to the documentation I am using for the calls endpoint: https://developers.hubspot.com/docs/api/crm/calls

Here is a link to another thread that helped me identify the problem I'm experiencing: https://community.hubspot.com/t5/APIs-Integrations/Getting-error-while-searching-filtering-contact-o...

Unfortunately, that form post is back in October of 2020 and it appears the same problem persists today. Has anyone been able to figure out a way around this?

Thanks for your help!

-Chris