Introduction
Almost all internationalization enabled applications save the datetime columns in UTC/GMT time zones and convert datetime to local time zones based on users time zone preferences.
This project includes a SQL function dbo.get_local_datetime (datetime, time_zone) to convert GMT based datetime values in the database to local timezone. SQL Function relies on a table containing time zone offsets by time zone ids. This table is created and populated by timezone_offsets.sql.
The project also includes a jar to generate timezone_offsets.sql the time zone offset SQL script for desired years, by default it generates information from 1995 to 2050. The source code to the Jar is also included within the jar. Just read readme.txt to get started using this.
Time Zones Covered
There are different opinions on how many time zones a business application should offer. For e.g., Microsoft Windows lists out all major Time zones which if you count are about 74. While this is set as a standard by Microsoft, many applications need to support subsets of these time zones. Java covers a wide range of time zone subsets based on major cities over the globe.
This table generated by timezone_offsets.sql covers 599 subsets of all the timezones supported by JDK 6.0. It also takes care of any changes made by the Energy Policy Act (EPAct) of 2005 or any other changes made in the past and possibly future changes as well. You can use the jar application to generate the time zone offsets SQL script for a limited period of years or for a default period. So if the next Java version adds 200 more sub time zones, all you need to do is regenerate this script.
Using the Code
All you need to do is run the script timezone_offsets.sql to your database and that's it!
You are done. You can run the run_test.sql to test the results.
To generate the time zone offsets SQL script you need to invoke the jar through the command prompt or simply double click the jar. However to generate the script for a limited period, you might need to invoke the jar through the command prompt and pass parameters for startyear and endyear.
Command e.g.
java -jar timezoneoffset.jar 2000 2015
How the Code Works
The script generation code basically loops through the array of time zones. It uses java.util.Calendar and java.util.TimeZone initializing the Calendar to the day from "startyear" or default 1995 incrementing day by day finding the offsets with time zone GMT and local time zone generating SQL inserts as it finds differences in offsets. This goes on till it reaches "endyear".
The SQL function searches for the relative offset as per the input date and time zone id and returns Local Time in the required time zone.
Let's walk through the SQL function first:
create function dbo.get_local_datetime
(@date datetime, @time_zone varchar(25))
returns datetime as
BEGIN
declare @local_time datetime
declare @offset_time int
select @offset_time = offset from timezone_offsets _
where @date between start_time_gmt and end_time_gmt and time_zone_id = @time_zone
set @local_time = dateadd(ms, isnull(@offset_time,0), @date)
return @local_time
END
The dbo.get_local_datetime function is pretty simple. It accepts time zone id for the desired output and datetime to be converted. All it does is look for the offset from timezone_offsets table based on the input date and then calls dateadd built in function in SQL to add the offset. If the offset is negative then it reduces the time else will add up. While it does this, it also checks if the time zone id passed is correct by wrapping the offset around isnull. It is here that if you want you can do modifications to return null or a blank string if the incorrect time zone id is passed to the function. I've also added appropriate index tz1 to the timezone_offsets table just to make sure performance is not hit.
Now let's look at the Java code which generates the data for timezone_offsets table. I'll only show and explain the core function area here:
public static StringBuffer getTZinfo() {
StringBuffer sb = new StringBuffer();
int goCommandCounter = 0;
TimeZone gmt = TimeZone.getTimeZone("GMT");
DateFormat dfgmt59 = new SimpleDateFormat("yyyy-MM-dd HH:mm:59");
dfgmt59.setTimeZone(gmt);
DateFormat dfgmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
dfgmt.setTimeZone(gmt);
Calendar starttime = Calendar.getInstance(gmt);
Calendar endtime = Calendar.getInstance(gmt);
starttime.set(startyear, 0, 1, 0, 0, 0);
endtime.set(endyear, 0, 1, 0, 0, 0);
Calendar c1 = Calendar.getInstance(gmt);
Calendar tempcal = Calendar.getInstance(gmt);
String[] x = TimeZone.getAvailableIDs();
for (String tzid : x) {
TimeZone localzone = TimeZone.getTimeZone(tzid);
if (!localzone.useDaylightTime()) {
sb.append("INSERT timezone_offsets
(time_zone_id,start_time_gmt,end_time_gmt,offset) \n");
sb.append("VALUES('" + localzone.getID() + "','" +
dfgmt.format(starttime.getTime()) + "','" +
dfgmt59.format(endtime.getTime()) + "'," +
localzone.getOffset(tempcal.getTimeInMillis()) + ")\n \n");
if (goCommandCounter >= 5000) {
sb.append("GO \n \n");
goCommandCounter = 0;
}
goCommandCounter++;
}
}
sb.append("GO \n \n");
sb.append("--Time zones implementing Daylight savings \n \n");
for (String tzid : x) {
TimeZone localzone = TimeZone.getTimeZone(tzid);
if (localzone.useDaylightTime()) {
Date mintime, maxtime = null;
int offset = 0;
int offset1 = 0;
c1.setTimeInMillis(starttime.getTimeInMillis());
tempcal.setTimeInMillis(starttime.getTimeInMillis());
mintime = c1.getTime();
while (endtime.getTime().after(c1.getTime())) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.DATE, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
c1.add(Calendar.DATE, -1);
tempcal.setTimeInMillis(c1.getTimeInMillis());
for (int b = 0; b < 25; b++) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.HOUR, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
c1.add(Calendar.HOUR, -1);
tempcal.setTimeInMillis(c1.getTimeInMillis());
for (int c = 0; c < 60; c++) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.MINUTE, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
maxtime = tempcal.getTime();
sb.append("INSERT timezone_offsets
(time_zone_id,start_time_gmt,
end_time_gmt,offset) \n");
sb.append("VALUES('" + localzone.getID()
+ "','" + dfgmt.format(mintime)
+ "','" + dfgmt59.format(maxtime)
+ "'," + localzone.getOffset
(tempcal.getTimeInMillis()) + ")\n \n");
if (goCommandCounter >= 5000) {
sb.append("GO \n\n");
goCommandCounter = 0;
}
goCommandCounter++;
mintime = c1.getTime();
}
tempcal.add(Calendar.MINUTE, 1);
}
}
tempcal.add(Calendar.HOUR, 1);
}
}
tempcal.add(Calendar.DATE, 1);
}
}
}
}
I start off with a string buffer to hold out string data and then begin with a declaration of two Timezone objects, two DateFormat objects. One timezone object stays in GMT and other one keeps on changing as the loop proceeds. java.util.Calendar is the one which loops through every single day from startyear through endyear. You may notice that there are two looks, the first one for the time zones which do not implement daylight savings and second one for time zones with day light savings. The second loop in the function is the interesting one. Here I keep on adding a day one by one to the calendar object until I find a difference in the offset for a given time zone. As I find a difference, I further enter an inner loop to loop through 24 hours of the day to find out at what hour the offset changes. Once I find the hour, I further loop down on one more inner loop to find at what minute the offset changed. Further I assume that offset changed on 59 seconds. We do not need to loop through seconds. You can see that I have used dfgmt59 SimpleDateFormat instance to force format 59 seconds in the output.
You might need to less often use the jar to regenerate the SQL Script and the Script I've added. timezone_offsets.sql should suffice unless new time zones are supported by newer versions of JDK or you need to generate data for fewer years than the defaults.
History
- 18th March, 2009: Initial post
