Subject RE: [IBO] TIMESTAMP question
Author Helen Borrie
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

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...