Adam wrote:

> Using a timestamp as an ordering mechanism is a bad idea, even when
> you are using UTC. It is not uncommon for server clocks to wander a
> fair bit, especially if both the power supply and any attached UPS is
> not excellent. It is unfortunately not uncommon for a server to lose
> or gain a few seconds a day. Pretty much every Windows server out
> there will synch with an NTP server at a calculated frequency (based
> on how far out it was at last synchronisation - usually around 7 - 10
> days for standalone or every hour for machines where the NTP server is
> on the local domain).

> The point is that, regardless of how little the clock wanders, if it
> does wander forwards, at some stage either an NTP service or a human
> will set it back. In the process, a record may come through with a
> earlier time than the record that chronologically followed it.

In most of my cases there is a single server so not a problem - but on
every machine I run a slave copy of Firebird locally as a backup and to
provide static data ( like station names ) and the clocks are synced
with the master server every minute if they are more than a second out.
We have had cases of local staff TRYING to change the time so they can
knock of early until they find out that even that is logged ;)

I can see a need for an ADDITIONAL sequence number in some situations,
but you still have to store the time ( such as when the train arrived
and left ;) ) and in most practical examples times can be managed so
that an additional field is not needed. In my cases the master record
number is a generator and the transaction records have a sequence number
in addition to master_id and the timestamp so you do not have to order
on times, but you need the index on time to such queries as 'where were
trains at Xo'clock' or 'which counter was x on at Xo'clock'

Older legacy systems could not even run through a change of daylight
saving time but now we need to run 24/7 :)

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php