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

  1. DEFAULT TIMESTAMP gives a value less than its previous value or worse
  2. 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

asked 08 Oct '13, 19:34

Scott%20Baldwin's gravatar image

Scott Baldwin
1567914
accept rate: 0%

edited 09 Oct '13, 07:30

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665


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%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 09 Oct '13, 07:39

1

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

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's gravatar image

Martin
8.6k116151237
accept rate: 14%

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

permanent link

answered 09 Oct '13, 07:38

Breck%20Carter's gravatar image

Breck Carter
26.2k430600866
accept rate: 20%

2

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
Your answer
toggle preview

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

Question tags:

×22
×6

question asked: 08 Oct '13, 19:34

question was seen: 1,154 times

last updated: 09 Oct '13, 07:43