momentumfrc / attendance-manager

Keep track of the attendence of our team members by allowing mentors to check students in and out.
0 stars 0 forks source link

Consider timezones in meeting report #48

Closed lost1227 closed 1 year ago

lost1227 commented 1 year ago

Meeting attendance is determined by grouping attendance sessions by date and counting the number of unique students.

SELECT
    CAST(checkin_date AS DATE) AS meeting_date,
    COUNT(DISTINCT student_id) AS student_count
FROM `attendance_sessions`
WHERE checkin_date >= '2023-04-01'
GROUP BY meeting_date;

However, grouping by day is very dependent on timezone, as two events that occurred on the same day in one timezone may have occurred on different days from the perspective of a different timezone. Since our meetings occur in the PTC/-08:00 timezone, but dates are stored in the database in UTC, this was causing meeting attendance to be "smeared" across 2 days (since check-ins that happened before 16:00 PST/0:00 UTC "counted" towards a different day from check-ins that happened on or after 16:00 PST/0:00 UTC).

The solution is to take the client's timezone as an argument (with an configurable default of PST/-08:00), then convert the UTC dates into the client's timezone before grouping.

SELECT
    CAST(CONVERT_TZ(checkin_date, "UTC", ?) AS DATE) AS meeting_date,
    COUNT(DISTINCT student_id) AS student_count
FROM attendance_sessions
GROUP BY meeting_date;

(I really, really hate timezones)

lost1227 commented 1 year ago

Fixes #46