Displaying a MySql TIMEDIFF() in a DB grid

Question

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)

When I put it in a DB grid in Delphi, the TIMEDIFFs are formatted like 12:00:07 AM, which is not what I want (that looks like a time, not a duration).

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.

How can I get output like 00:00:07 (a duration, not a time)?

Can I tweak the AnyDac query?
Can I explicitly format the output using the MySql statement?
Is thare an OnXXX() function which I can code to reformt (and how)?

[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

jachguate · Accepted Answer · 2013-03-07T08:07:20.033

2

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;

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.

edited Mar 07 '13 at 08:07

answered Mar 07 '13 at 03:11

jachguate

16,976
3
57
98

Sorry, it seems that I am too dumb to understand your answer. This is the closest I could get ` TestRunsOverviewADQuery.FieldByName('duration').DisplayFormat := 'hh:nn:ss';` and it doesn't compile. – Mawg says reinstate Monica Mar 07 '13 at 03:40
1

@Mawg doesn't compile is not a good description of what the compiler tells you. What's the error message? – jachguate Mar 07 '13 at 06:08
[DCC Error] fMainForm.pas(711): E2003 Undeclared identifier: 'DisplayFormat' – Mawg says reinstate Monica Mar 07 '13 at 07:10
1

@Mawg: that is because FieldByName returns a TField, and DisplayFormat introduced in one of its descendants. You'll have to cast it to the actual type of the field instance. – Marjan Venema Mar 07 '13 at 07:44
@MarjanVenema +1 Can you tell me how? – Mawg says reinstate Monica Mar 07 '13 at 07:52
1

@Mawg: hmm, a proper answer would require a look at more code than your question is showing. As you don't seem to be using persistent fields: check out the db unit. Find TField, find its descendants, figure out where DisplayFormat is introduced. Use a soft `as` cast `(FieldByName() as TSomeTypeOfField).DisplayFormat` and quard against an EInvalidCast exception. – Marjan Venema Mar 07 '13 at 08:04

Displaying a MySql TIMEDIFF() in a DB grid

1 Answers1

Edit

Linked