Open epa opened 11 months ago
Hmm, on further investigation it may not be quite so straightforward to fix as it appears sqsh does understand the datetime2
type (not just getting it as a string) and the fractional seconds come from the datesecfrac
field, which is populated by FreeTDS as microseconds. So although the type has 0.1 microsecond precision, FreeTDS is not passing that down.
Newer FreeTDS releases may have a decimicrosecond field; so let me see if I can build sqsh against that.
Haven’t looked at the code in a long time, but I think dsp_datetime_strip is responsible and is used in dsp_conv.c. I’d give it a try. Perhaps try patching line 455 and 461 there first.
I’m reluctant to change the default code for the precision of CS_BIGDATETIME_TYPE, but would consider an env var to specify it, similar to the way length and precision are configurable for floats. Would that meet your requirements?
I think it depends what else CS_BIGDATETIME_TYPE
is used for. If it's used for other datetimey types (perhaps on Sybase) and those perhaps have only millisecond precision, perhaps it shouldn't change just for the sake of MSSQL.
But if sqsh knows it is definitely fetching the Microsoft datetime2
which is defined to have 7 d.p. of precision, I think it should display that literal value. I can see you might sometimes want to round it to a more friendly-looking display, but truncating from 7 to 6 d.p. is a bit daft.
If there isn't a way for sqsh to know the real precision of the database type, or you're adamant that the default should stay at 6 decimal places, then I think setting the format string will be better than an environment variable. If %q
displays the fractional seconds with up to 6 d.p. then a new format %x
(or whatever name) should display the fractional seconds with all available precision.
It appears that current stable FreeTDS (1.4.10) does not pass through the higher precision to the CS_DATEREC
structure. It does this
daterec->datesecfrac = dr.decimicrosecond / 10u;
daterec->datesecprec = 1000000;
Note that datesecprec
gives the precision of the field but sqsh does not use this information.
I think if sqsh took account of datesecprec
then FreeTDS could in turn populate the full decimicrosecond value (and set datesecprec
to 10^7 instead of 10^6). That might also take care of the controversy about how many d.p. to display. It can display as many as needed for the stated precision.
Sounds good. I think that this would need changes to dsp_datetime_conv and dsp_datetime_len. IIRC this would still need you to set the datetime variable or the default server conversion will be used.
Drat, I thought I had a fix for this but it turns out the full precision is not reaching that FreeTDS code. When it divides by ten, that's because decimicrosecond
is always a multiple of ten. Although the datetime2
type has 7 d.p., somehow this is getting rounded to 6 d.p. before FreeTDS sees it.
I suspect this may not be possible. I'll see what the FreeTDS developers say in response to https://github.com/FreeTDS/freetds/issues/519
Recent versions of MSSQL add a
datetime2
type with greater precision than the olddatetime
. For older clients it is returned as a string, so sqsh can still run queries returning this type. But there's a subtle problem. The type supports fractional seconds with 7 decimal places of precision, but sqsh displays only 6 d.p. So for exampleBy explicitly converting to a wide enough
char
, you can see the full precision, but sqsh has discarded the last digit. (It appears to have truncated rather than rounded.)While an approximate result is often good enough, if you wanted to copy and paste into another query you need the full precision, since the truncated value usually won't be equal to the original. For example
Huh, how did I find the date and time but then there were no rows matching it? It's because the
datetime2
value was actually one digit longer but sqsh truncated.Since you show six d.p. currently, displaying one more wouldn't be unreasonable and it would make the output a lot more useful, by showing the exact value.