Here is a subject that I’d like to get some feedback since we are directly dealing with this today and this week. It appears that the default value of ‘0000-00-00’ is unacceptable with MySQL 5.5.49-win32…
One solution is changing the default date to an acceptable date of 1001-01-01 to avoid this bug.
The other solution is to keep using ‘0000-00-00’, then changing the data type TIMESTAMP instead of DATE.
Thoughts? Are there other solutions that we are overlooking?
The problem is that lots of places in OpenEMR the date fields are set to DEFAULT to 0000-00-00 and the logic looks for that. According to what I can find a NULL date field does return all zero formated. So storing as a NULL by default should be fine and not break the existing logic.
Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type (‘0000-00-00’ or ‘0000-00-00 00:00:00’).
The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as ‘2004-04-31’ are converted to ‘0000-00-00’ and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 6.1.7, “Server SQL Modes”, for more information.
NULL falls into the INVALID date category, so returns 0000-00-00
The special “zero” date ‘0000-00-00’ can be stored and retrieved as ‘0000-00-00’. When a ‘0000-00-00’ date is used through Connector/ODBC, it is automatically converted to NULL because ODBC cannot handle that kind of date.
As best I recall, null dates (including datetime) ARE valid and are not converted to anything else. There are various places in OpenEMR where dates are expected to be null if they are not known or not applicable… for example billing.bill_date is null if the item is not yet billed.
I think that should be the standard treatment, not depending on zero dates.
MD, I don’t think that’s an ‘aside’, I think that’s the whole point. We are forcing a default of ‘0000-00-00’ on many date fields in the schema now.
Like ODBC we have found other tools, Pentaho for example, that use JDBC and have the same issue 0000-00-00 in sql date checking logic fails in the queries created for reporting
Guys,
We have a developer we are paying to work on this issue. So we have an opportunity to fix it now. Looking for an agreement on how, so we can submit the changes to OpenEMR this week.
That is the idea. What I want is input form the esteemed members of this forum on the best way they think it should be done so that ALL dates are handled in the same manner.
Stored in UTC
Using the same default value to mean Empty
Using the Same Date picker for the UI
This is what I am paying to have done and I’l make a call on my own for the developer if necessary, but I would prefer a bit more consensus.
Switching to UTC could present some challenges. It will require ongoing knowledge of what time zone the clinic uses and whether it adjusts for DST. A lot of code will need changing. Conversion at upgrade time will need to be handled. Times for things that happened in a different time zone might be wrong. So consider if it’s worth the effort.
—> It will require ongoing knowledge of what time zone the clinic uses
and whether it adjusts for DST
Yes, I know that is part of the project (customer need), including user
level timezone configuration as well as site/instance level
Tony McCormick, CTO
Support: 866-735-0897, Direct: 713-574-6709
My Calendar: http://bit.ly/XznvDo
“Genuinely evolved interfaces are transparent, so transparent as to be
invisible” - William Gibson
Switching to UTC could present some challenges. It will require ongoing
knowledge of what time zone the clinic uses and whether it adjusts for DST.
A lot of code will need changing. Conversion at upgrade time will need to
be handled. Times for things that happened in a different time zone might
be wrong. So consider if it’s worth the effort.
–
Please be aware that e-mail communication can be intercepted in
transmission or misdirected. Please consider communicating any sensitive
information by telephone. The information contained in this message may be
privileged and confidential. If you are NOT the intended recipient, please
notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and
destroy this message.
Regarding the 0000-00-00 issue, there only appear to be several items (5 or 6) that need to be fixed per the above bug fix link.
Almost all the rest are set to:
NULL
And then there are a smattering that are set to:
NOT NULL
Seems reasonable to turn the offenders into NULL and see what happens.
The NOT NULL items are confusing(and wonder if strict mode may throw an error there also?). I am curious what that default actually becomes in the database.
After re-reading the linked bug report, some of the offenders can likely be dealt with by defaulting to CURRENT_TIMESTAMP.
The more difficult thing that will come with these changes is the support for it in the upgrade script (see the commit that brought in INNODB for strategies on attacking this issue).