Bug in TimeDiff interpretation using MyDAC

[John Smith22]

I have a problem that the TimeDiff function in MySQL returns erroneous results in MyDAC.

Consider the following example when run from the command console of MySQL:

Code: Select all

mysql> select timediff("2014-04-05 13:00:00", "2014-04-02 13:00:00");
+--------------------------------------------------------+
| timediff("2014-04-05 13:00:00", "2014-04-02 13:00:00") |
+--------------------------------------------------------+
| 72:00:00                                               |
+--------------------------------------------------------+
1 row in set (0.00 sec)

This result (72:00:00) is correct as the difference is 3 days (3*24h=72h).

When executed in MyDAC as this:

Code: Select all

procedure TForm5.Button1Click(Sender: TObject);
var Result : String ;
begin
  MyConnection1.Server := 'localhost' ;
  MyConnection1.Port := 3306 ;
  MyConnection1.Username := 'user' ;
  MyConnection1.Password := 'pw123' ;
  MyConnection1.Connect ;
  MyQuery1.Connection := MyConnection1 ;
  MyQuery1.SQL.Text := 'select timediff("2014-04-05 13:00:00", "2014-04-02 13:00:00");' ;
  MyQuery1.Execute ;
  Result := MyQuery1.Fields[0].AsString ;
end;

Result is returned as 00:00:00, which is incorrect.
Is this a bug or am I doing something wrong?

[AlexP]

Hello,

The TTime form in Delphi doesn't support values greater than 24 hours, therefore 0:00:00 is displayed. If you need to get the difference in hours, you can use the TIMESTAMPDIFF MySQL functions, for example:

Code: Select all

select TIMESTAMPDIFF(HOUR, "2014-04-05 13:00:00", "2014-04-02 13:00:00")

[John Smith22]

OK. I understand now.
Are there similar constraints I should be aware of when calling TIMESTAMPDIFF in Delphi using HOUR or MINUTE?

[AlexP]

Just like to hours, the same restrictions are imposed to minutes and seconds in Delphi - the value of minutes and seconds can't be greater than 59