I have a query that returns speed to response. It works correctly expect for instances when the timediff calculation results is over 24 hours. I get a error of "java.sql.SQLException: Illegal hour value '31' for java.sal.Time type in value"
The number of '31' varies with the variance of hours between the timestamps.
I'm not sure where to precede from here. I tried timestampdiff() but get an error while trying to run the query.
Any comments or suggestions would be much appreciated.
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 
1 Answers
Answers 1
There is "time of day" and there is "time span".
MySQL's TIMEDIFF() is returning a "time span" of 31 hours, etc.
Your Java code is expecting a "time of day" which, of course, should not be more than 23:59:59 (ignoring leap seconds).
One workaround is to say
TO_SECONDS(databunker.five9_calls.start_timestamp) - TO_SECONDS(databunker.purchases.time) but that won't return (for example) "31:58:47"; instead it will give "115127".
Another workaround is to avoid java.sal.Time and find a library that handles "time spans".
0 comments:
Post a Comment