Whichever timezone is relevant to the analysis later when the data is read.
If you want to see if an employee is late compared to the time their shift starts, the system needs to know the time their shift starts in UTC, because otherwise if they start a shift in during a timezone change, it’ll think they’re extremely late/early.
If you want to pay them for their (clock out - clock in) time, UTC.
If they’re a remote worker on your team, and you want see your entire team’s availability, you should probably see it in _your_ local time instead.
I’ve found that whenever it looks like I need to know the local time that the user had when they did something, it’s because I’m implicitly anchoring it to some other timestamp that my system doesn’t know and that should also be recorded in UTC (like, in this example, the time their shift is supposed to start).
Sometimes, the relevant timezone is whatever the local timezone was when and where the timestamp was recorded. Which is information that's lost on UTC conversion.
Sure, you can reconstruct it if you stored the "where" part somewhere else, and associated the "where" with the timestamp's timezone (e.g. the Offices table has a Timezone column that you JOIN with the punch-in times). But that assumes you stored it somewhere else. It also assumes said storage is readily available. It also assumes there was no human error in recording that value (what if the office was accidentally assigned Seattle PST instead of BC PST back when it didn't matter?) It also assumes it was possible to record it correctly in the first place (what if the office is in Kimberley, BC but the software only allowed selecting BC timezone?)
Alternatively, you store the timezone directly in the timestamp itself and avoid all these problems and more.
Comments
Whichever timezone is relevant to the analysis later when the data is read.
If you want to see if an employee is late compared to the time their shift starts, the system needs to know the time their shift starts in UTC, because otherwise if they start a shift in during a timezone change, it’ll think they’re extremely late/early.
If you want to pay them for their (clock out - clock in) time, UTC.
If they’re a remote worker on your team, and you want see your entire team’s availability, you should probably see it in _your_ local time instead.
I’ve found that whenever it looks like I need to know the local time that the user had when they did something, it’s because I’m implicitly anchoring it to some other timestamp that my system doesn’t know and that should also be recorded in UTC (like, in this example, the time their shift is supposed to start).
Some nuance applies, of course.
Sometimes, the relevant timezone is whatever the local timezone was when and where the timestamp was recorded. Which is information that's lost on UTC conversion.
Sure, you can reconstruct it if you stored the "where" part somewhere else, and associated the "where" with the timestamp's timezone (e.g. the Offices table has a Timezone column that you JOIN with the punch-in times). But that assumes you stored it somewhere else. It also assumes said storage is readily available. It also assumes there was no human error in recording that value (what if the office was accidentally assigned Seattle PST instead of BC PST back when it didn't matter?) It also assumes it was possible to record it correctly in the first place (what if the office is in Kimberley, BC but the software only allowed selecting BC timezone?)
Alternatively, you store the timezone directly in the timestamp itself and avoid all these problems and more.