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

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...

Yup. Arbitrary compromise between precision and readability...

- Thomas