Time Zone Information in Access and VBA

by Philipp Stiefel, originally published 2022-10-29

last revision on 2022-10-29

Image by Gerd Altmann auf Pixabay , used here under Pixabay License

At first thought, date and time might appear to be a very simple data point that can be treated as discrete values having the same meaning for everybody. However, in our globally interconnected world you, as a software developer, must see date and time in the context of the regional customs of the user working with your application. I already wrote about universal considerations when dealing with date and time in an Access/VBA application and about formatting dates explicitly for a particular region.

Today, I want to address another aspect of date and time: time zone information. In the day and age of remote work and distributed teams, a software developers must expect people in different time zones working with the same application, with the very same database. This boils down to the fact that my 09:00 o’clock is most likely different from your 09:00 o’clock.

For many applications this will be a very minor issue that can be safely ignored. However, if exact and accurate time information is important for the purpose of your application, you cannot ignore this topic.

Approach 1 – Common Denominator

One option to handle the potential ambiguity of date/time is to define a common denominator of what date and time values mean in our database. One such approach would be to explicitly convert all dates and times stored in a database to UTC (Universal Time Coordinated).

As long as the backend database server is a single instance in a single time zone, a variation of this approach could be to treat all times as being in the time zone of the server. This is simpler because server-side operations in active backend DBMS, such as Microsoft SQL Server, wouldn’t need any date/time conversions at all. However, you must pay specific attention to this if the server should ever be moved to another time zone.

If you store all dates in one defined time zone in the database no conversions for internal calculations are required. However, you still need to convert dates to/from the time zone of each user when the application processes user entered date/time information or displays such information. Particularly with data editing, this might turn out to be more difficult than it would appear at first glance. One major strength of Access is its simple and easy data binding capability. However, it is seriously complicated if you want to do any conversion of data between the database query and the UI control the data is edited in. It will often mean that you must forgo automatic data binding and work with unbound forms.

Approach 2 – Store Time Zone Information

An alternative option would be to store date/time unchanged but also store the time zone the current users is in with each date/time value in the database. The most significant advantage is that you do not need to do any conversion in between the UI control the user edits the data in and the record stored in the database, you just also display the time zone belonging to a date/time value. – The data binding issue described with the common denominator approach does not exist here.

In terms of usability, mixing dates from multiple time zones in one application is a drawback, as it might confuse users and lead to errors even if time zone information is displayed with each date/time value.

This approach also has another significant disadvantage. It requires you to implement the conversion from one time zone to another whenever you compare or calculate with date/time values. – This means additional work for the developer during implementation and additional computational work for the application when executing such operations.

Getting Time Zone Information

Whatever approach you choose; you must be able to determine the time zone of the user working with the application. This can be done by simply letting the user input this information when creating their user account for the application. – But what about users working from different locations in different time zones? What about Daylight Saving Times? What about the offset of the local time to the database server time? – Do you know the UTC offset of your local time?

Sure, you can store time zone reference data in your database and write the code to deal with the above issues. – The good news is: You don’t have to!

Microsoft Windows already comes with a lot of time zone data and stores information about the current (Windows) user’s time zone. Why not use this information? This reduces the code you have to write and exonerates the user from providing his time zone information to the application. The Windows time zone information can be retrieved with the Win32 API.

Using the GetTimeZoneInformation Win32 API Function in VBA

There is a single function in the Win32 API that provides the information your application needs to know about the time zone of the current user. That function is GetTimeZoneInformation. It will write the time zone info to a TIME_ZONE_INFORMATION structure for you to process. The return value of the function will tell you whether Daylight Savings Time is currently observed.

The API declaration and the API function call itself in VBA is fairly simple.

However, you need to carefully read the documentation of the TIME_ZONE_INFORMATION structure to understand the meaning of its members.

As this text focuses on converting date/times from/to a common denominator, the most important members of this type are the …Bias members.

• .Bias is the bias between UTC and local Standard time: UTC = local time + bias
• .StandardBias is the bias between local Standard time and local Standard time (sic!). It should come as no surprise that this value is zero most of the time. – Actually, I cannot imagine any scenario where this would be a different value.
• .DaylightBias is the bias between local Standard time and local Daylight Time. So, if your current time is a daylight savings time, you must add this value to the general .Bias to convert between local Daylight Time date/time and UTC.

Additional information in the TIME_ZONE_INFORMATION structure are the dates for transitioning between local Standard Time and local Daylight Time. – I’m not going to reiterate the documentation on how to interpret this data.

The data returned by the GetTimeZoneInformation function gives us all we need to derive all the following information.

The displayed message is built like this:

Admittedly, this string concatenation is a horrible mess. I strongly suggest to use a helper function similar to String.Format to make this more readable and maintainable. – I didn’t do that in the sample module provided here because I did not want to introduce a dependency on another custom function.

Despite being messy, the code used here should provide a working example on how to interpret and use all information provided by GetTimeZoneInformation. If you want to fully understand the details and use this API in your own application, I suggest you download my TimeZoneInformation module containing working sample code. (You must run the DisplayTimezoneInformation procedure in the VBA environment to display the time zone information message shown above.)

Conclusion

The basic calculations to handle different time zones are simple. With the Windows API function shown above, it is also fairly simple to retrieve the information required to handle different time zones.

The crux of the matter is to define and implement consistent handling and storage of date and particularly time values in your application and database. I outlined two approaches to solve this above, but neither is a silver bullet, and both will require significant additional work beyond creating the core functionality of your application.

Share this article: