mysql - Grails - MySQL 查询结果错误 : Bad format for Time

Question

我有这个查询(在 mySQL cmdline 中运行良好)，但是当我在 grails 中使用 executeQuery 运行它时，我得到这个错误:Bad format for Time

这是我的 grails 查询:

def aveTimeToClose = OstFacTicket.executeQuery
("SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(t.closed, t.created)))) as cl " +
"from OstFacTicket t WHERE t.created >= :sdate AND t.created <= :edate AND " +
"t.closed IS NOT NULL", [sdate:start, edate: end])

堆栈跟踪错误是:

 Error 2012-05-17 00:09:44,356 [http-bio-8080-exec-9] 
ERROR util.JDBCExceptionReporter
- Bad format for Time '187:22:05' in column 1
| Error 2012-05-17 00:09:44,365 [http-bio-8080-exec-9] ERROR errors.GrailsExceptionResolver  

在 mySQL cmdline 上运行正常:

mysql> SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(t.closed, t.created)))) as cl
from ost_fac_ticket t where created > '2011-01-01 08:12:49' AND created < 
'2011-12-31 10:12:49';
+-----------+
| cl        |
+-----------+
| 187:22:05 |
+-----------+

谢谢，我感谢任何帮助。

Answer 1

我最终关注了 mu is too short的建议，只需以秒为单位获取查询结果，然后使用 groovy 将秒数转换为我想要的格式。

基本上 -

def aveTimeToClose = OstFacTicket.executeQuery("SELECT AVG(TIME_TO_SEC(TIMEDIFF(t.closed, t.created))) as cl "+
        "from OstFacTicket t WHERE t.created >= :sdate AND t.created <= :edate AND t.closed IS NOT NULL", [sdate:start, edate: end])

aveTimeToClose 的结果以秒为单位，可以很容易地转换成日期时间格式。