SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ritesh Poojara / Convert GMT/UTC to Local datetime / Latest Posts

RE: Convert GMT/UTC to Local datetime

Ritesh Poojara — Mon, 11 May 2009 00:02:54 GMT

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 00Anyway 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.

RE: Convert GMT/UTC to Local datetime

john.arnott — Sun, 10 May 2009 19:59:40 GMT

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.

RE: Convert GMT/UTC to Local datetime

Ritesh Poojara — Sun, 10 May 2009 03:19:50 GMT

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[code]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)[/code][code]select * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.994'union allselect * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.995'[/code]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.

RE: Convert GMT/UTC to Local datetime

john.arnott — Sun, 10 May 2009 01:06:27 GMT

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:[code]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 -25200000Ritesh/Denver 2009-03-08 09:00:00.000 2009-11-01 08:00:00.000 -21600000Ritesh/Denver 2009-11-01 08:00:00.000 2010-03-14 09:00:00.000 -21600000[/code]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 [b]both[/b] 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.

RE: Convert GMT/UTC to Local datetime

Ritesh Poojara — Sat, 09 May 2009 20:47:59 GMT

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[code]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)[/code][code]select dbo.get_local_datetime('2009-03-08 08:59:59.555','Ritesh/Denver')union allselect dbo.get_local_datetime('2009-03-08 08:59:59.999','Ritesh/Denver')union allselect dbo.get_local_datetime('2009-03-08 08:59:59.998','Ritesh/Denver')[/code]Output2009-03-08 01:59:59.5572009-03-08 03:00:00.0002009-03-08 01:59:59.997Note the milli difference when you ask for 555 it returns 557 and 997 when you ask for 998However this should take care of the problem pointed out above. This made me to leave the millis to the application layer.

RE: Convert GMT/UTC to Local datetime

john.arnott — Sat, 09 May 2009 13:09:09 GMT

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.[code]select dbo.get_local_datetime('2009-03-08 08:59:59.000','America/Denver')union allselect dbo.get_local_datetime('2009-03-08 08:59:59.551','America/Denver')union allselect dbo.get_local_datetime('2009-03-08 09:00:00.000','America/Denver')[/code]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:[code]2009-03-08 01:59:59.0002009-03-08 08:59:59.5502009-03-08 03:00:00.000[/code]I inserted two rows in the table "timezone_offsets" with a new time_zone_id of "America/JohnDenver":[code]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)[/code]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 :[code]select dbo.john_get_local_datetime('2009-03-08 08:59:59.000','America/JohnDenver')union allselect dbo.john_get_local_datetime('2009-03-08 08:59:59.551','America/JohnDenver')union allselect dbo.john_get_local_datetime('2009-03-08 09:00:00.000','America/JohnDenver')union allselect dbo.john_get_local_datetime('2009-03-08 08:59:59.998','America/JohnDenver')[/code]The results are, I believe, more accurate than those provided by the original function:[code]2009-03-08 01:59:59.0002009-03-08 01:59:59.5502009-03-08 03:00:00.0002009-03-08 01:59:59.997[/code]

RE: Convert GMT/UTC to Local datetime

Ritesh Poojara — Sat, 09 May 2009 05:29:31 GMT

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 [code]select dbo.get_local_datetime('2009-03-08 08:59:59.000','America/Denver')union allselect dbo.get_local_datetime('2009-03-08 08:59:59.998','America/Denver')union allselect dbo.get_local_datetime('2009-03-08 09:00:00.000','America/Denver')[/code] Output is 2009-03-08 01:59:59.000[b]2009-03-08 08:59:59.997[/b]2009-03-08 03:00:00.000see something weird on the second line.Now try John's altered function from above[code]CREATE FUNCTION dbo.Get_local_datetime2 (@date DATETIME, @time_zone VARCHAR(25))RETURNS DATETIMEAS 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[/code]Run the same test on get_local_datetime2[code]select dbo.get_local_datetime2('2009-03-08 08:59:59.000','America/Denver')union allselect dbo.get_local_datetime2('2009-03-08 08:59:59.998','America/Denver')union allselect dbo.get_local_datetime2('2009-03-08 09:00:00.000','America/Denver')[/code]Output is [b]2009-03-08 08:59:59.0002009-03-08 08:59:59.997[/b]2009-03-08 03:00:00.000This time it's totally off on line 1 and 2The problem seems to be with AddDate check the output of following[code]select dateadd(ms, -21600000, '2009-03-08 08:59:59.000')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.990')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.991')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.992')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.993')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.994')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.995')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.996')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.997')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.998')union allselect dateadd(ms, -21600000, '2009-03-08 08:59:59.999')[/code]The output is2009-03-08 02:59:59.0002009-03-08 02:59:59.9902009-03-08 02:59:59.990[b]2009-03-08 02:59:59.9932009-03-08 02:59:59.9932009-03-08 02:59:59.9932009-03-08 02:59:59.9972009-03-08 02:59:59.9972009-03-08 02:59:59.9972009-03-08 02:59:59.997[/b]2009-03-08 03:00:00.000Undoubtedly there is always a chance for improvements. And thanks much for you suggestions.

RE: Convert GMT/UTC to Local datetime

john.arnott — Fri, 08 May 2009 10:26:52 GMT

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:[code]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)[/code]What will the function return when input with this data?[code]get_local_datetime('2009-03-08 08:59:59.500','America/Denver')[/code]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. [code]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)[/code]...and the function altered to use that end time:[code]create function dbo.get_local_datetime (@date datetime, @time_zone varchar(25))returns datetime asBEGIN 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_timeEND[/code]

Convert GMT/UTC to Local datetime

Ritesh Poojara — Sun, 05 Apr 2009 22:54:08 GMT

Comments posted to this topic are about the item [B]Convert GMT/UTC to Local datetime[/B]