DATETIME SQL type mappings

For some databases, the type mapping for DATETIME HOUR TO MINUTE has changed.

Summary of DATETIME storage changes

Starting with Genero 3.20, the native types used to store some of the DATETIME qual1 TO qual2 data types have changed.

Important: The native type mapping has not changed for the most common date/time types DATETIME YEAR TO SECOND, DATETIME YEAR TO FRACTION(N), DATETIME HOUR TO SECOND, DATETIME HOUR TO FRACTION(N). The DATETIME type concerned by this modification is DATETIME HOUR TO MINUTE, and only for the database brands listed in this topic.

DATETIME HOUR TO MINUTE data is now stored with the closest native time data type of the target database. Prior to version 3.20, such data was stored in a timestamp (YYYY-MM-DD hh:mm:ss[.ffff]), with the year/month/day parts set to 1900-01-01. This was not consistent with DATETIME HOUR TO SECOND storage, using time (hh:mm:ss[.ffff]) types.

Using the native time data type for time-only data simplifies interoperability with other applications and database components (for example, with data load/unload tools).

Note: Only the database brands listed in this page are concerned by this DATETIME storage change.

Database schema update

Existing databases on development and production sites will need data type modification for columns concerned by this change.

Depending on the type of database, an ALTER TABLE statement can be used. For example, with SQL Server, it is possible to convert a datetime2(0) column to time(0):
CREATE TABLE t1 ( pk INT, dt DATETIME2(0) )
INSERT INTO t1 VALUES ( 101, '1900-01-01 12:33:44' )
SELECT * FROM t1
ALTER TABLE t1 ALTER COLUMN dt TIME(0)
SELECT * FROM t1
DROP TABLE t1

For databases that do not support such date/time type modification with ALTER TABLE, create a new table with the new types, write some SQL or BDL code to fill the new table with the old table rows by doing a CAST(), drop the old table and rename the new table to the original table name.

Microsoft SQL Server

Table 1. Microsoft SQL Server date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
DATETIME HOUR TO HOUR DATETIME2(0) TIME(0)
DATETIME HOUR TO MINUTE DATETIME2(0) TIME(0)
DATETIME HOUR TO SECOND TIME(0) No change
DATETIME HOUR TO FRACTION(N) TIME(N) No change
DATETIME MINUTE TO MINUTE DATETIME2(0) TIME(0)
DATETIME MINUTE TO SECOND DATETIME2(0) TIME(0)
DATETIME MINUTE TO FRACTION(N) DATETIME2(n) TIME(n)
DATETIME SECOND TO SECOND DATETIME2(0) TIME(0)
DATETIME SECOND TO FRACTION(N) DATETIME2(n) TIME(n)
DATETIME FRACTION TO FRACTION(N) DATETIME2(n) TIME(n)

See also Date/time support with Microsoft SQL Server.

IBM DB2 LUW

Table 2. IBM DB2 LUW date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
DATETIME HOUR TO HOUR TIMESTAMP TIME
DATETIME HOUR TO MINUTE TIMESTAMP TIME
DATETIME HOUR TO SECOND TIME No change
DATETIME HOUR TO FRACTION(n) TIMESTAMP (1) No change
DATETIME MINUTE TO MINUTE TIMESTAMP TIME
DATETIME MINUTE TO SECOND TIMESTAMP TIME
DATETIME MINUTE TO FRACTION(n) TIMESTAMP (1) No change
DATETIME SECOND TO SECOND TIMESTAMP TIME
DATETIME SECOND TO FRACTION(n) TIMESTAMP (1) No change
DATETIME FRACTION TO FRACTION(n) TIMESTAMP (1) No change
Notes:
  1. Must use DB2 TIMESTAMP for fraction of seconds storage, DB2 TIME has no fractional part.

See also Date/time support with IBM DB2 LUW.

IBM Netezza

