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

  • Agile development principles have gone from something used only by cutting-edge teams to a mainstream approach used by teams large and small. If you're not using agile methods already though, or if you've only been exposed to agile on small projects here and there, you may wonder if agile can ever work in your environment. Read this eBook to learn the fundamentals of agile and how to increase the productivity of your software teams while enabling them to produce higher-quality solutions that better fulfill …

  • With 81% of employees using their phones at work, companies have stopped asking: "Is corporate data leaking from personal devices?" and started asking: "How do we effectively prevent corporate data from leaking from personal devices?" The answer has not been simple. ZixOne raises the bar on BYOD security by not allowing email data to reside on the device. In addition, Zix allows employees to maintain complete control of their personal device, therefore satisfying privacy demands of valued employees and the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds