Few secords error on datetime with FireBird

[herman]

Hello,

I fill a TimeStamp field with a TDateTime value, but when I look with a
SQL explorer is seems it's allways a few seconds later then the loaded
value.

I use a TQuery TDataSetProvider TSQLDataSet

The code is like
dtNow := Now; (could be any date / time)
Edit;
FieldByName('aname').ASDateTime = dtNow;
Post;
ApplyeUpdates;

Does anyone know where my time difference comes from?

Thanks in advance
Herman

[Bill Todd [TeamB]]

The value is first converted from Delphi's TDateTime format to the
internal format used by Firebird. Then it is converted from Firebird's
internal format back to Delphi's TDateTime format for use by SQL
Explorer. My guess is that the difference occurs due to imprecision in
the convertion routines.

[Bill Todd [TeamB]]

By the way, you should SELECT the date time value using the isql
ustility that comes with Firebird and see how that value compares to
the value you assigned to the field and the value displayed by SQL
Explorer.

[herman]

Thanks Bill,

OK, that's clear. Do you have any sugestions or samples on how to handle
this?

Regards
Herman

Post by Bill Todd [TeamB]
By the way, you should SELECT the date time value using the isql
ustility that comes with Firebird and see how that value compares to
the value you assigned to the field and the value displayed by SQL
Explorer.

[Bill Todd [TeamB]]

Post by herman
OK, that's clear. Do you have any sugestions or samples on how to
handle this?

No. If the cause is really the conversion routines then it is what it
is. I have heard of this happening at the millisecond level but never a
difference of several seconds. That is one reason I was curious to see
what isql returned for the value.

[herman]

Dear Bill,

This evening I stepped through the code again and looked at a different
table where the same value is stored.
Here an update sql is used and the stored datetime is correct.
looking at the table with the problem using isql I got
2008-02-24 20:54:43.2030 while the stored value showed 20:54:40 using
FormatDateTime('dd-mm-yy hh:nn:ss', adatetime);

This leaves us with an interesting feature....
Regards
Herman

Post by Bill Todd [TeamB]

Post by herman
OK, that's clear. Do you have any sugestions or samples on how to
handle this?

No. If the cause is really the conversion routines then it is what it
is. I have heard of this happening at the millisecond level but never a
difference of several seconds. That is one reason I was curious to see
what isql returned for the value.

[Bill Todd [TeamB]]

Post by herman
This evening I stepped through the code again and looked at a
different table where the same value is stored. Here an update sql
is used and the stored datetime is correct. looking at the table
with the problem using isql I got 2008-02-24 20:54:43.2030 while the
stored value showed 20:54:40 using FormatDateTime('dd-mm-yy
hh:nn:ss', adatetime);

Interesting. In your original message you said:

"I use a TQuery TDataSetProvider TSQLDataSet".

That is not possible since TQuery is a BDE component. Are you really
using TSQLDataSet, TDataSetProvider and TClientDataSet? If that is the
case then any change you make to the DateTime field using the
TClientDataSet is applied to the database using an SQL UPDATE statement
also.

[herman]

I'm sorry, you're right, it's TSQLQuery.

In the mean time I converted the TDateTime variables to TSQLTimeStamp.
The difference became even bigger.

Maybe I should examine it a bit deeper.

Regards
Herman

Post by Bill Todd [TeamB]

Post by herman
This evening I stepped through the code again and looked at a
different table where the same value is stored. Here an update sql
is used and the stored datetime is correct. looking at the table
with the problem using isql I got 2008-02-24 20:54:43.2030 while the
stored value showed 20:54:40 using FormatDateTime('dd-mm-yy
hh:nn:ss', adatetime);

"I use a TQuery TDataSetProvider TSQLDataSet".
That is not possible since TQuery is a BDE component. Are you really
using TSQLDataSet, TDataSetProvider and TClientDataSet? If that is the
case then any change you make to the DateTime field using the
TClientDataSet is applied to the database using an SQL UPDATE statement
also.