PostgreSQL proxy: timestamp output adds trailing ".0" compared to native PostgreSQL

[duerwuyi]

Bug Report

Which version of ShardingSphere did you use?

shardingsphere-proxy 5.5.2
postgres 17.6 as backend

Expected behavior

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c8 TIMESTAMP);

INSERT INTO t1 VALUES ('1973-06-03 10:30:01'); -- no fractional seconds
SELECT c8 FROM t1;

should return (in native PG):

1973-06-03 10:30:01

Actual behavior

testdb=> CREATE TABLE t100 (c8 TIMESTAMP);
CREATE TABLE
testdb=> INSERT INTO t100 VALUES ('1973-06-03 10:30:01');
INSERT 0 1
testdb=> SELECT c8 FROM t100 ;
          c8           
-----------------------
 1973-06-03 10:30:01.0
(1 row)

The semantic value seems identical, but the textual representation differs and may break clients/tests relying on stable output formatting.

We guess the bug is related to Timestamp.toString() in java.sql.Timestamp.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

ss_postgres.zip

run docker compose up -d in this file.

SQL to execute are shown above

[terrymanu]

Problem Understanding

• With shardingsphere-proxy 5.5.2 speaking PostgreSQL protocol to a PostgreSQL 17.6 backend, a timestamp column without fractional seconds is returned with a trailing “.0”, unlike native PostgreSQL which omits trailing zero fractional seconds. This can affect clients/
  tests that rely on stable textual formatting.

Root Cause

• The frontend text protocol outputs via PostgreSQLDataRowPacket#writeTextValue (database/protocol/dialect/postgresql/packet/command/query/PostgreSQLDataRowPacket.java), which calls each.toString() for non-binary columns. java.sql.Timestamp#toString() always emits
  a fractional part, yielding “.0” when there are no fractional seconds. This differs from PostgreSQL’s documented text output rules: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT (trailing zero fractional seconds should be
  omitted).

Analysis

• Simple queries default to text protocol; the timestamp value does not go through binary encoding and falls into the generic toString branch in writeTextValue.
• Native PostgreSQL serializes timestamp text according to the official formatting rules; the proxy lacks a type-aware formatter and uses JDK Timestamp#toString(), causing the “.0” suffix.
• This is a proxy-side protocol formatting mismatch, unrelated to user configuration or usage.

Conclusion (with code-design-level fix suggestion)

• This is a formatting defect in the proxy. Proposed design direction:
  • In the PostgreSQL text protocol write path (PostgreSQLDataRowPacket#writeTextValue), use type-aware formatting for timestamp/timestamptz/date/time instead of generic toString().
  • Add or reuse a PostgreSQL-consistent formatter under the database.protocol.postgresql module (e.g., a dedicated text formatter akin to PostgreSQLTimestampUtils) that enforces “omit trailing 0 fractional seconds” and handles timezone/precision per the official
    spec.
  • Encapsulate type checks and formatting in a factory/strategy to avoid scattered instanceof; let writeTextValue choose a formatter based on column type and only fall back to toString for unhandled types.
  • Add protocol/integration tests: a timestamp without fractional seconds should be asserted to return without “.0”; also cover fractional seconds, timestamptz, and date/time to ensure no regressions.
• We warmly welcome community contributors to submit a PR implementing this formatting alignment and adding tests. Thank you for the report and support!

[manasagar]

Please specify the required behaviour for timestamp with timezone/time with timezone