Connecting to a locally hosted mysql database via the Actual Technologies ODBC driver for Mac OSX. The table contains NULL timestamps values, which mysql represents as : SQL> select * from action_type; +----------------+-----------------------------------------+---------------+--------------------+ | type | comments | action_type_id| chg_date | +----------------+-----------------------------------------+---------------+--------------------+ | A.94(3) EPC | Normal examiner office action | 1 | 0000-00-00 00:00:00| | R.71(3) EPC | Intention to grant communication | 2 | 0000-00-00 00:00:00| | R.51(6) EPC | Notice of grant communication | 3 | 0000-00-00 00:00:00| +----------------+-----------------------------------------+---------------+--------------------+ SQLRowCount returns 3 3 rows fetched However, the same table in LO master build : Version 4.1.0.0.alpha0+ (Build ID: 989863d849b1e703e78afc413088c3ae5109313) TinderBox: MacOSX-Intel@1-built_no-moz_on_10.6.8, Branch:master, Time: 2013-01-06_10:52:34 shows the following : 02/01/1 00:00 02/01/1 00:00 02/01/1 00:00 The format of the date column is supposed to be in the default locale of the machine, i.e. French format of DDMMYYYY hh:mm, yet this this is displayed incorrectly on screen as above. Alex
@Putting Lionel + Julien on CC
There are two points here : - the visual representation of the NULL timestamp value on screen, i.e. the fact that the year is only represented as a single digit ; - the apparent 1 day date miscalculation compared to the default reference date of LO which I was led to believe was/is 18/01/1901. Alex
Sample table definition : CREATE TABLE `action_type` ( `type` varchar(15) NOT NULL DEFAULT '', `comments` varchar(40) DEFAULT NULL, `action_type_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `chg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`action_type_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Alex
In MySQL, "0000-00-00 00:00:00" is not a NULL value, but a zero timestamp. NULL and "0000-00-00 00:00:00" are distinct values. "0000-00-00 00:00:00" is a MySQL-specific idiosyncrasy, an "anomalous" value which does *not* conform to (from memory) the SQL standard, nor ODBC: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712480(v=vs.85).aspx and entries "SQL_TYPE_DATE/TIME/TIMESTAMP" in http://msdn.microsoft.com/en-us/library/windows/desktop/ms710150(v=vs.85).aspx The MySQL ODBC Connector (the driver provided by MySQL) has an option "convert zero date/timestamp to NULL" so as not to leak these anomalous values to ODBC applications; if the column is "NOT NULL", then it may confuse applications in other ways :-| I haven't tested how LibreOffice reacts to this (NULL in a non-NULLable column). This being said, if we can change something in LibreOffice so that it reacts more gracefully to such anomalous values, then it would be good, so let's keep this bug open to track this future improvement. To use applications that are not specifically designed for MySQL (such as LibreOffice...), I'd recommend running MySQL with NO_ZERO_DATE and NO_ZERO_IN_DATE options activated.
(In reply to comment #4) > In MySQL, "0000-00-00 00:00:00" is not a NULL value, but a zero timestamp. > NULL and "0000-00-00 00:00:00" are distinct values. "0000-00-00 00:00:00" is > a MySQL-specific idiosyncrasy, an "anomalous" value which does *not* conform > to (from memory) the SQL standard, nor ODBC: > Yes, I've always had misgivings about the way MySQL considered invalid dates... > The MySQL ODBC Connector (the driver provided by MySQL) has an option > "convert zero date/timestamp to NULL" so as not to leak these anomalous > values to ODBC applications; if the column is "NOT NULL", then it may > confuse applications in other ways :-| I haven't tested how LibreOffice > reacts to this (NULL in a non-NULLable column). > I will have a look to see if the Actual Technologies has such a parameter somewhere. Alex
This now works for me in latest 4312 prod release