April 5, 2009 at 10:54 pm
Comments posted to this topic are about the item Convert GMT/UTC to Local datetime
May 8, 2009 at 10:26 am
It can be dangerous to use BETWEEN with time values. Does the function given in this article work for times within the last second of an intended range? I don't think so. For example, consider this snippet of table data:INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Chihuahua','2009-04-05 09:00:00','2009-10-25 07:59:59',-21600000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Denver','2009-01-01 00:00:00','2009-03-08 08:59:59',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Denver','2009-03-08 09:00:00','2009-11-01 07:59:59',-21600000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Edmonton','2009-01-01 00:00:00','2009-03-08 08:59:59',-25200000)What will the function return when input with this data?get_local_datetime('2009-03-08 08:59:59.500','America/Denver')
A solution to this would NOT be to expand the definition of the ranges to the millisecond level. Due to rounding rules (discussed many times in various posts on SSC), BETWEEN just isn't the way to go. The end of the range is better defined as times before the start of the next range.
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Denver','2009-01-01 00:00:00','2009-03-08 09:00:00',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/Denver','2009-03-08 09:00:00','2009-11-01 08:00:00',-21600000)
...and the function altered to use that end time:
create function dbo.get_local_datetime
(@date datetime, @time_zone varchar(25))
returns datetime as
BEGIN
declare @local_time datetime
declare @offset_time int
select @offset_time = offset
from timezone_offsets
where @date >= start_time_gmt
and end_time_gmt > @date
and time_zone_id = @time_zone
set @local_time = dateadd(ms, isnull(@offset_time,0), @date)
return @local_time
END
May 9, 2009 at 5:29 am
DateAdd function in MS SQL behaves a little different then what we might want it to. The probability of user getting wrong output because of the milli seconds would be quite less as most of the time shifts occur when the applications are least used. I would suggest the application layer take care of milliseconds rounding.
Consider this
select dbo.get_local_datetime('2009-03-08 08:59:59.000','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 08:59:59.998','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 09:00:00.000','America/Denver')
Output is
2009-03-08 01:59:59.000
2009-03-08 08:59:59.997
2009-03-08 03:00:00.000
see something weird on the second line.
Now try John's altered function from above
CREATE FUNCTION dbo.Get_local_datetime2
(@date DATETIME,
@time_zone VARCHAR(25))
RETURNS DATETIME
AS
BEGIN
DECLARE @local_time DATETIME
DECLARE @offset_time INT
SELECT @offset_time = offset
FROM timezone_offsets
WHERE @date >= start_time_gmt
AND end_time_gmt > @date
AND time_zone_id = @time_zone
SET @local_time = Dateadd(ms,Isnull(@offset_time,0),@date)
RETURN @local_time
END
Run the same test on get_local_datetime2
select dbo.get_local_datetime2('2009-03-08 08:59:59.000','America/Denver')
union all
select dbo.get_local_datetime2('2009-03-08 08:59:59.998','America/Denver')
union all
select dbo.get_local_datetime2('2009-03-08 09:00:00.000','America/Denver')
Output is
2009-03-08 08:59:59.000
2009-03-08 08:59:59.997
2009-03-08 03:00:00.000
This time it's totally off on line 1 and 2
The problem seems to be with AddDate check the output of following
select dateadd(ms, -21600000, '2009-03-08 08:59:59.000')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.990')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.991')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.992')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.993')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.994')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.995')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.996')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.997')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.998')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.999')
The output is
2009-03-08 02:59:59.000
2009-03-08 02:59:59.990
2009-03-08 02:59:59.990
2009-03-08 02:59:59.993
2009-03-08 02:59:59.993
2009-03-08 02:59:59.993
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 03:00:00.000
Undoubtedly there is always a chance for improvements. And thanks much for you suggestions.
May 9, 2009 at 1:09 pm
It looks as though your test of my suggested modification to the function did not include changing the data table. Please notice that the end time in each row would also be changed to match the start time of the next range.
Let's run your original code with the original look-up table, and this time pick a fraction of a second that's not going to round up to the next second.
select dbo.get_local_datetime('2009-03-08 08:59:59.000','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 08:59:59.551','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 09:00:00.000','America/Denver')
This returns a completely wrong value in the second case, just echoing the input bcause the to-the-second value is not found in any of the defined ranges:
2009-03-08 01:59:59.000
2009-03-08 08:59:59.550
2009-03-08 03:00:00.000
I inserted two rows in the table "timezone_offsets" with a new time_zone_id of "America/JohnDenver":INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/JohnDenver','2009-01-01 00:00:00','2009-03-08 09:00:00',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('America/JohnDenver','2009-03-08 09:00:00','2009-11-01 08:00:00',-21600000)
Then ran my altered version of the function with the same three datetime values as above, and also the .998 seconds value you'd proposed :select dbo.john_get_local_datetime('2009-03-08 08:59:59.000','America/JohnDenver')
union all
select dbo.john_get_local_datetime('2009-03-08 08:59:59.551','America/JohnDenver')
union all
select dbo.john_get_local_datetime('2009-03-08 09:00:00.000','America/JohnDenver')
union all
select dbo.john_get_local_datetime('2009-03-08 08:59:59.998','America/JohnDenver')
The results are, I believe, more accurate than those provided by the original function:
2009-03-08 01:59:59.000
2009-03-08 01:59:59.550
2009-03-08 03:00:00.000
2009-03-08 01:59:59.997
May 9, 2009 at 8:48 pm
With the rows inserted I found even the orignal function with BETWEEN working same. Inserting the rows in the lookup table can give you desired results but I think we can handle it in a better way instead. Check this out.
we need a small change in lookup table
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.999',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2009-03-08 09:00:00.000','2009-11-01 07:59:59.999',-21600000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2009-11-01 08:00:00.000','2010-03-14 08:59:59.999',-21600000)
select dbo.get_local_datetime('2009-03-08 08:59:59.555','Ritesh/Denver')
union all
select dbo.get_local_datetime('2009-03-08 08:59:59.999','Ritesh/Denver')
union all
select dbo.get_local_datetime('2009-03-08 08:59:59.998','Ritesh/Denver')
Output
2009-03-08 01:59:59.557
2009-03-08 03:00:00.000
2009-03-08 01:59:59.997
Note the milli difference when you ask for 555 it returns 557 and 997 when you ask for 998
However this should take care of the problem pointed out above. This made me to leave the millis to the application layer.
May 10, 2009 at 1:06 am
On the plus side, that tweak certainly seems to work. But I'd like to know how we can be certain it will always work. The input datetime is rounded from 08:59:59.999 to 09:00:00.000, The lookup table values are also rounded (when inserted!!) as shown with a simple select:
select * from timezone_offsets where time_zone_id like 'Ritesh/%'
-- returns this:
time_zone_id start_time_gmt end_time_gmt offset
----
Ritesh/Denver 2008-11-02 08:00:00.000 2009-03-08 09:00:00.000 -25200000
Ritesh/Denver 2009-03-08 09:00:00.000 2009-11-01 08:00:00.000 -21600000
Ritesh/Denver 2009-11-01 08:00:00.000 2010-03-14 09:00:00.000 -21600000
So why does the function use the later time range row rather than the earlier? The value '2009-03-08 09:00:00.000' (whether from ms rounding or explicit) matches both the first and second offset entry, so we are in this case depending on the order of retrieval to be determined by the clustered index so that the last value found is the later range. This may work, but it is not considered good practice to depend on the order of data retrieval by any means other than an "order by" clause.
Rather than code "order by" into the function or depend on the clustered index for order, I think I still prefer to simply eliminate the ambiguity caused by the BETWEEN operator as I did in my version of the function. If one decides that depending on the clustered index makes sense from an expediency point of view, clear comments would be pretty much necessary in the code and documentation explaining what's going on.
May 10, 2009 at 3:19 am
Agreed there are questions which only MS SQL can answer. That is the reason I left the millis to Application level.
The max we can do to make it most nearest to perfect is set the millis to 997. Here is what happens
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.993',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.994',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.995',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.996',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.997',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.998',-25200000)
INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)
VALUES('Ritesh/Denver','2009-11-01 08:00:00.000','2010-03-14 08:59:59.999',-21600000)
select * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.994'
union all
select * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.995'
While clustered index and millis set to 997 take care of the problem and possibility of someone hitting those 2 milli 998 and 999 and getting wrong output is like Lightning strikes twice at the same place. I would rather ignore the millis sent to the function and just return output as per floor second passed by in this case 59th second.
If the issue makes much of a concern to application I would recommend to handle this slightest of possibility at application layer.
May 10, 2009 at 7:59 pm
In MS SQL 2005, the likelihood of hitting millisecond values of 998 or 999 is nil. Zero. It cannot happen. So, yes you could maybe guarantee proper behavior by specifying .997, but you intuitively don't want to do that -- it just smacks of a thrown-together design. So you want to stick to the 59.000 values because they look cleaner even if it leaves a one-second gap.
I would postulate that a one-second gap is enormous in a transaction processing environment. If an application is handling even a modest number like 5000 transactions an hour, you will most likely have some time-stamped in that one-second window. Why deliberately build in a logic error? What's the objection to my suggested approach specifying a "not greater than" limit for the end time? It lets you enter a very clean (to the minute) value and is absolutely accurate with no fudging about lightening strike values or compatibility with future versions of MS-SQL that handle datetime values to a much finer level of granularity.
This has been a stimulating discussion. I thank you for your clear and thorougn exposition of the problem and your spirited participation in our dialog.
May 11, 2009 at 12:02 am
Agreed that there are much chances of hitting the 1 second window. What I meant was to round of this one second to a perfect second 59 or 00
Anyway adding 997 seems to be much more cleaner and sure way to get over this possible problem in case of huge transactions I would make a change to script and add 997 to the end time.
Thank you, It was good to discuss this over with you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply