By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,903 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,903 IT Pros & Developers. It's quick & easy.

Best way to store multiple work times across timezones

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Anthony wrote:
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.
I would not implement this "timezone feature" at all unless you are
prepared for all the additional issues that will come along with it. An
employee's timezone is not a constant. It changes every time (s)he is
transferred or even takes a trip from the home office in San Diego to do
a couple of weeks of field work in (let's say) New Delhi. If the point
is to be able to display local standard time for each timestamp, then
you may be buying into tracking the entire history of the employees
movements. Are you sure you want to do that?
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 don't see what's to debate. I would do the whole thing in GMT at the
database level - and personally I would try like hell to get away with
using GMT at the presentation level as well. Conversion to local
standard time is no big trick - but it's still not guaranteed to be the
same as local clock time (because of DST) and so will inevitably confuse
people anyhow.
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.


I would recommend GMT over PST, it's just way more standard. Also, if
you do end up supporting time zone offsets, they are normally specified
as offsets from GMT. Someone in Adelaide could very likely tell you
that his offset is +09:30 from GMT - his Windows PC can tell him that
much - but what are the chances that he knows his offset from PST?

JMHO,
-rick-
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.