Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

(the question says it all)

asked 20 Feb '10, 13:05

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 25 Feb '10, 08:52

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822


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 = ;
permanent link

answered 25 Feb '10, 08:50

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

Thanks, you get today's Huge Bounty!

(25 Feb '10, 09:51) Breck Carter

@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:)

(25 Feb '10, 12:57) Volker Barth

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

answered 20 Feb '10, 13:30

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 20 Feb '10, 13:49

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

(20 Feb '10, 15:27) Reimer Pods
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×15
×10

question asked: 20 Feb '10, 13:05

question was seen: 2,448 times

last updated: 25 Feb '10, 09:48