Thank you Helen, I'm well aware of the fact that using the client's time is
never good practice. This project is currently only a proof of concept and
for now the software will always run on the same machine as the Firebird
server. I personally also didn't like the idea of using a floating point /
timestamp value as a primary key but I just don't see an easy way around
this. At first I used two separate integer fields (Delphi's TDateTime
integral and decimal parts split-up) but that causes difficulty in sorting,
so I ended up going the TIMESTAMP route. Now that you mentioned that the
timestamp on the server always returns zero milliseconds I might have to
start re-thinking the DB design.

Regards,
Don Schoeman


-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 10 December 2004 10:13 AM
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] TIMESTAMP question


At 09:37 AM 10/12/2004 +0200, you wrote:

>Thx for that info, that might explain the problem I had. The reason why I
>asked is that I record information from a hardware device and then store

the

>information into a "HISTORY" table. I use a TIMESTAMP field as part of the
>primary key. I noticed however that when I try and insert data too quickly,
>I get a primary key violation. I have resolved this issue for now by making
>sure that data is not inserted quicker than 15ms apart. Currently I'm not
>using 'NOW' when the inserting is performed but supply my own timestamp

like

>this: DSQL.ParamByName('P_TIMESTAMP').AsDateTime := Now; I'm now

wondering

>if it will make a difference if I use 'NOW' on a BEFORE INSERT trigger
>instead?

The timestamp that is read from server time always has zero
milliseconds. However, something you probably need to know is that
CURRENT_TIMESTAMP is read at the beginning of the statement, whereas cast
('now' as timestamp) returns the time when the change to the actual record
is posted. So - if the statement affects only one row, they are
equivalent; but if it affects multiple records, each record will have the
same CURRENT_TIMESTAMP or a distinct 'now'.

Rather than constrain (slow down) operations to avoid pk violations (or for
any reason, in fact!) I would strongly advising NOT using a timestamp type
in a key, ever (same goes for a float or double precision types) and I
would strongly recommend using GetExactTimeStamp() for data that are
time-sensitive at the subsecond or subminute levels.

Regarding DSQL.ParamByName('P_TIMESTAMP').AsDateTime := Now, do you realise
that this is not too reliable? Delphi's Now should return exact time,
including ms, but it is client time, not "database time". Software that
creates such inconsistency will get hammered in a quality audit...

Helen