DEFAULT TIMESTAMP and system clock

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

timestamp default-timestamp

asked 08 Oct '13, 19:34

Scott Baldwin
156●7●9●14
accept rate: 0%

edited 09 Oct '13, 07:30

Volker Barth
39.7k●358●546●815

flat view

3 Answers:

active answers oldest answers newest answers popular answers

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:)

permanent link

answered 09 Oct '13, 07:20

Volker Barth
39.7k●358●546●815
accept rate: 34%

edited 09 Oct '13, 07:39

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.

(09 Oct '13, 07:28) Volker Barth

flat view

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.

permanent link

answered 09 Oct '13, 02:57

Martin
9.0k●127●166●257
accept rate: 14%

flat view

Beware CURRENT TIMESTAMP! ( your computer can travel in time :)

permanent link

answered 09 Oct '13, 07:38

Breck Carter
32.5k●540●724●1050
accept rate: 20%

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

(09 Oct '13, 07:43) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

timestamp ×22
default-timestamp ×7

question asked: 08 Oct '13, 19:34

question was seen: 3,543 times

last updated: 09 Oct '13, 07:43

SAP SQL Anywhere

DEFAULT TIMESTAMP and system clock

Follow this question

Related questions