RowSet Date time StringValue millisecond precision

We should need to put secs*10000000 to get the 7 nanoseconds digits back, but they aren’t there.

on windows you always get limited nanoseconds mac and linux give more precision.

You mean collecting time, I mean storing and retrieving precisely. When the source is a DB, it sends you 7 nanoseconds digits. Do whatever you want, in any OS, but keep those digits intact.

I think that was the question here.

Only 7 digits? it should be 9 no?

MSSQL, as that of the OP, uses YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-]hh.mm

The ending hh:mm is TZ offset

Postgresql uses 6 for microseconds YYYY-MM-DD hh:mm:ss.nnnnnn

Thanks, so MSSQL does not support nanoseconds and use decimicroseconds (microseconds 6 digits, decimicroseconds 7 digits)

Well, they declare “nanoseconds” with granularity of 100 nanoseconds. It means storing 7 digits.

Their SQL functions includes options to retrieve parts of a DateTime value and one is “Read the nanoseconds” as:

SELECT DATENAME(NANOSECOND, GETDATE()) AS ‘NanoSecond’;

6 digits is the POSTGRESQL fractional limit, not MSSQL.

1 Like

I have thought of another method to get around precision issue. I query the database directly to retrieve the datetime formatted in ISO8601 format as a string, e.g.

CONVERT(VARCHAR, MyDateTime, 127)

I can then access the fractions of a second by splitting the string by the “.” and taking the second element, then convert to integer if needed etc.

As said above, for MSSQL you can get the nanosecs separately, to get it as an Integer use as

DATEPART(ns, MyDateTime) AS int_nanosecs