Table 3. IBM Netezza date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
DATETIME HOUR TO HOUR TIMESTAMP TIME
DATETIME HOUR TO MINUTE TIMESTAMP TIME
DATETIME HOUR TO SECOND TIME No change
DATETIME HOUR TO FRACTION(n) TIMESTAMP TIME (1)
DATETIME MINUTE TO MINUTE TIMESTAMP TIME
DATETIME MINUTE TO SECOND TIMESTAMP TIME
DATETIME MINUTE TO FRACTION(n) TIMESTAMP TIME (1)
DATETIME SECOND TO SECOND TIMESTAMP TIME
DATETIME SECOND TO FRACTION(n) TIMESTAMP TIME (1)
DATETIME FRACTION TO FRACTION(n) TIMESTAMP TIME (1)
Notes:
  1. Netezza can store fraction of seconds in TIME columns, but when fetching data, the NZ ODBC driver produces an SQL_TYPE_TIME, returning hh:mm:ss without the fraction part. This is a lack of NZ ODBC. See issue FGL-4807.

See also Date/time support with IBM Netezza.

Oracle MySQL and MariaDB

Table 4. Oracle MySQL and MariaDB date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
DATETIME HOUR TO HOUR TIME No change
DATETIME HOUR TO MINUTE TIME No change
DATETIME HOUR TO SECOND TIME No change
DATETIME HOUR TO FRACTION(n) TIME(n) No change
DATETIME MINUTE TO MINUTE DATETIME TIME
DATETIME MINUTE TO SECOND DATETIME TIME
DATETIME MINUTE TO FRACTION(n) DATETIME(n) TIME(n)
DATETIME SECOND TO SECOND DATETIME TIME
DATETIME SECOND TO FRACTION(n) DATETIME(n) TIME(n)
DATETIME FRACTION TO FRACTION(n) DATETIME(n) TIME(n)

See also Date/time support with Oracle MySQL and MariaDB.

PostgreSQL

Table 5. PostgreSQL date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
DATETIME HOUR TO HOUR TIMESTAMP(0) TIME(0)
DATETIME HOUR TO MINUTE TIME(0) No change
DATETIME HOUR TO SECOND TIME(0) No change
DATETIME HOUR TO FRACTION(N) TIME(N) No change
DATETIME MINUTE TO MINUTE TIMESTAMP(0) TIME(0)
DATETIME MINUTE TO SECOND TIMESTAMP(0) TIME(0)
DATETIME MINUTE TO FRACTION(N) TIMESTAMP(n) TIME(n)
DATETIME SECOND TO SECOND TIMESTAMP(0) TIME(0)
DATETIME SECOND TO FRACTION(N) TIMESTAMP(n) TIME(n)
DATETIME FRACTION TO FRACTION(N) TIMESTAMP(n) TIME(n)
Note: All PostgreSQL time data type mappings use TIME and TIMESTAMP without time zone.

See also Date/time support with PostgreSQL.

SAP HANA

Table 6. SAP HANA date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
Time class types
DATETIME HOUR TO HOUR TIMESTAMP TIME
DATETIME HOUR TO MINUTE TIME No change
DATETIME HOUR TO SECOND TIME No change
DATETIME HOUR TO FRACTION(n) TIMESTAMP (1) No change
DATETIME MINUTE TO MINUTE TIMESTAMP TIME
DATETIME MINUTE TO SECOND TIMESTAMP TIME
DATETIME MINUTE TO FRACTION(n) TIMESTAMP (1) No change
DATETIME SECOND TO SECOND TIMESTAMP TIME
DATETIME SECOND TO FRACTION(n) TIMESTAMP (1) No change
DATETIME FRACTION TO FRACTION(n) TIMESTAMP (1) No change
Date/time class types (2)
DATETIME YEAR TO YEAR TIMESTAMP SECONDDATE
DATETIME YEAR TO MONTH TIMESTAMP SECONDDATE
DATETIME YEAR TO DAY TIMESTAMP SECONDDATE
DATETIME YEAR TO HOUR TIMESTAMP SECONDDATE
DATETIME YEAR TO MINUTE SECONDDATE No change
DATETIME YEAR TO SECOND SECONDDATE No change
DATETIME YEAR TO FRACTION(n) TIMESTAMP No change
DATETIME MONTH TO MONTH TIMESTAMP SECONDDATE
DATETIME MONTH TO DAY TIMESTAMP SECONDDATE
DATETIME MONTH TO HOUR TIMESTAMP SECONDDATE
DATETIME MONTH TO MINUTE TIMESTAMP SECONDDATE
DATETIME MONTH TO SECOND TIMESTAMP SECONDDATE
DATETIME MONTH TO FRACTION(n) TIMESTAMP No change
DATETIME DAY TO DAY TIMESTAMP SECONDDATE
DATETIME DAY TO HOUR TIMESTAMP SECONDDATE
DATETIME DAY TO MINUTE TIMESTAMP SECONDDATE
DATETIME DAY TO SECOND TIMESTAMP SECONDDATE
DATETIME DAY TO FRACTION(n) TIMESTAMP No change
Notes:
  1. Must use SAP HANA TIMESTAMP for fraction of seconds storage, SAP HANA TIME has no fractional part.
  2. To be consistent with time class storage, all FGL DATETIME types that can be stored in SECONDDATE will be stored with that type. Otherwise, we use TIMESTAMP for the fraction of seconds.

See also Date/time support with SAP HANA.

SQLite

With SQLite, the mapping rules for FGL DATETIME use custom type names such as SMALLTIME and TINYDATETIME, to keep type information, for more details see DATE and DATETIME data types.

Table 7. SQLite date/time mapping changes
FGL Data Type FGL 3.10 FGL 3.20
Time class types
DATETIME HOUR TO HOUR TIMESTAMP SMALLTIME
DATETIME HOUR TO MINUTE SMALLTIME No change
DATETIME HOUR TO SECOND TIME No change
DATETIME HOUR TO FRACTION(n) TIME(n) No change
DATETIME MINUTE TO MINUTE TIMESTAMP SMALLTIME
DATETIME MINUTE TO SECOND TIMESTAMP TIME
DATETIME MINUTE TO FRACTION(n) TIMESTAMP(n) TIME(n)
DATETIME SECOND TO SECOND TIMESTAMP TIME
DATETIME SECOND TO FRACTION(n) TIMESTAMP(n) TIME(n)
DATETIME FRACTION TO FRACTION(n) TIMESTAMP(n) TIME(n)
Date/time class types
DATETIME YEAR TO YEAR TIMESTAMP TINYDATETIME
DATETIME YEAR TO MONTH TIMESTAMP TINYDATETIME
DATETIME YEAR TO DAY TINYDATETIME No change
DATETIME YEAR TO HOUR TIMESTAMP SMALLDATETIME
DATETIME YEAR TO MINUTE SMALLDATETIME No change
DATETIME YEAR TO SECOND DATETIME No change
DATETIME YEAR TO FRACTION(n) DATETIME(n) No change
DATETIME MONTH TO MONTH TIMESTAMP TINYDATETIME
DATETIME MONTH TO DAY TIMESTAMP TINYDATETIME
DATETIME MONTH TO HOUR TIMESTAMP SMALLDATETIME
DATETIME MONTH TO MINUTE TIMESTAMP SMALLDATETIME
DATETIME MONTH TO SECOND TIMESTAMP DATETIME
DATETIME MONTH TO FRACTION(n) TIMESTAMP DATETIME(n)
DATETIME DAY TO DAY TIMESTAMP TINYDATETIME
DATETIME DAY TO HOUR TIMESTAMP SMALLDATETIME
DATETIME DAY TO MINUTE TIMESTAMP SMALLDATETIME
DATETIME DAY TO SECOND TIMESTAMP DATETIME
DATETIME DAY TO FRACTION(n) TIMESTAMP DATETIME(n)
Notes:
  1. To be consistent with TIME class storage, types that can be stored in TINYDATETIME, SMALLDATETIME, DATETIME and DATETIME(n) will be stored with such type.

See also Date/time support with SQLite.