In the last post, I showed you how to set timezone in Windows Azure. In this post, I would like to show how to manage timezone in SQL Azure.

Current Limitation

At the time this post is written, unfortunately there’s no to change SQL Azure timezone.

http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-faq.aspx

Selecting Default UTC Timezone VS Local Timezone

Likewise in Windows Azure VM, SQL Azure use UTC as default timezone, regardless which datacenter you choose. Meaning that when you call getdate() function, it’s going to return you the current date and time according to the server’s timezone.

1. Use Default UTC TImezone

If you are building a new system with new created database without any existing data, I would consider UTC default timezone is fine.You can store the time as UTC inside your database. Optionally, if you want to ensure your local timezone to be displayed in UI, you can actually control it at the presentation layer.

2. Use Local Timezone

However, imagine you have existing application with bunch of data (including date time column) using your own local timezone, it will definitely messed up when you move to SQL Azure UTC timezone. The data inconsistency will definitely occur. Then you’ll need to determine either:

a. to convert your current data to UTC-timezone compliance OR

b. change your any SQL Objects (including functions, stored procedure, etc.) to match your local date time.

It’s a Tricks / Workaround, Not Actually a Solution

I’ve tried to look for various solution on the internet, mailing list, or even talk with Microsoft MVPs and Product Group folks but still there’s no single answer that could really satisfy me. Accordingly, I would call the following technique as a trick or workaround, instead of saying a solution.

Back to the second point discussed at above, converting your current data to UTC-timezone maybe very painful especially you have a lot of date and time data inside your table. In my personal opinion, I would prefer changing SQL Objects. But one very important consideration is to make it flexible and elegant enough, especially when in the future, we would need to convert back to the original state.

Well, you could definitely Find and Replace any objects inside SQL schema from getdate() to select dateadd(hh,8,getdate()). But of course it’s not going to be a nice way. As such, here’s what I’ve done to make it better.

1. Create a User-Defined Function: GetLocalDate()

CREATE FUNCTION [dbo].[GetLocalDate]

(

    @TimezoneDiffInHour TINYINT = 8

    -- default set to 8 (GMT +8 = Singapore Timezone) 

)

RETURNS DATETIME

AS

BEGIN

    RETURN DATEADD(Hh, @TimezoneDiffInHour , GETUTCDATE())

END

To call this function, you can either use:

SELECT dbo.GetLocalDate(DEFAULT)

-- OR

SELECT dbo.GetLocalDate(8)

-- 8 denotes Singapore Timezone

2. Script Out The Entire Database Schema

You would need to find and replace entire SQL objects to identify which of the object that use getdate() function. Before doing so, you’ll of course need to generate the entire database schema. You can do so using the generate script wizard in SQL Server Management Studio.

3. Find and Replace getdate() with dbo.GetLocalDate(DEFAULT)

The next step is to find which the database object that use getdate() function. Change it to dbo.GetLocalDate(DEFAULT). Execute it by altering the existing object.

By doing so, I can easily switch to UTC timezone system, just by only alter my GetLocaDate() function with Default value 0 on @TImezoneDiffInHour parameter. I don’t have to regenerate and convert back my objects again.

Frankly speaking, it may not the the best solution, but it just serves as workaround and fits my need. I won’t be surprise if there’s a better solution in the future, hopefully we can natively change the timezone in SQL Azure.

Introduction

I strongly believe that in most application we are building, we would need to store date and time. In some application, date and time play very important role, for instance: rental-similar application which could result big impact if date and time are incorrect. In other kinds of application, date and time may not be playing very significant role, for example: some of them is just as informational purpose, when the data is actually inserted in the system.

Regardless the importance level we discussed above, one essential factor that we need to be considered is the timezone of the system. Whether setting it as local country timezone or other timezone such as UTC / GMT, we would need to determine the it, typically on the server where our application is hosted.

Thus, when we type “DateTime.Now” inside your code, we should be able to get the correct result.

Timezone in Windows Azure

No matter which data center we selected in Windows Azure (remember, Windows Azure has 6 data centers world wide: 2 in America, 2 in Europe, and 2 in Asia), by default, Windows Azure VM would provide us UTC timezone.

If you are considering migrating your app to Windows Azure, you should ask yourself now what timezone your current application set. If it’s on UTC, you are safe, nothing to worry.

However, if you are running local time, (for example in Singapore, it’s UTC + 8 hour) and you want to ensure the consistency of your current data, then you will need to be cautious. You have “at least” 2 choice to go:

1. Use UTC on your app, which mean you would need to convert your current date/time data to UTC.
2. To set your preferred timezone in Windows Azure (it could be your local time).

I bet most of you will decline the first options .

How to set timezone in Windows Azure

Alright, I assume we go with option 2. To set timezone in Windows Azure, I believe there are actually a few ways via Powershell or by modifying registry. Honestly, I’ve not tried these option on Windows Azure, yet I am not pretty sure if it could be applied in Windows Azure. But there’s an option that is definitely working well.

Okay, there’s actually a command utility called “tzutil” that can be used to change timezone. Please take note that this command is only applicable in Windows 7 and Windows Server 2008 R2.

TZUTIL

You may try to run it using your command prompt by typing “tzutil /?” for the information.

To change to your preferred timzeon, simply run the following command.

tzutil /s "Singapore Standard Time" 

Run it as Start-up task

In Windows Azure, we would need to run this command as start-up task, to ensure that when is starting up, the command will be executed first.

1. To do that, create a empty file (using notepad), and paste the above tzutil command inside, just save the file as settimezone.cmd inside your Windows Azure Project.

In your project, ensure that this file is included, if not you’ll need to include it manually.

2. The next step is to set the properties of this file to Copy Always. This is to ensure that the file will be included when project is packaged before deployment.

3. Subsequently, we would need to tell Windows Azure to run the start-up task. This could be achieved by adding the following start-up section inside your ServiceDefinition.csdef file.

At earlier, I mentioned that the tzutil is only available in Windows 7 and Windows Server 2008 R2. Windows 7 is definitely out of context as there’s no such OS in Windows Azure.

Windows Azure at this moment allows us to choose either Windows Server 2008 or Windows Server 2008 R2. Both of them are running on 64 bit architecture.

By default (if you are not modifying anything in your configuration file), Windows Server 2008 will be selected.

In order to use tzutil, we would need to set the VM running as Windows Server 2008 R2. To do that, simply navigate to the ServiceConfiguration.cscfg file. In the ServiceConfiguration section, change the osFamily from 1 to 2.

*1 = Win 2008, while 2 = Win 2008 R2

We would also need to set the version of the OS. If you not preferring any OS, you can just simply put * and it will automatically perform update for you when there’s patch / new version of guest OS released.

5. Verification

If everything runs well, you should be getting your preferred timezone as expected.

Here’s how it’s look like, when I performed remote desktop to my Windows Azure VM.

What’s next?

In the app level, your are safe since you’ve successfully configure the timezone of your VM. But how about database level? What if there’s any stored procedure / function inside your code, use “getdate()” function?

I’ll discussed more on this topic in the next post. Stay tune…