我有一个将速度返回给响应的查询。除了timediff计算结果超过24小时的情况外,它可以正常工作。我得到一个错误"java.sql.SQLException:值中java.sal.Time类型的非法小时值'31‘“
'31‘的数量随时间戳之间小时数的变化而变化。
我不知道下一步该从哪里开始。我尝试过timestampdiff(),但在尝试运行查询时遇到错误。
如有任何意见或建议,我们将不胜感激。
SELECT a.*
FROM
(SELECT
databunker.purchases.id AS 'pur_id',
databunker.purchases.business AS 'pur_business',
databunker.purchases.time AS 'pur_time',
databunker.customers.id AS 'customer_id',
databunker.customers.phone_number AS 'customer_phone_#',
databunker.customers.is_primary AS 'customer_is_primary',
map.object_salesforce_id,
databunker.five9_calls.campaign_name,
databunker.five9_calls.start_timestamp AS 'start_of_call',
TIMEDIFF(databunker.five9_calls.start_timestamp, databunker.purchases.time) AS 'speed_to_response'
FROM databunker.purchases
LEFT OUTER JOIN databunker.customers ON (databunker.purchases.customer_id = databunker.customers.id)
LEFT OUTER JOIN (SELECT
databunker.mappings.object_salesforce_id,
databunker.mappings.object_id AS 'map_customer_id'
FROM databunker.mappings
WHERE databunker.mappings.object_class = 'customer') AS map
ON (databunker.purchases.customer_id = map.map_customer_id)
LEFT OUTER JOIN databunker.five9_calls ON (map.object_salesforce_id =
databunker.five9_calls.salesforce_id)
WHERE databunker.purchases.business = 'uma'
AND databunker.purchases.outcome_type = 'accepted'
AND databunker.purchases.time >= curdate() - 1
AND databunker.five9_calls.start_timestamp >= databunker.purchases.time
GROUP BY
databunker.purchases.id,
databunker.purchases.business,
databunker.purchases.time,
databunker.customers.id,
databunker.customers.phone_number,
map.object_salesforce_id,
databunker.five9_calls.campaign_name,
databunker.five9_calls.start_timestamp,
TIMEDIFF(databunker.five9_calls.start_timestamp, databunker.purchases.time)
ORDER BY databunker.purchases.id, databunker.purchases.time,
databunker.five9_calls.start_timestamp ASC) a
INNER JOIN
(
SELECT
databunker.purchases.id AS 'pur_id_2',
MIN(databunker.five9_calls.start_timestamp) AS 'call_time'
FROM databunker.purchases
LEFT OUTER JOIN databunker.customers ON (databunker.purchases.customer_id = databunker.customers.id)
LEFT OUTER JOIN (SELECT
databunker.mappings.object_salesforce_id,
databunker.mappings.object_id AS 'map_customer_id'
FROM databunker.mappings
WHERE databunker.mappings.object_class = 'customer') AS map
ON (databunker.purchases.customer_id = map.map_customer_id)
LEFT OUTER JOIN databunker.five9_calls ON (map.object_salesforce_id = databunker.five9_calls.salesforce_id)
WHERE databunker.purchases.business = 'uma'
AND databunker.purchases.outcome_type = 'accepted'
AND databunker.purchases.time >= curdate() - 1
AND databunker.five9_calls.start_timestamp >= databunker.purchases.time
GROUP BY
databunker.purchases.id
) b
ON a.pur_id = b.pur_id_2 AND a.start_of_call = b.call_time
发布于 2018-06-12 09:13:57
有“一天中的时间”和“时间跨度”。
MySQL的TIMEDIFF()
返回的“时间跨度”为31小时,等等。
您的Java代码需要一个"time of day“,当然,这个值不应该超过23:59:59 (忽略闰秒)。
一种解决方法是
TO_SECONDS(databunker.five9_calls.start_timestamp) -
TO_SECONDS(databunker.purchases.time)
但它不会返回(例如) "31:58:47";相反,它将返回"115127“。
另一种解决方法是避免使用java.sal.Time
,而是找到一个可以处理“时间跨度”的库。
https://stackoverflow.com/questions/50744961
复制相似问题