Thursday, February 02, 2006

To GMT or to not GMT

Toni and I are currently working on a project that has had as many as 9 developers working on it at one time, working from as many as four different locations, plus some developers also working from home. As a result, we've been doing a lot of work on VFE in recent months to make it more friendlier for team environment. One of the things we added to DBCX recently was the ability to write all of the metadata for a DBC out to an XML file and to load metadata from this XML file. This lets developers check in and check out individual DBCs and makes it easier for other developers to get their data dictionary changes without having to check in or check out the meta data for the entire project all at once. The project we're on currently has 47 DBCs, so you can imagine there's a lot of meta data as well.

Anyway, in working on this, I discovered that I needed to make sure that I properly dealt with different time zones. One of the offices is on West Coast time and well, Toni and I are likely to login from any number of time zones - and seeing as its February in Toledo right now, Hawaii Time sounds about perfect. So, in order to avoid any confusion related to timezones, I built the mechanisms for checking date and time of the changed metadata around GMT - Greenwich Mean Time. In a nutshell, in GMT it's always the same time everywhere in the world, no matter what time zone you're in - even if you're in Indiana or New Foundland. For more info on GMT, check this out.

I haven't done real well with posting code in the blog, so I put a file of the GMT related functions here.

There are 4 functions in this procedure file:
  • GMTDateTime() - which returns the current GMTDateTime. There's a Windows API call to get this.
  • GMTTimeDifference() - which returns the difference in seconds between the machine's local time and GMT. This function is used internally for the remaining functions.
  • GMTToLocal(ltDateTime) - which converts GMT a to its local equivalent.
  • LocalToGMT(ltDateTime) - which converts a local date & time to its GMT equivalent.
The code is commented pretty well, so I won't bore you with the details here.

This really got me thinking, should my applications be storing dates and times in GMT and then converting it to the current users local date and time for display purposes? It'd really depend on the application, but this is definitely something that I plan on giving somethought with my next application. Most of the time when we look at a date and time, we think of it in terms of our current locale, this could be a big issue in apps where the data comes from multiple locations and of course in web apps, where the users could be logging on at any time from any where.

So, I don't really have any guidelines or recommendations at this point for when GMT should or should not be used. If you have any, let me know. It's definitely something worth more thought.


At 2/03/2006 7:32 AM, Blogger wOOdy said...

Hi Mike,

You'd better use the term "UTC" instead of GMT, since that is what it's called in Windows. Internally Windows saves all datetimes in UTC and converts it to the local users time.

See also for some explanation of the history.

At 2/03/2006 10:24 AM, Blogger Mike Feltman said...

Well, there's Windows and then there's the real world. :)

I actually meant to mention that term as well and couldn't remember it when I wrote the entry.

At 2/07/2006 8:50 AM, Blogger Randy Jean said...

I toyed with using GMT (or UTC) in the database when doing some recent enhancements for a client. I didn't have these routines yet (why don't I learn to wait?) so I wound up using some funky stuff another developer had added to the database. Basically, their county table contains an offset (Hours from PST) that I use in conjunction with a flag on their company (or system) record that indicates if they are currently observing DST. This allows me to handle places like Arizona and Indiana which currently do not observe DST. I think hours from GMT would have been easier but in my case, local time being calculated is relative to where the sale is being held, not where the end user is.

At 2/13/2006 4:36 PM, Blogger Glenn said...

I can see where a database may wish to store datetime fields in UTC. Especially in an enterprise scope or a workflow application.In many cases such as a Firebird or Oracle timestamp field the datetime is loaded by the backend store for me.

We run into problems with a legacy FoxPro application that is storing datetime fields to the database in local time. We have a workflow process that is automatically creating database records in 3 tables as new work records are placed in a queue.
Technicians then retrieve these records and begin an analysis process with the main application.

A timestamp problem occurs as time rolls from EST to EDT or vice versa. In this workflow scenario a technician using the application can timestamp a record as retrieved and completed before the record created timestamp. It skews reporting statistics and jams the automatic record assignment logic for the hour that overlaps.

Our next version has these timestamp functions fully migrated to triggers, functions, and constraints in the backend and of course the server and database use UTC. I can't wait for those VFE UTC_LST functions.


Post a Comment

Links to this post:

Create a Link

<< Home