How to display TIMEDIFF(now, then) in a DB Grid?

Question

Sorry, I am very new to DbGrids.

Should I use the query's field editor and somehow add a new field that captures the TIMEDIFF and then just add that as a column in my DbGrid?

Or can/should I skip the field editor and somehow declare the TIMEDIFFF as a column?

For this table, I want a DbGrid with 4 columns : start time, end time, duration, description (run_id is the primary key & will not be displayed).

I am stumped as to how to get data into a 'duration' column ...

mysql> describe  test_runs;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| run_id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| start_time_stamp | timestamp   | YES  |     | NULL    |                |
| end_time_stamp   | timestamp   | YES  |     | NULL    |                |
| description      | varchar(64) | YES  |     | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.37 sec)

---

[Update] The query for the datasource is

SELECT start_time_stamp,
       end_time_stamp,
       TIMEDIFF(end_time_stamp, start_time_stamp) as duration,
       description

FROM test_runs ORDER BY start_time_stamp DESC

and when I execute it manually in MySql, I get

mysql> select TIMEDIFF(end_time_stamp, start_time_stamp) as duration FROM
+----------+
| duration |
+----------+
| NULL     |
| 00:04:43 |
| 00:00:13 |
| 00:00:06 |
| 00:00:04 |
+----------+
5 rows in set (0.00 sec)

but the corresponding column in the DB grid remains blank. Can anyone help? Thanks.

---

[Update] I am using AnyDac, if that helps. The query produces all fields, including the time difference, in MySql and also in the Delphi IDE when I use the AnYDac query editor and execute it.

The only problem is that I don't see it in the DB grid at run time. I double click the DB grid at design time and the columns are correct. The FielName property is set to duration, which is retuned by the query shown above. It doesn't exist in the database, but is calculated by the query; could that somehow be the problem??

---

[Aaaaaaaargh!!!] Someone tried to "improve" my code and set the query's text programatically at run-time (as SELECT * FROM test_runs), thus overwriting my design time query!! Since the databse table does not have a duration field, none was shown in the DB grid.

Words were had, voices were rasied and now I must apolgize for wasting your time. Sorry.

Answer 1

I would create a calculated field in your Query and add that field to your DbGrid.

so, as you say, with the Field Editor open for the query, right-click and select New Field (or press Ctrl-N). Give your new field a name (eg Duration), keep Component name default or rename if you desire. Set the Type as appropriate (DateTime most likely in this case) and set the Field Type to Calculated.

Then, in the OnCalcFields event of your Query, set the value of that field to the value you want. eg:

procedure TForm1.Query1CalcFields(DataSet: TDataSet);
begin
  Dataset.FieldByName('description').AsDateTime :=
     DataSet.FieldByName('end_time_stamp').AsDateTime - 
     DataSet.FieldByName('start_time_stamp').AsDateTime;
end;

Or, you could also include the Duration as an extra field in your select query. Unfortunately I don't have ready access to MySQL here, but it could be something like:

select run_id, start_time_stamp, end_time_stamp, description, 
  (end_time_stamp - start_time_stamp) as duration from test_runs;