After going through the time zone concepts in Part 1.We will be implementing the concepts technically. So let’s give it a start.
We need to create a table in our database for several time zone and user related entries.
TABLE 1: dbo.tblTimeZone
This table will be a master table containing all the info about different time zones with following columns.
- TimeZoneID: primary key.
- TimeZoneName: stores name of the time zones i.e. IST, BST, EST, MST, and CST and so on.
- LagInHours: This column will contain the difference from UTC in hours.
- LagInMinutes: This column will contain the difference from UTC in minutes.
- DST: This will be a Boolean column that will tell us whether the time zone follows DST or not.
- LagInHoursWithDST: This column will contain the difference from UTC in hours including DST if it follows DST.
- LagInMinutesWithDST: This column will contain the difference from UTC in minutes including DST if it follows DST.
- DSTStartDateTime: Date time from which the DST will be starting.
- DSTEndDateTime: Date time from which the DST will be ending.
- DSTYear: Year for which the DST is under action.
This table will be filled with different entries of time zones, DST column will be true if the time zone follows DST and then only LagInHoursWithDST and LagInMinutesWithDST will be having entries for a DSTYear.
Every year a new entry will be made for all the time zone that follows DST with new DSTYear.
Let me show you a table having entries for few time zones so that you can get a better idea.
So as we discussed in the previous post. Denver and Arizona both fall in MST but one follows DST and the other does not, for such cases we will be making entries in the time zone table in the above manner.
You can also see for different years DST starts at different time so for handling that we just need to refer the current year from DSTYear column of the time zone table.
TABLE 2: dbo.tblUsers
This table will contain the relationship of users with the time zone to which they belong. From front end it can be handled in various ways during user creation. Web applications do provide a module for the user creation. While creating a user put an extra dropdown that will contain all the time zones and this way we can map the users with the time zones.
In backend the tblUsers will look like below.
We can see that in this table we will be specifying a user against a time zone.
We will need a method in the application for converting the dates based on time zone for saving as well as displaying purpose.
For saving to the database we will convert the date time fields to UTC from the user time zone and for displaying after bringing the dates from the database we will convert them to the user time zone.
It will be good to create a utility method like GetDesiredDateTime.
This method will expect 3 parameters:
- DateTime under action.
- Current time zone.
- Desired time zone.
GetDesiredDateTime(dateInput, CurrentTimeZone, desiredTimezone)
Like for saving a date time field which is in IST to UTC, we will use this method as: GetDesiredDateTime(“20-10-2014 19:00:00”,”IST”,”UTC”)
And this should result in “20-10-2014 13:30:00”.
Similarly GetDesiredDateTime(“20-10-2014 15:30:00”,”UTC”,”IST”) will result in “20-10-2014 21:00:00”.
The application will first retrieve the current logged in user credentials and will fetch time zone out of it. If the user is not registered or in case this is a reporting system then the user time zone can also be fetched from the browser and should be stored in some kind of session variable.
Internally this method will call a procedure and this procedure will do following steps.
While converting date time from User Time Zone to UTC:
Find out that the current time zone (input field) follows DST or not from dbo.tblTimeZone.
If Yes then check for the current year. If not then take usual lag hours and minutes.
After finding the current year, check if the current date time is lying between the DSTStartDateTime and DSTEndDateTime.
If yes then take the lag hours and minutes of the DST. If not then take usual lag hours and minutes.
Finally you will be having the lag hours and minutes. Now you just need to subtract these hour and minutes to the input date time field and this will give you the UTC date time that you can save to the data base.
While converting date time from UTC to User Time Zone:
Find the lag hours and minutes from the desired time zone in exactly the same way as above and finally just add them to the input date time field.
Instead of subtract do addition when converting from UTC to logged in user time zone.
So this is how we can handle time zone in web applications.
Again with different requirements of different applications we may have to change few logics but the core substrate remains the same.
Thanks for giving it a read.