gpt4 book ai didi

java - 如何显示时间戳之间超过 24 小时的时差

转载 作者:可可西里 更新时间:2023-11-01 07:07:41 24 4
gpt4 key购买 nike

我有一个返回响应速度的查询。它在 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

enter image description here

最佳答案

有“一天中的时间”,也有“时间跨度”。

MySQL 的 TIMEDIFF() 返回 31 小时等的“时间跨度”。

您的 Java 代码需要一个“一天中的时间”,当然,它不应超过 23:59:59(忽略闰秒)。

一种解决方法是说

TO_SECONDS(databunker.five9_calls.start_timestamp) -
TO_SECONDS(databunker.purchases.time)

但这不会返回(例如)“31:58:47”;相反,它会给出“115127”。

另一种解决方法是避免 java.sal.Time 并找到一个处理“时间跨度”的库。

关于java - 如何显示时间戳之间超过 24 小时的时差,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50744961/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com