ASA Database Administration Guide

Database Options

Alphabetical list of options

TRUNCATE_TIMESTAMP_VALUES option [database]

Function  

Limits the resolution of timestamp values.

Allowed values  

ON, OFF

Scope  

Can be set for the PUBLIC group. This option should not be enabled for databases already containing timestamp data.

Default  

OFF

Description  

A TIMESTAMP value is precise to six decimal places in Adaptive Server Anywhere. However, to maintain compatibility with other software, which may truncate the TIMESTAMP value to three decimal places, you can set the TRUNCATE_TIMESTAMP_VALUES option to ON to limit the number of decimal places Adaptive Server Anywhere stores. The DEFAULT_TIMESTAMP_INCREMENT option determines the number of decimal places to which the TIMESTAMP value is truncated.

If the database server finds TIMESTAMP values with a higher resolution than that specified by the combination of TRUNCATE_TIMESTAMP_VALUES and DEFAULT_TIMESTAMP_INCREMENT, an error is reported.

In most cases, unloading the database and then reloading it into a new database in which the TRUNCATE_TIMESTAMP_VALUES and DEFAULT_TIMESTAMP_INCREMENT values have been set is the easiest solution to ensure the proper TIMESTAMP values are used. However, depending on the type of TIMESTAMP columns in your table, you can also do the following:

• If the TIMESTAMP columns are defined with DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP (so that the value is automatically updated by the server when the row is modified), you must empty the table before the TRUNCATE_TIMESTAMP_VALUES option is changed.

• If the TIMESTAMP column is defined with a value other than DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP, you can execute an UPDATE statement that casts the values to a string and then back to a TIMESTAMP. For example,

  UPDATE T
     SET ts = CAST( DATEFORMAT( ts, 'yyyy/mm/dd hh:nn:ss.ss')
   AS TIMESTAMP)

  Note that this process may lose more precision than is necessary. The format string to use depends on the number of digits of precision to be kept.

Example 

Setting the DEFAULT_TIMESTAMP_INCREMENT option to 100 000 causes truncation after the first decimal place in the seconds component, allowing a value such as '2000/12/05 10:50:53:700' to be stored.

SQL Anywhere Studio 9.0.1
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.