Geek Answers Handbook

Mapping MySql TIMEDIFF () in a DB grid

When I use the command line, this query gives me nice results (it shows what is happening, what TIMEDIFF is):

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, TIMEDIFF is formatted as 12:00:07 AM , which is not what I want (it looks like time, not duration).

I use AnyDac, and when I open the query editor during development and execute it, the result is also 12:00:07 AM , so AnyDac seems to be formatting it for some reason.

How can I get a conclusion, for example, 00:00:07 (duration, not time)?

  • Can I customize an AnyDac request?
  • Is it possible to explicitly format the output using a MySql statement?
  • Is there an OnXXX () function that I can code for conversion (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
0
source share
1 answer

Use the DisplayFormat property of the TField object to get the desired result, 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 tag to TDateTimeField. If your actual field is not an instance of TDateTimeField or a descendant, this will throw an EInvalidTypeCast, as you must give it to the correct class to which it belongs.

+2
source

More articles:


All Articles