sql time zone conversions

  • hi folks

    i need to convert a date/time field between two time zones

    the date/time stamp is in GMT Standard Time +01:00 which is correct

    but i want to convert it to UTC +00:00 - as the app we are using wants to manage the time series data and zones

    so 2023-04-05 23:59:43.410 GMT should be converted to 2023-04-05 22:59:43.410 UTC

    been playing around with the "AT Time Zone" function but that does not do as i require

    i could do an offset, but then ill need to keep adjusting that based on our current time zone

     

    any ideas for me?

     

    mal

  • Given that you know the from and to time zone (i.e., you know the offset difference), but your values are not datetimeoffset, why don't you just use dateadd()?

    AT TIME ZONE: When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone.

     

     

  • Can you provide examples of the data you have - and the expected value after converting?  My guess is that you have string data that you are converting to a datetime, then trying to apply AT TIME ZONE.

    Or - you do have a datetimeoffset but you haven't defined the time zone for the source column.  In that situation you need to stack AT TIME ZONE to get to the correct value.

    Declare @fromDateTime datetime = '2023-04-05 23:59:43.410';
    Select FromDate = @fromDateTime AT TIME ZONE 'GMT Standard Time'
    , UTCDate = @fromDateTime AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC';

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • hey ya, started working on the obvious - what ratbak said, just haven't had time to finish

    so basing it on the logic that

    SELECT substring(current_utc_offset,2,2) FROM sys.time_zone_info WHERE NAME = 'GMT Standard Time'

    will update to +00:00 when we get to end of summer time rather than current "GMT Standard Time +01:00"

     

    i think ill just need a simple function to do the time zone addition for me, so that it can be used in the data view

     

    below, _Timestamp is the GMT field, T2 is what i plan to be the UTC field

    declare @t2 as int
    set @t2 = (SELECT substring(current_utc_offset,2,2) FROM sys.time_zone_info WHERE NAME = 'GMT Standard Time')


    SELECT [id]
    ,[_NAME]
    ,[_NUMERICID]
    ,[_VALUE]
    ,[_TIMESTAMP]
    ,dateadd(hour,-@t2,[_TIMESTAMP])as t2
    ,[_QUALITY]
    , ROW_NUMBER() over (partition by _name,convert(varchar,[_TIMESTAMP],111) order by _timestamp desc) as r
    FROM [dbo].[ROBOT]
  • hi Jeff

    the data is datetime as below, data sample attached

    we have lots of these views for various sensors, so i just want to ensure i can write this view that does not need to be updated every time zone change! Screenshot 2023-05-11 084059

     

    Attachments:
    You must be logged in to view attached files.
  • now i am confused - the query i have seems to have the correct offset using

    [_TIMESTAMP] at time zone 'GMT Standard Time' AT TIME ZONE 'UTC' as _Timestamp_1

    data

    7615 UNI.S7.ROBOT.EFFICIENCY_DAY 0 55.033935546875

    2023-04-05 23:59:43.410

    2023-04-05 22:59:43.410 +00:00

    2023/04/05

    192

    1

    have i just been going crazy - i am 90% certain this wasn't working before

  • Couldn't say for sure - but I have seen issues where it doesn't seem to work when you have the time zones switched.  And it definitely appears to not work when you have only one AT TIME ZONE for a datetime/datetime2 data type.

    Also - if you then cast/convert back to a datetime/datetime2 data type you lose the offset so it is hard to tell what the actual value should be.  You can also get things messed up if you convert your datetime/datetime2 value to a datetimeoffset and then use AT TIME ZONE.  If you do that - the datetime/datetime2 value is assumed to be UTC so what you get is '2023-04-05 23:59:43.410 +00:00' and then AT TIME ZONE 'GMT Standard Time' converts that to '2023-04-06 00:59:43.410 +01:00' which is, of course - incorrect.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • _TIMESTAMP is a datetime datatype, so SQL has no way to determine the timezone.

    This is why you nee a double "AT TIME ZONE" to do the correct conversion.

    There is no need to try and manually calculate the offset

    SELECT  id
    , _NAME
    , _NUMERICID
    , _VALUE
    , _TIMESTAMP -- This is your time at GMT Standard Time (+01:00)
    , t2 = _TIMESTAMP AT TIME ZONE 'GMT Standard Time' -- Here you are telling SQL that the value provided is in "GMT Standard Time"
    AT TIME ZONE 'UTC' -- Here you are telling SQL to please convert the value provided to "UTC"
    , _QUALITY
    , r = ROW_NUMBER() OVER ( PARTITION BY _name, CONVERT(date, _TIMESTAMP) -- Rather partition by date, than by varchar
    ORDER BY _timestamp DESC )
    FROM dbo.ROBOT;

    • This reply was modified 1 year, 2 months ago by  DesNorton.
  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply