Re: %TIMESTAMP renders only milliseconds? -- RPG400-L

Yay, Bruce! This is the sort of stuff that I love, because I am at heart a geek. I will almost certainly never NEED to know that the digits after the microseconds are only a uniqueness value, but that's really great to know. It automatically makes me think of how it got implemented. Is it a plus-one counter that gets reset by the clock every microsecond? Yeah, that would work...

On 12/7/2019 9:09 PM, Bruce Vining wrote:

I'm a little confused by the reference to 12 decimal places and
microseconds. Microseconds are 10-6 of a second so we're talking 6
decimal places.

The RPG reference manual when discussing %timestamp has "The first
parameter is the value to be converted. If you do not specify a value,
%TIMESTAMP returns the current system timestamp. Only the first three
digits of the fractional seconds portion of the timestamp will be set to a
non-zero value. The remaining fractional seconds will be zero" so 3 digits
is it (at least for now).

The SQL reference manual when discussing current timestamp has "When the
CURRENT TIMESTAMP special register or a variable with the TIMESTAMP data
type is used with a precision greater than 6, the timestamp value is a
combination of the system clock and uniqueness bits. The uniqueness bits
are assigned in an ascending order. Therefore, comparison operations for
timestamps with any precision will represent an accurate order of when the
timestamps were assigned" so 6 digits is it (at least for now).

The API reference manual when discussing QWCCVTDT has, when documenting the
20-byte receiver variable "Offset 14-19 Microseconds" giving us 6 digits
(and that's the largest receiver variable currently defined so that's it
(at least for now)).

For completeness I'll also point out that the machine itself, that is the
8-byte system time-of-day (TOD) clock, is a 64-bit value with bit 51 (base
0) being incremented each microsecond (which is decidedly release dependent
but if you're reasonably current you'll get 1 or 4 microsecond
granularity). The remaining bits are known as uniqueness bits with the
machine returning (if you ask) a unique ascending value within each clock
tick. But the meaningful bits essentially provide microsecond (6 decimal)
precision (at least for now). I was actually surprised when I looked at
the 7.4 Knowledge Center and it indicated 1 microsecond granularity as I
thought it was still at 4 microseconds...

In any case the higher level interfaces (RPG, SQL, APIs) can't really get
to anything more precise that what the machine (MI) supports. Today that's
6 decimal positions. I can say that historically the time-of-day clock had
significantly less precision, so this can certainly change over time --
though I have no idea if and when any such change might be made in the
future.

Bruce

On Sat, Dec 7, 2019 at 8:53 PM Booth Martin <booth@xxxxxxxxxxxx> wrote:

"If the precision of the timestamp exceeds 6, the value is truncated."
is what I find when I look up microseconds in the sql manual.

So yes, it can have 12 decimal places but the last 6 will be zeros?

Let me be clear; I understand that sql is a great offering, and that I
am, at best, a novice with sql. I pursue these discussions because I
have so very much to learn.

In other words, thank you Vern.

On 12/7/2019 6:31 PM, Vernon Hamberg wrote:

Booth, SQL's current timestamp is already completely out to
microseconds - CL's RTVJOBA with retrieveing datetime into 20 digits
goes out to microseconds

It has been available a long time to use
EXEC SQL SET :TMSTMPFLD = current timestamp;
to get microseconds in RPG.

In more recent versions, SQL can return even more granularity.

Regards
Vern