April 4, 2009 at 12:43 am
Hi all,
Once i face a problem:
I have remote server running with different timezone. My web application has user login feature; and will show 'last online' date-time when user logged into the application.
Now, my problem was, user was seeing the 'last online' date-time according to timezone of server, where as many of our users are from different timezones. So, i need to change that date/time some how.
So, as a solution, I have created below function. Hope this will help to others who are developing applications with multiple timezones.
-- =============================================
-- Description:Convert system date into related timezone.
/* This function will take timezone as input value and
return the date according to timezone.
IMPORTANT: It is assumed that the input timezone will be in
format as below.
GMT::5:30
GMT::-6:0
*/
-- =============================================
CREATE FUNCTION [dbo].[_GetActualDate]
(
-- Add the parameters for the function here
@TimeZone varchar(50)
)
RETURNS DateTime
AS
BEGIN
DECLARE @TimeDiff float
DECLARE @retDate DateTime
--// START: Get Actual time difference from GMT 0.00 //--
---- This will give you time difference from 0.00 hrs (eg, 5.30 for GMT::5:30)
SELECT @TimeDiff=CONVERT(float,TimeFormat,0)
FROM (
SELECT Replace(SUBSTRING(isnull(@TimeZone,'GMT::5:30'),6,Len(isnull(@TimeZone,'GMT::5:30'))-5),':','.') TimeFormat
) AS tQuery
---- Convert the time difference into differnce for caculation.
SET @TimeDiff = CONVERT(DECIMAL(2,2),@TimeDiff / 24) + 0.01
------// IMPORTANT: Here '0.01' has been added to cope-up with fractioal difference in flaot value calculation //------
--// END: Get Actual time difference from GMT 0.00 //--
--// START: Calculate the actual date according to TimeZone //--
SELECT @retDate=(GetUTCDate()+@TimeDiff)
--// END: Calculate the actual date according to TimeZone //--
RETURN @retDate
END
==========================================================
Have fun 🙂
"Don't limit your challenges, challenge your limits"
April 4, 2009 at 6:16 am
Hi kruti
I'm not sure if I tried correct, but if I execute your function I get a difference of 10 minutes:
SELECT GETDATE() here, dbo._GetActualDate('GMT::2:00') here_converted
Result
here here_converted
----------------------- -----------------------
2009-04-04 14:11:59.377 2009-04-04 14:21:35.377
Greets
Flo
April 4, 2009 at 6:40 am
It may be because in sql server 'float' value inconsistent behavior.
Try some other value in place of '0.01' in above line.
---- Convert the time difference into differnce for caculation.
SET @TimeDiff = CONVERT(DECIMAL(2,2),@TimeDiff / 24) + 0.01
------// IMPORTANT: Here '0.01' has been added to cope-up with fractioal difference in flaot value calculation //------
"Don't limit your challenges, challenge your limits"
April 4, 2009 at 6:49 am
So on my server the right value is:
SET @TimeDiff = CONVERT(DECIMAL(2,2),@TimeDiff / 24) + 0.003333334
Seems to be funny for deployment 😀
Greets
Flo
April 5, 2009 at 1:59 am
Hey all,
Unless I am missing something fundamental, the timezone offset from GMT of the local server is given by:
SELECT DATEDIFF(HOUR, GetUTCdate(), GetDate());
and the time in the timezone with GMT time difference @offset_hours is:
SELECT DATEADD(HOUR, @offset_hours, GetUTCdate());
:hehe:
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2009 at 4:33 am
Good morning Paul!
I would also handle this different. Just alike your second approach to provide the client UTC offset to the function as DECIMAL.
I'm sure it's just a typo in your sample, but for time zone calculations you should use minutes instead of hours because there are also time-zones with 1/2 hour or even 1/4 hour differneces.
Also a pedant 😀
Flo
April 5, 2009 at 4:38 am
Hey Flo!
Talking of time zones, it's 10:36pm Sunday night here 😉
So it's more "good night than good morning".
The pedant comment made me laugh!
I had no idea there were time zones that weren't whole hours!
I learn something new every day...
Paul 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2009 at 4:45 am
Oops, sorry! Thought you're from America like most people here
It's also not really morning here (currently it's 12:42 pm in Germany). May ask where are you from?
I'm out for now, meet some friends.
Good night 😛
Flo
April 5, 2009 at 4:51 am
April 5, 2009 at 11:15 pm
Hey!!!
i have checked it on other servers too.. it is heavily dependent on float value.. yeah.. i admit here that it is not fully working and easily implementable.. :ermm:
Actually, i had only two servers.. and i had to do nominal changes in added fractional value (ie '0.01') and got closely near result..
if anybody hv any idea to correct it, it will be appreciated... 🙂
"Don't limit your challenges, challenge your limits"
April 6, 2009 at 12:14 am
Hi kruti
As written in my previous post, what about a decimal parameter instead of varchar? You eliminate the problems with the float and it is much faster because you have no string formatting.
Greets
Flo
April 6, 2009 at 12:49 am
Hi Flo,
According to your suggestion, if replace input value @TimeZone varchar(50) with @TimeDiff Decimal(2,2) then i can omit the string routine; but the below statement is still there.
SET @TimeDiff = CONVERT(DECIMAL(2,2),@TimeDiff / 24) + 0.01
or
SET @TimeDiff = (@TimeDiff / 24) + 0.01
which persist the problem... 🙁
"Don't limit your challenges, challenge your limits"
April 6, 2009 at 1:19 am
Hi kruti
To solve you problem, replace the FLOAT with a DECIMAL and handle minutes instead of days. This should work:
--DROP FUNCTION [_GetActualDate]
CREATE FUNCTION [dbo].[_GetActualDate]
(
-- Add the parameters for the function here
@TimeZone varchar(50)
)
RETURNS DATETIME
AS
--DECLARE @TimeZone varchar(50)
--SET @TimeZone = 'GMT::2:00'
BEGIN
DECLARE @TimeDiff DECIMAL(5, 2)
DECLARE @retDate DATETIME
-- Handle NULL parameter
SET @TimeZone = ISNULL(@TimeZone, 'GMT::5:30')
--// START: Get Actual time difference from GMT 0.00 //--
---- This will give you time difference from 0.00 hrs (eg, 5.30 for GMT::5:30)
SELECT @TimeDiff = CONVERT(DECIMAL(5,2), TimeFormat, 0)
FROM (SELECT REPLACE(SUBSTRING(@TimeZone,
6,
LEN(@TimeZone) - 5),
':',
'.'
) TimeFormat
) AS tQuery
---- Convert the time difference into differnce for caculation.
SET @TimeDiff = @TimeDiff * 60
------// IMPORTANT: Here '0.01' has been added to cope-up with fractioal difference in flaot value calculation //------
--// END: Get Actual time difference from GMT 0.00 //--
--// START: Calculate the actual date according to TimeZone //--
SELECT @retDate = DATEADD(MINUTE, @TimeDiff, GETUTCDATE())
--// END: Calculate the actual date according to TimeZone //--
RETURN @retDate
END
Greets
Flo
April 6, 2009 at 1:22 am
Hey all,
The DECIMAL needs to be (4,2) because the precision (4) includes the scale (2):
Apologies to flo who posted at the same time - but I'd already written this so I have to post it! 😀
Paul
GO
IF OBJECT_ID(N'[dbo].[GetClockTime]', N'FN') IS NOT NULL DROP FUNCTION [dbo].[GetClockTime];
GO
CREATE FUNCTION [dbo].[GetClockTime]
(
@OffsetHours DEC(4,2)
)
RETURNS DATETIME
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN DATEADD(MINUTE, 60 * @OffsetHours, GetUTCdate());
END
GO
PRINT dbo.GetClockTime(11.5)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 1:38 am
Hi Flo/Paul,
Thanks a lot for helping me..
here is the output of both functions.
GetDate(): 2009-04-06 13:06:29.197
Solution by Flo: 2009-04-06 12:54:29.193
Solution by Paul: 2009-04-06 12:54:29.193
"Don't limit your challenges, challenge your limits"
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply