Perhaps the timestamp_format option has been changed:
SET OPTION PUBLIC.timestamp_format = 'YYYY-MM-DD';
SELECT CURRENT TIMESTAMP;
current timestamp
'2010-02-20'
That option, plus date_format and time_format, do not affect what is stored, only what is returned as a string across the client-server boundary. Also, the date_order option affects input and output... but again, not what is stored.
Here's how to restore the default:
SET OPTION PUBLIC.timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS';
SELECT CURRENT TIMESTAMP;
current timestamp
'2010-02-20 08:01:29.953'
If your computer records seconds to more than 3 decimal places, SQL Anywhere will store up to six, and here's how to display all the digits:
SET OPTION PUBLIC.timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSSSSS';
SELECT CURRENT TIMESTAMP;
current timestamp
'2010-02-20 08:22:06.218000'
As you can see, my computer only records 3 decimal places. Here is a test that proves that SQL Anywhere records more; in order to guarantee that DEFAULT TIMESTAMP generates unique values, SQL Anywhere adds 0.000001 to subsequent values that are otherwise identical (the default_timestamp_increment option controls that):
CREATE TABLE t (
pkey TIMESTAMP NOT NULL DEFAULT TIMESTAMP PRIMARY KEY,
data INTEGER NOT NULL DEFAULT 0 );
INSERT t ( data ) SELECT row_num FROM RowGenerator ORDER BY row_num;
COMMIT;
SET OPTION PUBLIC.timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSSSSS';
SELECT pkey FROM t ORDER BY pkey;
pkey
'2010-02-20 08:22:19.984000'
'2010-02-20 08:22:19.984001'
'2010-02-20 08:22:19.984002'
'2010-02-20 08:22:19.984003'
'2010-02-20 08:22:19.984004'
'2010-02-20 08:22:19.984005'
...
answered
20 Feb '10, 13:30
Breck Carter
32.5k●539●724●1050
accept rate:
20%