Exploring DEFAULT TIMESTAMP

Values assigned by DEFAULT TIMESTAMP exhibit a special behavior...

...no, I'm not thinking about how it works for UPDATEs as well as INSERTs, but something else:

Values generated by DEFAULT TIMESTAMP are always increasing across all DEFAULT TIMESTAMP columns in the entire database, since that database was started.

It's the global quality of this "always increasing" behavior that came as a surprise to me. This existence of the @@DBTS global variable gives it away (more on @@DBTS later), but otherwise the Help isn't exactly in-your-face about this behavior.

For example...

• if DEFAULT TIMESTAMP generated '2008-11-29 12:06:03.984003' for column t2.def_ts,

• then the server clock was set back by one hour when it changes from Daylight Savings Time back to regular time,

• and then a value for a different column t1.def_ts was generated via DEFAULT TIMESTAMP,

• that value would be '2008-11-29 12:06:03.984004'. In this example, the second value is incremented by 1 microsecond, not decremented by one hour.

The DEFAULT_TIMESTAMP_INCREMENT database option controls how large the minimum increase in value is, from 1 microsecond (the default) to 1 second.

The @@DBTS global variable contains the last value generated by any DEFAULT TIMESTAMP clause since the database started; e.g., '2008-11-29 12:06:03.984011'. If the database is stopped and restarted, @@DBTS contains '0000-01-00 00:00:00.000000' until at least one DEFAULT TIMESTAMP value is generated.

Note that DEFAULT CURRENT TIMESTAMP does not behave this way, nor does the special variable CURRENT TIMESTAMP; they both return the current server clock time even if it is set backwards.

Why is this interesting?

Well, when writing the demonstration code for last week's posting about MobiLink losing data, MobiLink Fall Back, THERE WAS NO PROBLEM when DEFAULT TIMESTAMP was used to record when rows were inserted and updated on the consolidated database.

That's why I ended up using a trigger to assign CURRENT TIMESTAMP instead... because SQL Anywhere isn't the only DBMS that is used for MobiLink consolidated databases, and none of the other databases have a feature quite as cool as DEFAULT TIMESTAMP.

But... and this might be a big "But" for certain applicatons... if you really want to record the current server clock time in your database, no matter what time the clock says it is, then you need to use CURRENT TIMESTAMP instead of DEFAULT TIMESTAMP.