All Your GUCs in a Row: integer_datetimes

PostgreSQL has announced the integer_datetimes parameter to every connecting client since version 8.0, even though it has had only one possible value since PostgreSQL 10. The post explains the historical context: datetime values were originally stored as double-precision floats (counting seconds from 2000-01-01), which caused precision loss and subtle bugs. The integer format (64-bit microseconds from the same epoch) was introduced as a compile-time option in the 7.x era, became the default in 8.4, and became the only option in PostgreSQL 10 when Tom Lane removed the float build option after finding it was unused and buggy. The parameter persists in the connection startup protocol because binary-mode drivers must know the wire format to decode timestamps correctly — guessing wrong yields silently corrupt timestamps with no error. A historical example from 2010 shows an application writing float-format binary timestamps to an 8.4 server, producing dates around year 15000. The only remaining practical concern is pg_upgrade, which refuses to migrate a float-built cluster; dump and restore is the only option for any surviving pre-8.4 or pre-10 float-built clusters.

Source: https://postgr.es/p/9rT. 8 Sync News only summarizes and links out; content copyright belongs to the authors and original sources.