Click here to Skip to main content
Click here to Skip to main content

Convert GMT to Local Date time

, 18 Mar 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL function to convert GMT datetime to Local datetime

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()) {

                // region specific timezone
                //TimeZone localzone = TimeZone.getTimeZone("Australia/Melbourne");

                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) {

                        // take the time one day behind to loop hrs
                        c1.add(Calendar.DATE, -1);
                        //tempcal.add(Calendar.DATE, -1);
                        tempcal.setTimeInMillis(c1.getTimeInMillis());

                        // Now loop into hrs for further precision
                        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) {

                                // take the time one hr behind to loop minutes
                                c1.add(Calendar.HOUR, -1);
                                tempcal.setTimeInMillis(c1.getTimeInMillis());
                                //tempcal.add(Calendar.HOUR, -1);

                                // now loop further for minutes
                                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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ritesh Poojara
Software Developer
India India
Work as a Software/Database developer. I Live in Mumbai, INDIA. Love to share my work.

Comments and Discussions

 
GeneralThis is gr8 dude .. saved me a ton of time. PinmemberMember 869768721-Jun-12 11:43 
GeneralMy vote of 3 Pinmemberchirag.khatsuriya27-Jul-11 1:36 
GeneralThank you! PinmemberMattNeffer4-Jan-11 7:30 
GeneralSQL statement PinmemberDimkin14-Nov-10 14:39 
GeneralScalar UDF Performance PinmemberMember 41198773-Dec-09 5:00 
GeneralRe: Scalar UDF Performance PinmemberRitesh Poojara3-Dec-09 18:02 
GeneralRe: Scalar UDF Performance PinmemberRitesh Poojara3-Dec-09 18:53 
GeneralBrilliant PinmemberMember 41198771-Dec-09 16:30 
QuestionDaylight savings PinmemberJamiegoh130-May-09 19:20 
AnswerRe: Daylight savings PinmemberRitesh Poojara30-May-09 20:38 
GeneralRe: Daylight savings PinmemberAnony8831-May-09 9:49 
Generalvery good PinmemberDonsw19-Apr-09 16:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 18 Mar 2009
Article Copyright 2009 by Ritesh Poojara
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid