0 Comments

I don’t think I’ve ever had a good experience dealing with dates, times and timezones in software.

If its not some crazy data issue that you don’t notice until its too late, then its probably the confusing nature of the entire thing that leads to misunderstandings and preventable errors. Especially when you have to include Daylight Savings into the equation, which is just a stupid and unnecessary complication.

Its all very annoying.

Recently we found ourselves wanting to know what timezones our users were running in, but of course, nothing involving dates and times is ever easy.

Time Of My Life

Whenever we migrate user data from our legacy platform into our cloud platform, we have to take into account the timezone that the user wants to operate in. Generally this is set at the office level (i.e. the bucket that segregates one set of user profiles and data from the rest), so we need to know that piece of information right at the start of the whole process, when the office is created.

Now, to be very clear, what we need to know is the users preferred timezone, not their current offset from UTC. The offset by itself is not enough information, because we need to be able to safely interpret dates and times both in the past (due to the wealth of historical data we bring along) and in the future (things that are scheduled to happen, but haven’t happened yet). A timezone contains enough information for us to interact with any date and time, and includes a very important piece of information.

If/when daylight savings is in effect and what sort of adjustment it makes to the normal offset from UTC.

Right now we require that the user supply this information as part of the migration process. By itself, its not exactly a big deal, but we want to minimise the amount of involvement we require from the user in order to reduce the amount of resistance that the process can cause. The migration should be painless, and anything we can do to make it so is a benefit in the long run.

We rely on the user here because the legacy data doesn’t contain any indication as to what timezone it should be interpreted in.

So we decided to capture it.

No Time To Explain

The tricky part of capturing the timezone is that there are many users/machines within a client site that access the underlying database, and each one might not be set to the same timezone. Its pretty likely for them all to be set the same way, but we can’t guarantee it, so we need to capture information about every user who is actively interacting with the software.

So the plan is straightforward; when the user logs in, record some information in the database describing the timezone that they are currently using. Once this information exists, we can sync it up through the normal process and then use it within the migration process. If all of the users within an office agree, we can just set the timezone for the migration. If there are conflicts we have to revert back to asking the user.

Of course, this is where things get complicated.

The application login is written in VB6, and lets be honest, is going to continue to be written in VB6 until the heat death of the universe.

That means WIN32 API calls.

The one in particular that we need is GetTimeZoneInformation, which will fill out the supplied TIME_ZONE_INFORMATION structure when called and return a value indicating the usage of daylight savings for the timezone information specified.

Seems pretty straightforward in retrospect, but it was a bit of a journey to get there.

At first we thought that we had to use the *Bias fields to determine whether or not daylight savings was in effect, but that in itself  brought about by a misunderstanding, because we don’t actually care if daylight savings is in effect right now, just what the timezone is (because that information is encapsulated in the timezone itself). It didn’t help that we were originally outputting the currentoffset instead of the timezone as well.

Then, even when we knew we had to get at the timezone, it still wasn’t clear which of the two fields (StandardName or DaylightName) to use. That is, until we looked closer at the documentation of the function and realised that the return value could be used to determined which field we should refer to.

All credit to the person who implemented this (a colleague of mine), who is relatively new to the whole software development thing, and did a fine job, once we managed to get a clear idea of what we actually had to accomplish.

Its Time To Stop

At the end of the day we’re left with something that looks like this.

Public Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 63) As Byte
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 63) As Byte
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type


Private Function GetCurrentTimeZoneName() As String

    Dim tzi     As TIME_ZONE_INFORMATION

    If GetTimeZoneInformation(tzi) = 0 Then
        GetCurrentTimeZoneName = Replace(tzi.StandardName, Chr(0), "")
    Else
        GetCurrentTimeZoneName = Replace(tzi.DaylightName, Chr(0), "")
    End If
    
End Function

That function for extracting the timezone name is then used inside the part of the code that captures the set of user information that we’re after and stores it in the local database. That code is not particularly interesting though, its just a VB6 ADODB RecordSet.

Hell, taken in isolation, and ignoring the journey that it took to get here, the code above isn’t all that interesting either.

Conclusion

With the required information being captured into the database, all we have to do now is sync it up, like any other table.

Of course, we have to wait until our next monthly release to get it out, but that’s not the end of the world.

Looking back, this whole dance was less technically challenging and more just confusing and hard to clearly explain and discuss.

We got there in the end though, and the only challenge left now belongs to another team.

They have to take the timezone name that we’re capturing and turn it into a Java timezone/offset, which is an entirely different set of names that hopefully map one-to-one.

Since the situation involves dates and times though, I doubt it will be that clean.