The audit trail fixer

TOPlap keeps audit trails on all modifications made to its records. This audit trail keeps track, for example, each time you change the status of a record. Later you could run a report and find out how long this record was in a certain state.

Now it turns out that there was a small bug in the software, that made some of the audit trails unreliable¹. For example, the report may show that a record was in state X for 1720 minutes, but in actuality it was only 1360 minutes. We are going to write a script to recalculate audit trails for affected customers.

Fixing the audit trails is not simple. One major challenge is that all timestamps are kept as local times with a GMT offset. Unfortunately, GMT offsets are not time zones. The difference between time zones and GMT offsets is subtle but important: with just a GMT offset, you can't know if you have to apply daylight saving time changes, or not.

Through a lucky coincidence, it is possible to deduce time zones in this particular scenario. You see, we know that only two customers were affected by this bug: 1. FaxSchool, the Halifax school board, and 2. El Universidad Libre de Santiago (EULS). That means that each timestamp can be one of two time zones:

1. America/Halifax, which is GMT-4 (North hemisphere winter) or GMT-3 (North hemisphere summer)
2. America/Santiago², which is GMT-3 (South hemisphere summer) or GMT-4 (South hemisphere winter)

Based on the time of year and GMT offset, we can make a pretty good guess.

In the test input below, you see three columns. The first column is the timestamp (with GMT offset) that we want to 'fix'. The second column is the correct audit trail duration in minutes. The third column is the wrong audit trail duration in minutes. To arrive at the correct time, we must subtract the incorrect minutes and then add again the correct minutes. The resulting timestamp must, of course, be converted to local time again, because otherwise we'd risk subtle bugs elsewhere in the system.

2012-11-05T09:39:00.000-04:00	969	3358
2012-05-27T17:38:00.000-04:00	2771	246
2001-01-15T22:27:00.000-03:00	2186	2222
2017-05-15T07:23:00.000-04:00	2206	4169
2005-09-02T06:15:00.000-04:00	1764	794
2008-03-23T05:02:00.000-03:00	1139	491
2016-03-11T00:31:00.000-04:00	4175	763
2015-08-14T12:40:00.000-03:00	3697	568
2013-11-03T07:56:00.000-04:00	402	3366
2010-04-16T09:32:00.000-04:00	3344	2605

• The 1st record happens during the northern winter, so we can deduce that the -4:00 offset indicates Halifax. That year, summer time in Halifax ended on Sun, 4 Nov, 02:00. We subtract 3358 and add 969 minutes, and arrive at 2012-11-03T18:50:00.000-03:00 (Halifax DST).
• Lines 7 and 9 are from Halifax as well.
• Lines 2,3,4,5 and 10 are from Santiago.
• Records 6 and 8 are ambiguous: both Halifax and Santiago are in the same GMT offset on those days. But the calculation happens to be the same for both places so it makes no difference (You may assume that your input will always produce an unambiguous result, even when the place can't be deduced).

In the end, here are the corrected times corresponding to the example:

2012-11-03T18:50:00.000-03:00
2012-05-29T11:43:00.000-04:00
2001-01-15T21:51:00.000-03:00
2017-05-13T23:40:00.000-03:00
2005-09-02T22:25:00.000-04:00
2008-03-23T15:50:00.000-03:00
2016-03-13T10:23:00.000-03:00
2015-08-16T16:49:00.000-03:00
2013-11-01T07:32:00.000-03:00
2010-04-16T21:51:00.000-04:00

Now to arrive at your final answer, do the following. For each record, take just the hour in local time and multiply it by the line number. Sum all these products, this is your result. For the test input, the answer is: 18 * 1 + 11 * 2 + 21 * 3 + 23 * 4 + 22 * 5 + 15 * 6 + 10 * 7 + 16 * 8 + 7 * 9 + 21 * 10 = 866

Reading & reference materials

• IANA Time Zone database is the official source of time zone data used by libraries and operating systems. It's not easy to parse the information here, but it's good to know where it all comes from.
• timeanddate.com is incredibly useful. You can find all kinds of useful time-related information, such as when daylight saving time starts and ends for each time zone around the world. You can find DST-corrected time and date at any instant in history.

To play, please log in with one of these options:
GitHub Login | Google Login