One of the problems that we face on a hosted system is where different databases(site) are located in different time zones: eg: Dr, Bowen from NC and Dr. Miller from Seattle. They both use the same server using multi-site support. In this scenario if Dr. Bowen wants to retrieve the logs he created for a certain time period he may end either having to convert the time to his time zone. This also creates misrepresentation of data when you pull reports using the timestamped fields.
Presently php and mysql is taking the time_zone configurations from their respective ini files. We propose the following solution.
Bring a variable in the Globals to set the site time Zone.
Use php's date_default_timezone_set('Select Time Zone from globals') at the beginning of globals.php, which will set all php date calculation to be based on this time zone. Execute the query "set time_zone='Select Time Zone from globals'
Just before "$glrow = sqlQuery("SHOW TABLES LIKE 'globals'")" around line 225 in globals which will set the mysql connection time zone
Thoughts?
Eldho
www.zhservices.com
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
2. When passing the timezone to mysql, do not use the name; instead use the offset collected from php (since it seems like mysql does not have text timezone info installed by default). Pertinent link is: http://php.net/manual/en/datetimezone.getoffset.php
3. A good place to execute the mysql time zone query is in library/sql.inc after this block of code:
// Modified 5/2009 by BM for UTF-8 project ---------if(!$disable_utf8_flag){$success_flag=$database->Execute("SET NAMES 'utf8'");if(!$success_flag){error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".$database->ErrorMsg(),0);}}
There is one more modification required to finish this process. As per the mysql documentation when the data
is stored in a timestamp field it is converted to GMT and when retrieved display it according to the session
timezone setting. Only the timestamp field type support this behaviour.
If somebody is changing the timezone for an existing database this may lead to a problem for datetime field type
because it will not adjust to the timezone change.
Altering all the datetime fields to timestamp field is a possible solution for this problem.
Is this is something acceptable to the community?
Eldho
ZH Healthcare
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Regarding the timestamp issue, one thing to consider is that it does not support pre 1970 or post 2038 per this quote:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Would be nice, though, then would support a facility across regions. We can likely assume that mysql will "fix" this around 2030 or so(if OpenEMR is even still around at that time :) ). So, the only practical concern would be things in the past, like dob (which is only a date anyways). I think converting to TIMESTAMP may make sense; is it possible for mysql to do this(convert a DATETIME field to a TIMESTAMP field) and is there any mysql queries that will break when do this?
-brady OpenEMR
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The following query will do the necessary change:
ALTER TABLE `tablename` MODIFY `Fieldname` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.
This will convert the existing date time field to the timestamp (basically the equivalent GMT time ) as per the sever settings at that time.
It also don't have any effect on the date field and I dont think we have to change the date to timestamp type.
We may also create a method to search the existence of "datetime" field, when a change to timezone is being made and alter all those fields.
As all the new operating systems are programs are converting to 64 bit, I think the year 2038 problem will be fixed by mysql in the near future.
We only have to take care any fields which may have to store future values beyond year 2038, but in that case I don't think it will require "datetime" field as for a future date the time part will be irrelevant.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
Might as well change them all and see what happens (should keep the current datetime defaults though, for example, some default to blank or null I think). Trying to keep altering the datetime fields when change timezones seems very dirty; might as well try to convert them all in one swoop by converting everything to timestamp and be done with it. I can't think of any datetime fields that will be storing pre 1970 or post 2038; can you?
-brady OpenEMR
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The core OEMR tables can be altered through our upgrade scripts and we will do those changes to the sql upgrade files. But for the datetime fields if any used by different forms cannot be determined by us. Users may be using different forms from contrib folder or developed by themselves. The details about this can be obtained through querying information_schema at run time and we may do necessary alteration. But if you think this is not necessary we are fine with that.
Meantime have you reviewed the code and have any suggestion?
Regards
Eldho
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just placed a review on github. Would just focus on the OpenEMR (http://www.open-emr.org/wiki/index.php/FAQ#Are_OpenEMR_and_OEMR_the_same_thing.3F :) ) core code now. The problem with blindly changing things (unknown forms) is that we may do something bad like convert a forms DOB datetime to a timestamp (thus making everbody less than 43 years old). We can likely include the more popular forms used within the contrib directory if wish and will also need to incorporate it in the xmlformgen script (which should be straightforward).
One of the problems that we face on a hosted system is where different databases(site) are located in different time zones: eg: Dr, Bowen from NC and Dr. Miller from Seattle. They both use the same server using multi-site support. In this scenario if Dr. Bowen wants to retrieve the logs he created for a certain time period he may end either having to convert the time to his time zone. This also creates misrepresentation of data when you pull reports using the timestamped fields.
Presently php and mysql is taking the time_zone configurations from their respective ini files. We propose the following solution.
Bring a variable in the Globals to set the site time Zone.
Use php's date_default_timezone_set('Select Time Zone from globals') at the beginning of globals.php, which will set all php date calculation to be based on this time zone. Execute the query "set time_zone='Select Time Zone from globals'
Just before "$glrow = sqlQuery("SHOW TABLES LIKE 'globals'")" around line 225 in globals which will set the mysql connection time zone
Thoughts?
Eldho
www.zhservices.com
Hi,
If could do this well, would also avoid users needing to set the timezone manually in php.ini file for php versions 5.4 and greater.
Thoughts are:
1. In the globals screen, collect the possible timezones from php for user to select from (ie. do not hard-code the options into the codebase or database). Some pertinent links:
http://php.net/manual/en/class.datetimezone.php
http://php.net/manual/en/datetimezone.listidentifiers.php
2. When passing the timezone to mysql, do not use the name; instead use the offset collected from php (since it seems like mysql does not have text timezone info installed by default). Pertinent link is:
http://php.net/manual/en/datetimezone.getoffset.php
3. A good place to execute the mysql time zone query is in library/sql.inc after this block of code:
-brady
OpenEMR
Hi,
We have made the necessary changes as per the discussion and is available in the following link.
https://github.com/zhhealthcare/openemr/commit/a0de0d03cc1ef7c87711e87c4daef794eb838680
There is one more modification required to finish this process. As per the mysql documentation when the data
is stored in a timestamp field it is converted to GMT and when retrieved display it according to the session
timezone setting. Only the timestamp field type support this behaviour.
If somebody is changing the timezone for an existing database this may lead to a problem for datetime field type
because it will not adjust to the timezone change.
Altering all the datetime fields to timestamp field is a possible solution for this problem.
Is this is something acceptable to the community?
Eldho
ZH Healthcare
Hi,
Regarding the timestamp issue, one thing to consider is that it does not support pre 1970 or post 2038 per this quote:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Here is the mysql doc this was taken from:
http://dev.mysql.com/doc/refman/5.5/en/datetime.html
-brady
OpenEMR
Would be nice, though, then would support a facility across regions. We can likely assume that mysql will "fix" this around 2030 or so(if OpenEMR is even still around at that time :) ). So, the only practical concern would be things in the past, like dob (which is only a date anyways). I think converting to TIMESTAMP may make sense; is it possible for mysql to do this(convert a DATETIME field to a TIMESTAMP field) and is there any mysql queries that will break when do this?
-brady
OpenEMR
Hi,
The following query will do the necessary change:
ALTER TABLE `tablename` MODIFY `Fieldname` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.
This will convert the existing date time field to the timestamp (basically the equivalent GMT time ) as per the sever settings at that time.
It also don't have any effect on the date field and I dont think we have to change the date to timestamp type.
We may also create a method to search the existence of "datetime" field, when a change to timezone is being made and alter all those fields.
As all the new operating systems are programs are converting to 64 bit, I think the year 2038 problem will be fixed by mysql in the near future.
We only have to take care any fields which may have to store future values beyond year 2038, but in that case I don't think it will require "datetime" field as for a future date the time part will be irrelevant.
Hi,
Might as well change them all and see what happens (should keep the current datetime defaults though, for example, some default to blank or null I think). Trying to keep altering the datetime fields when change timezones seems very dirty; might as well try to convert them all in one swoop by converting everything to timestamp and be done with it. I can't think of any datetime fields that will be storing pre 1970 or post 2038; can you?
-brady
OpenEMR
Hi Brady,
The core OEMR tables can be altered through our upgrade scripts and we will do those changes to the sql upgrade files. But for the datetime fields if any used by different forms cannot be determined by us. Users may be using different forms from contrib folder or developed by themselves. The details about this can be obtained through querying information_schema at run time and we may do necessary alteration. But if you think this is not necessary we are fine with that.
Meantime have you reviewed the code and have any suggestion?
Regards
Eldho
Hi Eldho,
Just placed a review on github. Would just focus on the OpenEMR (http://www.open-emr.org/wiki/index.php/FAQ#Are_OpenEMR_and_OEMR_the_same_thing.3F :) ) core code now. The problem with blindly changing things (unknown forms) is that we may do something bad like convert a forms DOB datetime to a timestamp (thus making everbody less than 43 years old). We can likely include the more popular forms used within the contrib directory if wish and will also need to incorporate it in the xmlformgen script (which should be straightforward).
thoughts?
-brady
OpenEMR
Brady,
We have made the modifications suggested and added a code review in SourceForge.
Regards
ZH HealthCare
Hi,
Placed a review on github(also had a couple questions there to clarify some things):
http://github.com/zhhealthcare/openemr/commit/b8ce87465ba3a2c73e69ec404b51dd3df2e8c90d#commitcomment-2818727
thanks
-brady
OpenEMR