Is Greenplum built with support for 64-bit-integer dates and times?

Products

• VMware Tanzu Greenplum

Issue/Introduction

Is Greenplum built with support for 64-bit-integer dates and times?

According to the following PostgreSQL documentation, https://www.postgresql.org/docs/8.3/runtime-config-preset.html, we can see that:

"When timestamp values are stored as double-precision floating-point numbers (currently the default), the effective limit of precision might be less than 6."

This article explains how to confirm whether or not the PostgreSQL version used by Greenplum 5 is compiled with the option "--enable-integer-datetimes" to override the default behavior of using float-point numbers.

Environment

Product Version: 5.27

Resolution

From Greenplum, we can see the following:

[gpadmin@gpdb-sandbox ~]$ psql
psql (8.3.23)
Type "help" for help.
 
gpadmin=# SHOW integer_datetimes;
 integer_datetimes
-------------------
 on
(1 row)
 
gpadmin=# set  integer_datetimes = off;
ERROR:  parameter "integer_datetimes" cannot be changed
gpadmin=#

According to this documentation, https://www.postgresql.org/docs/8.3/runtime-config-preset.html:

"The following "parameters" are read-only, and are determined when PostgreSQL is compiled or when it is installed. As such, they have been excluded from the sample postgresql.conf file. These options report various aspects of PostgreSQL behavior that might be of interest to certain applications, particularly administrative front-ends."

When integer_datetimes is 'on', this means the enable-integer-datetimes is set during the compile-time as well.

Note: PostgreSQL changed the default to integer datetimes in version 8.4 and removed support for float datetimes altogether in version 10.