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.
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).
DATETIME
storage change.Database schema update
Existing databases on development and production sites will need data type modification for columns concerned by this change.
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
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) |
IBM DB2 LUW
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 |
- Must use DB2
TIMESTAMP
for fraction of seconds storage, DB2TIME
has no fractional part.
See also Date/time support with IBM DB2 LUW.
IBM Netezza
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) |
- Netezza can store fraction of seconds in
TIME
columns, but when fetching data, the NZ ODBC driver produces anSQL_TYPE_TIME
, returninghh: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
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) |
PostgreSQL
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) |
TIME
and
TIMESTAMP
without time zone.See also Date/time support with PostgreSQL.
SAP HANA
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 |
- Must use SAP HANA
TIMESTAMP
for fraction of seconds storage, SAP HANATIME
has no fractional part. - To be consistent with time class storage, all FGL
DATETIME
types that can be stored inSECONDDATE
will be stored with that type. Otherwise, we useTIMESTAMP
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.
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) |
- To be consistent with
TIME
class storage, types that can be stored inTINYDATETIME
,SMALLDATETIME
,DATETIME
andDATETIME(n)
will be stored with such type.
See also Date/time support with SQLite.