DATE and DATETIME data types

Informix^® provides two data types to store dates and time information:

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

MySQL provides the following data type to store dates:

DATE = for year, month, day storage.
TIME = for hour, minute, second storage.
DATETIME = for year, month, day, hour, minute, second storage.
TIMESTAMP = automatically updated when row is touched.

String representing date time information

Informix is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in Informix, MySQL can convert quoted strings to datetime data according the ISO datetime format ( YYYY-MM-DD hh:mm:ss' ).

Date arithmetic

Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used and an INTERVAL value if a DATETIME is used in the expression.
In MySQL, the result of an arithmetic expression involving DATE values is an INTEGER representing a number of days.
Informix automatically converts an integer to a date when the integer is used to set a value of a date column. MySQL does not support this automatic conversion.
Complex DATETIME expressions ( involving INTERVAL values for example) are Informix specific and have no equivalent in MySQL.

Solution

MySQL has the same DATE data type as Informix ( year, month, day ). So you can use MySQL DATE data type for Informix DATE columns.

MySQL TIME data type can be used to store Informix DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.

Informix DATETIME values with any precision from YEAR to SECOND can be stored in MySQL DATETIME columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the MySQL DATETIME value will be "1900-01-01 11:45:00".

SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax.

Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.