dhis2-devs-core team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Sun, Dec 6, 2015 at 1:18 PM, Mark Polak <mark@xxxxxxxxxxxxxxx> 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>