Question Re: DEFAULT TIMESTAMP and system clock in Technology Q&A

DEFAULT TIMESTAMP and system clock

Former Member — Tue, 08 Oct 2013 19:34:01 GMT

From the documentation

Columns declared with DEFAULT TIMESTAMP contain unique values so that applications can detect near-simultaneous updates to the same row. If the present timestamp value is the same as the last value, it is incremented by the value of the default_timestamp_increment option.

This all sounds reasonable, but what happens if the system clock is changed (for example set back due to a time server synchronization). Can this cause a situation where either

DEFAULT TIMESTAMP gives a value less than its previous value or worse
DEFAULT TIMESTAMP returns a value that is non-unique

This seems possible given the last sentence suggests that it is only compared against the previous value, so if the DEFAULT TIMESTAMP value does go backwards due to a clock change, it could conceivably produce the same value twice.

Thanks

Re: DEFAULT TIMESTAMP and system clock

MCMartin — Wed, 09 Oct 2013 02:57:29 GMT

If you want something unique from the database system use an autoincrement field or a GUID field. Or you can force uniqness of the timestamp field by declaring a unique index on it (anyway this may reject otherwise valid inserts 😉 ). Doing so would at least identify a system clock problem.

Re: DEFAULT TIMESTAMP and system clock

VolkerBarth — Wed, 09 Oct 2013 07:20:20 GMT

Well, according to some tests with 12.0.1.3942, the database engine seems to make sure that DEFAULT TIMESTAMP values are increasing monotonically - as long as the database engine is running.

I.e. when using a local database engine and modifying the system clock, it seems that

setting the system clock forward (say, to tomorrow) makes DEFAULT TIMESTAMP use the new (later) value
setting the system clock backward (say, to yesterday) makes DEFAULT TIMESTAMP ignore the new (former) value and continue to use the current values - increased as usual by the value of the "default_timestamp_increment" option.

In contrast, SELECT GETDATE() will always return the system time: When the system clock is set back to yesterday, it will display that. Therefore DEFAULT TIMESTAMP may be ahead of the system time...

However, when the engine is shutdown and restarted after the system clock has been set to a former time, it will happily use that system time for new DEFAULT TIMESTAMP values.

Given that, I would think that DEFAULT TIMESTAMP does protect a running system against decreasing values but does not guarantee that when the engine (or only the database?) is shutdown in-between.

If you are primarily worried over influences of time synchronization, then I would think that this should not be a problem:

For a running database engine, the engine seems to protect accordingly.
For a server restart, I would suspect that stopping and re-starting will usually take much longer than the usual timespans that are added/subtracted due to synchronization.

Just a switch between standard time and daylight saving time might need particular care - if so, feel free to use DEFAULT UTC TIMESTAMP:)

Re: DEFAULT TIMESTAMP and system clock

VolkerBarth — Wed, 09 Oct 2013 07:28:02 GMT

Conclusion: Except for the (extremly rare) case that you would

stop the database engine,
set back the clock to a former time,
restart the engine and
insert/update a row exactly at a datetime that is equal (on a micro-second base!) to a datetime that has been used before,

I would think you can expect these values to be UNIQUE. (And note: Even GUIDs are not truly guaranteed to be unique).

In order to make sure these values are unique, I would second Martin's suggestion to use an explicit UNIQUE KEY or UNIQUE INDEX. And obviously, a constraint or trigger could be used to make sure that values must be increasing - though that might be an expensive check for huge tables.

Re: DEFAULT TIMESTAMP and system clock

Breck_Carter — Wed, 09 Oct 2013 07:38:37 GMT

Beware CURRENT TIMESTAMP! ( your computer can travel in time 🙂

Re: DEFAULT TIMESTAMP and system clock

VolkerBarth — Wed, 09 Oct 2013 07:43:37 GMT

Well, DEFAULT TIMESTAMP does seem to behave differently.

And now one might ask whether a table with both a DEFAULT CURRENT TIMESTAMP and a DEFAULT TIMESTAMP (for "dtAdded" and "dtLastChanged" values) will have totally different values for newly inserted rows when the system-clock has been set backward in-between...