SOQL Difference Between two datetime columns

Question

I'm trying to find all records that have a difference of 5 years between two of their columns.

A mysql example of this would be

SELECT ...
FROM custom_object__c where mydatabase_ID__c > 1 and TIMESTAMPDIFF(YEAR, start_date__c, end_date__c) >= 5

start_date__c is the starting date and end_date__c is my ending date so if the end_date__c is more than 5 years from start_date__c I want the record returned.

I haven't seen anything close to this functionality using a SOQL query. This https://success.salesforce.com/answers?id=90630000000gvhiAAA seemed close but that is for a formuala, not SOQL.

I could do this with PHP but iterating over every row is going to be resource intensive and I'd prefer to avoid that if possible.

Answer 1

It is not possible out of the box. The standard workaround is to use a formula, then filter on that:

WHERE Year_Range_Length__c >= 5

Please note that this behavior is covered in the Force.com SOQL and SOSL Reference documentation on fieldExpresion Syntax (emphasis mine):

  fieldName comparisonOperator value

...

Syntax
value

Description
A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.