How to use Java timezone id in a Windows (non-Java) application?

Question

I need to add timezone information to a db table with user maintained locations. The data will be accessed mostly from Java code but there is also some PL/SQL and Win32 (Delphi) code which needs to understand the timezone information.

It seems straight forward to use the id from java.util.TimeZone. Java can easily convert that (obviously), Hibernate has built-in support for it and apparently also Oracle understands those timezone ids:

select TZ_OFFSET('Pacific/Marquesas') from dual.

The problem is: the timezone ids do not seem to be compatible with the Windows Timezone DB. For example, the java.util.timezone id "Pacific/Marquesas" (-09:30) is not in the timezone picklist in Windows. The registry does not contain it at all; see

\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Here I can only pick either -09:00 or -10:00. So, if I were to store the timezone like this, how can I get to the actual offset/DST infos in Windows (without Java)? Hopefully this does not require a mapping table which I have to keep up to date whenever it changes. Is there a globally accepted standard which works better than the java timezone id?

Update

The timezone info is used in combination with DATE columns on the database. Those columns contain local date/time values. If a location can be associated with those values, the location's timezone enables me to convert the date/time value to UTC or any other timezone whenever needed.

I realize that instead of DATE a TIMESTAMP_TZ data type or something similar would be more appropriate. However, this would require a data migration (for which the TZ is required again) and is not supported by the legacy applications which also work on the data (unless a lot of code is changed). The problem is almost the same if I had to convert the values to UTC.

Bottom line is I need to keep the DATE values in local time but I need to know for some of them which TZ that means.

Answer 1

I can give a little background, if not a real answer.

Many systems use the Olson implementation of timezone data. So those names work in many systems (most Unix, Java, Oracle I think). Microsoft does their own thing.

I see at the bottom of that Wikipedia link there's a reference to some mapping to the Windows world.

Good luck!

Answer 2

I realize this is not the best way to do it, but it might be sufficient in your case. Without knowing all the requirements I can't tell.

What do you need to use the time zone information for? Just to present the time with the correct offset and maybe also the name of the time zone?

You could continue to use Java to determine what the offset of the user is by looking up the user's selected time zone using Java. Each time the user logs in record in your database what the offset currently is. Then other apps can look at this information to determine how to format the time.

This assumes that users who regularly login are the ones that this needs to be done for. If that's not the case you could run a daily job to lookup the time zone for each user in Java and record the offset currently in effect.

Hackish, agreed, but the only other way I see is to maintain a mapping. And what happens when someone selects a time zone that you don't have a mapping for?