Description

Some datestyle and datetime related tests are failing (pgJDBC).
After investigation, the root cause is tied to the integer_datetimes flag not being present in ProxySQL startup parameters.

Details

• PostgreSQL historically allowed date/time values to be represented in two ways:
  • 64-bit integers (int64) (microsecond precision)
  • 64-bit floating-point doubles (less precise)
• In both cases, the payload is 8 bytes wide in binary format, but the interpretation depends on the integer_datetimes flag.
• Connectors need the value of this flag to correctly decide how to decode the 8-byte payload.
• In PostgreSQL versions before v10, the flag is not present, in which case the connector assumes value is in floating-point.
• Since PostgreSQL 10, integer_datetimes is always compiled as on (64-bit integer). The GUC still exists but is fixed to on and cannot be disabled.

Impact

• Connectors that ignore or misinterpret the integer_datetimes flag may decode timestamp values incorrectly.
• This leads to incorrect results when working with date/time fields.

Proposed Action

Add integer_datetimes flag in ProxySQL startup parameters status, hardcoded to on.