locked
Get time by region from UTC time RRS feed

  • Question

  • Hi,

    how can I have the local time from UTC time by region

    for exemple utc time 12:00 ==> Paris 11:00

    any idea

    thx

    Tuesday, December 2, 2014 1:37 PM

Answers

  • If you're going to have times being input from many regions, you'd be best served by adding an offset column:

    DECLARE @naTimes TABLE (id INT, value INT, datetime DATETIME, offset float)
    INSERT INTO @naTimes (id, value, datetime, offset)
    VALUES  
    (1, 10, GETUTCDATE(), -10.0), --HI
    (1, 10, GETUTCDATE(), -9.0),  --AK
    (1, 10, GETUTCDATE(), -8.0),  --PST
    (1, 10, GETUTCDATE(), -7.0),  --MST
    (1, 10, GETUTCDATE(), -6.0),  --CST
    (1, 10, GETUTCDATE(), -5.0),  --EST
    (1, 10, GETUTCDATE(), -4.0),  --AST
    (1, 10, GETUTCDATE(), -3.5),  --NST
    (1, 10, GETUTCDATE(), -3.0),  --PMST/WGT
    (1, 10, GETUTCDATE(), -1.0)   --EGT
    
    SELECT *, DATEADD(MINUTE,(offset*60.0),datetime) AS local
      FROM @naTimes

    This leaves no doubt as to what the correct offset for the timestamp is, and allows for the client application to supply only the offset, with the time provided by the server (in UTC).

    • Proposed as answer by Eric__Zhang Sunday, December 7, 2014 2:46 PM
    • Marked as answer by Charlie Liao Thursday, December 11, 2014 1:06 AM
    Tuesday, December 2, 2014 2:17 PM

All replies

  • Hello:

    You can use:

    select convert(time,(GETUTCDATE())) as UTC, convert(time,(GETDATE())) as LOCAL

    Regards

    JM Claudio Dba/Consultor SQL/BI Pasiona - Spain

    Tuesday, December 2, 2014 1:45 PM
  • That is best done by the client application.  The SQL Server only knows the timezone of the SERVER, not the client.
    Tuesday, December 2, 2014 1:55 PM
  • If you're going to have times being input from many regions, you'd be best served by adding an offset column:

    DECLARE @naTimes TABLE (id INT, value INT, datetime DATETIME, offset float)
    INSERT INTO @naTimes (id, value, datetime, offset)
    VALUES  
    (1, 10, GETUTCDATE(), -10.0), --HI
    (1, 10, GETUTCDATE(), -9.0),  --AK
    (1, 10, GETUTCDATE(), -8.0),  --PST
    (1, 10, GETUTCDATE(), -7.0),  --MST
    (1, 10, GETUTCDATE(), -6.0),  --CST
    (1, 10, GETUTCDATE(), -5.0),  --EST
    (1, 10, GETUTCDATE(), -4.0),  --AST
    (1, 10, GETUTCDATE(), -3.5),  --NST
    (1, 10, GETUTCDATE(), -3.0),  --PMST/WGT
    (1, 10, GETUTCDATE(), -1.0)   --EGT
    
    SELECT *, DATEADD(MINUTE,(offset*60.0),datetime) AS local
      FROM @naTimes

    This leaves no doubt as to what the correct offset for the timestamp is, and allows for the client application to supply only the offset, with the time provided by the server (in UTC).

    • Proposed as answer by Eric__Zhang Sunday, December 7, 2014 2:46 PM
    • Marked as answer by Charlie Liao Thursday, December 11, 2014 1:06 AM
    Tuesday, December 2, 2014 2:17 PM
  • Are these dates historical/future dates UTC stored in the database?

    Then retrieving it and converting it to local time is complex as you have to be aware of what daylight savings settings are in force in the 'local' area. This is known as the time observation, and can change within time zones and year on year.

    For example if I store '6 Jun 2012 9:35pm UTC' in the database, then in the UK the local time was '6 Jun 2012 10:35pm' as British Summer Time was in force.

    6 Jun 1943 9:35pm UTC actually was 6 Jun 1943 11:35pm, as British Double Summer Time was observed.

    Pre 1916, BST didn't even exist.

    So as you can see it's complex - and that's just the UK.

    Tuesday, December 2, 2014 2:17 PM