DateTime Fractional Seconds
Posted: Thu 09 Jan 2014 13:37
I've been using dotConnect version 8.0.17 in combination with MySQL 5.6.10 for a while. However after updating my development machine some parts of my code stopped working. The updated machine has dotConnect 8.2.65 and MySQL 5.6.15.
Two things I have noticed. First when saving a DateTime from .NET in my database there is a difference in seconds sometimes. When saving 2014-01-09 23:59:59 it sometimes becomes 2014-01-10 00:00:00.
The second thing is when I save a record with a DateTime and use the exact same DateTime to select the record again, no records are returned.
After some research I learned that MySQL supports fractional seconds since version 5.6.4. In my database I use the type DATETIME without the fractional part. DateTimes get stored as 2014-01-09 23:59:59 and not something like 2014-01-09 23:59:59.0000. I don't need and don't want to use the fractional part.
It looks like dotConnect is sending the full .NET DateTime, with milliseconds, to the database and the database is rounding the milliseconds to seconds and trying to select the DateTime according the milliseconds in stead of just the seconds.
Has there been a change in dotConnect concerning the DateTimes? If so, can you provide me with a workaround? After downgrading in my development environment to version 8.0.17 everything seems to work as expected again.
Two things I have noticed. First when saving a DateTime from .NET in my database there is a difference in seconds sometimes. When saving 2014-01-09 23:59:59 it sometimes becomes 2014-01-10 00:00:00.
The second thing is when I save a record with a DateTime and use the exact same DateTime to select the record again, no records are returned.
After some research I learned that MySQL supports fractional seconds since version 5.6.4. In my database I use the type DATETIME without the fractional part. DateTimes get stored as 2014-01-09 23:59:59 and not something like 2014-01-09 23:59:59.0000. I don't need and don't want to use the fractional part.
It looks like dotConnect is sending the full .NET DateTime, with milliseconds, to the database and the database is rounding the milliseconds to seconds and trying to select the DateTime according the milliseconds in stead of just the seconds.
Has there been a change in dotConnect concerning the DateTimes? If so, can you provide me with a workaround? After downgrading in my development environment to version 8.0.17 everything seems to work as expected again.