Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-40430

Allow SiteStats aggregator job to compensate for sakai_event data stored in a different timezone

    Details

    • Type: Bug
    • Status: RESOLVED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 11.4, 12.3
    • Fix Version/s: 19.0
    • Component/s: Sitestats
    • Labels:
      None
    • Property addition/change required:
      Yes
    • Test Plan:
      Hide

      Part A) Probably needs a developer to test this part. Requires the following environment:

      • Sitestats configured to use the event aggregator
      • A database that stores sakai_event dates in UTC, or "effectively UTC"
      • Set the property sakaiEventTimeZone@org.sakaiproject.sitestats.api.StatsAggregateJob=UTC

      Generate events in sakai that would occur on a different date if they were displayed in UTC time. Run the aggregator. Confirm the events are counted against the expected date for the local time zone.

      Part B) Review all dates and times in the UI and the report exports to make sure they are presented accurately and make sense in their given context. Be sure to also test with user preferences set to a time zone different from the server time zone.

      Show
      Part A) Probably needs a developer to test this part. Requires the following environment: Sitestats configured to use the event aggregator A database that stores sakai_event dates in UTC, or "effectively UTC" Set the property sakaiEventTimeZone@org.sakaiproject.sitestats.api.StatsAggregateJob=UTC Generate events in sakai that would occur on a different date if they were displayed in UTC time. Run the aggregator. Confirm the events are counted against the expected date for the local time zone. Part B) Review all dates and times in the UI and the report exports to make sure they are presented accurately and make sense in their given context. Be sure to also test with user preferences set to a time zone different from the server time zone.

      Description

      All my local MySQL databases, as well as our institution's Oracle databases, store sakai_event data in what appears to be UTC. I'm not entirely sure how these are stored under the hood in the database, but all the querying tools I have present times for current events as if they are in UTC, or "in the future" if it is respective to my local time zone (Eastern).

      MYSQL

      Event_date is a Timestamp column. According to MySQL docs, this internally stores the value as a unix timestamp, and calling unix_timestamp() on it will return the value directly. When I do that with a very recent sakai event row, it returns a value that corresponds to the time of the event plus the UTC offset, resulting in a value that is in the future. For example, a sakai_event performed on August 1 at 8:01pm EDT is stored using the unix timestamp corresponding to August 2 at 12:01am EDT.

      Update: Further investigation has shown this is caused by the mariadb driver that ships with Sakai. Switching to the mysql driver results in the events being recorded at the expected times. For example, these two events happened August 9, 2018 at 9:12am using the mariadb driver and 10:03am using the mysql driver:

      -----------------------------------------------+

      event_date unix_timestamp(event_date)

      -----------------------------------------------+

      2018-08-09 10:03:43 1533823423
      2018-08-09 13:12:21 1533834741
      ORACLE

      Event_date is a Date column, at least in our databases at Western that are several years old (a brand new installation may be different). According to Oracle docs, this stores year/month/day and hour/minute/second values with no information about time zone. As with MySQL with the mariadb driver, very recent events in sakai appear in the database as if they happened in the future or as if they were recorded in UTC. Since there is no timezone information stored with the value and there is no internal unix timestamp representation, I can't tell which it is, although UTC seems more likely.

      In either case, when the SiteStats aggregator job runs, it reads the data from this table using jdbc getTimestamp() and interprets it as if it happened our local time zone (Eastern). As a result, events that occur after 8pm Eastern will end up being counted towards the following day's total, as they are first stored as 12am UTC, then read back in by SiteStats as 12am Eastern the following day. An out-of-the-box Sakai installation (when set to use the aggregator) on MySQL from Ubuntu exhibits the issue, so it may be a common problem for institutions using the aggregator job to have. Note that the default SiteStats method of listening for live events is not affected because everything remains in the JVM as Java Date objects.

      This patch introduces a new sakai.property (sakaiEventTimeZone@org.sakaiproject.sitestats.api.StatsAggregateJob=<timezone>) to tell the aggregator job which timezone the dates from sakai_event use, by now calling getTimestamp(calendar) with a calendar created using the given timezone. This allows it to compensate for the discrepancy between how sakai_event stores dates and the local server time zone that SiteStats uses to count an event against a particular day. With this change, events occurring after 8pm are still counted toward the current date.

      If the property is not set, the original behaviour, with no adjustment for time zone, is preserved. This means that schools that do not have this issue don't need to make any changes.

      New property: sakaiEventTimeZone@org.sakaiproject.sitestats.api.StatsAggregateJob=<timezone>

      -----------------------------------------------

      The patch also corrects or clarifies a number of presentation issues in the UI related to dates that can be confusing for users:

      • As daily totals are counted with respect to the server time zone, this is now explicitly indicated in the UI. This could be confusing for users in other regions that may assume it was relative to their own local time zone.
      • Totals also do not take time into account, so the time component of the datepicker for reports has been removed. Users often expected to be able to pinpoint particular hour ranges in the reports when this is not possible.
      • Point-in-time data like the last updated time and the report generated time are now explicitly displayed in the user's local time zone according to their Sakai preferences settings. The last updated time, which appears on every page when the aggregator job is used, now shows both the user's time and the server time to assist the user in understanding the context of the daily totals.

      These clarifications will become more important when SAK-40018 is finished, as it will show exact times of events and present everything in the user's local time zone according to their Sakai preferences.

      Screenshots are attached that show the UI for a user in New Zealand accessing a Sakai installation in Canada.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  plukasew Paul Lukasewych
                  Reporter:
                  plukasew Paul Lukasewych
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Git Source Code