ASA SQL User's Guide
  Transact-SQL Compatibility
    Configuring databases for Transact-SQL compatibility

The special Transact-SQL timestamp column and data type

Adaptive Server Anywhere supports the Transact-SQL special timestamp column. The timestamp column, together with the tsequal system function, checks whether a row has been updated.

Creating a Transact-SQL timestamp column in Adaptive Server Anywhere 

To create a Transact-SQL timestamp column, create a column that has the (Adaptive Server Anywhere) data type TIMESTAMP and a default setting of timestamp. The column can have any name, although the name timestamp is common.

For example, the following CREATE TABLE statement includes a Transact-SQL timestamp column:

CREATE TABLE table_name (
   column_1 INTEGER ,
   column_2 TIMESTAMP DEFAULT TIMESTAMP
)

The following ALTER TABLE statement adds a Transact-SQL timestamp column to the sales_order table:

ALTER TABLE sales_order
ADD timestamp TIMESTAMP DEFAULT TIMESTAMP

In Adaptive Server Enterprise a column with the name timestamp and no data type specified automatically receives a TIMESTAMP data type. In Adaptive Server Anywhere you must explicitly assign the data type yourself.

If you have the AUTOMATIC_TIMESTAMP database option set to ON, you do not need to set the default value: any new column created with TIMESTAMP data type and with no explicit default receives a default value of timestamp. The following statement sets AUTOMATIC_TIMESTAMP to ON:

SET OPTION PUBLIC.AUTOMATIC_TIMESTAMP='ON'

The data type of a timestamp column 

Adaptive Server Enterprise treats a timestamp column as a domain that is VARBINARY(8), allowing NULL, while Adaptive Server Anywhere treats a timestamp column as the TIMESTAMP data type, which consists of the date and time, with fractions of a second held to six decimal places.

When fetching from the table for later updates, the variable into which the timestamp value is fetched should correspond to the column description.

Timestamping an existing table 

If you add a special timestamp column to an existing table, all existing rows have a NULL value in the timestamp column. To enter a timestamp value (the current timestamp) for existing rows, update all rows in the table such that the data does not change. For example, the following statement updates all rows in the sales_order table, without changing the values in any of the rows:

UPDATE sales_order
SET region = region

In Interactive SQL, you may need to set the TIMESTAMP_FORMAT option to see the differences in values for the rows. The following statement sets the TIMESTAMP_FORMAT option to display all six digits in the fractions of a second:

SET OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:ss.SSSSSS'

If all six digits are not shown, some timestamp column values may appear to be equal: they are not.

Using tsequal for updates 

With the tsequal system function you can tell whether a timestamp column has been updated or not.

For example, an application may SELECT a timestamp column into a variable. When an UPDATE of one of the selected rows is submitted, it can use the tsequal function to check whether the row has been modified. The tsequal function compares the timestamp value in the table with the timestamp value obtained in the SELECT. Identical timestamps means there are no changes. If the timestamps differ, the row has been changed since the SELECT was carried out.

A typical UPDATE statement using the tsequal function looks like this:

UPDATE publishers
SET city = 'Springfield'
WHERE pub_id = '0736'
AND TSEQUAL(timestamp, '1995/10/25 11:08:34.173226')

The first argument to the tsequal function is the name of the special timestamp column; the second argument is the timestamp retrieved in the SELECT statement. In Embedded SQL, the second argument is likely to be a host variable containing a TIMESTAMP value from a recent FETCH on the column.