TIP: Convert GMT to Local Date/Time

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.

# # #



About the Author

Ritesh Poojara

Live in Mumbai Love to share

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Packaged application development teams frequently operate with limited testing environments due to time and labor constraints. By virtualizing the entire application stack, packaged application development teams can deliver business results faster, at higher quality, and with lower risk.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds