（读者可略过）

# 需求实现

1、统计司机补货总机器数

 1 SELECT
2     t.count_date,
3     t.phone_num,
4     count(1) AS 'totalCount'
5 FROM
6     (
7         SELECT
8             DATE_FORMAT(supplement_time, "%Y-%m-%d") AS count_date,
9             phone_num,
10             device_id
11         FROM
12             cardslot_supplement_record
13         WHERE 1 = 1
14             @if(!isEmpty(startDate)){
15                 AND supplement_time > #startDate#
16             @}
17             @if(!isEmpty(endDate)){
18                 AND supplement_time < CONCAT(#endDate#, " 23:59:59")
19             @}
20         GROUP BY
21             count_date,
22             phone_num,
23             device_id
24     ) t
25 GROUP BY
26     t.count_date,
27     t.phone_num
View Code

2、统计司机补货总次数

 1 SELECT
2     t.count_date,
3     t.phone_num,
4     count(1) 'totalCount'
5 FROM
6     (
7         SELECT
8             DATE_FORMAT(supplement_time, "%Y-%m-%d") AS count_date,
9             phone_num,
10             DATE_FORMAT(
11                 supplement_time,
12                 "%Y-%m-%d %H"
13             ) AS 'count_date_hour'
14         FROM
15             cardslot_supplement_record
16         WHERE 1 = 1
17             @if(!isEmpty(startDate)){
18                 AND supplement_time > #startDate#
19             @}
20             @if(!isEmpty(endDate)){
21                 AND supplement_time < CONCAT(#endDate#, " 23:59:59")
22             @}
23         GROUP BY
24             count_date,
25             phone_num,
26             count_date_hour
27     ) t
28 GROUP BY
29     t.count_date,
30     t.phone_num
View Code

3、统计司机补货总时长

 1 SELECT
2     DATE_FORMAT(supplement_time, "%Y-%m-%d") AS 'count_date',
3     phone_num,
4     SEC_TO_TIME(
5         UNIX_TIMESTAMP(max(supplement_end_time)) - UNIX_TIMESTAMP(min(supplement_start_time))
6     ) AS 'diffTime'
7 FROM
8     cardslot_supplement_record
9 WHERE 1 = 1
10     @if(!isEmpty(startDate)){
11         AND supplement_time > #startDate#
12     @}
13     @if(!isEmpty(endDate)){
14         AND supplement_time < CONCAT(#endDate#, " 23:59:59")
15     @}
16 GROUP BY
17     count_date,
18     phone_num

 1 // 司机手机号码
2 private String phoneNum;
3
4 // 统计日期
5 private String countDate;
6
7 // 统计数量
8 private Integer totalCount;
9
10 // 时间差
11 private String diffTime;

 1 Sql异常┏━━━━━ Debug [cardslotSupplementRecord.queryDriverReplenishmentD...] ━━━
2 ┣ SQL：      SELECT DATE_FORMAT(supplement_time, "%Y-%m-%d") AS 'count_date', phone_num, SEC_TO_TIME( UNIX_TIMESTAMP(max(supplement_end_time)) - UNIX_TIMESTAMP(min(supplement_start_time)) ) AS 'diffTime' FROM cardslot_supplement_record WHERE 1 = 1 AND supplement_time > ? AND supplement_time < CONCAT(?, " 23:59:59") GROUP BY count_date, phone_num
3 ┣ 参数：     [2018-08-01 00:00:00, 2018-09-06 23:59:59]
4 ┣ 位置：     com.bluepay.operation.service.impl.DriverReplenishmentSummaryExportServiceImpl.executeQueryAndExport(DriverReplenishmentSummaryExportServiceImpl.java:46)
5 ┗━━━━━ Debug [ ERROR:Bad format for Time '162:30:34' in column 3] ━━━
6
7  [ERROR] 2018-09-06 16:22:56,856 -2592- [pool-3-thread-1] [com.bluepay.operation.service.export.impl.ExportBlockingQueueServiceImpl] 下载任务出现异常
8  org.beetl.sql.core.BeetlSQLException: java.sql.SQLException: Bad format for Time '162:30:34' in column 3
9     at org.beetl.sql.core.SQLScript.select(SQLScript.java:403)
10     at org.beetl.sql.core.SQLManager.select(SQLManager.java:475)
11     at org.beetl.sql.core.SQLManager.select(SQLManager.java:461)
12     at org.beetl.sql.core.mapper.SelectMapperInvoke.call(SelectMapperInvoke.java:29)
13     at org.beetl.sql.core.mapper.MapperJavaProxy.invoke(MapperJavaProxy.java:162)
14     at org.beetl.sql.core.mapper.MapperJava8Proxy.invoke(MapperJava8Proxy.java:92)
15     at com.sun.proxy.$Proxy109.queryDriverReplenishmentDuration(Unknown Source) 16 at com.bluepay.operation.service.impl.DriverReplenishmentSummaryExportServiceImpl.executeQueryAndExport(DriverReplenishmentSummaryExportServiceImpl.java:46) 17 at com.bluepay.operation.service.export.impl.ExportBlockingQueueServiceImpl$1$1.run(ExportBlockingQueueServiceImpl.java:75) 18 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
23 Caused by: java.sql.SQLException: Bad format for Time '162:30:34' in column 3
24     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
25     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
26     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
27     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
28     at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:5511)
29     at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5284)
30     at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5138)
31     at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:951)
32     at com.alibaba.druid.wall.WallFilter.resultSet_getString(WallFilter.java:1014)
33     at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:947)
34     at com.alibaba.druid.filter.stat.StatFilter.resultSet_getString(StatFilter.java:938)
35     at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:947)
36     at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getString(ResultSetProxyImpl.java:685)
37     at com.alibaba.druid.pool.DruidPooledResultSet.getString(DruidPooledResultSet.java:111)
38     at org.beetl.sql.core.mapping.type.StringTypeHandler.getValue(StringTypeHandler.java:46)
39     at org.beetl.sql.core.mapping.BeanProcessor.createBean(BeanProcessor.java:320)
40     at org.beetl.sql.core.mapping.BeanProcessor.toBeanList(BeanProcessor.java:177)
41     at org.beetl.sql.core.SQLScript.mappingSelect(SQLScript.java:438)
42     at org.beetl.sql.core.SQLScript.select(SQLScript.java:385)
43     ... 13 common frames omitted

# 问题解答

 1 SELECT
2     DATE_FORMAT(supplement_time, "%Y-%m-%d") AS 'count_date',
3     phone_num,
4     CONCAT(SEC_TO_TIME(
5         UNIX_TIMESTAMP(max(supplement_end_time)) - UNIX_TIMESTAMP(min(supplement_start_time))
6     ), '') AS 'diffTime'
7 FROM
8     cardslot_supplement_record
9 WHERE 1 = 1
10     @if(!isEmpty(startDate)){
11         AND supplement_time > #startDate#
12     @}
13     @if(!isEmpty(endDate)){
14         AND supplement_time < CONCAT(#endDate#, " 23:59:59")
15     @}
16 GROUP BY
17     count_date,
18     phone_num

# 问题总结

MySQL中诸如TIMEDIFF（时间差计算那一类函数）一类函数计算出的结果可能都是“hh:MM:ss”格式的值，如果想在Java程序中以String类型来接受处理，那么一定要事先确定这个以“时间格式”来表示的值，是否超过了时间表达的最大范围值，如果不能确定的，都最好在输出到程序中执行之前，对其进行字符串化再输出，以免在后续程序执行时抛出异常。

Captain&D所发布的博文均为原创，概不任意转载，如有参考必定给出原文链接。

posted @ 2019-05-13 12:23  Captain&D  阅读(1111)  评论(0编辑  收藏  举报