在数据库网格中显示MySql TIMEDIFF() [英] Displaying a MySql TIMEDIFF() in a DB grid
问题描述
当我使用命令行时,此查询给我令人愉悦的结果(它显示持续时间,这就是TIMEDIFF的含义):
When I use the command line, this query gives me pleasing results (it shows duation, which is what TIMEDIFF is about):
mysql> select timediff(end_time_stamp,start_time_stamp) from test_runs;
+-------------------------------------------+
| timediff(end_time_stamp,start_time_stamp) |
+-------------------------------------------+
| 00:00:07 |
| 00:00:11 |
| 00:01:23 |
+-------------------------------------------+
3 rows in set (0.00 sec)
当我将其放入Delphi的数据库网格中时,TIMEDIFF的格式类似于 12:00:07 AM
,这不是我想要的(看起来像一个时间,而不是一个持续时间)
When I put it in a DB grid in Delp the TIMEDIFFs are formatted like 12:00:07 AM
, which is not what I want (that looks like a time, not a duration).
我正在使用AnyDac,当我在设计时打开查询编辑器并执行它时,结果也是 12:00:07 AM
,所以看来AnyDac正在出于某种原因对其进行格式化
I am using AnyDac and when I open the query editor at design time and execute it, the result is also 12:00:07 AM
, so it seems that AnyDac is formatting it for some reason.
如何获得00:00:07这样的输出(持续时间而不是时间)?
How can I get output like 00:00:07 (a duration, not a time)?
- 我可以调整AnyDac查询吗?
- 我可以使用MySql语句显式格式化输出吗?
- 是否有一个OnXXX()函数,可以对其进行重新编码(以及如何编码)?
[更新]好吧,这段丑陋的代码符合我的要求,但是还有更优雅的方法吗?
[Update] Well, this ugly piece of code does what I want, but is there a more elegant way?
SELECT run_id,
start_time_stamp,
end_time_stamp,
CONCAT(CONCAT(CONCAT(CONCAT(LPAD(EXTRACT(HOUR FROM timediff(end_time_stamp,start_time_stamp)), 2, '0'), ":"),LPAD(EXTRACT(MINUTE FROM timediff(end_time_stamp,start_time_stamp)), 2, '0'), ":"), LPAD(EXTRACT(SECOND FROM timediff(end_time_stamp,start_time_stamp)), 2, '0'))) AS duration,
description
FROM test_runs ORDER BY start_time_stamp DESC
推荐答案
使用 TField 对象的"nofollow"> DisplayFormat
属性以获取所需的结果,例如:
Use the DisplayFormat
property of the TField
object to get the result you want, for example:
begin
MyQuery.Open;
MyQueryField.DisplayFormat := 'hh:nn:ss';
//or casting a TField to a descendant, like this:
(MyQuery.FieldByName('duration') as TDateTimeField).DisplayFormat := 'hh:nn:ss';
end;
编辑
我向TDateTimeField添加了强制类型转换.如果您的实际字段不是TDateTimeField的实例或后代,则将导致EInvalidTypeCast异常,因为您必须将其强制转换为它所属的正确类.
Edit
I added a cast to TDateTimeField. If your actual field is not an instance of TDateTimeField or a descendant it will result in a EInvalidTypeCast exception, since you have to cast it to the correct class it belongs to.
这篇关于在数据库网格中显示MySql TIMEDIFF()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!