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

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

  • Agile methodologies give development and test teams the ability to build software at a faster rate than ever before. Combining DevOps with hybrid cloud architectures give teams not just the principles, but also the technology necessary to achieve their goals. By combining hybrid cloud and DevOps: IT departments maintain control, visibility, and security Dev/test teams remain agile and collaborative Organizational barriers are broken down Innovation and automation can thrive Download this white paper to …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds