Re: Variable formatting of datetime with DateStyle=ISO

Nissim <nissim@nksystems.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