在特定日期时间之后，如何在Python脚本中过滤SOQL查询？

Question

我正在尝试从OPPORTUNITY对象中选择大于变量DateTime的所有记录。然而，我不知道怎么做。这是一个使用simple_salesforce包的Python脚本。我认为问题在于我从BigQuery表中检索到的“max_date”参数中缺少毫秒和+0000时区规范，或者我没有将max_date参数作为正确的数据类型传入

我的示例代码不起作用：

max_date = '2020-08-11T17:41:29'

SF_QUERY = ("""
  SELECT Id,
  CreatedDate
  FROM Opportunity
  WHERE CreatedDate > %s
  """ % max_date)

其中CreatedDate字段的格式如下：

2019-10-31T16:01:19.000+0000

查询返回错误

 Response content: [{'message': "line 8:57 no viable alternative at character '<EOF>'", 'errorCode': 'MALFORMED_QUERY'}]

如果我在%s周围添加引号，使其成为“%s”，则会出现错误

Response content: [{'message': "\n  AND BU_Last_Stage_Changed_Date__c >\n      ^\nERROR at Row:8:Column:7\nvalue of filter criterion for field 'BU_Last_Stage_Changed_Date__c' must be of type dateTime and should not be enclosed in quotes", 'errorCode': 'INVALID_FIELD'}]

谢谢你的帮助

参考文件：

• https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

• https://salesforce.stackexchange.com/questions/301323/passing-a-variable-in-soql-select-statement-with-python-and-simple-salesforce

• https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#format_datetime

Answer 1

解决方案是在datetime字符串的末尾附加一个“Z”，表示Zulu时间，表示UTC。动态变量%s不应该有引号。例如：

max_date = '2020-08-10T17:41:29' # in prod, pull this data from BigQuery
max_date = max_date + "Z"

SF_QUERY = ("""
  SELECT Id,
  CreatedDate
  FROM Opportunity
  WHERE CreatedDate > %s
  """ % max_date)