Re: Variable formatting of datetime with DateStyle=ISO

Nissim <nissim(at)nksystems(dot)com> writes:
> I just posted a message to the interfaces list about how this is causing
> problems in th JDBC driver, and I'm wondering if there's a reason why
> the EncodeDateTime function creates a different format string depending
> on whether the date has milliseconds. Would it break anything if it
> always returned:

> yyyy-mm-dd hh:mm:ss.SSzzz

Yes: all the applications that never store fractional seconds, and are
not expecting to find fractions in their returned results. I think the
existing behavior is a reasonable compromise, and puts the burden of
extra complexity where it belongs: on the apps that are using
fractional-second timestamps.

> Also, why are there only two digits of precision on the milliseconds?
> shouldn't there be three?

The system doesn't actually store "milliseconds". Timestamp is a
floating-point format internally, and so the true resolution is variable
depending on how far away you are from time zero. Over a 100-year range
the available resolution would be more like microseconds.

Having said that, 2 fraction digits does seem like a pretty arbitrary
choice. Thomas Lockhart might know why it was done that way, but he's
gone for vacation and won't be back for a week or so...

regards, tom lane