On Sun, Dec 6, 2015 at 1:18 PM, Mark Polak <m...@thedutchies.com> wrote:
> Do we then store the timezone with the dates? If no, we'd depend on the > server's date setting for a database to represent accurate dates? So moving > databases would require the server to have the exact same date settings? > > Are you saying that a report for *July 30* would look the same no matter > what timezone you're in? That might be confusing as someone entering an > event on a date might have to get the report for the next day to see that > event? That might be a very weird user experience. > No we will not design for that behavior ;) What I proposed implies that since we will have the timezone of each user, we will convert all dates coming from the clients on the server side, including both for saving events and for analytical data requests/reports. My point is that simply storing the timezone with each event in the database does not solve the problem. From the postgres docs <http://www.postgresql.org/docs/9.1/static/datatype-datetime.html>: "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone <http://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-TIMEZONE> parameter, and is converted to UTC using the offset for the timezone zone. When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3 <http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT> )." Lets consider a scenario for a global NGO: An event, lets say someone getting FP counselling, is captured in Vietnam for January 10. For the VN users set to UTC +7 we need to adjust to UTC by add 7 hours both when saving events and before querying the analytics tables. When the folks in the HQ on US east coast, UTC -5, want to see how many people who received FP counselling on January 10, both in Vietnam and world-wide, we need to have a common baseline in order to produce the aggregated value. That baseline is UTC. So we can adjust by subtracting 5 hours before doing the analytics query. So the idea is that we need to understand which timezone the dhis users are, since we potentially have users of a dhis instance in multiple timezones. We can store it once per user and adjust to UTC on the server side. We could also submit the timezone as part of the date strings in request, but that is more complicated and there is no reliable way to detect time zones from http web requests, so it will likely have to be defined by the user anyway. > > I think this stuff is generally pretty challenging and we need to consider > carefully before going with a solution. > -- Lars Helge Øverland Lead developer, DHIS 2 University of Oslo Skype: larshelgeoverland http://www.dhis2.org <https://www.dhis2.org>
-- Mailing list: https://launchpad.net/~dhis2-devs-core Post to : dhis2-devs-core@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs-core More help : https://help.launchpad.net/ListHelp
