Managing Timezone in SQL Azure

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.

image_286799A2

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. Winking smile

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.

image_554C1509

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.

This entry was posted in Uncategorized. Bookmark the permalink.

7 Responses to Managing Timezone in SQL Azure

  1. Pingback: A Deep Look inside Windows Azure Virtual Machines | Wely's Cloud Journey...

  2. Pingback: Azure 上SQL Database(PaaS)Time Zone时区问题处理 - IT大道

  3. fallout4 says:

    Hello, after reading this amazing article i am too happy to share my familiarity here with mates.

  4. Todd says:

    Could you utilize a VM for Sql Server and set the time zone of your choice?

  5. Marlon says:

    @TimezoneDiffInHour float = -4 /**usa**/

    Declare float
    TINYINT accept only 0 – 255

  6. John says:

    For anyone coming across this in future: another option (which is a lot nicer than having to make changes to the UTC offset in this function every time daylight saving time starts or ends!) is to use a new SQL Azure feature:

    RETURN CONVERT(datetime, SYSDATETIMEOFFSET() AT TIME ZONE ‘Time Zone Name’)

    You can pass in the name of a Windows time zone (e.g. ‘GMT Standard Time’), and this will always return the correct time, taking account of daylight saving time shifts.

Leave a Reply

Your email address will not be published. Required fields are marked *

*