Search code examples
mysqldatetimetimestampinformixmilliseconds

Mysql equivalent of datetime year to fraction(3) in informix

I have a table and in that table there is a column with datatype as datetime year to fraction(3).

So when i am creating the same table and column in mysql will datetime(3) as the type give the same result.?

Also when i am inserting or updating the data in that column by the following statement

insert into table_name (column) values (sysdate(3))

will that be same as that of informix.?

Thanks in advance.

Solution

  • You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes.

    For example, DATETIME(3) will give you millisecond resolution in your timestamps, and TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp.

    Read this: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

    NOW(6) will give you the present time with microsecond precision, and NOW(3) will do the same with millisecond precision.

    If your server gives you a bunch of different six-digit integers back when you issue this query a bunch of times, you're at a working version.

     SELECT MICROSECOND(CAST(NOW(6) AS DATETIME(6)))

    If you get an error, you're not at the right version.