Date Time Values and Time Zones

,

Introduction

DateTime always gives headaches to the database developers,

because of their various combinations. Here is another problem of Datetime. I have

given a solution and it is open to discussion. It will be highly appreciated

if you can share your ideas to this and your solutions.

What is the Use

Problem arises when your servers are spread across multiple

time zones and you are asked to combine all the data in to a main server.

Let's take a Hotel System for an example.

Assume a company in Hawaii which owned two hotel's in

Nairobi, Kenya   and another one in Kuala Lumpur ,Malaysia. In each

location separate SQL Servers are running. All the data need to be transferred

to the Main System which is running in Hawaii. Certainly there will be a problem in

Datetime if you are saving the data with their respective times.

So we need a solution to identify the actual time. One way of doing

this is, keeping the Location with each record. Then we know that we can get the  actual

time. But as you can imagine it will be a tedious task. What if we can keep

a common time for all of them ? We can keep all the Datetime in Universal Time

Coordinate, better known as Greenwich Mean Time.

As far as end users are concerned, it will be difficult them

to deal with GMT as they are already use to their own system time. So

the option would be to display the system time and convert them to GMT when

storing to database as well as convert when reading from the database.

How can we use SQL SERVER?

 GetUTCDate()  is a new function which added

to the function family of in SQLServer 2000.This function returns the datetime

value representing the current UTC time. The current time is derived from the

current local time and the time zone setting in the operating system of the

computer on which SQL Sever is running. It must be noted that it is not

returning where your Query. Most of the users think it is returning the GMT

of the PC where you run the Query.

If you are saving current time (GetDate()) now you have to

save the current GMT Time(GetUTCDate())

If you are saving user defined time like reservation time of

guests, then you must convert this time to GMT. Following Stored Procedure

will convert the current time to GMT.

/* Purpose : Convert DateTime to GMT Time Format
Author  : PPG Dinesh Asanka
Create Date : 2003-08-23
 Version  Date      Modification 
  */
Create PROCEDURE [dbo].[Convert_DateTime_to_GMT]
@dt_Date_Time as datetime
AS
select DATEADD ( hh ,  (DATEDIFF ( hh , GetDate(), GetUTCDate() )) , @dt_Date_Time )
GO

Here is an example to use this

function.

Select @dt = cast(‘2003/10/12 13:12’ as datetime)
Exec convert_GMT_to_DateTime @dt

Now the reading of datetime

field. It will be a another simple function like above.

/* Purpose : Convert GMT Time Format to System DateTime
Author  : PPG Dinesh Asanka
Create Date : 2003-08-23
 Version  Date      Modification 
*/CREATE PROCEDURE [dbo].[Convert_GMT_to_DateTime]
@dt_GMT as datetime
AS
select DATEADD ( hh ,  (DATEDIFF ( hh , GetUTCDate(),GetDate() )) , @dt_GMT )
GO

I don't think you would need an

example for this as it will be same as above.

Comparison with Oracle

I have a habit (Not sure

whether it is good or bad) of comparing SQL Server with Oracle once I found any

new feature in SQL Server. There are many functions in Oracle with

relation to the Time Zones. DBTIMEZONE is function equalent of Oracle 9i to the

SQL Server GetUTCDate(). SYS_EXTRACT_UTC returns the UTC time for a given time. TZ_OFFSET returns the offset from UTC for a

given time Zone name. There is a fantastic function in Oracle, called NEW_TIME, which

takes three arguments, which converts datetime of one zone to another and more

importantly there are 18 defined time zones. Some of them are Atlantic Standard

Time, Eastern Standard Time, Pacific Standard Time, Yukon Standard Time and many

others! Therefore we don't have to convert system time to GMT.

Conclusion

Next version of SQL Server has to cater

more functions for the Time Zone. Then SQL Server will be more user friendly as

far as end users and DBAs are concerned and will make DBA's job much easier.

Rate

2.5 (2)

Share

Share

Rate

2.5 (2)