Why am I only seeing the date portion of a timestamp value?

[Breck_Carter]

(the question says it all)

[VolkerBarth · Accepted solution]

Just for the record:

The real problem of the underlying forum question was the existence of a setting for option timestamp_format for an individual user. While the public setting was as wanted (and documented by Breck), the individual setting was not.

Note that each user can overwrite public settings with his own.

Solution: Unset the individual option with

`SET OPTION timestamp_format = ;

[Breck_Carter · Accepted solution]

Thanks, you get today's Huge Bounty!

[VolkerBarth · Accepted solution]

@Breck: Thank you so much - I had not noticed there was a bounty at all - the mighty and mysterious ways of the Administrator who seems to fear more than 1000 reputation points for himself:)

[Breck_Carter]

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'
...

[reimer_pods]

Cool, there's always something to learn here (or to get an update on a topic you thought you were familiar with).