0

More of an amusement than a real problem, but is it possible to get higher precision than microsecond in current_timestamp. Syntactically:

values current_timestamp(12)

works fine, but the last 6 digits always seems to be 0

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32

2 Answers2

2

That is possible, generally. But the documentation on CURRENT TIMESTAMP points out the following:

If a timestamp with a specific precision is desired, the special register can be referenced as CURRENT TIMESTAMP(integer), where integer can range from 0 to 12. The default precision is 6. The precision of the clock reading varies by platform and the resulting value is padded with zeros where the precision of the retrieved clock reading is less than the precision of the request.

It might be your system or the OS layer not providing enough precision. And the obvious question is how much time is needed to execute the SQL statement returning that value...

BTW: If you have multiple CURRENT TIMESTAMP in a single complex statement, the time is always the same. But if you have multiple, e.g., timestamp (generate_unique(), 12), they have different values.

data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • As mentioned it is more of an amusement when it comes to default current_timestamp. I discovered that the normal precion of a timestamp is not sufficient in Java 17 since it deals with nanoseconds by default. I.e if you insert a timestamp created in Java and retrieve it, it is truncated when you get it back. TIMESTAMP(9) seems to work ok for that scenario. – Lennart - Slava Ukraini Jan 24 '23 at 07:54
  • BTW: If you have multiple CURRENT TIMESTAMP in a single complex statement, the time is always the same. – data_henrik Jan 24 '23 at 08:02
  • Regarding BTW, I've noticed and could not figure it out. Did not even help to "hide" it inside a non-deterministic function: `create or replace function non_determinism() returns timestamp(12) language sql not deterministic return current_timestamp(12)` . However `generate_unique()::timestamp(12)` seems to work fine, now I just have to remember that:-) – Lennart - Slava Ukraini Jan 24 '23 at 08:21
1

Some "feature" of Db2 probably.
Linux x86-64:

$ db2 "values current_timestamp(12), timestamp (generate_unique(), 12)"; date +"%F-%H.%M.%S.%N"

1
--------------------------------
2023-01-24-14.04.12.953475000000
2023-01-24-11.04.12.953526000000

  2 record(s) selected.

2023-01-24-14.04.12.968668868

The date utility shows nanos, but Db2 doesn't...

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16