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[(N)] = for hour, minute, second and fraction of second storage.
DATETIME[(N)] = for year, month, day, hour, minute, second and fraction of second storage.
TIMESTAMP = Lilke DATETIME, but can 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.

The SQL Translator of the MySQL driver makes the following conversions automatically for the DATETIME types:

DATETIME HOUR TO MINUTE is converted to MySQL TIME (seconds set to 00).
DATETIME HOUR TO SECOND is converted to MySQL TIME.
DATETIME HOUR TO FRACTION(N) is converted to MySQL TIME(N).
DATETIME YEAR TO MINUTE is converted to MySQL DATETIME (seconds set to 00).
DATETIME YEAR TO SECOND is converted to MySQL DATETIME.
DATETIME YEAR TO FRACTION(N) is converted to MySQL DATETIME(N).

Other DATETIME types will be mapped to MySQL DATETIME(N) columns. Missing date or time parts default to 1900-01-01 00:00:00.

Important: MySQL version older than 5.6.4 and MariaDB versions older than 5.3.0 do not support fractional part of DATETIME. If you try to store a DATETIME x TO FRACTION(P) with such old server version, the fractional part is lost.

See also Date and time in SQL statements for good SQL programming practices.