Here is a debate we have been having at work about the design of our
timeclock database application. We have built an online timeclock
system for companies to use to keep track of their employees hours. In
brief, an employee can clock in and clock out by loggin through the
site. All of their work dates are recorded and the employeer can poll
reports based on employees in/out times, calculate hours, mark the
hours as paid etc.
The issue is that we are now implementing a timezone feature for each
employee. Currently, all times are recorded in PST off the server
clock which is calibrated to the US atomic clock. Now we have
implemented the ability for the user to choose their timezone, as the
system is intended to track multiple employees all over the world, and
view their in/out times and reports in the time zone they are in.
The debate is whether to record everyone's time in PST and calculate
their timezone offset when the data is queried, or to convert the time
to the selected timezone and write it to the database that way. Then
the data could be queried and just output without any manipulation.
I advocate storeing all the times in PST and letting the database do
the conversion when the data is queried. That way it is all uniform
consistant atomic values which allow for the most flexibility since
all the times can be converted to any other zone via some batch
process.
The argument for converting before is that no one will care about
seeing all times across multiple zones in a uniform format and that it
is more important (and less processing) to output the relative times
to the zone they were recorded in.
Any thoughts on this topic would be greatly appreciated as this has
recently become a hot topic. Which is the most practical, scalable,
and efficient way to do it? Any other designs are also greatly
welcome...
Thanks
