O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Working with Time Zones

Problem

Date/time values depend on two things: the location of the computer, and the location of the person changing the data on the computer. If both happen to be in the same place, there's no problem. But if they aren't, you may end up with values that can't be compared with each other.

Solution

There are two solutions to this problem—both affect the overall design of the database. The first approach is to store all date/time values using a single time zone, and then to convert them to local time as necessary. The second approach is to capture the local time zone whenever a date/time value is entered into the database; then, when you need to compare values, you can convert them to a common time zone.

Regardless of which approach you choose, the first thing you'll need is a table containing the information that will allow you to convert one time zone to another (see Figure 8-6). The important elements in this table are TimeZone, Location, and Offset.

The TimeZone field contains the standard abbreviations for the time zones. The values in this field are not unique, but the people that originally defined time zones did so in a way that would allow everyone to understand the appropriate time zone given their geographical location. Offset contains the number of hours that need to be added to Coordinated Universal Time, or UTC, to get the local time (for further details, see the Discussion section).

Assuming that all of the time values in your database are stored in ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required