CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Almost all applications enable for internationalization allow you to save the datetime columns in UTC/GMT time zones as well as allow you to 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 file to generate timezone_offsets.sql the time zone offset SQL script for desired years, by default it generates information from 1995 to 2050. Source code to the JAR is also included. Just read readme.txt to get started using this.
There are different opinions on how many time zones a business application should offer. For example, Microsoft Windows lists all of the major time zones, which is about 74 of them. 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.
Time Zones Covered
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 past and possibly future changes as too. You can use the jar application to generate the time zone offsets SQL script for a limited period of years or for default period. So if next java version adds 200 more sub time zones all you need to do is re-genarate this Script.
Using the code
All you need to do is run the script timezone_offsets.sql to your database. That’s it! You are done. You can run the run_test.sql to test the results.
To genarate the time zone offsets SQL script, you need to invoke the JAR thru the command prompt or simply double click the JAR. To generate the script for a limited period, you might need to invoke the JAR through the command prompt and pass parameters for the startyear and endyear:
java -jar timezoneoffset.jar 2000 2015
How Code works
The script generation code basically loops thru 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 until 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.
# # #
