Convert GMT/UTC to Local datetime

  • Comments posted to this topic are about the item Convert GMT/UTC to Local datetime

  • 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

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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