DATE and DATETIME data types

Informix®

Informix provides two data types to store date and time information:

  • DATE = for year, month and day storage.
  • DATETIME = for year to fraction (1-5) storage.

The DATE type is stored as an INTEGER with the number of days since 1899/12/31.

The DATETIME type can be defined with various time units, by specifying a start and end qualifier. For example, you can define a datetime to store an hour-to-second time value with DATETIME HOUR TO SECOND.

The values of Informix DATETIME can be represented with a character string literal, or as DATETIME() literals:
'2017-12-24 15:45:12.345'  -- a DATETIME YEAR TO FRACTION(3)
'15:45'   -- a DATETIME HOUR TO MINUTE
DATETIME(2017-12-24 12:45) YEAR TO MINUTE
DATETIME(12:45:56.333) HOUR TO FRACTION(3)
Informix is able to convert quoted strings to DATE / DATETIME data, if the string contains matching environment parameters. The string to date conversion rules for DATE is defined by the DBDATE environment variable. The string to datetime format for DATETIME is defined by the GL_DATETIME environment variable.
Note:

Within Genero programs, the string representation for DATETIME values is always ISO (YYYY-MM-DD hh:mm:ss.fffff)

Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is an INTEGER number of days when only DATE values are used, and an INTERVAL value if a DATETIME is used in the expression.

Informix automatically converts an INTEGER to a DATE when the integer is used to set a value of a date column.

Informix provides the CURRENT [ q1 TO q2 ] operator, to get the system date/time on the server where the current database is located. When no qualifiers are specified, CURRENT returns a DATETIME YEAR TO FRACTION(3). Informix also supports the SYSDATE operator, which returns the current system time as a DATETIME YEAR TO FRACTION(5).
Note:

The USEOSTIME configuration parameter must be set to 1 in order to get the subsecond precision in CURRENT and SYSDATE operators. See Informix documentation for more details.

SAP HANA®

SAP HANA provides following data type to store dates:

Table 1. SAP HANA date/time data types
SAP HANA data type Description
DATE for year, month, day storage
SECONDDATE for year, month, day, hour, minutes, seconds storage
TIME for hour, minutes, seconds storage
TIMESTAMP for year, month, day, hour, minutes, seconds, fraction(7) storage

As Informix, SAP HANA can convert quoted strings to date-time values.

By default, the date/time formats are:
  • 'YYYY-MM-DD' for DATE
  • 'HH:MM:SS' for TIME
  • 'YYYY-MM-DD HH:MM:SS' for SECONDDATE
  • 'YYYY-MM-DD HH:MM:SS.FF7' for TIMESTAMP
The date/time format can be changed at SQL session level with the SET command:
SET 'DATE_FORMAT' = 'DD/MM/YYYY';
SET 'TIME_FORMAT' = 'HH:MI:SS';
SET 'SECONDDATE_FORMAT' = 'DD/MM/YYYY HH:MI:SS';
SET 'TIMESTAMP_FORMAT' = 'DD/MM/YYYY HH:MI:SS.FF3';

Solution

Use the following conversion rules to map Informix date/time types to SAP HANA date/time types:

Table 2. Informix data types and SAP HANA equivalents
Informix data type SAP HANA data type
DATE DATE
DATETIME HOUR TO HOUR TIME
DATETIME HOUR TO MINUTE TIME
DATETIME HOUR TO SECOND TIME
DATETIME HOUR TO FRACTION(n) TIMESTAMP (for fraction part)
DATETIME MINUTE TO MINUTE TIME
DATETIME MINUTE TO SECOND TIME
DATETIME MINUTE TO FRACTION(n) TIMESTAMP (for fraction part)
DATETIME SECOND TO SECOND TIME
DATETIME SECOND TO FRACTION(n) TIMESTAMP (for fraction part)
DATETIME FRACTION TO FRACTION(n) TIMESTAMP (for fraction part)
DATETIME YEAR TO YEAR SECONDDATE
DATETIME YEAR TO MONTH SECONDDATE
DATETIME YEAR TO DAY SECONDDATE
DATETIME YEAR TO HOUR SECONDDATE
DATETIME YEAR TO MINUTE SECONDDATE
DATETIME YEAR TO SECOND SECONDDATE
DATETIME YEAR TO FRACTION(n) TIMESTAMP
DATETIME MONTH TO MONTH SECONDDATE
DATETIME MONTH TO DAY SECONDDATE
DATETIME MONTH TO HOUR SECONDDATE
DATETIME MONTH TO MINUTE SECONDDATE
DATETIME MONTH TO SECOND SECONDDATE
DATETIME MONTH TO FRACTION(n) TIMESTAMP
DATETIME DAY TO DAY SECONDDATE
DATETIME DAY TO HOUR SECONDDATE
DATETIME DAY TO MINUTE SECONDDATE
DATETIME DAY TO SECOND SECONDDATE
DATETIME DAY TO FRACTION(n) TIMESTAMP
The DATE and DATETIME types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.date = { true | false }
dbi.database.dsname.ifxemul.datatype.datetime = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.

SAP HANA and Informix DATE data type are equivalent and store year, month, day values.

SAP HANA TIME data type can be used to store Informix DATETIME HOUR TO SECOND, DATETIME HOUR TO MINUTE values, and any other DATETIME type with qualifiers HOUR, MINUTE, SECOND, except FRACTION(n). If the time-class DATETIME uses fraction of seconds such as DATETIME HOUR TO FRACTION(n), the native SAP HANA type must be TIMESTAMP, because SAP HANA TIME does not store fractions of seconds. Missing time parts default to 00:00:00. For example, when using a DATETIME MINUTE TO SECOND with the value of "45:23", the SAP HANA TIME value will be "00:45:23".

Informix DATETIME values with any qualifiers from YEAR to SECOND can be stored in SAP HANA SECONDDATE columns. DATETIME types with FRACTION(n) can be stored in SAP HANA TIMESTAMP columns. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME DAY TO MINUTE with the value of "23 11:45", the SAP HANA SECONDDATE value will be "1900-01-23 11:45:00".

CURRENT expressions

When using the CURRENT keyword without qualifiers, the SQL translator in ODI drivers will not convert this expression to a native equivalent, because it is difficult to find the required date/time precision. Depending on the context, the Informix CURRENT expression will be converted to match the target DATETIME or DATE type. In SQL statements, always use qualifiers after the CURRENT keyword, or use a DATETIME variable assigned with the current date/time set by program, and use this variable as SQL parameter.
-- Next CURRENT keyword will not be converted!
SELECT COUNT(*) INTO cnt FROM customer WHERE creadate < CURRENT
-- Best practice:
DEFINE dtcurr DATETIME YEAR TO FRACTION(3)
LET drcurr = CURRENT -- OK: it's the built-in language CURRENT expression!
SELECT COUNT(*) INTO cnt FROM customer WHERE creadate < dtcurr

Date/time SQL functions

Table 3. Informix and SAP HANA date/time SQL functions
Informix SAP HANA
today current_date
current hour to second current_time
current year to fraction(5) current_timestamp