PostgreSQL DATETIME type mapping change

Conversion of DATETIME type with fractional seconds to PostgreSQL TIME(N)/TIMESTAMP(N) was invalid and has been reviewed.

Before Genero 3.00, the PostgreSQL driver converted DATETIME types as follows:

Starting with Genero 3.00, when creating a table in a BDL program with CREATE TABLE, the types are converted in a different way.

The SQL Translator of the PostgreSQL driver makes the following conversions automatically for the DATETIME types:
  • DATETIME HOUR TO MINUTE is converted to PostgreSQL TIME(0) WITHOUT TIME ZONE (seconds set to 00).
  • DATETIME HOUR TO SECOND is converted to PostgreSQL TIME(0) WITHOUT TIME ZONE.
  • DATETIME HOUR TO FRACTION(N) is converted to PostgreSQL TIME(N) WITHOUT TIME ZONE.
  • DATETIME YEAR TO MINUTE is converted to PostgreSQL TIMSTAMP(0) WITHOUT TIME ZONE (seconds set to 00).
  • DATETIME YEAR TO SECOND is converted to PostgreSQL TIMESTAMP(0) WITHOUT TIME ZONE.
  • DATETIME YEAR TO FRACTION(N) is converted to PostgreSQL TIMESTAMP(N) WITHOUT TIME ZONE.
Note: This behavior change is related to the bug fix FGL-3893.

This bug fix introduces a incompatibility and can have an impact on applications using DATETIME HOUR TO MINUTE, DATETIME HOUR TO FRACTION(N) or DATETIME YEAR TO FRACTION(N). If you are using one of these types, consider reviewing your database schema, to modify the column types accord to the new SQL type conversion rules.